Best practices

This guide collects the operational advice that the how-to guides apply one task at a time. It is organized by decision. Each section covers one choice: table design and loading, compression, layout for skipping, maintenance, query speed, and safe object storage and Iceberg use. Every default and lock level here is the shipped behavior. The configuration reference lists the settings and their bounds.

When to choose a columnar table

pgColumnar suits wide, append-mostly, analytical tables. These tables are scanned by ranges and aggregates far more often than they are updated row by row. It is not a replacement for a narrow, write-hot, point-lookup table. Use this test: if a query reads a few columns out of many and touches a range of rows, columnar wins. If a query fetches whole rows by primary key at high frequency, keep the heap. The limitations page lists the features a columnar table does not support. Read it before you migrate a table you cannot easily migrate back.

Design and load a table

Load in large batches. The writer packs rows into a row group, the unit of the write. It divides each row group into chunk groups of up to pgcolumnar.chunk_group_row_limit rows (default 10000). A chunk group is the unit a scan skips. A column within a chunk group is a chunk, compressed and encoded on its own. Small transactions produce small, poorly compressed row groups, and many of them to scan later. Prefer COPY or a multi-row INSERT ... SELECT over row-at-a-time inserts. Load in batches that fill a row group (pgcolumnar.stripe_row_limit, default 150000 rows), so each row group compresses well.

Use parallel_copy for a large file. pgcolumnar.parallel_copy splits a server-side file across workers and scales the load into one table. It runs a coordinator plus the loader workers, so it needs one worker slot more than the loader count, in max_worker_processes and max_parallel_workers. Without the extra slot, the load moves no rows. The caller needs the pg_read_server_files role.

Do not over-partition. Native partitioning multiplies catalog and planning cost. Use it when a partition key genuinely bounds most queries, not by default. A single well-clustered columnar table often skips as much as a partitioned heap.

Choose compression and encoding

The codec trades ratio against speed. The block codec is one of none, pglz, lz4, or zstd, and defaults to zstd. zstd gives the best ratio. lz4 decompresses fastest for a scan-bound workload where storage is cheap. none and pglz exist for special cases. pgcolumnar.compression_level applies to zstd and ranges from 1 to 22, with a default of 3. Raise it for cold, archival data, where a slower write buys lasting space. Leave it low for tables you rewrite often.

Let the encoder choose the encoding. Before the codec runs, each chunk takes the encoding that makes it smallest. The encodings include dictionary, run-length, delta, frame-of-reference, delta-of-delta, and FSST for strings. Specialized float encodings are available too. You do not select these encodings. You feed the encoder data it can exploit. Sorted or low-cardinality columns encode far smaller, which is another reason to cluster.

Spend encode effort where it pays. encode_effort is full or fast. Leave it at full for the best ratio. Switch a column to fast when a text-heavy load is CPU-bound on encoding. You then accept a slightly larger file for a faster write. pgcolumnar.fsst_verdict_reuse (default 16) reuses the FSST decision for a column whose character is stable between row groups.

-- archival table: maximize ratio
SELECT pgcolumnar.set_options('events_cold', compression => 'zstd', compression_level => 19);
-- hot ingest table: favor write speed
SELECT pgcolumnar.set_options('events_hot', compression => 'lz4', encode_effort => 'fast');

Lay the table out for skipping

A scan skips a chunk group when the group's per-column minimum and maximum exclude the predicate. That test is only as sharp as the data's order. A column whose values are scattered across every chunk group cannot be pruned. A column whose values are clustered into a few groups prunes the rest. So the most effective layout choice is to cluster on the column you filter by ranges. That column is most often a timestamp.

• pgcolumnar.recluster(table) re-establishes the sort order incrementally and online. It runs under ShareUpdateExclusiveLock, so reads and writes continue. It is the daemon-friendly verb, and a no-op on an already-sorted table.
• pgcolumnar.cluster(table) does the eager, one-shot reorg under AccessExclusiveLock. Use it for a first sort or a full rewrite in a maintenance window, not on a live hot table.
• pgcolumnar.sort_status(table) reports how much of the table is in order. Measure before and after a sort rather than guess.

Read Columnar Chunk Groups Removed by Filter in EXPLAIN (ANALYZE) to confirm pruning happens. Correlation decides it, not intention. A column you believe is ordered, but that arrives interleaved, does not prune until you cluster it.

Keep tables maintained

Deletes and updates leave dead rows in chunk groups. Without maintenance, every scan still reads and filters those rows. Match the verb to the damage:

Verb	What it does	Lock
pgcolumnar.compact(table)	retires row groups that are fully deleted	ShareUpdateExclusiveLock
pgcolumnar.compact_rewrite(table, min_deleted_fraction, max_groups)	rewrites partially deleted groups to drop dead rows	ShareUpdateExclusiveLock
pgcolumnar.recluster(table)	restores sort order online	ShareUpdateExclusiveLock
pgcolumnar.truncate(table)	returns reclaimed end blocks to the OS	ShareUpdateExclusiveLock plus a brief conditional AccessExclusiveLock
pgcolumnar.cluster(table) / vacuum_sorted(table)	eager full reorg	AccessExclusiveLock

Let the daemon carry the routine load. Set pgcolumnar.autovacuum = on; it is off by default. A background worker then calls only the two online verbs, compact_rewrite and recluster. It never calls the AccessExclusiveLock reorgs, so it does not block a session. It wakes every pgcolumnar.autovacuum_naptime (default 60s). It acts when a table crosses pgcolumnar.autovacuum_compact_threshold (default 0.2, the deleted fraction). It also acts on pgcolumnar.autovacuum_recluster_threshold (default 0.05, the appended fraction). The worker registers as an autovacuum-kind process, so it yields when a session asks for a stronger lock.

Drive a manual cadence from the catalog. pgcolumnar.maintenance_due(table) reports whether compaction and reclustering are due under the same fractions. A scheduled job can then skip tables that do not need work.

Run truncate to give disk back. The online verbs reclaim space inside the relation. Only truncate returns end blocks to the operating system. It holds its AccessExclusiveLock briefly and conditionally. It takes the strong lock only for the physical shrink, and yields rather than wait. It cannot run inside a transaction block.

Make queries fast

• Project only the columns you need. Column pruning decodes only the columns a narrow SELECT names. SELECT * decodes everything. On a wide table this is the single biggest reduction in decode cost.
• Late materialization is on by default (pgcolumnar.enable_late_materialization). A row the filter rejects does not have its other columns built. Decode cost then scales with rows emitted. Leave it on.
• Per-vector qual gating skips a no-match vector's payload decode. It only pays off once a query projects enough non-filter columns. The width gate pgcolumnar.qual_skipvec_min_payload_cols (default 20) turns it on for wide projections. It turns off for narrow aggregates, and the default suits most workloads.
• Index-only scans are on by default (pgcolumnar.enable_index_only_scan). A chunk group answers from the index alone once VACUUM marks it all-visible. That happens when the group has no deletes and predates the oldest snapshot. Keep VACUUM current to keep index-only scans effective. Any write clears the bit. The executor always re-checks the snapshot, so an index-only answer never returns a row you should not see.
• Read the plan. EXPLAIN (ANALYZE) reports Files Pruned for the Iceberg wrapper and Columnar Chunk Groups Removed by Filter for a local scan. If a range query is not pruning, the table is not clustered on that column.

Run object storage safely

Remote access to s3://, http://, and https:// endpoints is default-deny. pgcolumnar.objstore_allowed_endpoints is an allow-list, and an empty list allows nothing. It is a superuser-only setting, so a role cannot widen its own reach. Keep it as narrow as the deployment allows.

• Link-local and instance-metadata addresses are refused unconditionally, even when listed. The ranges 169.254.0.0/16 and fe80::/10 are the cloud-credential-theft surface. They have no legitimate object-storage use. You cannot rely on the allow-list alone to block them, and you do not need to.
• Prefer per-role credentials over ambient environment keys. The object-storage foreign-data wrapper takes access_key_id, secret_access_key, session_token, and credentials_required from a foreign server and user mapping. Each role then signs with its own credentials. The secret lives in pg_user_mapping, which is not world-readable, rather than in a process-wide AWS_* environment variable.
• Leave buffered reads on (pgcolumnar.objstore_buffered). They coalesce a column chunk into one request instead of many ranged reads. For a fast link and a large export, raise pgcolumnar.objstore_part_size above its 8 MiB default.
• Grant the server-file roles sparingly. parallel_copy and Parquet reads require pg_read_server_files. parallel_export_parquet requires pg_write_server_files. Both roles also reach remote endpoints through the allow-list, so treat them as privileged grants.

Run Iceberg safely

• The reader is read-only and delete-correct. pgcolumnar.iceberg_scan reads a table at its current snapshot. It applies all three delete kinds: position, equality, and format-version 3 deletion vectors. It never returns a deleted row, and it never writes the table.
• Use the foreign-data wrapper when you filter. A bare iceberg_scan reads every data file. The pgcolumnar_iceberg wrapper receives the query predicate. It prunes whole files by partition and by metrics before it opens them. The partition transforms are identity, bucket[N], truncate[W], and the temporal transforms. Pruning is only ever an optimization, so the wrapper returns exactly the rows the bare scan would. Prefer it whenever you filter.
• Keep the REST catalog token off the wire and out of the log. A per-role bearer token belongs on a pgcolumnar_iceberg_catalog user mapping. It then lives in pg_user_mapping and is never a function argument. So it does not appear in the statement log or pg_stat_activity. For a rotating credential, use OAuth2 client credentials on the mapping instead of a static token. The client secret travels only in the token-request body. The same allow-list and link-local refusal govern every catalog, storage, and OAuth request.

Verify, do not assume

Two settings exist to check your work rather than trust it. Set pgcolumnar.enable_late_materialization or pgcolumnar.enable_index_only_scan to off to compare a query against the optimization. Read sort_status and maintenance_due to measure a table's real state before you schedule work. The optimizations preserve correctness, so any difference you see is performance, not results.