PAX Table Best Practices

This guide explains how to use the PAX storage format in SynxDB in production. It covers when to choose PAX, how to keep it healthy under real write patterns, and how to get the most out of its filtering and encoding features. For the full feature reference, syntax, options, and GUCs, see PAX storage format.

Decide when to use PAX

PAX works best on wide tables that are loaded in batches and queried by analytical workloads, such as filters, aggregations, and joins that touch only some columns. Each file and group in PAX carries some overhead (statistics, encoding headers, bloom filters). More rows and more analytical scans spread that overhead over more work, so each query costs less.

A regular heap table usually works better for narrow tables that mostly handle single-row lookups and frequent small updates. These tables have fewer rows and scans, so there is less work to spread the overhead over.

Manage ingestion and fragmentation

Each INSERT or COPY statement creates a new micro partition on every segment. Frequent small transactions, such as row-by-row inserts or a CDC pipeline that commits every few rows, build up many small micro partitions per segment. PAX pays the same per-file and per-group overhead no matter how many rows a micro partition holds, so a fragmented table scans and compresses worse than one loaded in a few large batches.

Batch your writes as much as you can, and sort rows before loading them (see Improve the compression ratio). Doing both at once reduces fragmentation and encoding overhead.

If a table is already fragmented, a plain VACUUM will not help: it does not merge micro partitions. It only reclaims space from rows that updates and deletes have already marked for removal in existing files. To merge small micro partitions into larger ones, run VACUUM FULL or CLUSTER instead. Both rewrite the whole table and take an exclusive lock, so run them during a maintenance window, not as a routine task. Also note that pg_stat_user_tables does not track dead or live tuple counts for PAX tables. Do not use it to decide when a table needs compaction. Instead, inspect the table directly (see Monitor table health).

Choose an index, or rely on statistics

The PAX storage format supports btree, hash, gin, and bitmap indexes. Creating a gist, spgist, or brin index on a PAX table fails (see Limitations for PAX tables). Even a supported index adds write overhead and extra storage. In an MPP cluster, it also competes with the file-skipping that PAX already does on its own.

For OLAP-style filters on wide tables, such as range predicates, IN lists, or equality checks used to skip files, use minmax_columns and bloomfilter_columns instead of an index. These options let PAX skip whole files or groups without any random access, and the only ongoing cost is storage.
Reserve indexes for lookups on a unique or near-unique key. There, an index scan can fetch a small number of rows directly, instead of scanning a whole PAX file.

For example, track minmax statistics on the columns your queries filter on most often:

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;

-- The minmax statistics on b let PAX go straight to the matching file or group.
SELECT * FROM p2 WHERE b = 4;

-- The same statistics let PAX skip the file completely when no row can match.
SELECT * FROM p2 WHERE b < 0;

Choose column encodings

Match each column’s compresstype to what the data looks like. Do not leave every column at the table-wide default:

Data pattern	Recommended encoding
Repeated or low-cardinality values	`rle`
Monotonic or evenly-spaced integers, dates, or timestamps (counters, device IDs, regular timestamps)	`deltadelta`
Slowly-changing float metrics (CPU usage, temperature, sensor readings)	`gorilla`
Boolean flags	`bool`
General numeric or text columns with no specific pattern	`zstd` for the best ratio, `lz4` for faster decompression, or `zlib`

See Compress column data with encoding for the full option list and the constraints on the time-series encodings.

Set the encoding per column with the ENCODING clause:

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 only affects data written after you set it. It does not rewrite data that is already there.

Use declarative partitioning and clustering to narrow scans

PAX has no partitioning feature of its own. To partition a PAX table, use SynxDB’s native declarative partitioning and set the access method on the partitioned table. Each leaf partition is then created as a PAX table:

CREATE TABLE events(id int, event_time timestamptz, payload text) USING PAX
PARTITION BY RANGE (event_time) (START ('2026-01-01') END ('2026-02-01') EVERY (INTERVAL '1 day'));

Partitioning a PAX table this way lets the query planner prune whole partitions before PAX’s own file- and group-level filtering even runs, which helps most when queries consistently filter on the partition key, such as a time range, and each partition holds a good number of rows.

If several columns show up in your query filters, use with(cluster_columns='b,c,d', cluster_type='zorder'). Sorting the data with zorder encoding means any of the cluster_columns can benefit from filtering. Sorting by a single column, by comparison, only helps filters on that one sorted column and barely helps filters on the other columns. Cluster sorting only improves minmax-based filtering; cluster sorting has no effect on bloom filter-based filtering.

Tune group and file size together

pax.max_tuples_per_group, pax.max_tuples_per_file, and pax.max_size_per_file all interact with each other. Tune them together for your workload, not one at a time:

Archival or append-only workloads with few point lookups: increase pax.max_tuples_per_group. Larger groups spread the per-group overhead over more rows and give the encoder longer runs of data to compress. Both effects improve the compression ratio for the deltadelta and gorilla encodings. Larger pax.max_tuples_per_file and pax.max_size_per_file values also mean fewer micro partitions from large batch loads.
Workloads with frequent point lookups or selective WHERE filters: keep the defaults, or use smaller values. Larger groups mean PAX has to decompress more data on every access. They also make minmax filtering coarser, because that filtering works at the group level.

Monitor table health

Use pax_dump_groups() (see Inspect PAX table internal structure) to check, from time to time, how many micro partitions and groups a table has, and how many rows each one holds. If many groups hold far fewer rows than pax.max_tuples_per_group, or a segment has many micro partitions, the table is likely fragmented from small or frequent writes, and is a good candidate for VACUUM FULL or CLUSTER.

It also helps to track pg_total_relation_size() after heavy UPDATE/DELETE activity. PAX marks rows as deleted instead of rewriting files right away, so table size will not shrink until you run VACUUM FULL.

Choose a backup tool

Both gpbackup and pg_dump cover PAX tables, so choose based on the size of the backup rather than on the storage format.

Use gpbackup and gprestore for routine backups of a production cluster. They run in parallel across all segments, so they scale with the cluster and move data directly to and from each segment.

Use pg_dump for a single schema, a migration to another cluster, or a small database. The plain-text dump keeps the storage format, the table options in the WITH clause, the per-column ENCODING attributes, and the partition structure of a PAX table, and you restore it by running the SQL statements it contains. Because all data passes through the coordinator, pg_dump runs much slower on a large cluster and needs enough disk space on the coordinator host to hold the whole dump. Stay with the plain-text format, because the archive formats that pg_restore reads are not yet fully supported for PAX tables. For details, see Back up and restore PAX tables.

As with any backup strategy, test a full restore in a non-production environment before you depend on it in production.

Query array columns without UNNEST

PAX tables often store multi-valued attributes, such as tags or categories, as array columns. A common way to filter or aggregate on these values is to UNNEST the array into one row per element, then filter or GROUP BY on the result. On a PAX table, this UNNEST pattern gives up most of the benefits of the storage format. Unnesting forces the planner off SynxDB’s cost-based optimizer (GPORCA) and onto the row-based Postgres planner. Instead of a vectorized scan, you get a Nested Loop over a Function Scan on unnest.

For membership and overlap checks, use the native array operators in the WHERE clause instead of UNNEST:

-- Prefer this: stays on the vectorized scan path.
SELECT count(*) FROM events WHERE tags && ARRAY['checkout'];   -- overlaps
SELECT count(*) FROM events WHERE tags @> ARRAY['checkout'];   -- contains

-- Avoid this: forces a row-by-row plan.
SELECT count(*) FROM (
  SELECT id FROM events, LATERAL unnest(tags) AS t(tag) WHERE t.tag = 'checkout'
) s;

The difference shows up directly in EXPLAIN. The array-operator query keeps the cost-based optimizer and scans the table once:

Optimizer: GPORCA

The equivalent query using UNNEST falls back to the row-based Postgres planner, which checks the array one element at a time:

->  Nested Loop
      ->  Seq Scan on events
      ->  Function Scan on unnest t
            Filter: (tag = 'checkout')
Optimizer: Postgres query optimizer

In testing on a table with a few million rows, the same membership check ran several times faster with && than with the equivalent UNNEST query. The gap grows even wider for GROUP BY-style aggregation over array elements: UNNEST followed by GROUP BY on the exploded rows can force an on-disk sort, because every array element becomes its own row that must be sorted and grouped.

If you need a count for each of a known, limited set of candidate values, compute one count(*) FILTER (WHERE tags && ARRAY[value]) per candidate instead. Computing the counts this way keeps the query on the vectorized path and avoids unnesting. Use UNNEST only when the set of values is unbounded or unknown ahead of time. In that case, expect the query to scale like a row-based query, not like a PAX analytical scan.

Combine with the vectorized execution engine

PAX works well with the vectorized execution engine. Query plan parameters let you turn vectorized query plans on or off, and enabling them can improve query performance.

Vectorized plans take advantage of the columnar storage in PAX tables. They use memory efficiently and speed up query execution. Because PAX tables process data in batches, they support high-throughput queries well. For more details, see vectorization query computing.

For example:

SET vector.enable_vectorization = on;  -- Enables vector query plans.
SET vector.max_batch_size = 16384;  -- Sets the maximum number of rows per batch.

SynxDB’s PAX storage also supports thread-level vectorized scanning, but only for parallel scans of local files. For specific settings, see threaded execution on single node.