PostgreSQL elephant logo on a deep navy grid background, illustrating the PostgreSQL 19 release

Figure: The official PostgreSQL elephant logo (postgresql.org) on a generated background.

PostgreSQL 19 landed on 19 November 2026, and the headline feature is one every
operations team has wanted for years: the ability to reclaim disk space from a bloated
table without taking the table offline. The new REPACK command does exactly that,
and REPACK (CONCURRENTLY) drops the exclusive lock that made VACUUM FULL and
CLUSTER unusable on a busy production database.

This release is not just a syntax addition, though. PostgreSQL 19 also brings in-core
control over query plans, “read-your-writes” consistency on standby servers, logical
replication of sequence values, and a batch of default changes that will bite anyone
who upgrades without reading the migration notes. Below is a hands-on look at what
changed, what it means on a real Linux server, and what to check before you upgrade.

Official project home: https://www.postgresql.org/

REPACK: table maintenance without a maintenance window

For years the standard advice for a bloated table was VACUUM FULL or CLUSTER. Both
rewrite the entire table, and both take an ACCESS EXCLUSIVE lock while they do it.
On a table that receives traffic, that means reads and writes block for the entire
duration of the rewrite. On a large table, “the entire duration” can be hours.

PostgreSQL 19 folds both commands into a single, better-named tool. The official
documentation describes it as “rewrite a table to reclaim disk space”, and the
synopsis is:

Sql
REPACK [ ( option [, ...] ) ] [ table_and_columns [ USING INDEX [ index_name ] ] ]
REPACK [ ( option [, ...] ) ] USING INDEX

where the options are VERBOSE, ANALYZE and CONCURRENTLY. VACUUM FULL and
CLUSTER are retained for backwards compatibility, but the project now points new
deployments at REPACK.

The behaviour that matters is this one: “When a table is being repacked, an ACCESS
EXCLUSIVE lock is acquired on it, unless the CONCURRENTLY option is given.”

Sql
-- Reclaim space on a single table without blocking readers or writers
REPACK (CONCURRENTLY, ANALYZE, VERBOSE) public.events;

-- Reorder rows to match an index, also non-blocking
REPACK (CONCURRENTLY) public.events USING INDEX events_created_at_idx;

Note the MAINTAIN privilege requirement: to repack a table you must have the
MAINTAIN privilege on it. This is a deliberate change from the old behaviour where
table ownership was enough.

AI-generated illustration of a table being rewritten without downtime while a continuous stream of data flows between two cubes

Figure: AI-generated illustration of the REPACK concept — a table rewritten alongside live traffic. Not an official PostgreSQL screenshot.

Two caveats from the official docs are worth repeating. First, REPACK “is not a good
way to prevent transaction ID or multixact ID wraparound: it rewrites the whole table
and all of its indexes, so it takes much longer than VACUUM, and it can fail after
most of the work is done, especially with CONCURRENTLY on a busy table.” Use
VACUUM for wraparound protection. Second, the whole-database form (REPACK with no
table name) cannot run inside a transaction block and cannot be combined with
CONCURRENTLY.

Progress is observable via the new pg_stat_progress_repack view, which also tracks
CLUSTER and VACUUM FULL.

pg_plan_advice: pinning the query plan from inside the server

Plan stability has historically required either pg_hint_plan, a bolted-on
extension, or rewriting queries until the planner behaved. PostgreSQL 19 ships
pg_plan_advice as a contributed module, with a companion pg_stash_advice that
applies stored advice automatically by query.

The workflow is genuinely interesting. Load the module, then ask EXPLAIN what plan it
chose and what advice string would reproduce that plan:

Sql
LOAD 'pg_plan_advice';

EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;

The output includes a generated advice block:

Bash
Generated Plan Advice:
JOIN_ORDER(f d)
HASH_JOIN(d)
SEQ_SCAN(f d)
NO_GATHER(f d)

JOIN_ORDER(f d) says f should be the driving table, HASH_JOIN(d) says d should
sit on the inner side of a hash join, and so on. You can then enforce just the parts
you care about:

Sql
SET pg_plan_advice.advice = 'JOIN_ORDER(f d)';

The module can be loaded system-wide via shared_preload_libraries, per-session via
session_preload_libraries, or per-session with the LOAD command. The docs are
candid about the risk: “since the planner often makes good decisions, overriding its
judgment can easily backfire.” If the underlying data distribution shifts, a pinned
plan can be much worse than the one the planner would have chosen on its own.

Read-your-writes on standbys: WAIT FOR LSN

Applications that spread reads across standby servers have long hit a consistency
wall: write to the primary, immediately read from a standby, and the standby may not
have replayed the write yet. PostgreSQL 19 adds a WAIT command to close that gap.

Sql
WAIT FOR LSN '0/16B3748'
  WITH (MODE 'standby_replay', TIMEOUT '10s', NO_THROW);

The mode determines how far the standby must progress: standby_replay (the default),
standby_write, standby_flush, or primary_flush. The command returns success,
timeout, or not in recovery. With NO_THROW, a timeout is reported as a status
string rather than an error.

The practical pattern is to capture the LSN of the write on the primary, then wait for
that exact LSN on the standby before issuing the read:

Sql
-- on the primary
SELECT pg_current_wal_lsn() AS lsn INTO lsn FROM write_result;

-- on the standby, before reading
SELECT pg_advisory_lock(4711);
EXECUTE format('WAIT FOR LSN %L WITH (MODE %L, TIMEOUT %L)', lsn, 'standby_replay', '10s');

Logical replication gets sequences and no more restarts

Enabling logical replication previously required restarting the server to raise
wal_level to logical. PostgreSQL 19 removes that dance: creating the first logical
replication slot automatically raises the effective WAL level. The new
effective_wal_level setting shows what is actually in force.

Logical replication of sequence values is new too. Publish them with FOR ALL SEQUENCES when creating the publication:

Sql
CREATE PUBLICATION my_pub FOR ALL TABLES, FOR ALL SEQUENCES;

There is also a new EXCEPT clause for CREATE PUBLICATION ... FOR ALL TABLES, so you
can publish everything except a couple of tables, and CREATE SUBSCRIPTION can now
pull its connection settings from a foreign server via the SERVER clause.

AI-generated illustration of two synchronised database nodes connected by a streaming replication channel

Figure: AI-generated illustration of replication and read-your-writes consistency. Not an official PostgreSQL screenshot.

Autovacuum gets parallel workers and a scoring system

Autovacuum can now process indexes using parallel workers, controlled by
autovacuum_max_parallel_workers at the server level and the per-table
autovacuum_parallel_workers storage parameter. The actual count is capped by
max_parallel_workers, so size that first.

Autovacuum also gained a prioritisation scoring system, exposed through the new
pg_stat_autovacuum_scores view, so you can see which tables the daemon considers
most in need of attention.

Table scans can now mark pages as all-visible in the visibility map. Previously only
VACUUM and COPY ... FREEZE could do that, so a plain SELECT used to leave the
visibility map untouched and gave later vacuum runs more work to do.

Performance and SQL additions

Foreign-key constraint checks got faster — in the project’s own testing this made bulk
inserts nearly 2x faster. The planner can now convert some NOT IN clauses into
more efficient anti-joins, and can aggregate before joining to reduce rows processed.

For asynchronous I/O, the io_method = worker setting now automatically controls the
number of background workers based on workload. The old io_workers variable is gone,
replaced by four new ones: io_min_workers, io_max_workers, io_worker_idle_timeout
and io_worker_launch_interval.

The default TOAST compression method changed from pglz to lz4, which is more
efficient. Window functions lead(), lag(), first_value(), last_value() and
nth_value() now accept IGNORE NULLS / RESPECT NULLS.

The everyday developer win is INSERT ... ON CONFLICT DO SELECT ... RETURNING,
which returns the existing row on conflict and can optionally lock it with
FOR UPDATE / FOR SHARE — a single-round-trip “get or create”:

Sql
INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO SELECT
RETURNING id, old.id IS NULL AS created;

Also new: COPY TO can output JSON, including a single JSON array via the
FORCE_ARRAY option; COPY TO works directly on partitioned tables; COPY FROM
gained ON_ERROR SET_NULL; and the SQL/JSON path language picked up string methods
such as lower(), upper(), replace(), split_part() and btrim().

Observability improved as well. New views include pg_stat_lock, pg_stat_recovery,
pg_stat_autovacuum_scores and pg_stat_progress_repack. EXPLAIN ANALYZE has a
new IO option (with a matching auto_explain setting) that reports asynchronous I/O
activity, and log_lock_waits is now on by default.

The upgrade checklist

This is the part to read twice. PostgreSQL 19 ships with several defaults changed, and
the migration notes are explicit about the incompatibilities.

JIT is now disabled by default. It was on by default since PostgreSQL 12, but the
cost model that decided when to compile proved unreliable. If you run large analytical
queries, re-enable it manually and re-check your plans.

standard_conforming_strings is now always on. Dumps created by a pre-19
pg_dump or pg_dumpall with standard_conforming_strings = off will not load into
PostgreSQL 19. Recreate those dumps with a PostgreSQL 19 client, or generate them with
standard_conforming_strings = on.

max_locks_per_transaction default doubled from 64 to 128. Lock size allocation
changed, so existing settings must effectively be doubled to match their previous
capacity.

RADIUS authentication was removed. PostgreSQL only supported RADIUS over UDP,
which the release notes describe as unfixably insecure.

MD5 passwords now warn on successful login. Silence it with
md5_password_warnings, but the right move is to migrate to SCRAM.

Password expiry warnings were added. The new password_expiration_warning_threshold
server variable defaults to seven days.

The default index opclasses for inet and cidr moved from btree_gist to GiST,
because the old ones could exclude rows that should be returned. pg_upgrade will
refuse to upgrade clusters still using btree_gist inet / cidr indexes.

io_workers was removed in favour of the four new io_worker_* variables.

Also note: carriage returns and line feeds are now disallowed in database, role and
tablespace names, and pg_upgrade will refuse clusters that use them.

To upgrade, use pg_upgrade, a pg_dumpall dump/restore, or logical replication —
a dump/restore or pg_upgrade is required for any move from a previous major release.
On Debian and Ubuntu, the PostgreSQL APT repository already carries the release as
postgresql-19 (19.0), alongside postgresql-client-19:

Bash
sudo apt install postgresql-19 postgresql-client-19

If you manage your own repository, add the PGDG apt repository for your distribution
and install from there — that is where the new major version lands first.

Verdict

PostgreSQL 19 is a “reliability at scale” release. REPACK (CONCURRENTLY) is the
feature that will change daily operations for most teams, because it turns a
scheduled outage into a background job. pg_plan_advice moves plan control into
core, WAIT FOR LSN fixes a consistency bug that application teams have been
work-around-ing for years, and the parallel autovacuum work finally keeps up with
modern storage.

The caveats are the defaults. A release that flips standard_conforming_strings,
halves your effective lock capacity, disables JIT and removes RADIUS is not a
apt upgrade away — read the migration notes, test your restore path, and pin your
old cluster until you have.

Last modified: 2026年11月21日

Author

Comments

Write a Reply or Comment

Your email address will not be published.