Skip to content

Releases: commandprompt/pgcolumnar

Release list

v1.0-alpha4

v1.0-alpha4 Latest
Latest

Choose a tag to compare

View all tags
@jdatcmd jdatcmd released this 17 Sep 19:45
v1.0-alpha4
0d3100d
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

What's Changed

Read more

Contributors

jdatcmd, OffgridwithJD, and linuxhikerpm
Assets 2
Loading

pgColumnar 1.0-alpha3

pgColumnar 1.0-alpha3 Pre-release
Pre-release

Choose a tag to compare

View all tags
@jdatcmd jdatcmd released this 03 Sep 14:47
v1.0-alpha3
9628414
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

pgColumnar 1.0-alpha3 release notes

Release date: 2026-09-02
Previous release: 1.0-alpha2 (2026-08-18)

pgColumnar is a columnar table access method for PostgreSQL. This is the third
alpha. Its theme is retention and skipping. Rows can now expire on a declared
interval. A bulk load can refuse work it has already done. Three more predicate
shapes prune whole chunk groups. The on-disk native format, PGCN v1, is
unchanged. Existing tables are read and written as before.

This release requires one upgrade command. See "Upgrading" at the end.

Highlights

  • Retention. pgcolumnar.expire drops row groups whose rows are all older
    than an interval you declare on the table. It works on whole row groups, so it
    reclaims space without rewriting live data.
  • Bulk loads can refuse a repeat. pgcolumnar.parallel_copy records a
    fingerprint of each load. A load it has already taken is refused rather than
    duplicated.
  • Three more predicate shapes prune chunk groups.
    date_trunc(unit, ts) now drives skipping in both its range and its equality
    form, and so do IN (...) and = ANY(array).
  • The scan tells the planner what order it is in. A sorted rewrite leaves an
    ordering behind. The scan now reports it, so the planner can skip a sort.
  • The vectorized aggregate takes a wider range of queries, including a
    target list that itself contains aggregates.

Retention

pgcolumnar.set_options takes ttl_column and ttl_interval.
pgcolumnar.expire then retires every row group whose maximum value in that
column is older than the cutoff. A group is dropped whole. Rows inside the
retention window are never touched.

The interval must be positive. A negative interval would put the cutoff in the
future, which would retire groups whose rows are still current.

Bulk ingest

pgcolumnar.parallel_copy records a fingerprint for each completed load in
pgcolumnar.load_fingerprint. Re-running the same load is refused. This makes a
retried ingest safe to repeat after a failure, without a manual check for
partial work.

Skipping and the planner

A predicate on date_trunc(unit, ts) now prunes chunk groups. Both the range
form and the equality form work. IN (...) and = ANY(array) prune too.

Skip predicates are evaluated most-selective-first, so a group that can be ruled
out cheaply is ruled out first.

The cost model and the zone-map sample now read the row-group geometry a table
was written with, rather than the current setting. Changing a setting no
longer reprices existing data.

Maintenance and reporting

pgcolumnar.vacuum_sorted() self-gates. When the relation is already sorted on
the requested key, it does nothing rather than rewriting the table.

pgcolumnar.sort_status reports sorted_kind, so a reader can tell which kind
of ordering a table carries.

Correctness fixes

Two defects in this list were silent: the operation looked correct and the data
was wrong. Both were found in the review before this release and both were
reproduced before being changed.

  • A projection created mid-transaction missed every write that followed it
    (#875). A write before pgcolumnar.add_projection() in the same transaction
    left the new projection empty of everything written after it. Measured: 116
    rows in the base table, 105 in the projection, with no error raised. A
    covering projection scan then answered as though those rows did not exist. The
    same defect in pgcolumnar.drop_projection() left rows in a projection
    storage whose catalog rows were already deleted.

  • An Arrow import ignored the width, sign and scale the file declared
    (#881). The importer decoded with the target column's parameters instead of
    the file's. A uint64 value above 2^63 was stored as a negative number. An
    int64 file read into an int column returned 1,0,2,0 for 1,2,3,4. A
    decimal(10,2) value of 1.25 was stored as 0.0125. A
    fixed_size_binary(32) was read as its first 16 bytes. All four imported
    without an error and the wrong values were persisted. They are refused now.

    The 1,0,2,0 is worth recognising if you imported integers. The reader took
    its stride from the target column, so four-byte reads walked an
    eight-byte-per-value buffer. Every second read landed on the high half of a
    small positive number, which is zero. The signature is a real value alternating
    with a zero, not a column of ascending garbage.

  • Index entries for live rows are no longer destroyed (#838).

  • A scrollable cursor no longer answers a backward fetch with forward rows
    (#842).

  • sum(bigint) and avg(bigint) no longer accumulate across a rescan (#840).

  • date_trunc(unit, ts) = 'infinity' returns the matching row again (#836).

  • An encoded NUL no longer defeats the Iceberg traversal guard (#844).

  • pgcolumnar.set_options no longer writes past three stack arrays.

  • Deleted rows no longer count toward the planner's row estimate.

  • A custom scan no longer hides the children of an INHERITS parent.

  • TRUNCATE retires the old storage's catalog rows, and a transaction that
    writes, truncates and writes again keeps the rows it should.

  • ALTER TABLE ... SET ACCESS METHOD heap drops the catalogs keyed to the
    relation.

  • A parallel export refuses a destination too long to hold the names it
    generates.

  • pgcolumnar.compact_rewrite and pgcolumnar.maintenance_due reject NaN
    thresholds.

Known issues

  • A rewrite makes a projection read as absent (#876). TRUNCATE, vacuum and
    recluster mint a new storage id, and the projection rows keep the old one. The
    projection is still declared and its data is intact.
    pgcolumnar.rebuild_projections() re-records them. The error message names
    that function.

  • Two visibility-map clears have tests but no verdict (#877). The clears on
    the recluster and partial-rewrite paths gained coverage in this release.
    Whether a defect sits behind them is not known, and there is no reported
    symptom.

    The rule they implement is not speculative. Its third application, on
    pgcolumnar.expire, was a real defect: an index-only scan answered from the
    index for a row group that had been retired. That one is fixed. These two are
    the same rule on two other paths, with no demonstrated symptom on either.

    Of the two issues in this section, #876 can affect you today and has a
    recovery command. #877 has no known user-visible effect and is listed so the
    state is on the record, not because there is something to act on.

Upgrading

Install this build, then run the following in every database that has the
extension:

ALTER EXTENSION pgcolumnar UPDATE;

This is required. The upgrade adds two columns to pgcolumnar.options for
retention and creates the pgcolumnar.load_fingerprint table. It creates
pgcolumnar.expire(regclass), which is the entry point for the retention
feature above. It replaces four more function definitions:
pgcolumnar.parallel_copy and pgcolumnar.set_options are dropped and
recreated at a new signature, and pgcolumnar.maintenance_due and
pgcolumnar.sort_status change in place. No table data is converted and no SQL
you write changes.

See docs/installation.md for the commands, including how to list the databases
that need the update.

Scope and limitations

  • This is an alpha. Interfaces may change before 1.0.
  • Retention drops whole row groups. A group is retired only when every row in it
    is outside the retention window.
  • On PGXN this release is 1.0.0-alpha.3, while CREATE EXTENSION reports
    1.0-alpha3. PGXN requires a semantic version, which needs three integer
    components. The extension's own version has two. The two names refer to the
    same release.

The complete, itemized list of changes is in CHANGELOG.md.

Loading

pgColumnar 1.0-alpha2

pgColumnar 1.0-alpha2 Pre-release
Pre-release

Choose a tag to compare

View all tags
@jdatcmd jdatcmd released this 18 Aug 15:29
v1.0-alpha2
1fbffb1
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

pgColumnar 1.0-alpha2 release notes

Release date: 2026-08-18
Previous release: 1.0-alpha (2026-08-04)

pgColumnar is a columnar table access method for PostgreSQL. This is the second
alpha. It adds read-only Apache Iceberg support, reads and writes over
S3-compatible object storage, a maintenance daemon, and a broad round of
statistics, planner, performance, and security work. The on-disk native format
(PGCN v1) is unchanged; existing tables are read and written as before.

This release requires one upgrade command. See "Upgrading" at the end.

Highlights

  • Apache Iceberg, read-only. Read an Iceberg table at its current snapshot
    three ways: by metadata path, through a REST catalog, or as a foreign table.
    Row-level deletes of all three kinds (position, equality, and format-version-3
    deletion vectors) are applied under their sequence rules, columns resolve by
    schema field id, and the foreign-data wrapper prunes whole data files from a
    query predicate.
  • Object storage. The Parquet and Iceberg readers, the Parquet export
    functions, and the foreign-data wrapper read from and write to s3://,
    http://, and https:// URLs. Remote access goes through a separate module,
    is confined to an operator-set endpoint allow-list, and refuses link-local
    addresses.
  • Maintenance and operations. A new pgcolumnar.autovacuum daemon performs
    online upkeep, pgcolumnar.maintenance_due reports what a table needs, and a
    stripe flush can run across background workers.
  • Security and hardening. Six memory-safety and denial-of-service fixes on
    the read and object-store paths, several from an adversarial audit, each with a
    regression test and a proof that removing the fix reintroduces the failure.

Apache Iceberg support (read-only)

  • Filesystem tables. pgcolumnar.iceberg_scan(metadata_path) reads a table
    given a column definition list. It resolves each output column to a schema
    field id, so a data file written before a column rename still reads. It applies
    position deletes, equality deletes, and format-version-3 deletion vectors
    (Puffin roaring bitmaps), each under its own sequence and scope rule, and
    verifies deletion-vector checksums, offsets, and cardinality. A data file with
    no field ids is bound by the table's schema.name-mapping.default; one with
    neither field ids nor a name mapping is refused rather than guessed. Only
    Parquet data files are read. Recorded paths are rebased onto the table's actual
    location and refused if they resolve outside it. Introspection functions
    iceberg_current_snapshot, iceberg_data_files, read_avro_manifest, and
    read_manifest_list are included.
  • REST catalog. pgcolumnar.iceberg_rest_scan(catalog_uri, namespace, table_name) resolves a table through a catalog and reads it with the same
    projection and delete rules. The first argument may instead name a foreign
    server of the pgcolumnar_iceberg_catalog wrapper, which holds the catalog URI
    in server options and the bearer token or OAuth2 client credentials in a user
    mapping, so one role's secret is private from another and never appears in a
    function argument or the statement log. When the catalog vends short-lived
    storage credentials in its load-table reply, the reader uses them for the data
    files. iceberg_rest_namespaces and iceberg_rest_tables list a catalog.
  • Foreign-data wrapper. A foreign table over an Iceberg table
    (pgcolumnar_iceberg, option metadata_path) receives the query predicate and
    prunes whole data files before opening them: by partition value for identity,
    bucket[N], truncate[W], and the temporal transforms, and by stored
    minimum and maximum for integer and boolean columns. Pruning only removes files
    that cannot match, so results are unchanged, and EXPLAIN (ANALYZE) reports
    Files Pruned.

Object storage

  • The Parquet read and export functions, the Parquet foreign-data wrapper, and
    the Iceberg reader accept s3://, http://, and https:// URLs wherever they
    accept a local path. s3:// requests are signed with AWS Signature Version 4;
    https:// verifies the server certificate when the object-store module is
    built with OpenSSL.
  • Remote access lives in a separate module, pgcolumnar_objstore, loaded on
    first use, so no second TLS stack enters the main server process by default.
  • pgcolumnar.objstore_allowed_endpoints lists the endpoints remote access may
    reach. It is empty by default, which refuses every remote endpoint, and it is
    superuser-only. Link-local and instance-metadata addresses are refused after
    name resolution.
  • Object-store credentials come from the server process environment, never a
    function argument or a log line.

Maintenance and operations

  • pgcolumnar.autovacuum is a maintenance daemon for the online upkeep that core
    autovacuum does not perform on a columnar table.
  • pgcolumnar.maintenance_due(rel, compact_due_fraction, recluster_due_fraction)
    reports whether a table is due for compaction or reclustering.
  • pgcolumnar.parallel_flush dispatches a stripe flush across background workers.
  • pgcolumnar.fsst_verdict_reuse caches a column's FSST keep-or-drop verdict, so
    a repeated write does not re-run the substring search.

Statistics and the planner

  • pgcolumnar.analyze() now collects most_common_vals and most_common_freqs,
    places histogram_bounds at PostgreSQL's own positions, honours the per-column
    statistics target, and counts null_frac over live rows.
  • EXPLAIN (ANALYZE) reports Columnar Usable Skip Predicates beside the skip
    counters.
  • The index-fetch cost penalty sizes row groups by a table's effective
    stripe_row_limit, and the grouped vector aggregate shares the scan node's
    input-cost estimate, so the planner prices a columnar scan more accurately.
  • The Iceberg foreign-data wrapper estimates a scan's row count from the
    manifests rather than a constant, so join planning above a large Iceberg table
    is sound.

Performance

  • A parameterized predicate (col >= $1 from a prepared statement or PL/pgSQL)
    now drives chunk-group skipping. On a generic plan such a scan previously read
    every chunk group.
  • Group and per-vector skipping read only the columns a query's predicates
    reference, rather than every column's zone map. On a wide table a
    one-predicate scan reads far fewer zone-map rows.
  • Reads of the delete_vector catalog use its index rather than a sequential
    scan, so a scan of a table with deletes is no longer proportional to the
    catalog size.
  • The Iceberg foreign-data wrapper decodes only the columns a query references.
  • The ungrouped batch fold gathers only the referenced columns per row, and a
    columnar scan whose filter cannot be pushed down skips decoding the filtered
    columns.

Security

  • The native varlena decoder bounds a value's stored length against its buffer, so
    a corrupt chunk or catalog row is refused with a clean error rather than an
    out-of-bounds read or a detoast through a bad pointer.
  • The local file read path no longer has a stat-before-open race, and the Iceberg,
    Avro, Parquet, Arrow, and parallel-copy readers refuse a FIFO or other
    non-regular file with a non-blocking open rather than a cancel-resistant hang.
  • The Iceberg reader refuses several classes of malformed or hostile table
    metadata, including a null manifest path that had crashed the backend, a null
    or negative position-delete ordinal, a null manifest-list sequence number, and
    a dangling current-schema-id.
  • The Thrift and Avro field-skip loops are interruptible, so a crafted Parquet
    footer or Avro manifest can no longer spin the backend uncancellably.
  • The object-store client refuses a URL path or host carrying CR or LF, closing an
    HTTP request-line injection.
  • The native dictionary decode path no longer reads uninitialized memory, and the
    Parquet dictionary decode path no longer reads out of bounds on a crafted file.

Correctness fixes

  • Concurrent UPDATE or DELETE of the same columnar row serializes on the row
    identity, so the losing writer gets a retryable serialization failure rather
    than a lost update.
  • A predicate on a column declared over a domain, and a bigint column compared
    against an unadorned integer literal, now prune chunk groups.
  • CREATE TABLE ... USING pgcolumnar AS SELECT no longer fails when the source is
    another access method.
  • pgcolumnar.sort_status works for a non-superuser who owns the table.
  • Failed export_parquet and export_arrow no longer leave a partial file.

Internal changes

  • The extension's exported C symbols are namespaced under pgcolumnar, and the
    custom scan node is PgColumnarScan. The native encoding-descriptor wire layout
    and the delete-vector visibility logic are each single-sourced, with the on-disk
    format unchanged and verified byte-identical.
  • default_version is 1.0-alpha2. Upgrade scripts from both previously shipped
    versions (1.0-dev, which the v1.0-alpha tag installed, and 1.0-alpha) ship
    with the extension, so a single ALTER EXTENSION pgcolumnar UPDATE reaches
    1.0-alpha2 from either.

Upgrading

Install this build, then run the following in every database that has the
extension:

ALTER EXTENSION pgcolumnar UPDATE;

This is required. The C-symbol rename moves the symbol names each installed
function recorded when it was created; without the catalog update those records
point at symbols the new library does not export, and reading an existing
columnar table fails with could not find function "columnar_handler". No data
is converted and no SQL you write changes. The upgrade replaces catalog entries
only.

See docs/installation.md for the commands, including how to list the databases
that need the update.

Scope and limitations

  • Iceberg support is read-only, at a table's current snapshot, and reads Parquet
    data files only.
  • Object-storag...
Read more
Loading

pgColumnar 1.0-alpha

pgColumnar 1.0-alpha Pre-release
Pre-release

Choose a tag to compare

View all tags
@jdatcmd jdatcmd released this 04 Aug 17:59
v1.0-alpha
36350b9
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

First tagged release of pgColumnar: columnar storage for PostgreSQL, written as a
clean-room, MIT-licensed implementation. Tables are created USING pgcolumnar after
CREATE EXTENSION pgcolumnar, and are written in the native on-disk format PGCN v1.

Supported: PostgreSQL 15, 16, 17, 18 and 19 beta.

What it does

  • Column-oriented table access method with per-column compression (none, pglz,
    lz4, zstd), an adaptive per-vector encoding cascade, zone maps for row-group
    and per-vector skipping, and per-chunk bloom filters.
  • Column projection, on by default: a scan reads only the columns a query
    references. Worth 6.9x on a single-column aggregate over a 100M-row, 21-column
    fixture.
  • Full DML: INSERT, UPDATE, DELETE, index scans, index-only scans through a
    columnar visibility map, unique constraints, foreign keys and triggers.
  • Parallel scan, and parallel COPY loading through pgcolumnar.parallel_copy.
  • Arrow and Parquet import and export, including a Parquet foreign-data wrapper.
  • Clustering and maintenance: vacuum_sorted, declarative sort_by, online
    recluster, sort_status, compaction and space reclaim.
  • Opt-in vectorized aggregates, grouped and ungrouped, serial and parallel.

Measured

100,000,000-row TSBS cpu fixture, 21 columns, all engines on the same host with the
same (hostname, time DESC) index. Full tables and method in
docs/benchmarks.md.

Storage, the same 100M rows:

heap pgColumnar (zstd) TimescaleDB columnstore
22 GB 6,590 MB (3.4x smaller) 7,975 MB

Query time, stated plainly. Against heap, pgColumnar wins the wide scan-bound
aggregates (q4, q5, q6, q8) by roughly 20 to 50 percent and loses the one-host
queries. Against TimescaleDB it is slower on every query TimescaleDB completes,
by 1.6x on q4 and q5 and by 101x, 442x and 679x on q1, q2 and q3.

The one-host gap has a known cause and it is a layout one: this fixture is stored in
load order, which is sorted on neither key (measured correlation 0.013 for time,
-0.004 for hostname), so the zone maps skip nothing. Measured on q2, pgColumnar
reads 667 of 667 row groups and filters 99,995,680 rows to return 4,320. A user with
this shape would cluster on hostname, which is what TimescaleDB's segmentby does
for it. That configuration is not measured here, so the q1-q3 gap should not be
read as a property of columnar storage until it is.

Verification

  • Full five-major matrix: 112 suites on each of PostgreSQL 15, 16, 17, 18 and 19.
    ALL VERSIONS PASSED, zero failures.
  • Sanitizer gate: ASAN + UBSAN, violations fatal, over the write, read, encode and
    import/decode paths.
  • CI builds every major and runs the suites on 17 and 18 for every pull request.

Known limitations

This is an alpha and its sharp edges are written down rather than left in the tracker.
The full list is in CHANGELOG.md
and docs/limitations.md:

  • Point lookups are slower than heap, and the cost of a fetch grows with table
    width: an index fetch decodes the attribute prefix up to the highest-numbered
    column the query reads, not only the columns it reads.
  • The index-fetch cost model is bounded, not modelled against the plan above it,
    so a consumer that stops early inherits more of the penalty than it should
    (#376).
  • The grouped vectorized aggregate's parallel arm is declined on an expression
    grouping key, because the core Finalize is priced off a group estimate that can be
    25x to 42x wrong (#369).
    Off by default.
  • The by-row-number fetch cache is bounded by 4 x (cap + retained position indexes + groupBuffer) rather than 4 x cap; a table of many wide varlena columns
    measured one 62 MB entry against a 32 MB cap
    (#364).
  • Bulk load is slower than heap on most shapes, from 0.67x to 8.83x depending on
    column type and width.

A note on the extension version

VERSION and this tag read 1.0-alpha, but the extension's own default_version is
still 1.0-dev, so SELECT extversion FROM pg_extension reports 1.0-dev. That
field governs ALTER EXTENSION UPDATE and moving it needs an upgrade script that does
not exist yet; breaking installs to tidy a version string is the wrong trade. It will
move with the first release that ships one.

Installation

See docs/installation.md.
make && make install, then CREATE EXTENSION pgcolumnar;.

The clean-room record is in
PROVENANCE.md.

Loading

pgColumnar v1.0-dev

pgColumnar v1.0-dev Pre-release
Pre-release

Choose a tag to compare

View all tags
@jdatcmd jdatcmd released this 21 Jul 17:16
v1.0-dev
65e2cb6
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

pgColumnar v1.0-dev is a pre-release marker for the 1.0 development line
(on-disk format 2.2). It builds from one source tree on PostgreSQL 13 through 19,
and every test suite passes on all seven majors.

Capabilities

  • Column-oriented table access method (USING columnar) with per-column
    compression, chunk-group minimum and maximum skipping, per-chunk bloom filters,
    and a vectorized scan and aggregate path with late materialization.
  • Compression codecs none, pglz, lz4, and zstd.
  • count(*) from catalog metadata, parallel scan, and read stream prefetch on
    PostgreSQL 17 and later.
  • Full index-only scan through a columnar visibility-map fork, on by default.
  • Multiple projections (C-Store model): declared column subsets stored sorted,
    with a planner projection scan, on by default.
  • Sorted storage with columnar.vacuum_sorted.
  • Arrow and Parquet export and import, flat and nested (one-dimensional arrays
    and composite types), self-contained with no libarrow or libparquet dependency.

Documentation

Installation, user guide, administration, configuration, SQL reference,
features, benchmarks, limitations, and testing are under
docs/. The full list of
changes is in
CHANGELOG.md.

Status

This is a pre-release. The VERSION file is the source of truth for the release
marker. PostgreSQL 19 is validated against 19beta2; revalidation against the
final release is pending.

Loading