Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Table Class Configuration Parameters

This document describes the configuration parameters related to table classes in the system.

Note!
To ensure system stability and security, manually modifying these parameters should be done with caution.


HEAP Tables / Append-Optimized (AO) Table Configuration Parameters

default_table_access_method

Sets the default table access method when the CREATE TABLE command does not explicitly specify one.

Data Type Default Value Valid Values Setting Scope
enum heap heap / ao_row / ao_column / mars2 / mars3 master; session

gp_create_table_random_default_distribution

Controls table creation when using DISTRIBUTED BY or CREATE TABLE to create a HEAP table without a CREATE TABLE AS clause.

  • For CREATE TABLE: if this parameter is set to off (the default), and the table creation command does not include a DISTRIBUTED BY clause, YMatrix selects the distribution key according to the following rules:
    • If a LIKE or INHERITS clause is specified, YMatrix copies the distribution key from the source or parent table.
    • If a PRIMARY KEY or UNIQUE constraint is specified, YMatrix selects the largest subset of key columns as the distribution key.
    • If neither constraints nor LIKE or INHERITS clauses are specified, YMatrix selects the first suitable column as the distribution key. (Columns of geometric or user-defined types are not valid for YMatrix distribution keys.)
  • If the parameter is set to on, and no DISTRIBUTED BY clause is provided, YMatrix follows these rules:
    • If no PRIMARY KEY or UNIQUE columns are specified, the table is distributed randomly (DISTRIBUTED RANDOMLY). The distribution remains random even if the command includes LIKE or INHERITS clauses.
    • If PRIMARY KEY or UNIQUE columns are specified, a DISTRIBUTED BY clause must also be included. Otherwise, the command fails.
  • For CREATE TABLE AS commands without a distribution clause:
    • If the Postgres query optimizer creates the table and the parameter is off, the distribution strategy is determined by the command.
    • If the Postgres query optimizer creates the table and the parameter is on, the distribution strategy is random.
    • If Greenplum ORCA creates the table, the distribution strategy is always random, regardless of the parameter value.
Data Type Default Value Setting Scope
boolean off master; system; reload

gp_default_storage_options

Sets default values for table storage options when using the CREATE TABLE command.

  • Supported options (specify multiple values as a comma-separated list):
    • appendoptimized. Note: You can use appendoptimized=value syntax to specify the append-optimized table storage type. appendoptimized is a simplified alias for the legacy appendonly option. YMatrix stores appendonly in the catalog and displays it when listing storage options for append-optimized tables.
    • blocksize
    • checksum
    • compresstype
    • compresslevel
    • orientation
  • You can use this parameter instead of specifying storage options in the WITH clause of CREATE TABLE. Options specified in CREATE TABLE override those set by this parameter.
  • Not all combinations of storage options are valid. An error is returned if invalid options are specified. See the CREATE TABLE command for more information.
  • Default values can be set at the database or user level. When parameters are set at different levels, the following precedence applies (from highest to lowest) when a user creates a table:
    1. Values specified in the WITH or ENCODING clause of the CREATE TABLE command
    2. Values set for the user using ALTER ROLE...SET
    3. Values set for the database using ALTER DATABASE...SET
    4. System-wide values set using the gpconfig tool
  • Parameter values are not cumulative. For example, if the database-level setting includes appendoptimized and compresstype, and the user sets orientation, the database-level options are ignored.
  • Example: This ALTER DATABASE command sets default orientation and compresstype for the mytest database: ALTER DATABASE mytest SET gp_default_storage_options = 'orientation=column, compresstype=rle_type'. Append-optimized tables created in mytest use column orientation with RLE compression. You only need to specify WITH in the appendoptimized=TRUE clause.
  • Example: This gpconfig command sets system-wide default storage options. If multiple options are set, the value must be enclosed in single quotes: gpconfig -c 'gp_default_storage_options' -v 'appendoptimized=true, orientation=column'.
  • Example: This gpconfig command displays the current parameter value. The value must be consistent across the YMatrix master and all segments: gpconfig -s 'gp_default_storage_options'.
Default Value Valid Values Setting Scope
appendoptimized=FALSE
blocksize=32768
checksum=TRUE
compresstype=none
compresslevel=0
orientation=ROW		 appendoptimized= TRUE / FALSE
blocksize= integer between 8192 and 2097152
checksum= TRUE / FALSE
compresstype= ZLIB / ZSTD / QUICKLZ2 / RLE_TYPE / NONE
compresslevel= integer between 0 and 19
orientation= ROW / COLUMN		 Set via gpconfig: master; session; reload
Set at table creation: table

gp_appendonly_compaction

Enables compaction of segment files during VACUUM operations.

  • When disabled, VACUUM only truncates segment files to the EOF value, consistent with current behavior.
  • Administrators may disable compaction under high I/O load or low disk space conditions.
Data Type Default Value Setting Scope
boolean on master; session; reload; superuser

gp_appendonly_compaction_threshold

Specifies the threshold ratio (percentage) of hidden rows to total rows in a segment file that triggers compaction during FULL (lazy VACUUM) without the VACUUM option.

  • If the ratio of hidden rows in a segment file is below this threshold, no compaction occurs and a log message is issued.
Data Type Default Value Valid Values Setting Scope
int 10 0 ~ 100 master; session; reload

gp_safefswritesize

Specifies the minimum size for safe write operations on AO tables in immature file systems.

  • When a byte count greater than 0 is specified, the append-optimized writer pads data to that size to prevent data corruption due to file system errors.
  • Each immature file system has a known safe write size, which must be specified when using YMatrix with such file systems.
  • This is typically set as a multiple of the file system's block size; for example, Linux ext3 uses 4096 bytes, so 32768 is commonly used.
Data Type Default Value Valid Values Setting Scope
int 0 0 ～ INT_MAX master; system; restart


MARS2 Table Configuration Parameters

compress_threshold

Compression threshold. Controls how many tuples are compressed at once in a single table. It defines the maximum number of tuples in a compression unit.

Data Type Default Value Valid Values Setting Scope
int 1000 1 ~ 10000 table

mars2_automerge_threshold

Controls the number of L0 runs across all MARS2 tables that triggers a merge.

  • Individual tables can override this using the table option level0_merge_threshold.
  • If set at the system level, a reload is required. If modified at the session level, it takes effect immediately.
Data Type Default Value Valid Values Setting Scope
int 32 10 ~ 2048 master; session/system; reload

level0_merge_threshold

Controls the number of L0 runs in a single table that triggers a merge.

Data Type Default Value Valid Values Setting Scope
int 32 1 ~ 2048 table

level0_upgrade_size

Controls the size (in MB) for upgrading a single table from L0 to L1. When a merge in L0 produces a run larger than this size, it is upgraded to L1.

Data Type Default Value Valid Values Setting Scope
int 25 1 ~ 10000 table

level1_upgrade_size

Controls the size (in MB) for upgrading a single table from L1 to L2. When a merge in L1 produces a run larger than this size, it is upgraded to L2.

Data Type Default Value Valid Values Setting Scope
int 1000 1 ~ 10000 table

mars2_sort_mem

Controls the sort memory size (in KB) per insert. For partitioned tables, this memory is shared among partitions.

  • Default is 2097152KB (2GB). Valid range is 128KB ～ 2147483647KB (up to 2048GB).
  • Requires reload if set at system level; takes effect immediately at session level.
Data Type Default Value Valid Values Setting Scope
int 2097152 128 ~ 2147483647 master; session/system; reload

mars2_sort_mem_core

Controls the minimum sort memory allocated per partition in a partitioned table.

  • Default is 16384KB (16MB). Valid range is 128KB ～ 2147483647KB (up to 2048GB).
  • Requires reload if set at system level; takes effect immediately at session level.
Data Type Default Value Valid Values Setting Scope
int 16384 128 ~ 2147483647 master; session/system; reload


MARS3 Table Configuration Parameters

Note!
These parameters must be configured at table creation using the WITH clause. They apply to individual tables and cannot be modified after creation.

compress_threshold

Compression threshold. Controls how many tuples are compressed at once in a single table. It defines the maximum number of tuples in a compression unit.

  • Also controls the size of a Range, which is the basic unit of compression. Can be tuned to balance compression efficiency and read performance. Too low a value reduces compression; too high increases memory usage.
Data Type Default Value Valid Values Setting Scope
int 1200 1 ~ 100000 table

level_size_amplifier

Specifies the amplification factor for level sizes.

  • Determines the threshold for triggering a merge: rowstore_size * (level_size_amplifier ^ level). A higher value results in slower reads but faster writes.
  • Choose based on workload characteristics (write-heavy vs. read-heavy, compression ratio, etc.).
  • Note: Ensure the number of runs per level does not become excessive, as this may degrade query performance or block new inserts.
Data Type Default Value Valid Values Setting Scope
int 8 1 ~ 1000 table

prefer_load_mode

Specifies the internal data loading mode for MARS3.

  • normal: Normal mode. New data is first written to row-store runs in L0. After accumulating rowstore_size tuples, it is flushed to column-store runs in L1. Compared to bulk, this incurs an extra I/O, and columnar conversion is asynchronous. Suitable for high-frequency, small-batch writes where I/O capacity is sufficient and latency is critical.
  • bluk: Bulk load mode. Suitable for low-frequency, large-batch writes. Data is written directly to column-store runs in L1. Compared to normal, this reduces I/O and makes columnar conversion synchronous. Recommended when I/O capacity is limited and latency is not a concern.
  • For more details, see MARS3 Overview.
Data Type Default Value Valid Values Setting Scope
enum normal normal / bluk table


rowstore_size

Controls when an L0 run switches to the next one. A new run is created when the data size exceeds this value (in MB).

  • Proportional to read amplification, inversely proportional to write amplification. Choose based on workload (write-heavy vs. read-heavy).
Data Type Default Value Valid Values Setting Scope
int 64 8 ~ 1024 table


MARS3 Degraded Storage Configuration Parameters

matrixts.enable_object_cache

Enables or disables file system caching for object storage.

Data Type Default Value Setting Scope
boolean off master; session; reload

mars3.degrade_credential_update_interval

Sets how often (in seconds) to refresh session access credentials.

  • To enhance object storage security, some environments require periodic credential updates. This parameter controls how frequently (in seconds) the session re-fetches authorization information from the tablespace system catalog to ensure up-to-date credentials when accessing object storage.
Data Type Default Value Valid Values Setting Scope
int 0 0 ～ INT_MAX master; system; reload

mars3.degrade_probe_interval

Sets the interval (in seconds) for background tasks to check whether data meets the conditions for offloading.

  • Requires mxstop -u restart to take effect after modification.
Data Type Default Value Valid Values Setting Scope
int 3600 1 ～ INT_MAX master; system; reload

mars3.enable_object_prefetch

Enables or disables in-memory caching for object storage.

Data Type Default Value Setting Scope
boolean off master; session; reload

mars3.enable_objectstore

Enables or disables automatic data offloading.

  • Requires mxstop -u restart to take effect after modification.
Data Type Default Value Setting Scope
boolean off master; system; reload


External Table Configuration Parameters

gp_external_enable_exec

Enables or disables external tables that execute OS commands or scripts on segment hosts (CREATE EXTERNAL TABLE EXECUTE syntax).

  • Must be enabled if using Command Center or MapReduce features.
Data Type Default Value Setting Scope
boolean on master; system; restart

gp_external_enable_filter_pushdown

Enables filter pushdown when reading from external tables.

  • If pushdown fails, the query proceeds without pushing the filter (YMatrix applies the constraint locally).
Data Type Default Value Setting Scope
boolean on master; session; reload

gp_external_max_segs

Sets the number of segments used to scan external table data, preventing system overload and resource contention with concurrent operations.

  • Applies only to external tables using the gpfdist:// protocol.
Data Type Default Value Valid Values Setting Scope
int 64 1 ～ INT_MAX master; session; reload

gp_initial_bad_row_limit

If set to n, YMatrix stops processing input rows when the first COPY rows contain format errors during n or external table import.

  • If valid rows are processed within the first n rows, processing continues.
  • Setting to 0 disables this limit.
  • Alternatively, use the COPY clause in SEGMENT REJECT LIMIT or external table definitions to limit rejected rows.
  • INT_MAX is the maximum integer value storable in the system.
Data Type Default Value Valid Values Setting Scope
int 1000 0 ~ INT_MAX master; session; reload

gp_reject_percent_threshold

For single-row error handling on COPY and external tables with SELECT, sets the number of rows processed before SEGMENT REJECT LIMIT n PERCENT begins calculating rejection rates.

Data Type Default Value Valid Values Setting Scope
int 300 0 ~ INT_MAX master; session; reload

mx_discard_gpfdist_data

Discards data packets from the gpfdist v1 protocol.

Data Type Default Value Setting Scope
boolean off master; session; reload

readable_external_table_timeout

Specifies the time (in seconds) YMatrix waits before canceling a query when data stops arriving from an external table during a SQL read operation.

  • Default 0 means no timeout; queries are not canceled.
  • If a gpfdist query runs for a long time and returns error 间歇性网络连接问题, set a value for readable_external_table_timeout. If gpfdist returns no data within the specified time, YMatrix cancels the query.
Data Type Default Value Valid Values Setting Scope
int 0 0 ～ INT_MAX master; system; reload

writable_external_table_bufsize

Buffer size (in KB) used by YMatrix for network communication, such as the gpfdist utility and external web tables (using HTTP).

  • YMatrix stores data in the buffer before writing.
  • Valid range is 32KB ～ 131072KB (up to 128MB).
Data Type Default Value Valid Values Setting Scope
int 64 32 ～ 131072 segments; session; reload

verify_gpfdists_cert

When using the gpfdists protocol with SSL security for external tables, controls whether SSL certificate authentication is enabled.

  • Default on enables SSL authentication when YMatrix communicates with the gpfdist utility for data I/O.
  • off disables SSL certificate authentication. The following SSL exceptions are ignored:
    • YMatrix does not trust the self-signed SSL certificate used by gpfdist.
    • The hostname in the SSL certificate does not match the host running gpfdist.
  • Set to off to disable authentication during testing of communication between YMatrix external tables and the gpfdist utility.
  • Note: Disabling SSL certificate verification for gpfdists exposes security risks.
Data Type Default Value Setting Scope
boolean on master; session; reload
← Previous
Automatic vacuum
Next →
Lock management