PAX Storage Format

SynxDB supports the PAX (Partition Attributes Across) storage format.

PAX is a database storage format that combines the benefits of row-based storage (NSM, N-ary Storage Model) and column-based storage (DSM, Decomposition Storage Model). It is designed to improve query performance, particularly in terms of cache efficiency. In OLAP scenarios, PAX offers batch write performance similar to row-based storage and read performance like column-based storage. PAX can adapt to both cloud environments with object storage models and traditional offline physical file-based storage methods.

Compared to traditional storage formats, PAX has the following features:

  • Data updates and deletions: PAX uses a mark-and-delete approach for data updates and deletions. This effectively manages changes in physical files without immediately rewriting the entire data file.

  • Concurrency control and read-write isolation: PAX uses Multi-Version Concurrency Control (MVCC) to achieve efficient concurrency control and read-write isolation. The control granularity reaches the level of individual data files, enhancing operation safety and efficiency.

  • Index support: PAX supports B-tree indexes, which help speed up query operations. This is particularly useful for improving data retrieval speed when dealing with large amounts of data.

  • Data encoding and compression: PAX offers multiple data encoding methods (such as run-length encoding and delta encoding), type-specific encodings for time-series data (delta-of-deltas for integers and timestamps, Gorilla for floats, and bit-packing for booleans), and general compression options (such as zstd, zlib, and lz4), with various compression levels. These features help reduce storage space requirements while optimizing read performance.

  • Statistics: Data files contain detailed statistics that are used for quick filtering and query optimization, reducing unnecessary data scanning and speeding up query processing.

  • Vectorized engine: PAX also supports a vectorized engine. It aims to further enhance data processing capabilities and query performance, especially in applications like data analysis and report generation.

Applicable scenarios

The hybrid storage capability of PAX makes it suitable for complex OLAP applications that need to handle large amounts of data writes and frequent queries. Whether you are looking for a high-performance data analysis solution in a cloud infrastructure or dealing with large datasets in a traditional data center environment, PAX can provide strong support.

Usage

Create a PAX table

To create a table in PAX format, you need to set the table access method to PAX. You can do this in one of the following ways:

  • Use the USING PAX clause explicitly when creating the table, for example:

    CREATE TABLE t1(a int, b int, c text) USING PAX;
-- t1 is a PAX table and can be used like a normal heap table.

  • Set the default table access method to PAX and then create the table:

    -- Set the default table access method. From now on, newly created tables will use PAX format.
SET default_table_access_method = pax;

-- Implicitly use the default access method, which is PAX.
CREATE TABLE t1(a int, b int, c text);

When creating a table, you can also specify minimum and maximum value information for certain columns to speed up queries:

-- Use WITH(minmax_columns='b,c') to specify that columns b and c
-- should record min and max statistics.
-- This helps optimize queries involving these two columns,
-- because the system can quickly determine which data blocks might contain matching data.
CREATE TABLE p2(a INT, b INT, c INT) USING pax WITH(minmax_columns='b,c');

INSERT INTO p2 SELECT i, i * 2, i * 3 FROM generate_series(1,10)i;

-- because column b has minmax statistics,
-- the system can quickly locate the data blocks that might contain the value, speeding up the query.
SELECT * FROM p2 WHERE b = 4;

-- Similarly, because of the minmax information on column b, the system can quickly determine that no data blocks can meet this condition
-- (if all generated values are positive), possibly returning no data immediately and avoiding unnecessary data scans.
SELECT * FROM p2 WHERE b < 0;

-- Modify the minmax statistics settings for table p2 to apply only to column b. For data inserted later,
-- only column b will maintain this statistical information, and it won't affect existing data or trigger any rewrites or adjustments.
ALTER TABLE p2 SET(minmax_columns='b');

View the format of an existing table

To check whether a table is in PAX format, you can use one of the following methods:

  • Use the psql command \d+:

    gpadmin=# \d+ t1
                                            Table "public.t1"
Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
a      | integer |           |          |         | plain    |             |              |
b      | integer |           |          |         | plain    |             |              |
c      | text    |           |          |         | extended |             |              |
Distributed by: (a)
Access method: pax

  • Query the system catalog tables pg_class and pg_am:

    SELECT relname, amname FROM pg_class, pg_am WHERE relam = pg_am.oid AND relname = 't1';

relname | amname
---------+--------
t1      | pax
(1 row)

Compress column data with encoding

Each column in a PAX table can use its own compression encoding that matches the column’s data characteristics. Set the encoding per column with the ENCODING clause, or set a table-wide default with WITH (compresstype=...) that applies to every column without an explicit ENCODING clause. For the full list of compression types and their valid values, see PAX-related SQL options.

The following example assigns a different encoding to each column:

CREATE TABLE metrics (
    ts    TIMESTAMPTZ ENCODING (compresstype=deltadelta),
    id    INT         ENCODING (compresstype=deltadelta),
    value FLOAT8      ENCODING (compresstype=gorilla),
    flag  BOOL        ENCODING (compresstype=bool),
    name  TEXT        ENCODING (compresstype=zstd)
) USING pax;

An encoding affects only the data written after it is set. It does not rewrite existing data.

Type-specific encodings for time-series data

For time-series workloads, PAX provides three type-specific encodings that compress integer, float, and boolean columns far more than general compression does. All three are lossless. Because they target specific data types, apply them per column with the ENCODING clause rather than as a table-wide default.

compresstype

Applicable types

Best for

deltadelta

int2, int4, int8, date, timestamp, timestamptz

Timestamps at regular intervals, monotonic counters, and device IDs

gorilla

float4, float8 (also works on integer types)

Slowly-changing float metrics, such as CPU usage, temperature, and sensor readings

bool

bool only

Boolean flag columns, such as active, is_deleted, and alarm

Keep the following constraints in mind:

  • compresslevel does not apply to these three encodings.

  • bool encoding is not compatible with the porc_vec storage format. This constraint is enforced when data is inserted, not when the table is created — a CREATE TABLE statement combining bool encoding with porc_vec succeeds, but the first INSERT into that column fails.

Improve the compression ratio

deltadelta and gorilla encode each value relative to the preceding value (delta-of-deltas and XOR respectively), so their compression ratio depends on whether adjacent rows hold similar values. Two factors control this:

  • Write data in sorted order. Sort rows before loading them, typically with ORDER BY <tag_column>, <time_column>, so that adjacent rows within each segment hold similar values. Without sorting, MPP hash distribution scatters rows randomly and reduces the compression ratio.

    -- Sorted write: adjacent rows hold similar values, which compresses well.
INSERT INTO metrics SELECT * FROM source ORDER BY id, ts;

  • Increase the group size. The GUC parameter pax.max_tuples_per_group controls how many rows are encoded together in each group. Larger groups give the encoder longer sequences to find patterns in and amortize per-group overhead over more values. General compression types (zstd, zlib, and lz4) operate on raw byte streams and are less sensitive to the group size.

    Increasing the group size comes with tradeoffs: reads must decompress the entire group, which raises per-access latency and memory usage. In addition, PAX min/max filtering operates at the group level — larger groups make the filter coarser and can cause more irrelevant data to be scanned.

    For append-only archival or time-series workloads with infrequent point lookups, increasing this value can yield better compression. For workloads that involve frequent point lookups or analytical queries with WHERE filters, keeping the default is generally safer.

    SET pax.max_tuples_per_group = 65536;

Support for TOAST

If some columns in a PAX table contain large values, you can enable TOAST storage to store these large values in a separate TOAST file. This helps to make the data in the main data file more compact, allowing you to scan more tuples within the same data size.

By default, the TOAST storage is enabled for PAX tables. Unlike PostgreSQL, the TOAST storage supported by PAX does not rely on Page management, which allows PAX to store data larger than 2 MiB.

You can configure TOAST-related thresholds using the following parameters. For more details, refer to PAX-related system parameters.

  • pax.enable_toast

  • pax.min_size_of_compress_toast

  • pax.min_size_of_external_toast

Support for clustering

SynxDB has introduced z-ordering support for PAX tables, which greatly improves performance when managing and querying multi-dimensional data. Using the clustering feature, you can organize data in PAX tables using two different sorting strategies: index-based clustering and reloptions-based clustering. Note that the two strategies are mutually exclusive, and you can only choose one.

Clustering strategies

You can use the clustering feature of SynxDB to physically sort and optimize table data. The goal is to improve query performance by reorganizing the physical storage order of the data. In PAX tables, the clustering feature provides two different strategies for sorting data:

  • Index-based clustering. This strategy is based on B-tree indexes and works similarly to the native clustering method in PostgreSQL. It is suitable when an index has already been created for the table.

    Example:

    CREATE TABLE t2(c1 int, c2 int) USING PAX;
CREATE INDEX c1_idx ON t2(c1);
CLUSTER t2 USING c1_idx;
DROP TABLE t2;

    Index-based clustering drops the column compression that compresstype sets on the table. For more information, see Limitations for PAX tables.

  • reloptions-based clustering. PAX tables support clustering operations based on reloptions where cluster_columns are specified, and it provides the following sorting methods:

    • Z-order clustering: This method encodes the values of multiple columns into a byte array and sorts by this byte array. It is ideal for cases where multiple columns are used as query conditions (with no fixed order for the columns in the query). This method greatly enhances performance for multi-dimensional data queries. However, it is not suitable for string columns with the same prefix. When cluster_type is not specified, PAX uses z-order sorting as the default clustering strategy:

      CREATE TABLE t2(c1 int, c2 float, c3 text) USING PAX WITH (cluster_columns='c1,c2');
INSERT INTO t2 SELECT i, i, i::text FROM generate_series(1, 100000) i;
CLUSTER t2;
DROP TABLE t2;

      In this example, the data in table t2 is sorted using z-order by the c1 and c2 columns. This sorting method provides great performance improvement in multi-dimensional query scenarios.

    • Lexical clustering: This method sorts by the values and order of the columns, and it is primarily used for sorting and optimizing queries on string columns with the same prefix. For example:

      CREATE TABLE t2(c1 int, c2 float, c3 text) USING PAX WITH (cluster_columns='c1,c2', cluster_type='lexical');
INSERT INTO t2 SELECT i, i, i::text FROM generate_series(1, 100000) i;
CLUSTER t2;
DROP TABLE t2;

      In this example, the data in table t2 is sorted lexically by the c1 and c2 columns. This sorting method is well-suited for columns with the same prefix.

Conflicts between clustering types

Note that index-based clustering and cluster_columns-based clustering cannot be used at the same time. If a table has already been clustered based on an index (or cluster_columns), you cannot specify cluster_columns (or an index) for clustering.

An example of handling cluster conflicts:

-- Creates a table and uses index-based clustering
CREATE TABLE t2(c1 int, c2 float, c3 text) USING PAX;
CREATE INDEX t2_idx ON t2(c1);
CLUSTER t2 USING t2_idx;

-- Tries to use cluster columns (will fail)
ALTER TABLE t2 SET (cluster_columns='c1,c2', cluster_type='zorder');
-- Tries to use lexical clustering (will fail)
ALTER TABLE t2 SET (cluster_columns='c1,c2', cluster_type='lexical');

-- Drops the index and successfully use lexical clustering
DROP INDEX t2_idx;
ALTER TABLE t2 SET (cluster_columns='c1,c2', cluster_type='lexical');

In the above example, after table t2 is clustered using an index, attempts to set z-order or lexical clustering using cluster_columns will fail, until the index is dropped.

Bloom filter support

SynxDB introduces support for bloom filters, allowing you to generate and maintain bloom filter information at the column level. This feature helps quickly filter data blocks and improves query performance, especially when using IN conditions with multiple values, greatly reducing unnecessary data scans.

Bloom filter options

You can specify the columns for which you want to record bloom filter information by setting the bloomfilter_columns option. For example:

CREATE TABLE p1 (
    a int,
    b int,
    c text
) USING PAX WITH (bloomfilter_columns='b,c,a');

In this example, bloom filters will be generated for columns b, c, and a in the table p1.

The size of the bloom filter is controlled by the following GUC (global user configuration) parameters:

  • pax.max_tuples_per_file: Controls the maximum number of tuples stored in each data file.

  • pax.bloom_filter_work_memory_bytes: Controls the maximum memory allowed for the bloom filter.

For example:

-- Sets the maximum number of tuples per file.
SET pax.max_tuples_per_file = 131073;

-- Sets the maximum bloom filter memory.
SET pax.bloom_filter_work_memory_bytes = 1048576;  -- 1MB

-- Creates a table and specifies bloom filter options for columns.
CREATE TABLE p1 (
    a int,
    b int,
    c text
) USING PAX WITH (bloomfilter_columns='b,c,a');

The size of the generated bloom filter is calculated as follows:

ceil(min(pax.max_tuples_per_file * 2, pax.bloom_filter_work_memory_bytes))

ceil is a rounding function, which ensures that the size of the bloom filter is always a power of 2.

Sparse filtering

Sparse filtering is a data scanning optimization provided by the PAX storage format. It improves query performance by skipping file scans that do not meet conditions and reducing the amount of data block to be scanned within files.

In addition to being triggered directly by a WHERE clause, this optimization can also be triggered by a Runtime Filter generated from a HashJoin. When a PAX table is the outer table (the table being scanned) of a HashJoin, the runtime filter can be pushed down to the PAX storage layer. This allows it to use the column’s min/max statistics to filter out irrelevant data files before the scan, further improving join query performance. For more details, see Push down runtime filters to Table Access Method (AM).

To use sparse filtering, make sure that the following conditions are met:

  • The system parameter pax.enable_sparse_filter is set to ON (default value).

  • Statistics have been configured for the relevant columns (min/max or bloom filter).

  • The query contains filter conditions (WHERE clause) for these columns

Expression support examples

PAX supports basic conditional expressions. For example:

-- Creates test table
CREATE TABLE a(v1 int, v2 int, v3 int) USING PAX WITH(minmax_columns='v1, v2, v3');

-- Basic conditions
SELECT * FROM a WHERE v1 <= 3;                                    -- Full condition support. root is OpExpr.
SELECT * FROM a WHERE v1 != NULL;                                -- Full condition support. root is NULLTest.

-- Multiple conditions
SELECT * FROM a WHERE v1 <= 3 AND v2 >= 3;                       -- Full condition support. Tree structure has only one level.
SELECT * FROM a WHERE v1 <= 3 AND v2 >= 3 AND v3 >= 3;          -- Full condition support.
SELECT * FROM a WHERE v1 <= 3 AND v2 >= 3 AND v3 != NULL;       -- Full condition support.

PAX supports nested expression structures and certain operators. For example:

-- Creates test table.
CREATE TABLE a(v1 int, v2 int, v3 int) USING PAX WITH(minmax_columns='v1, v2, v3');

-- Nested expressions.
SELECT * FROM a WHERE (v1 <= 3 OR v2 > 3) AND v3 >= 10;         -- Full support for all conditions.
SELECT * FROM a WHERE (v1 <= 3 AND v2 > 3) OR v3 >= 10;         -- Supports complete nested expressions.

-- Operators.
SELECT * FROM a WHERE v1 + v2 <= 3;
SELECT * FROM a WHERE v1 + 10 <= 3;

PAX can handle complete expression trees, including nested AND/OR conditions. This means that all the above queries can be optimized, and SynxDB will use all available filter conditions to improve query efficiency.

Supported expression types

PAX sparse filtering supports the following expression types:

  • Arithmetic operators (OpExpr)

    • Supports addition (+), subtraction (-), and multiplication (*)

    • Division operations are not supported (due to difficulty in estimating negative number ranges)

    • Examples: a + 1, 1 - a, a * b

  • Comparison operators (OpExpr)

    • Supports <, <=, =, >=, >

    • Examples: a < 1, a + 1 < 10, a = b

  • Logical operators (BoolExpr)

    • Supports AND, OR, NOT

    • Example: a < 10 AND b > 10

  • NULL value tests (NullTest)

    • Supports IS NULL, IS NOT NULL

    • Examples: a IS NULL, a IS NOT NULL

  • Type casting (FuncExpr)

    • Only supports basic type casting

    • Example: a::float8 < 1.1

    • Custom functions are not supported, such as func(a) < 10

  • IN operator (ScalarArrayOpExpr)

    • Supports IN expressions

    • Example: a IN (1, 2, 3)

Partial condition support

When a query contains unsupported expressions (such as custom functions), PAX adopts a partial condition support strategy:

  • Identifies and extracts supported conditions.

  • Uses supported conditions for sparse filtering.

For example:

-- Creates custom function v2
CREATE OR REPLACE FUNCTION func(v2 double precision)
RETURNS double precision AS $$
BEGIN
  RETURN v2 * 2;
END;
$$ LANGUAGE plpgsql;

-- Uses custom function in query
SELECT * FROM a WHERE v1 < 3 AND func(v2) < 10;  -- PAX will use v1 < 3 for filtering

In this example, although func(v2) < 10 cannot be used for sparse filtering, SynxDB still uses v1 < 3 to optimize query performance. This approach ensures that partial performance optimization is achieved even in complex queries.

Note

  • The effectiveness of sparse filtering depends on data distribution and query conditions.

  • It is recommended to enable statistics on columns that are frequently used as filter conditions.

  • Certain conditions (such as custom functions) are ignored.

Use fast filter for two-phase column read

For queries with simple predicates on wide PAX tables, the vectorized scan can take a fast filter path that splits the column read into two phases:

  1. Read only the columns referenced by the predicate, evaluate the predicate natively, and produce a pass/fail bitmap.

  2. Read the remaining output columns only for the rows that passed phase 1. If no rows pass, skip phase 2 and move on to the next group.

This path bypasses the Arrow expression engine for the predicates it can handle and avoids reading output columns for rows that the query discards. The savings grow with table width and predicate selectivity.

The fast filter applies to the following predicates:

  • Integer comparisons: =, <>, <, <=, >, >=, including cross-type forms such as int2 column versus int4 constant.

  • String operations: LIKE '%substr%', NOT LIKE '%substr%', equality, and inequality with the empty string.

  • IN-list membership.

The vectorized executor removes the predicates that the fast filter handles from the Arrow FilterNode, so the system never evaluates them twice.

The optimization is controlled by pax.enable_fast_filter, which is on by default. Turn it off when you need to compare execution paths or diagnose a result discrepancy:

-- Disable the fast filter for the current session.
SET pax.enable_fast_filter = off;

The fast filter composes with the TopK runtime filter (see Skip PAX row groups for TopK queries): TopK runs first and rejects whole row groups using statistics alone; the fast filter runs after, on the groups that survive.

Speed up sparse fetches that go through an index

PAX compresses column data one group at a time, and a group holds 131072 rows by default. When an index fetches a few scattered rows, the scan decompresses the whole group’s column data even if only one row matches. To avoid that, PAX splits each compressed column into chunks of a fixed row count, compresses each chunk on its own, and records where every chunk starts at the head of the column stream. A read then decompresses only the chunk that holds the matching row.

pax.enable_chunk_index controls this optimization and is on by default. pax.chunk_index_rows sets the number of rows per chunk, defaults to 8192, and accepts values in [1024, 131072]. Both parameters apply when data is written, so a read needs no settings.

When chunking applies

  • Chunking applies to the data you write after you set the parameters, and leaves existing data as it is.

  • A read ignores both parameters. Data files carry their own format marker, and SynxDB decodes each file by its actual format, so existing data stays readable and one table can hold both formats.

  • PAX chunks variable-length columns (text, varchar, numeric, and similar) by default. It chunks a fixed-length column (int, float8, timestamp, and similar) only when compresstype sets a general compression (zstd, zlib, or lz4). An uncompressed fixed-length column needs no chunking, because a read locates the value directly by offset.

  • PAX chunks a group only when the group holds more rows than pax.chunk_index_rows, so small tables show no difference.

  • Execution paths that read a whole column, including statistics collection and the vectorized scan, run at the same speed. This optimization targets index fetches under row-mode execution.

Apply chunking to existing data

PAX chunks new writes only. To chunk existing data, rewrite the table:

CREATE TABLE sales_new (LIKE sales INCLUDING ALL) USING pax WITH (compresstype=zstd);
INSERT INTO sales_new SELECT * FROM sales;

Do not rewrite a PAX table with VACUUM FULL or index-based CLUSTER. Both drop the column compression. For more information, see Limitations for PAX tables.

Adjust the rows per chunk

The smaller the chunk, the less data a sparse fetch decompresses, but the compression ratio drops and storage grows. Lower pax.chunk_index_rows for tables that mostly serve index point lookups, and keep the default for tables that mostly run full scans. Set the parameter in the session before you write data:

SET pax.chunk_index_rows = 2048;

To keep newly written data in the unchunked format, turn the optimization off before you write:

SET pax.enable_chunk_index = off;

View data change records on PAX tables in WAL logs

Data operations related to PAX tables are recorded in the Write-Ahead Logging (WAL) system. This ensures reliable backups between primary and mirror nodes, which assists in failover processes.

You can use the pg_waldump tool to view the WAL logs for PAX tables. The logs are stored in the $COORDINATOR_DATA_DIRECTORY/pg_wal directory or the pg_wal directory within the Segment node’s data directory.

The PAX table WAL logs can be used for these purposes:

  • Data recovery: Helps restore data during system failures or data inconsistencies.

  • Failover support: Ensures data synchronization between primary and mirror nodes.

  • Debugging and analysis: Identifies and analyzes specific operations on PAX tables.

Example: operate PAX tables and view WAL logs

  1. Create a PAX table.

    CREATE TABLE a (
    v1 int,
    v2 int,
    v3 int
) USING PAX;

    This creates a PAX table a with columns v1, v2, and v3.

  2. Insert data into the table:

    INSERT INTO a VALUES (1, 2, 3);

  3. Locate the node where the PAX table resides:

    -- Views the IDs and data directory locations of all nodes in the cluster
SELECT * FROM gp_segment_configuration;

-- Identifies the Segment ID of table a based on the results of the above query
-- This helps determine the segment and data directory location of table a
SELECT gp_segment_id FROM gp_distribution_policy WHERE localoid = 'a'::regclass;

  4. Find the corresponding WAL log in the pg_wal directory of the identified node. For example:

    ls $COORDINATOR_DATA_DIRECTORY/pg_wal

    Locate the most recent WAL file to analyze the relevant operations. In this example, assume the WAL log file is named 00000001000000000000000A.

  5. Query the relfilenode of the PAX table to associate the WAL log with the PAX table:

    SELECT relfilenode FROM pg_class WHERE relname = 'a';

    The relfilenode is the unique identifier of the table in the WAL log.

  6. Use the pg_waldump tool to parse the WAL log and identify operations related to the PAX table:

    pg_waldump -f $COORDINATOR_DATA_DIRECTORY/pg_wal/00000001000000000000000A

    In the output, search for the previously obtained relfilenode to find operations associated with the PAX table.

    Example output of pg_waldump:

    rmgr: PAX       len (rec/tot):     68/   104, tx:    593, lsn: 0/016E1D98, prev 0/016E1D60, desc: INSERT off 3, blkref #0: rel 1663/16384/19780 blk 0

    In this example:

    • rmgr: PAX indicates the record type.

    • rel 1663/16384/19780 corresponds to the relfilenode of the PAX table.

    • INSERT indicates that an insert operation was recorded.

By following the steps above, you can track and analyze WAL logs related to PAX tables in SynxDB for debugging, recovery, or performance optimization purposes.

Inspect PAX table internal structure

SynxDB provides a diagnostic UDF pax_dump_groups() that allows you to inspect the internal storage structure of a PAX table. This function returns the micro partition and group layout information, which is useful for tuning storage parameters and debugging performance issues.

The function returns a table with the following columns:

  • micro_partition_id: The ID of the micro partition.

  • num_cols: The number of columns in the micro partition.

  • group_index: The 0-based index of the group within the micro partition.

  • num_rows: The number of rows in the group.

Before using this function, you need to register it:

CREATE FUNCTION pax_dump_groups(pax_relid Oid)
RETURNS TABLE(micro_partition_id int, num_cols int, group_index int, num_rows int)
AS '$libdir/pax.so', 'pax_dump_groups'
LANGUAGE C STRICT
EXECUTE ON ALL SEGMENTS;

Usage example:

-- Create a PAX table and insert data in multiple batches.
-- Each INSERT creates a separate micro partition on each segment.
CREATE TABLE t1(a int, b int, c text) USING PAX;
INSERT INTO t1 SELECT i, i * 2, 'text_' || i FROM generate_series(1, 50000) i;
INSERT INTO t1 SELECT i, i * 2, 'text_' || i FROM generate_series(50001, 100000) i;
INSERT INTO t1 SELECT i, i * 2, 'text_' || i FROM generate_series(100001, 150000) i;

-- Inspect the internal structure of the PAX table.
SELECT * FROM pax_dump_groups('t1'::regclass);

Example output (in a 2-segment cluster):

 micro_partition_id | num_cols | group_index | num_rows
--------------------+----------+-------------+----------
                        0 |        3 |           0 |    24983
                        1 |        3 |           0 |    24935
                        2 |        3 |           0 |    24913
                        0 |        3 |           0 |    25017
                        1 |        3 |           0 |    25065
                        2 |        3 |           0 |    25087
(6 rows)

Note

Because the function is executed on all segments, the output contains one row per micro partition per segment. Each INSERT operation creates a new micro partition (with a distinct micro_partition_id) on each segment. In this example, 3 inserts produce 3 micro partitions per segment, resulting in 6 rows for a 2-segment cluster. The total num_rows across all rows equals 150000.

By analyzing the output, you can understand the data distribution across micro partitions and groups, which helps in:

  • Evaluating whether the current pax.max_tuples_per_group and pax.max_tuples_per_file settings are appropriate.

  • Identifying uneven data distribution across micro partitions.

  • Debugging storage-related performance issues.

Back up and restore PAX tables

pg_dump includes PAX tables in a plain-text dump, so you can use it without any extra option to back up a database, migrate it to another cluster, or restore a single schema. You restore such a dump by running the SQL statements it contains. The dump preserves the storage format, the table-level options in the WITH clause, the per-column ENCODING attributes, and, for a partitioned PAX table, the partition structure.

Warning

Use the plain-text format for PAX tables. Support for the archive formats that pg_dump writes with -Fc, -Fd, or -Ft, and that pg_restore reads, is not complete for PAX tables. Such a command can report success and still leave you without a backup you can rely on, so do not use an archive format to back up a database that contains PAX tables.

Dump a database that contains PAX tables:

pg_dump -d mydb -f mydb.sql

For a non-partitioned PAX table, the dump sets the access method before the CREATE TABLE statement and keeps the storage options in the statement:

SET default_table_access_method = pax;

CREATE TABLE s.t1 (
    id integer,
    name text,
    score integer DEFAULT 0,
    COLUMN id ENCODING (compresstype=zstd,compresslevel=3)
)
WITH (minmax_columns=id, storage_format=porc_vec) DISTRIBUTED BY (id);

For a partitioned PAX table, the dump writes USING pax after the PARTITION BY clause and attaches each partition with a separate ALTER TABLE ... ATTACH PARTITION statement:

CREATE TABLE s.t2 (
    id integer,
    dt date,
    val text
)
PARTITION BY RANGE (dt) USING pax DISTRIBUTED BY (id);

ALTER TABLE ONLY s.t2 ATTACH PARTITION s.t2_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Note

When you write the statement yourself, follow the same clause order that the dump uses. If the statement combines PARTITION BY with DISTRIBUTED BY, place USING pax after PARTITION BY. If you place USING pax first, the statement fails with a syntax error at DISTRIBUTED.

Restore the dump into a new database by running the statements it contains, in the same way as for any other table:

createdb mydb_restored
psql -d mydb_restored -f mydb.sql

Limitations for PAX tables

  • For index support, the PAX storage format supports btree, hash, gin, and bitmap indexes. Creating a GiST, SP-GiST (gist/spgist), or BRIN index on a PAX table fails with an error.

  • Currently, if a field is too long, it will be stored in a TOAST file. This TOAST is different from PostgreSQL’s TOAST tables, and they only share the same name.

  • Backup coverage is limited to the plain-text output of pg_dump, which you restore by running its SQL statements. The archive formats that pg_dump -Fc, -Fd, and -Ft produce are not yet fully supported for PAX tables.

  • VACUUM FULL and index-based CLUSTER (CLUSTER ... USING <index_name>) drop the column compression that compresstype sets on a PAX table. The table can grow several times larger, while compresstype in the table definition keeps its original value, so the table definition gives no sign of the change. To compact table data, create a new table instead, rewrite the rows with INSERT INTO ... SELECT, and set compresstype explicitly on the new table. A plain VACUUM and CLUSTER based on cluster_columns keep the compression.

Best practices

For decision-oriented guidance on when to use PAX, how to manage ingestion and fragmentation, index and encoding choices, parameter tuning, monitoring, backup planning, and querying array columns, see PAX Table Best Practices.