Configuration reference

pgColumnar has two kinds of settings:

  • Server settings. These settings use the pgcolumnar. prefix. The list is below. For almost all of them, you do not need a special privilege. You can set them in postgresql.conf, for one session with SET, for one role, or for one database. A few need a special privilege or a specific time. Setting pgcolumnar.enable_end_truncation or pgcolumnar.objstore_allowed_endpoints needs superuser. You can set pgcolumnar.unique_lock_buckets only at server start. The row for each of these gives the condition again.
  • Per-table storage options. You set these options with pgcolumnar.set_options. They apply to one table. That table uses them when it writes new data.

Server settings

Storage layout

Setting Type Default Description
pgcolumnar.stripe_row_limit integer 150000 Maximum rows per row group. The row group is the unit of write and the granularity at which whole segments are appended. Range 1000 to INT_MAX.
pgcolumnar.chunk_group_row_limit integer 10000 Maximum rows per chunk group. The chunk group is the band a scan skips as a unit when a filter cannot match its minimum and maximum. Within a chunk group each column is encoded in fixed 1024-value vectors. Range 100 to INT_MAX.
pgcolumnar.encoding_sample_rows integer 2048 The number of rows that the writer samples to select the value encoding of a vector. The writer estimates each candidate on a sample of windows. The windows contain consecutive values and have an equal distance between them. Thus the sample shows the global shape and also the local runs. The writer then applies only the two best candidates to the full vector. A value of 0 applies each candidate to each vector. This is the behaviour of earlier versions. The writer changes a value below 128 to 0, because a smaller sample cannot put the candidates in order. This setting changes the write speed. It can also change the compression ratio. It does not change correctness.

Compression

Setting Type Default Description
pgcolumnar.compression enum zstd Default codec for new chunks. One of none, pglz, lz4, zstd. lz4 and zstd are available only when the extension was built with those libraries.
pgcolumnar.compression_level integer 3 Level for the zstd codec. Range 1 to 22. Higher levels compress more and write more slowly.
pgcolumnar.fsst_min_gain_percent integer 5 Minimum size reduction, in percent, for FSST string encoding to be kept for a column chunk. Range 0 to 99. See below.
pgcolumnar.fsst_verdict_reuse integer 16 How many later row groups may reuse a column’s FSST keep-or-drop verdict before it is decided again. Range 0 to INT_MAX.
pgcolumnar.parallel_flush boolean off Opt-in. When on, a stripe flush of two or more columns fans the per-column encode and compress work out to background workers. The stored bytes match the serial path. It helps one large flush of many numeric columns by up to 14 percent. A wide text-heavy flush regresses, because it copies the buffered bytes through shared memory. Frequent small flushes regress too, so it is off by default. Enable it for a wide numeric bulk load in the session that runs it.

To build the FSST codes for each vector is one of the larger costs of a load of text data. A value of 0 keeps FSST if it makes any reduction after the block codec. A small reduction does not always pay for the cost of the encode. The default of 5 keeps FSST only if it saves 5 percent or more.

Measurements show the effect of the default. For the data shapes where FSST wins by a small quantity, such as high-entropy text, the stored size increases by approximately 2 percent. The load time of the same data decreases by approximately one third. For the data shapes where FSST wins by more than the margin, such as low-cardinality text, the setting changes nothing. The writer selects the same encoding and writes the same bytes. Wide values also stay the same.

To get the behaviour of earlier versions, set the value to 0. Then FSST stays if it makes any reduction. The setting applies when the writer writes data. Thus it changes new chunks, but it does not change the chunks that are already on disk. It never changes the values that a table returns.

Scan and execution

Setting Type Default Description
pgcolumnar.enable_custom_scan boolean on Use the columnar custom scan path for columnar tables.
pgcolumnar.enable_qual_pushdown boolean on Push scan qualifiers down so per-chunk min and max values can skip chunk groups.
pgcolumnar.enable_vectorization boolean on Use the vectorized aggregate path for supported ungrouped aggregates.
pgcolumnar.enable_group_vectorization boolean off Use the vectorized aggregate path for GROUP BY queries on a columnar table. Off by default; see why grouped vectorization is off by default.
pgcolumnar.groupagg_max_groups integer 1000000 Cap on the group count the grouped vectorized aggregate builds. Over the cap the query errors. Range 1 to INT_MAX.
pgcolumnar.enable_bloom_filter boolean on Skip chunk groups on equality filters using per-chunk bloom filters.
pgcolumnar.enable_read_stream boolean on Prefetch block reads with the read stream API. Effective on PostgreSQL 17 and later.
pgcolumnar.enable_ungrouped_vector_agg boolean off Answer an ungrouped aggregate (count, sum, avg, min, max with no GROUP BY) with a batch fold over decoded vectors instead of row-at-a-time. Off by default.
pgcolumnar.enable_parallel_vector_agg boolean off Let the ungrouped batch fold run as a parallel partial aggregate under Gather, each worker folding its own row groups. Requires pgcolumnar.enable_ungrouped_vector_agg. Off by default.
pgcolumnar.enable_column_projection boolean on Read only the columns a query references rather than every column of the row group.
pgcolumnar.enable_index_fetch_penalty boolean on Charge a columnar index scan for the row-group decode its per-row heap fetches force, so the planner does not treat a columnar fetch as if it were a heap page read. Set to off to restore the pre-1.0-alpha planner behaviour.
pgcolumnar.enable_late_materialization boolean on Evaluate the scan qualifier before building the columns it does not read, so decode cost scales with rows emitted rather than rows scanned.
pgcolumnar.qual_skipvec_min_payload_cols integer 20 Minimum projected non-qual columns before per-vector qual gating skips a no-match 1024-row vector’s payload decode. Range 0 to 100000.

Index-only scan and projections

Setting Type Default Description
pgcolumnar.enable_index_only_scan boolean on Allow index-only scans on columnar tables, served by the columnar visibility-map fork. Set to off to force a plain index scan.
pgcolumnar.enable_projection_scan boolean on Let the planner scan a covering projection instead of the base table when one serves the query better.
pgcolumnar.enable_sorted_pathkeys boolean on Let the columnar scan tell the planner the order a sorted rewrite left the rows in, so ORDER BY on that key needs no Sort. Only a lexicographic run recorded by pgcolumnar.vacuum_sorted that still covers every row group is advertised. Set to off to restore the behaviour of always planning a Sort.

Maintenance and disk reclaim

Setting Type Default Description
pgcolumnar.reclaim_coalesce boolean on During online compaction, split an oversized freed range on reuse and coalesce adjacent freed ranges, so space is reclaimed under fragmentation. Off reverts to whole-range reuse.
pgcolumnar.enable_end_truncation boolean off Allow pgcolumnar.truncate() to return trailing reclaimed blocks to the operating system. Off makes pgcolumnar.truncate() a no-op. Requires superuser to set.
pgcolumnar.autovacuum boolean off Run the maintenance daemon. When on, it runs compact_rewrite and recluster on columnar tables that cross a threshold. It uses only ShareUpdateExclusiveLock and yields to any stronger lock. It never blocks a reader or a writer. See the administration guide. Reloadable, not a per-session setting.
pgcolumnar.autovacuum_naptime integer 60 Seconds between daemon sweeps. Each sweep starts one worker per database. Range 1 to 86400. Reloadable.
pgcolumnar.autovacuum_compact_threshold float 0.2 Deleted fraction at which the daemon rewrites a table with compact_rewrite. Range 0.0 to 1.0. Reloadable.
pgcolumnar.autovacuum_recluster_threshold float 0.05 Appended fraction at which the daemon reclusters a table that has a recorded clustering key. Range 0.0 to 1.0. Reloadable.

Object storage

These govern the object-store module that reads and writes remote Parquet and Arrow files. See Object storage for the URL schemes and the credential model.

Setting Type Default Description
pgcolumnar.objstore_allowed_endpoints string '' (empty) The endpoints the module may connect to, comma-separated as host or host:port. An entry with no port matches any port on that host; add a port to restrict it. Empty refuses every remote endpoint, so a role that can read or write server files cannot reach an arbitrary host through the extension. Link-local addresses, including 169.254.169.254, are refused whether or not they are listed. Superuser-only, so a role cannot widen its own reach.
pgcolumnar.objstore_s3_addressing string path The S3 request addressing style. path sends s3://bucket/key to endpoint/bucket/key; virtual sends it to bucket.endpoint/key, which is what AWS now prefers. Under virtual-host addressing the allow-list still authorizes the endpoint, not the per-bucket hostname.
pgcolumnar.objstore_buffered boolean on Coalesce remote Parquet reads to one request per column chunk instead of many small ranged reads.
pgcolumnar.objstore_part_size integer 0 Multipart part size in bytes for a remote export. 0 uses the module default of 8 MiB. Raise it for a fast link. Range 0 to INT_MAX.

Concurrent writes

Setting Type Default Description
pgcolumnar.enable_unique_insert_lock boolean on Serialize concurrent inserts of the same unique-index key with a transaction-scoped advisory lock, so overlapping same-key inserts conflict correctly.
pgcolumnar.unique_lock_buckets integer 128 Advisory-lock buckets per unique index. Bounds how many advisory locks a transaction holds per unique index. Equal keys always share a bucket; unrelated keys may share one, which only over-serializes. Range 1 to 1048576. Settable only at server start: the bucket is part of the lock tag, so backends that disagree on this value would not serialize against each other.
pgcolumnar.enable_row_update_lock boolean on Serialize a concurrent UPDATE or DELETE of the same row on the row identity, so the losing writer gets a retryable serialization_failure instead of duplicating the row and losing an update. Off restores the prior behavior.
pgcolumnar.row_lock_buckets integer 1024 Advisory-lock buckets per storage for same-row UPDATE/DELETE serialization. Bounds the row locks a transaction holds, so a bulk update cannot exhaust the lock table. Unrelated rows may share a bucket, which only over-serializes. Range 1 to 1048576. Settable only at server start: the bucket is part of the lock tag.

Internal settings

These settings are registered but are not tuning knobs. They are listed here because they appear in pg_settings and a reader who finds one there deserves an answer.

Setting Type Default Description
pgcolumnar.bulk_parallel_writer boolean off Internal. Set by pgcolumnar.parallel_copy loader workers so they skip the storage-row creation lock when the row already exists committed, which is what lets several atomic writers load one table at once. Marked GUC_NOT_IN_SAMPLE; leave it alone. Setting it by hand is safe but pointless: the skip only fires when the storage row is already committed, which is exactly when the lock guards nothing.
pgcolumnar.maintenance_hold_ms integer 0 Internal, for tests. A maintenance verb holds ShareUpdateExclusiveLock this many milliseconds, interruptibly, so a test can observe the daemon yield to a stronger lock. 0 disables it. Range 0 to 600000. Leave it at 0.
pgcolumnar.sink_fail_after integer -1 Internal, for tests. A fault-injection point that fails an export write after this many bytes, by the path a full disk takes. -1 disables it. Range -1 to INT_MAX. Leave it at -1.

Per-table storage options

pgcolumnar.set_options sets the storage options of one table. The new values apply to the data that the writer writes after the change. The data that is already on disk does not change. It changes only when a command rewrites the table, for example pgcolumnar.vacuum.

The table must already be an ordinary table using the pgcolumnar access method. Setting options on anything else raises relation "..." is not a columnar table.

A partitioned table is rejected as well. It holds no data of its own, so options set on it would never be read. Set them on each partition, which is where the rows are written. For an ordinary table, convert it first, then set its options:

ALTER TABLE events SET ACCESS METHOD pgcolumnar;
SELECT pgcolumnar.set_options('events', compression => 'zstd');

SELECT pgcolumnar.set_options(
    'events',
    chunk_group_row_limit => 20000,
    stripe_row_limit      => 300000,
    compression           => 'zstd',
    compression_level     => 6);
Argument Type Description
table_name regclass The columnar table to change. Anything that is not an ordinary table using the pgcolumnar access method is rejected, including a partitioned table.
chunk_group_row_limit integer Per-table override of pgcolumnar.chunk_group_row_limit.
stripe_row_limit integer Per-table override of pgcolumnar.stripe_row_limit.
compression name One of none, pglz, lz4, zstd.
compression_level integer Level for the zstd codec, 1 to 22.
encode_effort name full (default) or fast. How much work the writer spends choosing an encoding. See below.
sort_by name[] Declared physical sort key (#288), applied by pgcolumnar.vacuum_sorted(t) with no columns. Column names, so it survives pg_dump/restore. Not auto-maintained; re-run after inserts. Cannot name a virtual generated column. Clear with reset_options(t, sort_by => true).
ttl_column name The timestamp or timestamptz column a retention is measured on. Set it with ttl_interval; neither works alone. Nothing is deleted until you call pgcolumnar.expire(t) by name.
ttl_interval interval How long a row is kept, measured from ttl_column. pgcolumnar.expire(t) then drops row groups whose rows are all older than this. A group with one live row is kept whole.

The function does not change an argument that keeps its default value of NULL. The function refuses a value that is outside the permitted range of a limit or a level.

pgcolumnar.reset_options has no ttl_column or ttl_interval argument. To clear a declared retention, set ttl_interval to a value long enough that no row reaches it, or recreate the table.

Why grouped vectorization is off by default

pgcolumnar.enable_group_vectorization is a real speed-up. Take 4,000,000 rows grouped into 200,000 keys. SELECT k, sum(m) FROM t GROUP BY k runs in 403.7 ms with it on and 781.0 ms with it off, a factor of 1.93. The answers are identical. That figure is the minimum of seven interleaved pairs on one machine, against a build made without --enable-cassert. An assert build measures a larger factor, about 2.2. The assertion checks run once per memory context reset, and the ordinary Agg resets far more contexts than the vectorized fold does. Your own factor depends on the build, the group count, the key width and the aggregate.

It stays off because of how it fails, not because of how it performs. The grouped path builds a hash table that does not spill, so pgcolumnar.groupagg_max_groups bounds it at 1,000,000 groups. The cap is checked during execution, against the real group count. By then the plan is fixed, and the path cannot hand the query back to an ordinary Agg. Over the cap the query stops:

ERROR:  54000: grouped vectorized aggregate exceeded pgcolumnar.groupagg_max_groups (1000000)
HINT:  Raise pgcolumnar.groupagg_max_groups, or set pgcolumnar.enable_group_vectorization = off.

Measured on a table with 1,500,000 distinct keys: the same query succeeds with the setting off and raises that error with it on.

A default carries that behaviour to tables the user did not choose it for. The failure appears when a table grows past the cap. A query that ran yesterday then fails today, with no change to the query and no change to the setting. Turn the setting on for a workload whose group count you know. Raise the cap for one that approaches it.

Encode effort

encode_effort = fast does not do the FSST substring search. This applies when the writer writes text columns and other columns of variable length. All other parts stay the same. The dictionary, the run-length encoding, the numeric schemes and the block codec all continue to operate. The same code reads a table that the writer wrote with either value. Thus this setting controls cost only. It does not control compatibility.

The setting decreases the compression ratio, increases the load speed, and increases the read speed. The quantity of each effect depends fully on the data:

Text shape (1,000,000 rows, one column) Load speed-up with fast Extra space
32-char hashes 5.7x 2.7%
E-mail addresses 3.5x 12.2%
64-char hashes 3.1x none
256-char high-entropy 2.4x none
Short low-entropy text 1.2x to 2.7x none

The measurements used seven data shapes. For five of them, fast wrote storage that is identical byte for byte. Thus the work that it did not do gave no benefit. For the other two shapes, the search gives a large benefit. You cannot know which condition applies to a column until you try it. For this reason the option applies to one table, and the default keeps the full search.

When the setting changes anything at all

The full search always costs write time. It changes the stored bytes and the read time only when the writer keeps the symbol table it built.

The writer builds the table, then compares the result against the same data without it, after the block codec has run. It keeps the table only when the saving clears pgcolumnar.fsst_min_gain_percent, which is 5 by default. Otherwise it drops the table and the column takes its ordinary encoding.

The outcome depends on the data, and you cannot predict it. The decision is made for each column chunk. Two people who both write “text-shaped” test data get opposite answers. Four shapes were measured here at the default codec. The table was dropped in every one. full and fast then produced byte-identical storage and equal read times:

Text shape Table kept? Bytes, full Bytes, fast
Repeated small alphabet dropped 922,693 922,693
Timestamped log lines dropped 5,670,736 5,670,736
URL-shaped text dropped 3,111,366 3,111,366
Hex hashes dropped 35,520,180 35,520,180

On other shapes the table survives, and there full is a real storage win at almost no read cost. A second set of measurements found full 31.8% smaller on one shape and 9.6% smaller on another, with read times of 1.08x and 0.99x. The extra decoding is paid back by reading fewer bytes.

So the setting is worth measuring on your own column and not worth guessing. Write the column both ways and compare:

SELECT sum(datalength) FROM pgcolumnar.stats('your_table');

If the two totals match, the writer dropped the table and encode_effort buys you nothing but load time on that column. If full is smaller, that is what it saves you, and the read cost is small when the codec is on.

Read cost with the codec off

compression = 'none' keeps the symbol table, because there is no codec to beat. That isolates the encoding, and it is the only setting under which the read cost is large. These figures measure the encoding on its own. They are not what a default installation sees, and the storage column is far larger than a default installation stores.

Measured on 1,000,000 rows in one text column, SELECT count(v), with the vectorized paths off so that the scan decodes every value. The minimum of seven interleaved pairs:

Text shape Read with full Read with fast fast is Storage with fast
128-char hex 267.6 ms 143.9 ms 1.86x faster 1.94x larger
URL-shaped text 131.5 ms 96.0 ms 1.37x faster 3.79x larger

Both figures fell when the reader learned to decode a symbol with one machine word. Quote them with the version they came from.

Use fast for a bulk load if you will compact the table later. pgcolumnar.vacuum, pgcolumnar.compact_rewrite and pgcolumnar.recluster rewrite the data. They use the effort value that applies at that time. Thus you can load a table at a low cost, then compress it fully after the load.

The option applies to one table and is not a session setting. This is deliberate. If it were a session setting, two sessions with different values would store the same data in two different forms.

pgcolumnar.reset_options returns options to the server defaults:

SELECT pgcolumnar.reset_options(
    'events',
    chunk_group_row_limit => true,
    compression           => true);

If a boolean argument is true, the function resets that option on the table.