Skip to content

pgColumnar documentation

pgColumnar is a column-oriented storage extension for PostgreSQL, implemented as a table access method. A table created USING pgcolumnar stores its data by column, with per-column compression, chunk-group skipping, and a vectorized aggregate path. It targets analytic workloads: large scans, aggregates, and column projections over append-mostly data.

pgColumnar builds from one source tree on PostgreSQL 15 through 19. It is licensed under the MIT License.

Where to start

If you want to Read
See what pgColumnar provides Features
Install the extension and load it into a server Installation
Create columnar tables, load data, and query them User guide
Operate columnar tables in production Administration
Look up a setting and its default Configuration reference
Look up a pgcolumnar.* function SQL reference
Check release status, type coverage, and known constraints Limitations and compatibility
See size and latency numbers Benchmarks
Run the test suite Testing

When to use columnar storage

A columnar table stores each column separately and compresses it. A read that uses a subset of the columns and scans many rows gets a benefit from this. The scan reads and decompresses only the columns that the query asks for. The minimum and maximum of each chunk also let the scan skip the groups of rows that cannot match a filter.

Use pgColumnar for:

  • Fact tables and event logs that are appended to and read with aggregates or wide scans.
  • Queries that select a few columns from a table with many columns.
  • Data that compresses well and is queried more often than it is updated.

Row storage (the default heap) remains the better choice for high-rate single row updates and deletes, and for point lookups that return whole rows. pgColumnar supports updates, deletes, and indexes, but its storage layout is built for append-mostly data. See Limitations and compatibility.

When not to use pgColumnar

The clearest case against columnar storage is a table whose bytes are mostly one large value per row. An independent validation measured this on 2026-08-05, on a 2,000,000 row ledger table. Each row held 1 KiB of incompressible data. The figures below are theirs.

operation heap pgColumnar
point lookup by indexed uuid 1.0 ms 203 ms 200x slower
ordered 100,000 row full-row segment 42 ms 998 ms 24x slower
full-table full-row export scan 630 ms 1747 ms 2.8x slower
bulk INSERT ... SELECT of 2M rows 19.9 s 42.6 s 2.1x slower
table size 2604 MB 2051 MB 1.27x smaller
narrow GROUP BY aggregate 266 ms 115 ms 2.3x faster

Read the last two rows with the others. Columnar storage still wins the narrow aggregate, which is what it is for. It wins little on size, because only about a quarter of the bytes are the kind that compress. Every row-wise operation loses.

Use heap when most of your bytes are one large value per row. Use heap when point lookups are the main access pattern. Use pgColumnar when queries read a few columns out of many.

How it fits together

A columnar table is an ordinary PostgreSQL relation. It works with transactions, WAL, replication, indexes, COPY, and pg_dump. The extension adds:

  • A table access method named pgcolumnar. New tables are written in the native on-disk format, PGCN v1.
  • A set of catalog tables and functions in the pgcolumnar schema.
  • Planner and executor paths for columnar scans, aggregates, index-only scans, and projections, controlled by settings under the pgcolumnar. prefix.

Design and internals

The documents above are for users and administrators. The design and format specifications are separate: