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. There are two exceptions. pgcolumnar.enable_end_truncation needs superuser. You can set pgcolumnar.unique_lock_buckets only at server start. The row for each exception gives this 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 vector. The vector is the unit of encoding and of min/max skipping. 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.

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.
`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.

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.

Column cache¶

Setting	Type	Default	Description

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.

Concurrent unique inserts¶

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.

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.

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.
`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)`.

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.

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 and increases the load 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.

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.