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, but see the note below: a value under 1024 costs text compression.
`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 block codec for new chunks. One of `none`, `pglz`, `lz4`, `zstd`. `lz4` and `zstd` are available only when the extension was built with those libraries. This setting also participates in the lightweight encoding decisions that run before the codec, so `none` is not the same cascade with compression removed; see Compression and the encoding cascade.
`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.enable_post_codec_encoding_choice`	boolean	`on`	Keep a lightweight encoding only when the encoded column chunk is smaller than the raw one AFTER the block codec has run. The encoders choose on uncompressed bytes, but what is stored is compressed, and bit-packing can whiten a stream the codec was exploiting; measured on ClickBench `hits`, 16 of 77 fixed-width columns were stored larger encoded than raw. Turning this off restores the pre-1132 behaviour of trusting the pre-codec choice. See Compression and the encoding cascade.
`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	`on`	Use the vectorized aggregate path for `GROUP BY` queries on a columnar table. The planner takes it only when the group count cannot exceed `pgcolumnar.groupagg_max_groups`; see how the grouped path stays safe on by default.
`pgcolumnar.groupagg_max_groups`	integer	`1000000`	Cap on the group count the grouped vectorized aggregate holds in its table at once. At the cap the node hands its groups to a core `Finalize` and carries on, when every aggregate has a partial state; otherwise the query errors. The planner takes the grouped path only when the group count cannot exceed this. Range 1 to INT_MAX.
`pgcolumnar.enable_bloom_filter`	boolean	`on`	Skip chunk groups on equality filters using per-chunk bloom filters.
`pgcolumnar.enable_join_runtime_filter`	boolean	`on`	Wrap a serial inner Hash Join so the build keys can skip fact-table groups and reject non-matching rows. Direct columnar scan only. Group skip needs the fact table clustered on the join key. On by default.
`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. A unique-key inner Hash Join can keep that fold. 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.index_min_blocks`	integer	`3`	Heap pages at or above which a keyed read of a `pgcolumnar` metadata catalog probes that catalog's index instead of reading it sequentially. These catalogs are shared by every columnar table in the database, so the same catalog is one page in a small database and hundreds in a large one, and below a few pages a sequential read is cheaper than a probe. The default is measured rather than chosen, and it is a default rather than a truth: the best value differs per catalog, because the hot catalogs are read more often per retired row group. Change it only to undo a regression you have measured. A very large value makes every converted lookup read its catalog sequentially, as 1.0-alpha4 and earlier did for most of them; `0` always probes. Range 0 to INT_MAX.
`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, and `expire` when `pgcolumnar.autovacuum_expire` is also on. 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_expire`	boolean	`off`	Let the daemon run `pgcolumnar.expire` on every table that declares a retention, once per sweep, before compaction. Off by default because `expire` deletes rows: turning the daemon on does not by itself apply a retention. No effect while `pgcolumnar.autovacuum` is off. Reloadable.
`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 files, reads remote Iceberg tables, and writes remote 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. The link-local ranges and the known instance-metadata addresses, such as `169.254.169.254` and Alibaba Cloud's `100.100.100.200`, are refused whether or not they are listed; see the refused ranges. 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_pin_reads`	boolean	`on`	Pin each remote read to the version of the object it opened. On, every ranged GET sends the ETag from the opening HEAD as `If-Match`, and the `x-amz-version-id` from the HEAD as `versionId=` when the store sends one other than `null`. A GET that names a version needs the `s3:GetObjectVersion` permission; if the store answers it with 403, the reader asks again without the version and keeps `If-Match`. Turn it off only for a server whose ETag changes from one request to the next for the same bytes, such as nodes behind a load balancer. Off, a read of an object overwritten during the read can return rows from both versions without an error, and so can two reads of one object in one statement (#1894). The check of the object's length stays on: each 206 `Content-Range` total must match the HEAD's length, and a 416 for bytes inside that length fails the read. See limitations.
`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. An upload holds at most 10000 parts, so the part size limits the object size: 8 MiB parts allow about 78 GiB. An export that needs more parts fails before it sends part 10001. AWS S3, Google Cloud Storage and Cloudflare R2 need parts of at least 5 MiB (5242880 bytes); to them, a smaller part size fails when the export starts a multipart upload. Range 0 to 536870912 (512 MiB): the export holds one part in memory, in one allocation that PostgreSQL caps at 1 GiB.
`pgcolumnar.export_group_bytes`	integer	`268435456`	The byte cap for one Parquet row group or Arrow record batch that `export_parquet`, `export_arrow` and `parallel_export_parquet` buffer. The writer counts each row in the form it writes it, and starts a new group before a row that would pass the cap, as well as every 65536 rows (Parquet) or 16384 rows (Arrow). A row larger than the cap goes in a group of its own; only such a group passes the cap. Lower it to bound the memory an export holds. `parallel_export_parquet` passes the caller's value to its workers. Range 1024 to 268435456 (256 MiB). See Export size limits.

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. `recluster` also waits, after it has listed the groups it will rewrite and before it locks them, so a test can write while it runs. `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 relation must already use the pgcolumnar access method, and must be either an ordinary table or a materialized view. Both hold rows of their own, so the writer reads their options. Setting options on anything else raises relation "..." is not a columnar table.

A partitioned table is rejected. It holds no rows of its own, so options set on it would never be read. Set them on each partition, which is where the writer writes. To use options on a table that is not columnar yet, convert it first:

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 relation to change: an ordinary table or a materialized view using the `pgcolumnar` access method. Anything else 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`. See the note below: a value under 1024 costs text compression.
`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 `date`, `timestamp` or `timestamptz` column a retention is measured on. On a `date` column the cutoff is truncated to the day, so a row dated on the cutoff is kept. Set it with `ttl_interval`; neither works alone. Nothing is deleted until `pgcolumnar.expire(t)` runs: by name, or from the daemon with `pgcolumnar.autovacuum_expire` on.
`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.
`sketch_columns`	name[]	Columns that get a distinct-count sketch in each row group: about 2 KB per row group per column, in `pgcolumnar.sketch`. The type needs an extended hash function, and a text column a deterministic collation; anything else is refused with SQLSTATE 0A000. Rows written before the option was set have no sketch. Clear with `reset_options(t, sketch_columns => true)`.

A stripe_row_limit below 1024 disables FSST on text columns. A vector is a fixed 1024 values. A row group smaller than that never fills one, so the chunk-shared FSST symbol table is not built. Measured on 200,000 rows of a text column, compression = none, against 12,800,000 raw bytes:

`stripe_row_limit`	FSST tables	stored	of raw
1000	0	13,625,000	106.4%
1200	166 of 167	6,998,031	54.7%
2000	100 of 100	6,990,641	54.6%

At 1000 the column costs more than storing the bytes uncompressed. The accepted minimum is 1000 and the vector is 1024, so the most aggressive legal setting is the one that pays this cost. Use 1024 or more unless you have measured that you want the opposite (#1017).

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.

How the grouped path stays safe on 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.

Until 1.0-alpha5 it was off by default, because of how it fails, not 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.

On by default, that error would reach tables nobody chose the path for. So the planner now takes the grouped path only when the group count cannot exceed the cap. It uses an upper bound, not an estimate:

the table's live row count, because no grouping makes more groups than rows;
for date_bin and date_trunc keys, the number of buckets the time column's range allows, which is usually far smaller.

A query over more rows than the cap, grouped by a plain key, runs the ordinary plan and succeeds. An estimate could not be the gate. On a key where one value fills half the table and the rest are unique, a fresh ANALYZE estimated 29,356 groups for 2,000,001.

The cap is reached only when something changes after planning: the cap is lowered, or the table grows past it. A prepared statement can see this. Since 1.0-alpha5 most such queries no longer error (#1300). The grouped node runs under a core Finalize HashAggregate when every aggregate has a partial state. These are count, min, max, and sum and avg over integer and float columns. At the cap the node hands its groups to the Finalize and empties its table. Then it carries on. The Finalize combines the groups, and it spills to disk within work_mem. EXPLAIN ANALYZE reports each hand-off in Columnar Group Table Flushes. It reports the most the table held at once in Columnar Group Table Peak Memory. The other aggregates, sum and avg over bigint and numeric, keep the serial node. At the cap it writes its groups to a sort, which spills within work_mem, and empties its table. At the end it merges each group's parts and finishes them once. The answer, scale included, is the one core gives. A group key whose type has no ordering cannot be sorted, so such a query runs the ordinary plan. The error above is no longer reached. To keep larger tables on the fast path, raise pgcolumnar.groupagg_max_groups.

Size the cap to the memory you can give the node. Each group it holds takes about 650 bytes, so the default of 1,000,000 groups is about 650 MB. That holds whatever work_mem says. It is also per grouped node and per parallel worker. So plan on 650 bytes times the cap, times the workers plus one. Past the cap the node's memory stays the same, but rows move to disk. So lowering the cap trades memory for time spent spilling. Measured on 10,000,000 rows with 100,000 groups and a cap of 1,000, the hand-off spilled 24 MB and ran 3.3 times longer. The merge wrote about 1.5 GB of temporary files and ran 4.9 times longer. Those keys rarely repeat between hand-offs, which is the worst case. A cap near the real group count costs almost nothing.

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. It accepts exactly what pgcolumnar.set_options accepts, and refuses exactly what it refuses:

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.