pgColumnar 1.0-alpha3 release notes

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

Github

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⁶³ 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.

I use PostgreSQL at work. It's critical for our operations, and yet no one in our team is an expert. My knowledge is cobbled together from general SQL knowledge (university and 10+ years work) and lots of articles, stack overflow, and trial & error (and more recently some AI Q&A) for PostgreSQL in particular. I've been using it for many years now and we have made many objective improvements over that time.

Still, I'm working on guesswork and magic most of the time. I have a rough intuition of how it does things, but have never had it spelled out in full. I've looked through this sub-reddit for book recommendations but most of what I can see are self-promotes, which are difficult to gauge for quality/purpose; or "how to use PostgreSQL" books. I could probably learn a decent amount from the latter by skimming past the parts I know (though I unfortunately get de-motivated quickly by a book when I have to do this). But ideally I'd like something that digs into how PostgreSQL's internals actually work. How does it marshall the computer's resources to do what it needs to do? And then at the mid-level, how does its engine optimise queries, and how does it maintain itself? I understand this is always changing and could go very deep but some fundamentals would really help me have a stronger intuition about it.

Does anyone have any book recommendations in this vein? Or maybe the more appropriate book is more generally about relational database engines in general?

I'm looking for best practices to keep documentation in my schema.

I prefer the look of `--` and `/* ... */` comments, as they (a) get proper syntax highlighting, (b) can go anywhere (line before, line after, same line at the end), (c) look good when split over multiple lines and (d) can even go inside a statement (when using `/* .. */`).

But when using migrations these code comments may end up in place I'm not looking at.

So I want to have some sort of schema dump that includes my comments. And I know for this there's the `COMMENT ON` command, but I've never seen it being used in practice. It seems so cumbersome compared to the code-comments mentioned earlier.

Any best practices someone can share with me? Both related to "the keeping of schema comments" and the dumping of a schema that properly groups related statements (create, create policy, comment on, etc.) together?

We recently run into quite nasty edge case with Debezium + PostgreSQL failover. Everything looked healthy after failover, connector was running, database was fine, no obvious errors. But problem was replication slot on new primary could start ahead of offset stored by Kafka Connect. Basically you can end with situation where Debezium thinks it should continue from one LSN, while Postgres already lost part of WAL it needs. And connector can look “healthy” while you actually have a gap in CDC. We tested few approaches with Patroni and PostgreSQL 16, and also looked into what changes with PG17 failover slots.

In a rolling deployment, web processes may update quickly while background workers continue running old code against queued jobs. A direct column rename or drop can therefore break work that started before the deploy. What migration sequence do you use for this? An expand-and-contract approach could add the replacement column, deploy code that can read both and writes the new one, backfill in bounded batches, verify old-worker and queue age, switch reads, stop the dual write, and only then remove the original column. Which PostgreSQL-specific checks make that safe: dependency inspection, lock-timeout settings, NOT VALID constraints, catalog or query monitoring, and a minimum observation window? How do you prove no old process still references the column before the final DDL?

Adding some context. This came from a problem I ran into while managing the Postgres team at Cloudflare: BI teams wanted long-running queries, so we often spun up dedicated read replicas. But read replicas still have tradeoffs around hot_standby_feedback and max_standby_streaming_delay.

Streambed started as:

Postgres WAL → S3 → query from psql

Iceberg was the first target, but real-world CDC looks more like:

small batch → commit → small batch → commit

Small commits keep data fresh, but they also create files, manifests, metadata, and copy-on-write work. So I tested DuckLake as a target format.

One benchmark slice: 1M rows, 100k updates, flush=1,000.

Iceberg COW: 269s. DuckLake + DuckDB catalog: 6.6s. Roughly 41x faster for this specific Streambed CDC-style workload.

Caveat: this bypasses Postgres logical replication and psql-wire; it measures the lakehouse writer/catalog path over local MinIO.

Blog: https://streambed.dev/blog/ducklake-target-support/

PostgreSQL 18 ships three async I/O modes via io_method, and the default is worker, not io_uring. On my cold seq scan benchmark io_uring was the fastest of the three: 1.60s vs 1.88s for worker and 2.65s for sync. Measured on a VM, so treat the ratio as the finding, not the absolute numbers.

Enabling it is one setting plus a restart:

sudo -u postgres psql -c "ALTER SYSTEM SET io_method = 'io_uring'"

sudo pg_ctlcluster 18 main restart

sudo -u postgres psql -tAc 'SHOW io_method' # must print io_uring

After enabling, you can watch it actually work. I wrote an eBPF tool (uringscope) that attaches to the kernel's io_uring tracepoints and shows what Postgres submitted, per-request latency, and how many reads detoured through kernel worker threads:

curl -LO https://github.com/rch0wdhury/uringscope/releases/latest/download/uringscope-$(uname -m)

chmod +x uringscope-$(uname -m) && sudo mv uringscope-$(uname -m) /usr/local/bin/uringscope

sudo uringscope -a -d 20 # then run a seq scan in another session

Check https://github.com/rch0wdhury/uringscope

Disclosure: I'm the author of the tool.

I've recently become pretty interested in the Postgres wire protocol after working on a few projects that required understanding it in more detail.

So I ended up building https://pgwire-explorer.dhuk.net/

It’s an interactive tool for exploring Postgres protocol messages, including their structure and fields, while also letting you see the actual bytes that would be sent over the wire and how those bytes map back to each part of the message.

I originally built it to help myself learn, but figured it might also be useful to anyone working on Postgres drivers, proxies, connection poolers, protocol implementations, or anything else at the wire protocol level.

Would be interested in any feedback, corrections, or suggestions from people who know this area well.

Small caveat: I’m not a frontend developer, so I used AI to help with the frontend implementation

I am trying to install PostgreSQL. I started with trying to install 18.6.1, and when that failed, I tried 17.11. For both of them, once I got to "Choose Components" I clicked "Create Spatial Database" per my course. The installer loads, and installs the database, until the very end, where I get this error:

createdb: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "postgres"

This is the same error for both versions. I am on Windows 11, 64-bit.

One of the most exciting features I've been contributing to in Serverpod is Model Projections, and how it unlocked type-safe partial column selection in Dart!

**The Problem:**
In database-driven applications, full entity models often carry unnecessary data over the wire. Fetching 30 columns when an endpoint only needs a user's name and their author's city wastes bandwidth and compute. Traditionally, developers had to write raw SQL queries or map heavy entities manually into DTOs.

**The Solution: Model Projections**
Model Projections allow developers to declare lightweight projected models directly in Serverpod schemas:

\- Granular Field Picking: Declare only the exact fields needed for your endpoint.
\- Relation Flattening: Pull nested relations directly into top-level fields (e.g. mapping "author.name" straight into "authorName").
\- Optimized SQL Generation: Serverpod automatically computes the minimal SQL SELECT clause and joins required for the projection.

**The Ripple Effect: Ad-Hoc Column Selection**
Building the query engine for Model Projections naturally gave birth to ad-hoc column selection:

1. "findAsJson(select: (table) => \[table.name\])" allows flexible on-the-fly column queries.
   
2. We separated FullModelInclude and JsonCompatiblelnclude so that typed "find()" queries remain 100% compile-time safe, while JSON and projected queries enjoy flexible, partial data fetching.

Designing developer-first APIs that combine SQL query efficiency with Dart's compile-time type safety has been an incredible experience!

Check out the Pull Request on GitHub to see the implementation and discussions:
[https://github.com/serverpod/serverpod/pull/5630\](https://github.com/serverpod/serverpod/pull/5630)

I've been building an open-source (Apache 2.0) self-hosted platform called Hobbyist, and the piece this subreddit might find interesting is the database layer.

The problem: running many small Postgres instances for side projects means paying (in RAM or dollars) for databases that are idle 95% of the time. Managed serverless Postgres solves this, but you rent it forever.

The approach: each project gets real PostgreSQL 18 in a container; not a fork, no custom storage engine, nothing bolted on. In front of it sits a proxy that speaks the Postgres wire protocol. When a database has no activity, the container shuts down completely. When a client connects, the proxy holds the connection, cold-starts the container (~170ms measured), and forwards traffic. Clients just see a slightly slow first connection.

Because it's stock Postgres, everything works as expected; extensions, pg_dump, your existing tooling; and there's a one-command hobby eject that hands you the data directory and containers if you want out. Backups today are pg_dump-based; snapshots exist internally but aren't exposed in the CLI yet.

Honest caveats: this is v0-alpha and not production ready. The 170ms figure is from my hardware; making that number reproducible on cheap machines is the current roadmap priority. I'd particularly value scrutiny from people who know where wire-protocol proxying gets hairy (auth handoff, TLS, prepared statements across restarts, LISTEN/NOTIFY through sleep cycles).

Repo: https://github.com/uziiuzair/hobbyist

Site: https://hobbyist.sh

Hey everyone, sharing out the harness that my cofounder used for benchmarking PgCache.

(what we do differently is caching data, not results, and then we keep it all fresh using postgres logical replication .. which also helps us invalidate when needed)

The harness drives identical traffic at (i) stock Postgres and (ii) Postgres sitting behind PgCache. It uses the public dba.stackexchange.com data dump for the schema and data. The workload models real page loads (a sequence of queries, the way an app actually hits the database) instead of a single repeated SELECT.

A couple other things:

- this runs locally through Docker Compose, or on AWS with an RDS origin and an EC2 driver box (closer to a real deployment).

- Results land as Prometheus metrics: throughput and p99 latency per lane, origin CPU, cache hit rate.

Happy to answer questions on the methodology or the workload model, and I'd love to hear some skeptical feedback.