YMatrix

Version Release History
Platform Requirements

YMatrix

Quick Start

Standard Cluster Deployment

Data Modeling

Connecting to The database

Data Writing

Data Migration

Data Query

Maintenance and Monitoring

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

scenario

DDL Best Practices

This document describes best practices for creating tables using YMatrix.

0 Naming Conventions

Specification Required
Database names, table names, column names, function names, view names, sequence names, aliases, etc., must use a combination of lowercase letters, underscores, and numbers, but the first letter must be lowercase, and they must not start with pg. Object names must not exceed 63 characters in length Yes
Field (or column) names must not use reserved field names or keywords, such as oid, xmin, ctid, select, insert, update, etc. Yes
Primary key index names are pk_column_name, unique index names are uk_column_name, and regular index names are idx_column_name Yes
Partitioned tables should use the parent table name as the prefix and the child table rule characteristics as the suffix, such as parent table order, child table order_p_202010, order_p_202009 Yes
When a field means “whether xx,” it is recommended to name it using the format is_xxx and select the Boolean type, such as is_deleted No
It is best to keep the database name consistent with the application service name No
Table names should be clear and meaningful, avoiding ambiguous abbreviations or overly lengthy names. It is recommended to follow the format business name_table purpose, with views prefixed and temporary tables prefixed with tmp. Recommended: trade_config, payment_task. Avoid: tradeconfig, paymenttask. No

1 Row storage and column storage

Storage mode Applicable to
Row storage Data is frequently updated or inserted.
Need to access multiple fields of a table at the same time
column storage There are many fields, but most queries access only a few fields.
Perform aggregation operations on a small number of fields.
Individual fields in the table are updated periodically without modifying other fields.
For most OLAP business and time-series scenarios, it is recommended to prioritize column storage.

2 storage engine

YMatrix offers four storage engines: HEAP, AO and MARS3. We typically select the appropriate storage engine based on data scale and write performance requirements.

Storage Engine Supported Applicable to
HEAP Row storage Frequently updated tables
Select row-stored tables
AO Row storage/Column storage Tables and partitioned tables are not updated frequently after initial data loading, and data is only inserted in batches.
MARS3 Row-column hybrid storage/Column storage Time Series Scenario
OLTP scenarios involving high-frequency, small-batch data writes
OLAP scenarios involving low-frequency, large-volume data writes

Notes!
For more information about storage engines, see Storage Engine Overview。

3 Data Types

Precise data types are very helpful for storage space, memory usage, and query efficiency. The following considerations should be taken into account when selecting data types:

  • Minimize. For integer or floating-point types, select the smallest type that can represent the specific range. For example, for an age field, use int2 instead of int4/int8.
  • If using a vectorized execution engine, prioritize types that better leverage vectorized performance potential, such as int or float, while ensuring business requirements are met.
  • If using extended data types MXKV2, these types must be used in conjunction with a vectorized execution engine to achieve their unique performance benefits.

4 Distribution Key

YMatrix supports distributing data across different nodes to leverage distributed parallel processing capabilities and improve performance. When selecting a distribution key, the following factors should be considered:

  • Data uniformity. Evenly distributing data can effectively avoid the bottleneck effect and balance the load across different nodes. For example, a single column that can evenly distribute data across all segments can be used as the distribution key. The column used as the distribution key should have a large number of values that are evenly distributed; otherwise, data skew may occur. In special cases, random distribution can be considered to ensure absolutely uniform data distribution. The query statement for checking data distribution is as follows: SELECT gp_segment_id, count(*) FROM table_name GROUP BY gp_segment_id;.
  • JOIN convenience. For large tables requiring joins, use the join fields as the distribution key whenever possible. This significantly improves performance through local joins (Join) and avoids data movement across nodes.
  • Discrete fields. Whenever possible, choose the field with the most discrete values in the table as the distribution key. The field with the largest value for count(distinct field_name) (i.e., the number of unique values in this field) is the most suitable for use as a distribution key.
  • Avoid using columns from the WHERE clause of a query as distribution keys; instead, use them as partition keys.
  • Validate data after initial and incremental data loading to reduce data skew.
  • Do not use dates or timestamps as distribution keys.

5 Partition Settings

YMatrix is a distributed database that creates different partitions based on data volume. When configuring partitions, the following key points should be considered:

  • It is recommended to enable the Automated Partition Management Strategy for partitioned tables.
  • If you have object storage resources and the partitioned table is a MARS3 table, we recommend that you enable the automatic storage degradation feature for the table.
  • Partition size. For time series scenarios, it is generally recommended to partition by year or month, rather than by day. In practice, overly detailed partition planning is inconvenient to maintain and detrimental to performance: under column storage, an excessive number of physical files can actually hinder query optimization.
  • Partition reservation. There is no need to reserve too many partitions in advance; they can be created prior to use. For example, if partitioning is done by month, the 12 new partitions for the next year can be created at the end of each year. Partition maintenance costs. Time series scenarios often use time as the partition key, and as time passes, backups and migrations can be performed on a partition basis. Historical partitions that are confirmed to have no data or no valid data should be cleaned up in a timely manner. For details, see Routine Cleaning.
  • Use partitions only when partition elimination (partition pruning) can be achieved based on query conditions. Prioritize range partitioning over list partitioning, and avoid distributing and partitioning a table on the same column.
  • Minimize the use of default partitions and avoid multi-level partitioning. Prioritize creating fewer partitions with more data in each partition.

Notes on partition maintenance operations for MARS3 tables:

  • Performing ALTER operations on partitions requires locking the entire table, which will cause mutual blocking between queries.
  • Querying too many partitions at once will significantly slow down the speed. Therefore, for MARS3 tables, it is generally advisable to control a single partition on a single segment to around 5GB.

Notes on performing partition maintenance operations on HEAP/AOCO tables:

  • For HEAP tables, it is generally advisable to control a single partition on a segment to be around 5GB. At the same time, pay attention to controlling the index size.
  • For AOCO tables, it is generally advisable to control a single partition on a segment to be around 3GB. At the same time, pay attention to controlling the index size.

Note!
The above recommended partition sizes apply to YMatrix 6.X.

6 Block size

You can configure the compression threshold by specifying compress_threshold at the MARS3 table level. Increasing this value appropriately can reduce the number of I/O operations, but it may also lower the block filtering rate.

7 Compression and Non-Compression

Compressed storage is generally recommended, especially for column-store tables, where compressed storage is more effective. Compressed storage allows idle CPU resources to be used to replace read/write I/O time, alleviating I/O pressure and improving query efficiency.

If you use the MARS2 or MARS3 storage engine to create tables:

  • For compression types, YMatrix recommends using chain compression. It reduces the amount of data stored on disk and decreases the amount of data that needs to be read during queries.
  • Compression also incurs decompression overhead, so query optimization should prioritize algorithms with faster decompression speeds. YMatrix's proprietary algorithms, except for Gorilla, generally outperform LZ4 in decompression speed and can be selected based on data characteristics.

HEAP does not support explicit compression.

8 Indexes

Indexes are a technique used to accelerate queries. Index data is typically stored separately, so this section will discuss them separately.

For HEAP tables and AO tables, indexing methods include Btree, Brin, Bitmap, and Hash. For text and spatial data, indexing methods include Gin, Gist, Odin, Spgist, and Toin.

MARS3 currently supports Brin indexes. You can optionally create one or more mars3_brin indexes to improve query efficiency.

9 Copy table

  • The data of the replicated table is saved on each segment, which reduces the cross-partition movement and redistribution of small table data.
  • Large tables are not suitable for using replication table distribution strategies, because replication of large table data to each node comes at a cost in terms of storage and maintenance.
    • For example, a scene detailed data table has billions of data, but the user permission table only has hundreds of pieces of data. Now you need to associate the scene detailed data table to the user permission table through query statements. If the two are directly related, hundreds of billions of data will be derived. Only by copying the user permission table to each Segement can such a large consumption be avoided.
  • The copy table is set using the DISTRIBUTED REPLICATED parameter.
  • In addition to the above functions, using this policy can also remove restrictions on actions that user-defined functions can perform on nodes.

10 masteronly table

A masteronly table is a table distribution method where data is stored only on the master node and is not distributed to other compute nodes (segments).

  • Suitable for business scenarios requiring high TP performance, avoiding the complexity and performance overhead associated with distributed architectures. Since data is only stored on the master node, operations on this table do not need to be distributed to other nodes, reducing the overhead of distributed coordination and thereby improving transaction processing (TP) performance in distributed deployments.
  • A masteronly table can be defined using the DISTRIBUTED MASTERONLY clause. For detailed usage, refer to CREATE TABLE.
  • In masteronly mode, the database can be started using the mxstart -am command.
  • Notes on usage a. Using the master node to store data will consume master node resources, so pay attention to resource allocation. b. When performing analytical queries (AP), the advantages of distributed parallel computing cannot be utilized, which may result in reduced analytical efficiency.

11 views

  • If there are many cluster queries in the query scenario, we recommend that you create the [Continuous Cluster] (/doc/latest/datamodel/cv) view.
  • If the query scenario contains the continuous aggregation of data in the last period of time, we recommend that you create a [sliding window] (/doc/latest/datamodel/sliding_window) view.