DDL Best Practices

This document describes best practices for creating tables in YMatrix.

0 Naming Conventions

Rule	Required
Database names, table names, column names, function names, view names, sequence names, and aliases must use lowercase letters, underscores, and digits only. The name must start with a lowercase letter, must not begin with `pg`, and must not exceed `63` characters in length	Yes
Column names must not use reserved keywords or SQL keywords such as `oid`, `xmin`, `ctid`, `select`, `insert`, `update`	Yes
Primary key index names should follow the pattern `pk_字段名`; unique index names should follow `uk_字段名`; regular index names should follow `idx_字段名`	Yes
Partitioned tables should be named using the parent table name as prefix and partitioning rule as suffix. For example: parent table `order`, child tables `order_p_202010`, `order_p_202009`	Yes
When a field represents `是否xx`, it is recommended to name it using the format `is_xxx` and use BOOLEAN type, e.g., `is_deleted`	No
It is best practice for database names to match application service names	No
Table names should be clear and self-explanatory. Avoid ambiguous abbreviations and overly long names. Follow the pattern `业务名称_表的作用`. Use a prefix for views and use `tmp` as prefix for temporary tables. Recommended: `trade_config`, `payment_task`. Avoid: `tradeconfig`, `paymenttask`	No

1 Row Storage vs Column Storage

Storage Mode	Suitable For
Row-oriented	Frequent updates or inserts Queries accessing multiple columns of a table simultaneously
Column-oriented	Tables with many columns but queries access only a few Aggregation operations on a small number of columns Frequent updates to individual columns without modifying others Recommended for most OLAP and time-series workloads

2 Storage Engines

YMatrix provides four storage engines: HEAP, AO, and MARS3. Choose the appropriate engine based on data volume and write performance requirements.

Storage Engine	Supports	Suitable For	Unique Features
HEAP	Row storage	Tables requiring frequent updates Tables using row storage
AO	Row or column storage	Tables and partitioned tables with infrequent updates after initial load, where data is inserted in batch mode	Integration with vectorized execution engine
MARS3	Hybrid row-column or column storage	Time-series scenarios OLTP scenarios with high-frequency, small-batch writes OLAP scenarios with low-frequency, large-batch writes	Integration with vectorized execution engine Encoding chain compression Automatic storage tiering

Note!
For more information about storage engines, see Storage Engine Overview.

3 Data Types

Choosing precise data types helps reduce storage space, memory usage, and improve query efficiency. Consider the following:

Minimize size: For integers or floating-point numbers, choose the smallest type that can represent the required range. For example, use int2 instead of int4 or int8 for an age column.
When using the vectorized execution engine, prefer data types that better leverage vectorization (e.g., int, float) while meeting business needs.
Extended data types such as MXKV2 deliver optimal performance only when used with the vectorized execution engine.

4 Distribution Keys

YMatrix distributes data across segments to leverage parallel processing. Consider the following when selecting a distribution key:

Uniform distribution: Distribute data evenly to avoid skew and balance segment load. Choose a column with high cardinality and uniform value distribution. Use random distribution if necessary to ensure perfect balance. Check data distribution using: SELECT gp_segment_id,count(*) FROM table_name GROUP BY gp_segment_id;.
JOIN performance: For large tables frequently joined, use the join key as the distribution key to enable local joins and avoid cross-segment data movement.
High cardinality: Prefer columns with high distinctness (i.e., large number of unique values). The higher the count(distinct 字段名) value, the better the column suits as a distribution key.
Avoid using columns commonly used in WHERE clauses as distribution keys; consider using them as partition keys instead.
Validate data distribution after initial and incremental data loads to minimize skew.
Do not use date or timestamp columns as distribution keys.

5 Partitioning

YMatrix is a distributed database. Create partitions based on data volume. Key considerations:

Set up Automated Partition Management (APM) for partitioned tables.
If object storage is available and the table uses MARS3, enable Automated Storage Tiering.
Partition size: For time-series data, partition by year or month. Avoid daily partitions. Excessive partitioning complicates maintenance and harms performance—especially for columnar storage, where too many physical files reduce query optimization benefits.
Partition pre-allocation: Do not create too many partitions in advance. Create them just before use. For monthly partitions, create the next year’s 12 partitions at year-end.
Maintenance cost: In time-series scenarios, use time-based partitioning. Back up and migrate data by partition. Clean up obsolete or empty historical partitions promptly. See Routine Vacuuming.
Only use partitioning when queries can benefit from partition elimination (pruning). Prefer range partitioning over list partitioning. Do not use the same column for both distribution and partitioning.
Minimize use of default partitions. Avoid multi-level partitioning. Prefer fewer partitions with more data per partition.

Special notes for MARS3 table partition maintenance:

Operations like ALTER require full table locks and may block concurrent queries.
Querying too many partitions at once significantly slows performance. For MARS3 tables, keep per-partition size around 5GB per segment.

Special notes for HEAP/AOCO table partition maintenance:

For HEAP tables, aim for ~5GB per partition per segment. Also monitor index sizes.
For AOCO tables, aim for ~3GB per partition per segment. Also monitor index sizes.

Note!
The above partition size recommendations apply to YMatrix 6.X.

6 Block Size

For MARS3 tables, configure the compress_threshold parameter at the table level. Increasing this value reduces I/O frequency but may lower block-level filtering efficiency.

7 Compression vs Non-compression

Compression is generally recommended, especially for column-oriented tables. It trades idle CPU resources for reduced I/O time, alleviating I/O pressure and improving query performance.

If using the MARS3 storage engine:

YMatrix recommends encoding chain compression, which reduces on-disk data size and the amount of data read during queries.
Compression introduces decompression overhead. Prioritize fast decompression algorithms during query tuning. YMatrix's proprietary algorithms (except gorilla) typically decompress faster than LZ4. Select based on data characteristics.

HEAP does not support explicit compression.

8 Indexes

Indexes accelerate queries and are stored separately. This section covers indexing specifics.

For HEAP and AO tables, supported index methods include B-tree, BRIN, Bitmap, and Hash. Specialized indexes for text, spatial, and other data types include GIN, GiST, ODIN, SP-GiST, and TOAST.

MARS3 currently supports BRIN indexes. You may optionally create one or more mars3_brin indexes to improve query performance.

9 Replicated Tables

Replicated tables store a full copy of data on every MXSegment, reducing cross-segment data motion for small tables.
Large tables should not use replication due to high storage and maintenance costs.
- Example: A detailed fact table has hundreds of millions of rows, while a user permission table has only hundreds of rows. Joining these directly could generate billions of intermediate rows. Replicating the small permission table to all segments avoids expensive redistribution.
Use the DISTRIBUTED REPLICATED parameter to define replicated tables.
Additionally, this strategy removes restrictions on user-defined functions executing across nodes.

10 Master-only Tables

Master-only tables store data exclusively on the master node (MXMaster), not distributed to compute nodes (MXSegments).

Ideal for high-throughput transactional (TP) workloads, avoiding complexity and overhead of distributed architecture. Operations on such tables do not require dispatching to segments, reducing coordination cost and improving TP performance.
Define master-only tables using the DISTRIBUTED MASTERONLY clause. See CREATE TABLE for details.
Use the mxstart -am command to start the database in master-only mode.
Usage considerations:
a. Storing data on the master consumes master resources; monitor resource allocation carefully.
b. Analytical queries (AP) cannot leverage distributed parallelism, potentially leading to reduced performance.

11 Views

If your workload involves frequent aggregate queries, consider creating a Continuous View (CV).
If your queries often involve rolling aggregations over recent data windows, consider creating a Sliding Window view.

← Previous

Example: Smarthome Best Practice

Connect from clients

English Русский 简体中文