Dev & EngARTICLE

PostgreSQL 19 brings concurrent REPACK, atomic upsert, and priority-based autovacuum

PostgreSQL 19 brings concurrent REPACK, atomic upsert, and priority-based autovacuum
Image: Roberto Diniz
Article · Dev & Eng
Reading
7 min
Tables
1
Code
6
In short

The release lost ambitious features like property graph queries and temporal UPDATE/DELETE, but brought concrete changes in locking, replication, and maintenance that affect those who operate databases day to day.

PostgreSQL 19 was released this week after repeated delays, and much of the conversation focused on what was left out. Zsolt Parragi, a developer at Percona, published a roundup on the company's blog (republished on Planet PostgreSQL) of what actually made it into the final version, after ambitious features like property graph queries, temporal UPDATE/DELETE with FOR PORTION OF, partition merge and split, online checksum transition, and GROUP BY ALL were reverted before the cutoff.

The list of reverts is long, and according to Parragi, most of them are expected to resurface in PostgreSQL 20. But what remains is no small matter: there are syntax changes that any developer will notice, and execution and maintenance improvements that matter directly to those operating the database in production.

REPACK replaces the confusion between VACUUM FULL and CLUSTER

Through version 18, VACUUM FULL and CLUSTER shared the same internal implementation, but had different names and syntax for the same idea: rewriting the entire table. PostgreSQL 19 introduces REPACK as a single name for the operation:

sql
REPACK mytable;              -- equivalent to VACUUM FULL mytable
REPACK mytable USING INDEX mytable_pk;  -- equivalent to CLUSTER

The real novelty is REPACK (CONCURRENTLY), which creates a new table, captures the changes made to the original table via logical decoding of the WAL, and swaps the two at the end, with a very brief ACCESS EXCLUSIVE lock. Unlike the pg_repack extension, which uses row-level triggers (and therefore adds overhead to every concurrent write), native REPACK reads the WAL directly and works even with wal_level = replica, without needing to enable logical replication beforehand.

The operation requires a primary key or replica identity index, does not work on partitioned tables, unlogged tables, materialized views, or system catalogs, and cannot run inside a transaction block. Anyone who relies on features that pg_repack offers, such as sorting by an arbitrary column or moving the table to another tablespace, still needs the extension.

Upsert that returns without rewriting the row

INSERT ... ON CONFLICT already supported DO UPDATE and DO NOTHING, but neither handled well the common case of just needing the row's id, whether it already existed or not. The solution until now was an artificial UPDATE (which generates a new row version, fires triggers, and causes bloat) or a CTE combining DO NOTHING with SELECT, which could simply return nothing if another transaction inserted the same key between the two steps.

PostgreSQL 19 solves this with ON CONFLICT DO SELECT:

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

The statement returns the id both when the insert succeeds and when the email already exists, as a single atomic operation. The RETURNING clause is mandatory: DO SELECT without it is an error. The syntax also accepts FOR UPDATE and WHERE, for those who already know they'll modify the returned row next. One detail that doesn't change: the sequence associated with the key is still consumed even in case of conflict.

IGNORE NULLS in window functions

Anyone who has tried to implement "fill with the last known value" in a time series knows how much work that took. With IGNORE NULLS, applicable to lag, lead, first_value, last_value, and nth_value, the problem becomes trivial:

lag(temperature) IGNORE NULLS OVER (ORDER BY ts) AS previous_known,
last_value(temperature) IGNORE NULLS OVER (ORDER BY ts) AS last_known
tstemperatureprevious_knownlast_known
10:0021NULL21
10:01NULL2121
10:02NULL2121
10:03232123

Before, achieving the same result required combining COALESCE, subqueries, or custom aggregate functions. Now it's just one more clause in the window function.

WAIT FOR LSN: read consistency, with a serious caveat

In architectures that write to the primary and read from the replica, the classic problem is the stale read: the application writes, then asks to read right after, and the replica hasn't applied that WAL yet. WAIT FOR LSN solves this without requiring synchronous replication:

sql
-- on the primary, right after COMMIT
SELECT pg_current_wal_insert_lsn();

-- on the replica, before the read
WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '1s');

Without TIMEOUT the wait is indefinite; with TIMEOUT, the statement raises an error on expiration, unless NO_THROW is specified. Parragi, however, points out a bug reported by Jobin Augustine that wasn't fixed in time for version 19.0: if the LSN returned by pg_current_wal_insert_lsn() falls exactly on a WAL page boundary, the wait can hang until some unrelated WAL is written, or until the timeout expires. In Augustine's tests, this happened in about 1 out of every 2,000 waits.

Until then, I would recommend not using this feature in production, or at least always specifying a TIMEOUT.

Zsolt Parragi, developer at Percona

The fix has already been submitted and should land in version 19.1. Anyone planning to use WAIT FOR LSN for read routing on a replica should wait for that patch, or at the very least never omit TIMEOUT.

Parallel, priority-based autovacuum

Through version 18, only manual VACUUM parallelized index cleanup; autovacuum processed the table and indexes sequentially. Version 19 changes this with two new settings:

sql
ALTER SYSTEM SET autovacuum_max_parallel_workers = 4;
SELECT pg_reload_conf();

ALTER TABLE large_events SET (autovacuum_parallel_workers = 2);

The default is 0 (off), and these limits are independent of autovacuum_max_workers, which controls how many different tables autovacuum processes at the same time. The second gain is prioritization: before, tables were processed in the order they appeared in pg_class, so a table close to transaction wraparound could end up waiting behind several small tables. Now there's a configurable scoring system (autovacuum_freeze_score_weight, autovacuum_vacuum_score_weight, etc.) visible through the new pg_stat_autovacuum_scores view, which orders what gets vacuumed first.

Regular scans can now mark the visibility map too

Before, only VACUUM and COPY ... FREEZE could mark a page as all-visible. In append-heavy workloads (constant inserts, few updates), recently written pages stayed unmarked until the next vacuum, forcing scans to check visibility row by row. In PostgreSQL 19, a regular SELECT that scans an entire page and confirms all rows are visible can set the bit on its own. The biggest gain shows up in index-only scans, which on all-visible pages no longer need to visit the table, and in the following vacuum itself, which can skip those pages. It's worth noting that the scan only marks all-visible, not all-frozen: freezing rows remains an exclusive job for vacuum.

pg_plan_advice and pg_stash_advice: plan hints in core

Two new modules in contrib, disabled by default, bring to the core something the pg_hint_plan extension already offered: manual control over the planner's decisions. With pg_plan_advice loaded, EXPLAIN can show the recipe for the current plan:

sql
LOAD 'pg_plan_advice';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM a JOIN b ON a.id = b.a_id WHERE a.x < 100;

The output includes lines such as JOIN_ORDER(b a) and HASH_JOIN(a), which can be reapplied via SET pg_plan_advice.advice = '...' to force a different plan. pg_stash_advice goes further: it stores these recipes by query ID and applies them automatically in subsequent sessions, which opens room for locking down unstable plans and, in the future, for automated query-tuning tools.

Logical replication without restarting the server

Enabling logical replication used to require changing wal_level from replica to logical, which demanded a server restart. Now PostgreSQL automatically switches to logical when the first logical replication slot is created, and switches back to replica when the last one is removed; the new effective_wal_level setting shows the value actually in use.

Sequences also start being logically replicated, but not continuously: the publication needs to include FOR ALL SEQUENCES, and the subscriber only copies the current values when the subscription is created or when ALTER SUBSCRIPTION ... REFRESH SEQUENCES is executed explicitly, usually right before a switchover. For major-version upgrades, the feature only helps starting with the migration from PostgreSQL 19 to 20, since it requires a publisher on that version or newer.

What's left for later

Nothing that was left out is final: Parragi is clear in saying he expects to see graph queries, temporal operations, and partition split/merge come back in future versions. For those administering databases today, the practical lesson is to separate what's a quiet win (faster tuple deforming, radix sort, aggregation before join, faster foreign key checking) from what requires a conscious decision, such as adopting REPACK (CONCURRENTLY) instead of pg_repack, or waiting for the 19.1 patch before relying on WAIT FOR LSN for read routing.

Translated from the Brazilian Portuguese original · Read the original