Skip to content
chornous.dev

Type two or more letters. Esc closes.

Index of sheets
Theme

Sheet 03 · Writing

All notes

PostgreSQL 19: REPACK CONCURRENTLY, pg_plan_advice, WAIT FOR LSN, and the incompatibilities to check first

The PostgreSQL Global Development Group released PostgreSQL 19 on November 19, 2026. It adds REPACK (CONCURRENTLY), pg_plan_advice, read-your-writes on standbys and INSERT ... ON CONFLICT DO SELECT, turns JIT off by default, removes RADIUS, and forces standard_conforming_strings on.

Pl. 94 · pipeline drawing generated from the slug “postgresql-19-repack-plan-advice-upgrade-notes”

The PostgreSQL Global Development Group released PostgreSQL 19 on November 19, 2026. The headline features target people who run Postgres under load: a non-blocking way to remove bloat, a way to pin query plans, and read-your-writes on replicas. The release notes also carry a list of incompatibilities long enough that you should read it before you schedule the upgrade.

REPACK replaces VACUUM FULL and CLUSTER

VACUUM FULL and CLUSTER both lock the table until they finish. PostgreSQL 19 adds REPACK, which reclaims the space and can reorder rows to match an index. With the CONCURRENTLY option it does that without blocking reads or writes:

-- PostgreSQL 18: blocks the table for the duration
VACUUM FULL orders;

-- PostgreSQL 19: same cleanup, reads and writes keep running
REPACK (CONCURRENTLY) orders;

VACUUM FULL and CLUSTER stay available for compatibility. If a maintenance-window job handles bloat for you today, test the built-in command against the same tables.

Autovacuum got help too. It can vacuum indexes with parallel workers, controlled by autovacuum_max_parallel_workers, and it picks tables with a new scoring system that you can inspect in pg_stat_autovacuum_scores.

Pinning plans with pg_plan_advice

The new pg_plan_advice extension lets you stabilize the planner's choices and try alternatives with a "plan advice" mini-language. Its companion, pg_stash_advice, stores advice and applies it by query identifier. You can fix a slow plan for one query without changing the application that sends it.

For application code

Three changes reach your queries:

  • INSERT ... ON CONFLICT DO SELECT returns the existing row through RETURNING on conflict, which turns "get or create" into one round trip.
  • WAIT FOR LSN on a standby waits until replay reaches a given log sequence number, so a read sent to a replica can see a write you made on the primary.
  • lead(), lag(), first_value(), last_value() and nth_value() accept IGNORE NULLS, and COPY can write JSON, including a single JSON array.
INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO SELECT
RETURNING id;

Logical replication no longer needs a restart to switch on: the server raises the effective WAL level to logical when you create the first slot. You can also publish sequence values with FOR ALL SEQUENCES.

The project says faster foreign key checks made bulk inserts nearly 2x faster in its testing. The planner now turns NOT IN into an anti-join when no NULLs are present, and it can aggregate before a join.

Read the incompatibility list

Moving data to 19 takes pg_dumpall, pg_upgrade or logical replication. These are the items that will bite first:

PostgreSQL 19 changes to check before upgrading
ChangeWhat breaks
JIT disabled by defaultLarge analytical queries that relied on JIT may slow down until you set jit = on
RADIUS support removedpg_hba.conf entries using RADIUS stop working
standard_conforming_strings forced onDumps made by older pg_dump with the setting off won't load; dump with the 19 tools
btree_gist inet/cidr opclassespg_upgrade refuses clusters with those indexes
CR or LF in database, role or tablespace namesNow disallowed; pg_upgrade refuses those clusters
max_locks_per_transaction default 64 to 128Custom values must double to keep the same capacity
io_workers removedReplaced by io_min_workers and io_max_workers among others
Empty json_array() resultReturns [] where it returned NULL
MULE_INTERNAL encoding removedDump and restore those databases with a different encoding

Two more need a reindex after pg_upgrade. Full text search and pg_trgm now use the database's locale provider for character classification, which changes output on ICU or builtin-provider databases whose LC_CTYPE differs, such as C. The fuzzystrmatch functions changed how they treat non-ASCII characters. The release notes recommend reindexing indexes that depend on either.

PostgreSQL 19 also warns after every successful MD5 password login. The project deprecated MD5 in 18 and says it will remove it in a future release, so move those roles to SCRAM now. With LZ4 support compiled in, the default TOAST compression becomes lz4.

Server-side SNI arrives through a new pg_hosts.conf, so one server can present a different TLS certificate per hostname.

This week

  1. Run pg_upgrade --check against a copy of production and fix whatever it rejects: btree_gist inet/cidr indexes, names with line breaks, MULE_INTERNAL databases.
  2. Grep pg_hba.conf for radius and your config for io_workers and max_locks_per_transaction.
  3. Benchmark your heaviest analytical queries with jit = off before you decide whether to turn it back on.
  4. Move MD5 roles to SCRAM.
  5. If you stage the upgrade through dump and restore, take the dump with the PostgreSQL 19 pg_dump.

Volodymyr Chornous