"Queries got slow after upgrading PostgreSQL" is a common complaint, and one of the usual causes is a step that's easy to skip because the upgrade itself already reported success. When I upgraded my search engine's database from PostgreSQL 15 to 16, the upgrade notes record that step done before any traffic was allowed back: rebuild the planner's statistics. This is why that line exists, and what changed about it in PostgreSQL 18.
What pg_upgrade leaves behind
The upgrade used pg_upgrade --link, which hard-links the data files into the new cluster instead of copying them; that was the only mode that fit, with about 124 GB of free disk against a database of around 211 GB. The tables came across intact, and the verification afterwards compared row counts: 144,299,656 rows in url, 679,600,534 in link and 4,409,860 in host, the same as before.
What didn't come across were the planner's statistics. PostgreSQL's documentation for versions 16 and 17 says it plainly: because optimizer statistics are not transferred by pg_upgrade, it tells you to run a command to regenerate them at the end of the upgrade. Until that runs, the planner is choosing join orders and access paths for hundreds of millions of rows without knowing how the data is distributed.
I've seen what that looks like on the same database. In a separate incident, two newly added columns had never been analyzed, and the planner guessed 22,000 matching hosts where the real number was zero, then picked a plan that scanned a whole table on every cycle. After a major-version upgrade without statistics, every table is in that position.
vacuumdb --analyze-in-stages
The command for it is vacuumdb with --analyze-in-stages. According to its documentation, it only calculates statistics for the optimizer, with no vacuum, in three stages: the first uses the lowest possible statistics target to produce usable statistics quickly, and the later stages build the full statistics. The documentation also says when to use it: only for a database that has no statistics or wholly incorrect ones, such as one just populated by pg_upgrade, because in a database with good statistics the early, rough stages can make plans temporarily worse.
On this upgrade, all three stages ran to completion before traffic resumed. The upgrade notes record that it was fast even at this scale, because ANALYZE works from a sample of each table rather than reading all of it. Only then were the crawlers and indexers started again, and the logs showed normal passes.
The order matters more than the speed. The first stage exists so you could let traffic back early and have rough statistics within minutes. Waiting for all three stages here was cheap insurance against the first hour of traffic running on guessed plans.
What changed in PostgreSQL 18
If you're upgrading today, the documentation for PostgreSQL 18 describes different behavior: unless you pass --no-statistics, pg_upgrade now transfers most optimizer statistics from the old cluster. Not all of them: statistics created explicitly with CREATE STATISTICS, custom statistics from extensions, and the cumulative statistics system's counters are left behind. So it still tells you to regenerate what's missing, and suggests starting with vacuumdb --all --analyze-in-stages --missing-stats-only.
The step doesn't go away; it gets cheaper. On 16 or 17, analyze everything before you let the application back in. On 18, analyze what's missing, and check that any extended statistics you rely on came back.
The same upgrade had its share of other surprises, from hard links that needed one shared mount to a new cluster that only talked to itself. Statistics were the quiet one: nothing fails without them. Queries just get slower.
Leave a comment
Comments are reviewed before they appear.