PostgreSQL 19 Upgrade: Breaking Changes and New Defaults to Check First

The PostgreSQL 19 upgrade is close. RC1 shipped on October 15, and the release team has scheduled RC2 for November 12 and general availability for November 19, 2026. Most changes are welcome, but several of them can stop pg_upgrade, break old clients or change query performance without any error. This is the list we check before upgrading a production cluster.

Everything below comes from the PostgreSQL 19 release notes and the version 19 documentation. Release notes can still change before the final release, so check them again on release day.

Changes that can stop pg_upgrade

These are the ones to find first, because pg_upgrade refuses to run until they are fixed. On a large cluster you want to know about them weeks ahead, not during the maintenance window.

• btree_gist indexes on inet/cidr. The btree_gist documentation says its gist_inet_ops and gist_cidr_ops opclasses are unreliable: index searches may miss rows. PostgreSQL 19 makes the built-in GiST inet_ops the default, and pg_upgrade will fail if the old cluster still has indexes with the faulty opclasses. Replace them with inet_ops indexes before the upgrade.
• Database, role or tablespace names with carriage returns or line feeds are now disallowed, and pg_upgrade will not upgrade a cluster that has them.
• The MULE_INTERNAL encoding is removed. Databases using it have to be dumped and restored with another encoding.

-- run in every database: indexes that will block pg_upgrade to 19
SELECT n.nspname, t.relname AS table_name, c.relname AS index_name, o.opcname
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_opclass o ON o.oid = ANY (i.indclass::oid[])
WHERE o.opcname IN ('gist_inet_ops', 'gist_cidr_ops');

On a big table, building the replacement with CREATE INDEX CONCURRENTLY can take hours, and both indexes exist side by side until you drop the old one. Plan the disk space and do it well before the upgrade date.

Changes that can break clients and logins

• RADIUS authentication is removed. The notes explain that PostgreSQL only supported RADIUS over UDP, which is unfixably insecure. Any radius line in pg_hba.conf needs a replacement, such as LDAP, certificates or SCRAM.
• MD5 password logins now produce a warning after successful authentication. MD5 passwords were already deprecated in PostgreSQL 18. The warning can be turned off with md5_password_warnings, but the better fix is moving those roles to SCRAM.
• Password expiry warnings. The new password_expiration_warning_threshold warns users about expiring passwords, seven days ahead by default. Service accounts with VALID UNTIL will start seeing it.
• standard_conforming_strings is always on. Dumps made with an older pg_dump from a server running with this setting off will not load correctly. Use the PostgreSQL 19 pg_dump, or make sure the setting is on when dumping. escape_string_warning is removed.
• Smaller behaviour changes. json_array() over zero rows now returns an empty JSON array instead of NULL. READ ONLY transactions now pass that status to postgres_fdw sessions. The sync_error_count column in pg_stat_subscription_stats is renamed to sync_table_error_count, and the wait event type BUFFERPIN becomes BUFFER. Monitoring queries that use these names need an update.

A quick pre-upgrade scan for the items above:

-- names with CR/LF block pg_upgrade
SELECT 'database' AS kind, datname AS name FROM pg_database WHERE datname ~ '[\r\n]'
UNION ALL
SELECT 'role', rolname FROM pg_roles WHERE rolname ~ '[\r\n]'
UNION ALL
SELECT 'tablespace', spcname FROM pg_tablespace WHERE spcname ~ '[\r\n]';

-- RADIUS lines in pg_hba.conf stop working
SELECT line_number, type, database, user_name, auth_method
FROM pg_hba_file_rules WHERE auth_method = 'radius';

-- roles still on MD5 passwords (needs superuser)
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

-- anything still running with standard_conforming_strings = off?
SHOW standard_conforming_strings;

New defaults that change performance

These do not raise errors. They show up later as slower or faster queries, or as different behaviour under load.

• JIT is off by default. The notes say the cost-based JIT activation proved unreliable, so jit now defaults to off. Sites with many large analytical queries have to enable it themselves. Compare the timings of your heaviest reports before and after, and enable JIT only where it helps.
• max_locks_per_transaction default goes from 64 to 128. The lock table allocation also changed, so the notes say a custom value must be doubled to keep the same capacity. If you set 256 because of many partitions, the equivalent is now 512. We covered the error you get when it is too low in out of shared memory and max_locks_per_transaction.
• TOAST compression defaults to lz4. default_toast_compression changes from pglz to lz4. The documentation explains that existing values are not recompressed. Only new values use the new method, unless the data is rewritten, for example by pg_restore.
• wal_compression = on now picks the best available method instead of always pglz.
• log_lock_waits is on by default. Waits longer than deadlock_timeout are logged. This is useful, but on a busy system with lock contention it adds log volume. Make sure your log shipping and disk can take it.

New features worth planning for

• REPACK replaces VACUUM FULL and CLUSTER, which stay for compatibility. REPACK (CONCURRENTLY) does most of the work under a SHARE UPDATE EXCLUSIVE lock, captures concurrent changes with logical decoding, and takes ACCESS EXCLUSIVE only for the final swap. It needs a primary key or another index-based replica identity, does not work on partitioned or unlogged tables, needs a free slot within max_repack_replication_slots, and cannot run in a transaction block.
• WAIT FOR LSN gives "read your writes" on standbys: the application waits until a replica has replayed a given LSN before reading from it. The default mode is standby_replay, and a TIMEOUT option keeps a lagging replica from hanging the request.
• Logical replication without a restart. With wal_level = replica, the server can enable logical decoding when it is needed. The new effective_wal_level shows the level actually in use.
• Sequence replication. Publications can include ALL SEQUENCES, and ALTER SUBSCRIPTION ... REFRESH SEQUENCES syncs their values. This removes a manual step from logical-replication upgrades.
• Autovacuum. Indexes can be vacuumed by parallel autovacuum workers (autovacuum_max_parallel_workers plus the per-table autovacuum_parallel_workers), and a new scoring system decides which tables autovacuum processes first, visible in pg_stat_autovacuum_scores.
• I/O workers scale automatically with io_method = worker, controlled by io_min_workers and io_max_workers.

What changes on large databases

• Choose the upgrade method by size and downtime. On terabyte clusters, dump and restore is usually too slow. pg_upgrade with --link or --clone keeps the downtime short, and logical replication can bring it close to zero. We walk through pg_upgrade and logical replication in our PostgreSQL major version upgrade guide.
• Rehearse on a full-size copy. Index rebuilds, ANALYZE after the upgrade and the checks above all take time in proportion to data size. Measure the whole sequence on a copy with production volume, not an estimate.
• Re-measure analytical queries. With JIT now off, long reports over big tables are where you are most likely to see a change. Keep a list of the heaviest queries and their timings from before the upgrade.
• Partitioned schemas need the new lock math. Thousands of partitions mean many locks per query. Double any custom max_locks_per_transaction before restarting on 19.
• lz4 does not shrink existing data. After pg_upgrade the old values stay pglz. Do not expect space savings until data is rewritten.
• REPACK (CONCURRENTLY) needs room. It needs free disk space at least equal to the table plus its indexes, and the documentation says it fails if more than about 104 million rows are updated or deleted concurrently. For the biggest tables, run it in a quiet period.