How-to guides¶

Task-focused recipes for each pgColumnar feature. Each recipe ends with a short tuning note, and a permissions note where one applies. For the advice behind these tuning notes, collected by decision rather than by task, see the Best practices guide. For full function signatures see the SQL reference. For every server setting see the Configuration reference.

Load pgcolumnar in shared_preload_libraries before you begin. It installs the planner and executor hooks that every backend needs.

Create a columnar table¶

Create a new table with the access method, or convert an existing heap table.

-- new table
CREATE TABLE events (id bigint, ts timestamptz, region text, amount numeric)
  USING pgcolumnar;

-- convert an existing table (heap to columnar, or back)
SELECT pgcolumnar.alter_table_set_access_method('events', 'pgcolumnar');

On PostgreSQL 15 and later the conversion runs ALTER TABLE ... SET ACCESS METHOD in place and keeps the table identity and dependents. On 13 and 14 it copies through a new table and swaps the names, so the original OID and its dependent objects are not preserved.

Tuning. Columnar storage rewards wide scans over a few columns. Keep small, highly selective point-lookup tables as heap. Put analytic and append-heavy tables on pgcolumnar.

Load data efficiently¶

COPY and multi-row INSERT both work. A bulk load writes best in large transactions, because each transaction seals its own row groups.

COPY events FROM '/data/events.csv' WITH (FORMAT csv, HEADER true);

Load a local file in parallel with a pool of background writers.

SELECT pgcolumnar.parallel_copy('events', '/data/events.csv');   -- workers auto
SELECT pgcolumnar.parallel_copy('events', '/data/events.csv', 8);

Tuning. Many small commits produce many small row groups, which read less efficiently. Run pgcolumnar.vacuum after a trickle load to combine them.

Permissions. The caller of parallel_copy needs the pg_read_server_files role.

Choose compression and encoding¶

Each column is encoded automatically (run-length, frame-of-reference, bit-packing, delta, delta-of-delta, Gorilla XOR, dictionary, and FSST for text). A block codec then compresses the encoded chunk. The codec is zstd by default.

-- per table
SELECT pgcolumnar.set_options('events', compression => 'zstd', compression_level => 6);
SELECT pgcolumnar.set_options('events', encode_effort => 'fast');

-- session default for new chunks
SET pgcolumnar.compression = 'lz4';

The codec is one of none, pglz, lz4, or zstd. compression_level applies to zstd and ranges from 1 to 22, default 3. encode_effort is full by default, or fast to skip the costly FSST search on a text-heavy load.

Tuning. Use zstd for the best ratio and lz4 for the fastest decompression. Raise compression_level toward its maximum of 22 for cold data that is written once and read rarely. Set encode_effort to fast when a text load is CPU-bound and the ratio matters less. Re-run pgcolumnar.vacuum to re-encode existing data under new options.

Sort a table to skip more chunk groups¶

pgColumnar records a per-chunk minimum and maximum in a zone map. A scan skips a chunk group when its filter cannot match that range. Sorting the table on the columns you filter tightens those ranges, so more groups are skipped.

-- one-shot ascending sort on a key
SELECT pgcolumnar.vacuum_sorted('events', 'region', 'ts');

-- declare a key, then re-apply it later with no arguments
SELECT pgcolumnar.set_options('events', sort_by => ARRAY['region','ts']);
SELECT pgcolumnar.vacuum_sorted('events');

-- Z-order (Morton) clustering over several numeric columns at once
SELECT pgcolumnar.cluster('events', 'customer_id', 'amount');

vacuum_sorted sorts ascending and tightens the first column most. cluster uses a Z-order curve, so filters on more than one of its columns all skip more groups. cluster takes numeric columns only.

Tuning. Sort on the column your selective queries filter on. A sort is a trade-off: it groups one dimension tightly while spreading the others. For a live table use pgcolumnar.recluster, which re-establishes the same Z-order under a weaker lock so reads and writes continue. Read pgcolumnar.sort_status to see how much order remains, and re-sort when it decays.

SELECT pgcolumnar.recluster('events', 'customer_id', 'amount');   -- online
SELECT * FROM pgcolumnar.sort_status('events');

Skip more chunk groups on equality filters¶

A bloom filter skips a chunk group when an equality filter names a value the group does not hold. It complements the minimum and maximum, which skip range filters only. Bloom filtering is on by default (pgcolumnar.enable_bloom_filter).

EXPLAIN (ANALYZE) SELECT * FROM events WHERE customer_id = 42;

Tuning. It works best on a clustered or sorted column, because equal values then sit in few chunk groups. Read Columnar Chunk Groups Removed by Filter to confirm the skip. A scattered high-cardinality column gains little. Turn the feature off with SET pgcolumnar.enable_bloom_filter = off to compare.

Add a projection for a second sort order¶

A table has one physical sort order. A projection stores a column subset a second time under a different sort key, so a second access pattern also prunes well. The planner reads the projection instead of the base table when the projection covers every column the query needs and gives a lower cost.

SELECT pgcolumnar.add_projection(
    'events', 'events_by_customer',
    columns  => ARRAY['customer_id', 'amount', 'ts'],
    sort_key => ARRAY['customer_id']);

EXPLAIN SELECT sum(amount) FROM events WHERE customer_id = 42;

Adding a projection fills it from the existing rows. Later inserts write to the base table and to every projection, so each projection adds write cost. Drop one with pgcolumnar.drop_projection('events', 'events_by_customer').

Tuning. Add a projection only for a hot access pattern the base sort order serves poorly. Confirm the plan shows Columnar Projection. Keep the column list minimal, because a projection that misses one queried column is never used. See Projections in the administration guide for detail.

Add indexes and use index-only scans¶

A columnar table supports B-tree and other index types. An index helps a highly selective point lookup that no chunk-group skip can serve. An index-only scan answers a query from the index alone when the chunk groups it reads are all visible.

CREATE INDEX ON events (order_id);
VACUUM events;
EXPLAIN (ANALYZE) SELECT order_id FROM events WHERE order_id = 9001;

Tuning. Keep VACUUM current, because any write clears the all-visible mark and forces a fetch from the row data. pgcolumnar.enable_index_only_scan and pgcolumnar.enable_index_fetch_penalty shape when the planner prefers an index over a scan. Prefer a sort key or a projection for a range query. Reserve an index for a selective point lookup.

Reclaim space after deletes and updates¶

A delete or update marks rows dead. Space returns when you compact.

SELECT pgcolumnar.compact('events');                    -- retire fully-dead groups
SELECT pgcolumnar.compact_rewrite('events', 0.3);       -- rewrite groups >= 30% dead
SELECT pgcolumnar.vacuum('events');                     -- combine small groups + reclaim
SELECT pgcolumnar.truncate('events');                   -- return end blocks to the OS

compact and compact_rewrite hold only ShareUpdateExclusiveLock, so they run against a live table. compact_rewrite rewrites groups whose dead fraction is at least min_deleted_fraction (default 0.2), and max_groups caps how many one call rewrites (0 means no cap). vacuum rewrites the whole relation.

Tuning. Prefer compact and compact_rewrite for online reclaim. Reserve vacuum for a full reorganization window. Cap compact_rewrite with max_groups to bound each call and keep it incremental.

Keep tables optimized automatically¶

A background daemon can compact and recluster tables on a schedule. The threshold values below are examples, not the defaults.

-- postgresql.conf
pgcolumnar.autovacuum = on
pgcolumnar.autovacuum_naptime = '60s'
pgcolumnar.autovacuum_compact_threshold = 0.2
pgcolumnar.autovacuum_recluster_threshold = 0.1

Tuning. Lower the thresholds to keep tables tighter at the cost of more background work. The daemon calls the online operations only, so it does not take an exclusive lock. A table whose clustering is already intact reclusters as a fast no-op, so the daemon does not churn storage.

Read and write Parquet¶

Read a Parquet file directly, inspect its schema, or load it into a table.

SELECT * FROM pgcolumnar.read_parquet('/data/events.parquet')
  AS t(id bigint, ts timestamptz, region text);
SELECT * FROM pgcolumnar.parquet_schema('/data/events.parquet');
SELECT pgcolumnar.import_parquet('events', '/data/events.parquet');

Export a table to one file, or in parallel to a directory of files.

SELECT pgcolumnar.export_parquet('events', '/data/events.parquet');
SELECT pgcolumnar.parallel_export_parquet('events', '/data/events_out', 8);

Expose a Parquet file, directory, or Hive layout as a foreign table.

CREATE SERVER pq FOREIGN DATA WRAPPER pgcolumnar_parquet;
CREATE FOREIGN TABLE events_parquet (id bigint, ts timestamptz, region text)
  SERVER pq OPTIONS (path '/data/events', partition_columns 'region');

parallel_export_parquet writes one part-NNNN.parquet file per worker into the directory, and read_parquet or the foreign table reads the directory back as one relation.

Tuning. Project only the columns you need, because the reader reads only those columns. A predicate on a partition_columns column removes whole files before they open. EXPLAIN (ANALYZE) reports Files Pruned and the row groups read and skipped.

Permissions. Reading needs the pg_read_server_files role, and parallel_export_parquet needs pg_write_server_files.

Import and export Arrow¶

The Arrow IPC file format works the same way as Parquet.

SELECT pgcolumnar.import_arrow('events', '/data/events.arrow');
SELECT pgcolumnar.export_arrow('events', '/data/events.arrow');

Tuning. Use Arrow to exchange data with an in-process analytic engine without a Parquet encode step. Use Parquet for durable, compressed files.

Use object storage¶

Every path that accepts a local path also accepts an s3://, http://, or https:// URL. Credentials come from the server process environment.

SELECT * FROM pgcolumnar.read_parquet('s3://bucket/events.parquet')
  AS t(id bigint, region text);
SELECT pgcolumnar.export_parquet('events', 's3://bucket/events.parquet');

Set the endpoint allow-list first, because it gates every remote scheme.

-- postgresql.conf (a superuser setting)
pgcolumnar.objstore_allowed_endpoints = 's3.amazonaws.com, minio.internal'

The environment supplies AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_SESSION_TOKEN, AWS_REGION, and AWS_ENDPOINT_URL. A host that resolves to a link-local or instance-metadata address is refused even when it is listed.

Tuning. Raise pgcolumnar.objstore_part_size to use larger multipart parts over a high-latency network. Choose pgcolumnar.objstore_s3_addressing to match your provider's path or virtual-host style. Keep the allow-list as small as your buckets require.

Query an Apache Iceberg table¶

Read an Iceberg table at its current snapshot from a metadata path.

SELECT region, sum(amount) FROM pgcolumnar.iceberg_scan(
  '/warehouse/db/events/metadata/00042.metadata.json')
  AS t(id bigint, region text, amount int)
  GROUP BY region;

SELECT * FROM pgcolumnar.iceberg_current_snapshot('.../00042.metadata.json');
SELECT * FROM pgcolumnar.iceberg_data_files('.../00042.metadata.json');

The reader resolves each output column to a schema field id, so a file written before a column rename still reads. It applies position deletes, equality deletes, and format-version 3 deletion vectors. The metadata path may be a local path or an s3://, http://, or https:// URL.

Tuning. Select only the columns you need. Read from object storage under the same allow-list as every other remote access.

Permissions. The function requires the pg_read_server_files role.

Query an Iceberg REST catalog¶

Name a table by catalog, namespace, and table instead of a metadata path.

SELECT * FROM pgcolumnar.iceberg_rest_scan(
  'https://catalog.example.com', 'analytics', 'events')
  AS t(id bigint, region text, amount int);

SELECT * FROM pgcolumnar.iceberg_rest_namespaces('https://catalog.example.com');
SELECT * FROM pgcolumnar.iceberg_rest_tables('https://catalog.example.com', 'analytics');

For per-role credentials, name a foreign server instead of a URI. The server holds the catalog URI. The current role's user mapping holds the bearer token, kept in pg_user_mapping where it is not world-readable. On a multi-warehouse catalog, add a warehouse server option; it is sent on the config request.

CREATE SERVER cat FOREIGN DATA WRAPPER pgcolumnar_iceberg_catalog
  OPTIONS (catalog_uri 'https://catalog.example.com', warehouse 'analytics_wh');
CREATE USER MAPPING FOR analyst SERVER cat OPTIONS (token 's3cr3t');

SELECT * FROM pgcolumnar.iceberg_rest_scan('cat', 'analytics', 'events')
  AS t(id bigint, region text, amount int);

A user mapping may carry OAuth2 client credentials rather than a static token. The catalog then mints a short-lived bearer.

CREATE USER MAPPING FOR analyst SERVER cat OPTIONS (
  oauth_client_id 'app', oauth_client_secret 's3cr3t', oauth_scope 'catalog');

Tuning. A catalog can vend short-lived storage credentials in its reply. iceberg_rest_scan then reads the table files with those credentials, not the server environment. The client secret travels in the request body, never a URL or a log line.

Prune Iceberg data files with the foreign-data wrapper¶

iceberg_scan receives no query predicate, so it cannot skip files. The foreign-data wrapper gives Iceberg a predicate-bearing scan that prunes whole files.

CREATE SERVER ice FOREIGN DATA WRAPPER pgcolumnar_iceberg;
CREATE FOREIGN TABLE events (id bigint, region text, amount int)
  SERVER ice OPTIONS (metadata_path '/warehouse/db/events/metadata/v3.metadata.json');

-- reads only the matching files; EXPLAIN ANALYZE shows "Files Pruned"
SELECT sum(amount) FROM events WHERE region = 'eu';

Partition pruning covers identity, bucket[N], truncate[W], and the temporal transforms. It prunes year, month, day, and hour on a timestamp or timestamptz column, and year, month, and day on a date column. Metrics pruning covers integer and boolean columns by their stored minimum and maximum. Pruning never changes the rows returned. A predicate the wrapper cannot decide reads the file and returns the same rows.

Tuning. Filter on a partition column to remove whole files. Filter on an integer or boolean column to prune by metrics. Confirm the effect with EXPLAIN (ANALYZE), which reports Files Pruned. A predicate on an unsupported type still returns correct rows, only without the pruning.

Tune concurrent writes¶

pgColumnar serializes two conflicting writes so neither is lost. A concurrent insert of the same unique key, and a concurrent UPDATE or DELETE of the same row, each take a transaction-scoped advisory lock. The losing writer gets a retryable error, which the application repeats.

# postgresql.conf (both bucket counts are read at server start only)
pgcolumnar.row_lock_buckets = 4096
pgcolumnar.unique_lock_buckets = 512

Tuning. pgcolumnar.enable_row_update_lock and pgcolumnar.enable_unique_insert_lock are on by default and should stay on for correctness. Raise row_lock_buckets or unique_lock_buckets for a workload with many concurrent single-row writes, so fewer unrelated rows share a bucket. Retry a serialization_failure (SQLSTATE 40001) in the application. See Concurrency in the limitations guide.

Vectorize a GROUP BY aggregate¶

The vectorized aggregate path computes an ungrouped aggregate over decoded vectors. An opt-in setting extends it to GROUP BY.

SET pgcolumnar.enable_group_vectorization = on;
EXPLAIN (ANALYZE) SELECT region, sum(amount) FROM events GROUP BY region;

Tuning. Turn it on for a GROUP BY with a bounded number of distinct groups. The plan then shows Columnar Vectorized Group Keys. A query that exceeds pgcolumnar.groupagg_max_groups (default 1000000) raises an error, so leave the setting off for high-cardinality grouping.

Count rows quickly¶

An unfiltered count(*) reads the per-chunk-group row counts from metadata and decodes no column data.

EXPLAIN (ANALYZE) SELECT count(*) FROM events;

Tuning. A filter, or a table with deleted rows, forces a fold over the surviving rows and decodes the filtered columns. Keep the filter on a sorted or bloomed column so chunk-group skipping removes most groups first.

Measure and introspect¶

Inspect physical layout, sort quality, and query plans.

SELECT * FROM pgcolumnar.stats('events');          -- per-row-group rows, dead rows, size
SELECT * FROM pgcolumnar.sort_status('events');    -- sorted vs appended groups
SELECT pgcolumnar.analyze('events');               -- refresh planner statistics
EXPLAIN (ANALYZE) SELECT sum(amount) FROM events WHERE region = 'eu';

EXPLAIN (ANALYZE) on a columnar scan reports Columnar Pushed-Down Filters, Columnar Usable Skip Predicates, and Columnar Chunk Groups Removed by Filter.

Tuning. Read Columnar Chunk Groups Removed by Filter to confirm a filter skips data. A low removal count on a selective filter means the sort key does not match the query. Re-sort on the filtered column, then check the count again.

Benchmark your own workload¶

Reference numbers live in the benchmarks guide. Measure your own tables the same way.

\timing on
EXPLAIN (ANALYZE, BUFFERS) SELECT sum(amount) FROM events WHERE region = 'eu';

Tuning. Warm the cache with one run and time the next. Read the Columnar counters in the plan to confirm the query took the path you measure, on both the columnar table and any row-store baseline. Compare a columnar table against a heap table of the same rows, not against a different query. Load enough rows to fill the row groups, because a small table hides the skipping and decode effects.