Postgres 19 Upgrade Checklist: Five Defaults That Change Without Touching Your Queries

Why the Postgres 19 upgrade is a defaults problem

Postgres 19 is in late beta. Beta 4 landed on 24 September, a release candidate is expected in early October, and general availability is expected later in the month, according to the DevX summary of the project announcement. Most coverage so far is about the new features: online table repacking, plan advice, parallel autovacuum. Those are optional. You opt in when you are ready.

The changes that bite are the ones you do not opt into. A setting flips, an authentication method disappears, a catalogue object stops being supported, and your application code is untouched but behaves differently on Monday morning. For a Postgres 19 upgrade, that list is short enough to audit in an afternoon, and most of it can be checked with a query.

This piece collects those changes, says who is affected by each, and gives you the SQL to find out whether you are. It is based on the beta documentation as summarised by PlanetScale, DevX and the project itself. Beta behaviour can still change before GA, so treat each item as a thing to verify against the final release notes.

Five changes that need a decision

JIT is off, and your slow reports may notice

JIT compilation has been on by default since Postgres 12. In 19 it is off. PlanetScale’s write-up gives the reasoning: compilation overhead hurts short queries whose cost estimate lands just above the JIT threshold, so a query that should take 20 ms pays for compilation it never needed.

That is a fair trade for OLTP and a bad one for a nightly report that scans 200 million rows. If you run both kinds of workload on one cluster, do not flip jit back on globally. Turn it on for the reporting role or database, and leave the application role alone.

-- What are we running today?
SHOW jit;
SHOW jit_above_cost;

-- After upgrading: re-enable for the reporting role only
ALTER ROLE reporting SET jit = on;

-- Find the queries most likely to have relied on JIT
SELECT queryid, calls, mean_exec_time, jit_functions, jit_generation_time
FROM pg_stat_statements
WHERE jit_functions > 0
ORDER BY total_exec_time DESC
LIMIT 20;

The pg_stat_statements query is the useful part. Run it on your current version, before the upgrade. Any statement with a non-zero jit_functions count is a candidate for a regression, and the list is usually shorter than people expect.

The two defaults that change your logs

Two changes alter what lands in your log files. Lock wait logging is on by default, which is good news for anyone who has debugged a stall without it, but it means a busy cluster will write more lines. And every login with an MD5-hashed password now produces a deprecation warning.

If you still have MD5 hashes, that second one can be loud. A connection pooler that opens hundreds of connections an hour will log a warning for each. Do not suppress the warning permanently. Use it as the nudge it is meant to be and schedule the SCRAM migration. First, find out how many roles are affected.

-- Needs superuser. Lists roles still storing MD5 hashes.
SELECT rolname
FROM pg_authid
WHERE rolpassword LIKE 'md5%';

-- Confirm what new passwords will use
SHOW password_encryption;

What will stop pg_upgrade

Three items can halt or break a migration outright. The first is the one most likely to surprise you.

GiST indexes on inet and cidr columns. The default index behaviour for those types changes, existing indexes need rebuilding, and per the beta notes pg_upgrade refuses to continue while the old ones exist. If you index IP ranges for allow-lists or geolocation lookups, this is you.

-- GiST indexes on inet or cidr columns
SELECT i.indrelid::regclass AS table_name,
       i.indexrelid::regclass AS index_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam
JOIN pg_attribute a ON a.attrelid = i.indrelid
                   AND a.attnum = ANY (i.indkey::int2[])
WHERE am.amname = 'gist'
  AND a.atttypid IN ('inet'::regtype, 'cidr'::regtype);

The second is RADIUS. The method is removed, so a pg_hba.conf line that says radius is a startup problem on the new server. Search the file now. The third is standard_conforming_strings, which is forced on. Old dumps created with it off need to be recreated with the new tools, and any legacy code that relies on backslash escapes inside plain string literals will behave differently.

Also on the list: carriage returns and line feeds are no longer allowed in database, role and tablespace names. Nobody names a database that way on purpose, but a migration script that interpolated unclean input once might have.

REPACK: useful, with one sharp edge

REPACK moves table compaction into core, replacing the pg_repack and pg_squeeze extensions for most uses. Its concurrent mode holds a SHARE UPDATE EXCLUSIVE lock while it copies the table and takes ACCESS EXCLUSIVE only for the final swap. PlanetScale’s example shrinks a bloated 1884 MB table to roughly 1085 MB and clears about 4.2 million dead tuples.

The requirements are specific. wal_level must be replica or higher, the table needs a primary key or a replica identity index, and unlogged tables are excluded. The caveat to put in your runbook is that it is not MVCC-safe: a transaction holding a snapshot from before the swap can see an empty table afterwards. A long-running reporting transaction that overlaps a REPACK is exactly that case.

Six features that did not make it

With beta 4, the project pulled six announced features, per DevX: SQL/PGQ graph queries, temporal FOR PORTION OF updates, partition merge and split, online data checksum toggling, and DDL retrieval for roles, tablespaces and databases.

The stated reason for graph queries was that the implementation lacked path variables and shortest-path search, which made it too incomplete to ship. Coverage of the beta cycle is inconsistent on this point, and some articles still describe the pulled features as if they ship. If your upgrade case depends on any of the six, read the final release notes before you commit to a date.

This is the part of the release process that deserves more credit than it gets. Pulling a feature in the last beta, with a public explanation, is the behaviour you want from the database holding your data.

A short pre-upgrade plan

• Run the four queries above on production and record the results.
• Restore a recent backup to a staging host and run pg_upgrade in check mode against the release candidate.
• Replay a day of read traffic and compare pg_stat_statements between versions, focusing on anything that used JIT.
• Grep pg_hba.conf for radius and fix it first. It is the cheapest item on the list and the most embarrassing to miss.
• Decide on MD5: migrate now, or accept warnings and put a date on the migration.

Postgres rewards teams that treat major upgrades as a routine rehearsal, not an event. Once the final release notes are out, the same checks give you a repeatable template for 20 and beyond.