pgColumnar Extension Reaches Version 1.0-alpha4 with Hilbert Clustering and Join Filter in Postgres

PostgreSQL doesn't have native columnar storage, but since version 12 it has had a stable API for custom table access methods, the same mechanism that allows swapping heap for any other way of storing rows on disk. It's on this API that Joshua D. Drake builds pgColumnar: an extension that registers a columnar TAM inside Postgres, without forking the database and without a middleware layer in front of queries. He published the release notes for 1.0-alpha4 on Planet PostgreSQL, released on September 17, 2026, and the theme of this round is quite specific: the physical layout of data and the optimizer's ability to skip work it doesn't need to do.

For anyone running analytics on top of a Postgres already in production, this matters for a direct reason: normally the way out for OLAP in this scenario is to replicate data to a Citus, a ClickHouse, a DuckDB via FDW, or accept the cost of sequential scans on giant heap tables. pgColumnar proposes solving this without leaving the relational database, with CREATE TABLE ... USING pgcolumnar. The on-disk format, PGCN v1, remains unchanged in this release, which means existing tables continue to be read and written exactly as before: anyone already in production with the extension doesn't need to convert anything.

Hilbert versus Z-order: what changes in the execution plan

The most structural novelty is Hilbert curve clustering. Until now pgColumnar organized data in Z-order (Morton order), a technique common in multidimensional indexes for being cheap to compute. The problem with Z-order is that it has jumps at bit boundaries: two numerically close keys can end up in distant positions in the physical ordering, which forces the executor to scan more chunk groups than it should for a range filter.

The Hilbert curve doesn't have these jumps, and the extension exposes this via two new functions: pgcolumnar.cluster_hilbert(table, VARIADIC columns), which rewrites the table under AccessExclusiveLock (the same cost as a CLUSTER or VACUUM FULL), and pgcolumnar.recluster_hilbert(table, VARIADIC columns), which does the same work online under ShareUpdateExclusiveLock, keeping reads and writes running during the reorganization. Drake reports that, in a test with 200,000 rows across two columns, Hilbert ordering read 1.24x to 2.04x fewer chunk groups than Z-order for the same range filter.

The practical recommendation that comes with the number is sober, and it's the same one any experienced DBA would give: Z-order remains good for point lookups, Hilbert wins on range filters across multiple columns, and the advantage shrinks as the filter's search box grows. In other words, it's not an automatic migration. Before running recluster_hilbert in production it's worth measuring your own data corpus, and the extension helps with that: pgcolumnar.sort_status reports how much of a table is actually in physical order.

It's worth noting a design detail in the API: why two new verbs instead of one parameter in the existing cluster function? Because PostgreSQL doesn't allow extending the cluster(regclass, VARIADIC name[]) signature with an optional parameter before a VARIADIC, in either direction. It's a limitation of the PL language, not an arbitrary choice by the project.

Star Schema Join Gains an Automatic Filter

The second major change targets the most common analytics pattern: a hash join between a large columnar fact table and smaller dimensions. Starting with this version, a serial inner Hash Join builds, from the join keys it has already hashed on the build side, a filter that does two things at once: it uses a key range to skip entire chunk groups in the fact table, and it uses a Bloom filter to reject rows that certainly won't match.

The release notes bring three measured scenarios that explain why the pgcolumnar.enable_join_runtime_filter parameter comes enabled by default for everyone:

• Fact table clustered by the join key: 19 of 20 chunk groups eliminated, only 1 read.
• Spread-out fact table (no clustering by the key): 0 groups eliminated, but the Bloom filter still rejects more than 15,000 of 19,800 non-matches.
• Build side too large: the Bloom filter disables itself.

This third case is the safety argument: a filter that doesn't help at all turns itself off, instead of becoming overhead. But eliminating entire groups only happens if the fact table is clustered by the join key, which directly connects back to the decision to use cluster_hilbert or recluster on the right column. It's worth noting the limitations: the filter only applies to a serial inner Hash Join over a direct columnar scan, and doesn't cover LEFT, SEMI, ANTI, CROSS, parallel scans, or scans with projection. A vectorized aggregate without grouping also still runs on top of an inner Hash Join with a unique key on the dimension side, since a dimension with a unique key works as a pure filter on the fact table; dimensions with duplicate keys and LEFT joins remain on the traditional plan.

Fixes That Matter to Those Who Trust Critical Data to the Extension

Besides the two major features, this alpha's bug fix list deserves attention from anyone evaluating whether to place pgColumnar near data that matters. The most serious: a column chunk whose recorded length exceeded 4 GB was being cast down to 32 bits in the index-fetch path. In practice, this could make the fetch read the wrong bytes and report them as valid data, silently. Now both cast points raise the XX001 error instead of returning garbage. It's the kind of bug that only shows up in tables large enough to have chunks in that range, but it justifies not treating an extension in alpha stage as ready for data that can't be wrong.

Other fixes from the same round: the validity bitmap copy in a coalesced fetch now respects the chunk boundary before running, closing off a potential read beyond the buffer. Projections now survive DDL: a rewrite re-records its projections, RENAME COLUMN propagates the new name, DROP COLUMN is refused when a projection depends on the column, and in-place TRUNCATE clears each projection's storage along with the base. The block codec also stopped leaking buffers on the two paths that abandoned them without freeing.

Planner and Parallelism: Adjustments That Save Real I/O

Three smaller fixes, but with a direct practical effect on execution cost: reads of adjacent columns in the same row group, in the index-fetch path, are now coalesced into a single I/O read instead of one per column. Parallel index construction now actually distributes the work among the launched workers, something that didn't happen before: a backend read the entire table alone while the parallel workers sat idle, a waste of allocated resources any DBA would recognize by looking at the plan. And the parallel scan cost in the optimizer stopped dividing I/O by the number of workers, since PostgreSQL splits CPU among workers but leaves the disk work whole; now the columnar cost reflects this correctly. As a consequence, the planner stopped preferring an index scan with fetch that did much more work: a correlated range of 50,000 rows started choosing the pure columnar scan instead of the index scan, because the fetch penalty now charges a per-row term, capped at half a chunk group.

What Remains Open

Two known problems remain documented as such, unfixed in this version: adjusting the compression codec can increase the size of a table with high-entropy text (the writer only keeps FSST when it beats the alternative by a configurable margin, and never compares storing FSST codes without further compression); in a test with 200,000 rows of random hex text, zstd wrote 1.777% more than with no compression at all. And the block codec compresses a region it later discards, costing about 25% more write CPU on incompressible data, without affecting what is read or stored.

Upgrading and What to Consider Before Adopting

The update requires running ALTER EXTENSION pgcolumnar UPDATE; on every database that uses the extension. It's the smallest upgrade in the series so far: it only creates the two Hilbert clustering functions, without converting existing data, without replacing any function, and without changing any SQL already written. It's worth noting a versioning curiosity: on PGXN this release appears as 1.0.0-alpha.4 (semantic versioning requires three integer components), while CREATE EXTENSION reports 1.0-alpha4, the two-component format the extension itself uses internally. They're the same release with two labels.

For anyone evaluating pgColumnar as an OLAP path within an existing Postgres stack, the point that deserves caution is the usual one with alpha-stage software: the interfaces can still change before 1.0, and Hilbert clustering sorts entire row groups on rewrite, which means a table under continuous writes degrades that order until the next reorganization. This doesn't invalidate the project, but it defines where it serves well today: analytical workloads with predictable maintenance windows, not systems that require perfect ordering at all times without intervention. Before betting on production, the sensible path remains the usual one: look at the actual EXPLAIN for your own workload, compare with enable_join_runtime_filter on and off, and decide on the clustering curve based on the table's sort_status, not on the expectation generated by changelog numbers.