PostgreSQL 19: REPACK, Atomic Upsert, and What Got Cut

PostgreSQL 19 is landing this month, and the headliner isn’t a cloud integration or a shiny new data type. It’s a primitive backend developers have been kludging around since Postgres 9.5: an atomic upsert that actually returns the conflicting row. That’s joined by native online table rebuilding (finally in core), temporal row mutations, and official query plan hints — a release that fixes daily workflow friction rather than chasing trends. The graph query feature everyone was excited about? Reverted five days before the final cut. Here’s what actually shipped.

ON CONFLICT DO SELECT: The Upsert You Actually Needed

The old Postgres upsert story was messy. ON CONFLICT DO UPDATE could return a row, but you’d pay for a no-op write on every conflict. ON CONFLICT DO NOTHING returned nothing. Getting the existing row cleanly required a CTE workaround — or worse, a three-statement SELECT/INSERT/SELECT pattern that breaks under concurrent load.

PostgreSQL 19 fixes this with ON CONFLICT DO SELECT:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice Johnson')
ON CONFLICT (email) DO SELECT
RETURNING *;

One statement. Atomic. Returns the inserted row or the conflicting one — whichever applies. Benchmarks from the PostgreSQL mailing list put it at 35,788 transactions per second versus 9,222 tx/sec for the DO UPDATE no-op approach — roughly four times faster. The DO NOTHING CTE workaround manages 28,929 tx/sec, so DO SELECT beats even the clever workaround by 24%.

It requires a RETURNING clause and a conflict target. Row-level locking (FOR UPDATE, FOR SHARE) works as expected. This is the primitive idempotent APIs, tag-resolution pipelines, and exactly-once event processors have been waiting for.

REPACK Is Now in Core

Until now, online table maintenance in Postgres required the pg_repack extension — a third-party dependency with its own version compatibility dance. VACUUM FULL and CLUSTER work, but they hold exclusive locks, which means downtime for large tables.

PostgreSQL 19 ships REPACK natively:

REPACK TABLE orders CONCURRENTLY;

The table stays accessible throughout. No more timing maintenance windows around VACUUM FULL, no more pinning to whichever pg_repack version matches your Postgres minor version. For shops running large, write-heavy tables, this alone justifies the upgrade.

SQL/PGQ Was Cut — and That’s the Right Call

The most anticipated feature in PostgreSQL 19 was SQL/PGQ: native property graph queries over existing relational tables, no separate graph database required. It was in Beta 2. Then on September 7 — five days before the final release — the PostgreSQL team reverted it.

The right call. PostgreSQL’s reputation for correctness comes from exactly this kind of discipline. Shipping a half-baked graph layer would have been a mistake. SQL/PGQ is now targeting PostgreSQL 20 (September 2027). If you need graph queries today, Apache AGE still works and covers most use cases.

Temporal Queries and Other DX Wins

PostgreSQL 19 completes the SQL:2011 temporal feature set with UPDATE ... FOR PORTION OF and DELETE ... FOR PORTION OF. Instead of manually splitting rows when modifying time-bounded data, the database handles the boundary math automatically:

DELETE FROM reservations
FOR PORTION OF valid_period FROM '2026-10-01' TO '2026-10-15'
WHERE room_id = 42;

Other notable additions: GROUP BY ALL infers non-aggregate columns automatically, COPY TO now outputs NDJSON natively (no more row_to_json() wrangling), parallel autovacuum finally processes multiple indexes simultaneously, and sequence values now replicate correctly in logical replication — a gap that caused subtle drift after failovers.

Breaking Changes: Check These Before You Upgrade

PostgreSQL 19 has several changes that will bite you silently if you skip the release notes:

• JIT is disabled by default. It was enabled since PostgreSQL 13, but the optimizer’s cost model for JIT was unreliable. If you run analytical workloads that benefit from JIT, add jit = on to postgresql.conf explicitly.
• LZ4 is the new default TOAST compression. Faster than pglz for both read and write. Existing data is unaffected — only new writes change. Revert with ALTER SYSTEM SET default_toast_compression = pglz if needed.
• RADIUS authentication is removed. If any clients use RADIUS auth, migrate before upgrading.
• MD5 deprecation warnings begin. Not removed yet, but plan the migration to SCRAM-SHA-256 now.

PostgreSQL 19 is expected to reach general availability this month. The official release notes are worth a thorough read before staging upgrades. If you’re managing Crunchy Data or RDS clusters, check the Crunchy Data upgrade guide — their advice on JIT and vacuum tuning is particularly practical.