skip to content

After pg_upgrade from PostgreSQL 14 to 18, which planner statistics arrive with the data, which do not, and what do you run before reopening traffic?

level: seniorimportance: should knowfreq 42%

answer

  1. 18 dumps statistics under the hood
  2. CREATE STATISTICS objects come back empty
  3. cumulative counters start from zero
  4. missing-stats-only, in stages
  5. then analyze-only for autovacuum

basics

~10 s

PostgreSQL 18's pg_upgrade carries most per-column and per-table planner statistics, but not extended statistics, extension-made statistics or cumulative activity counters. Run vacuumdb --all --analyze-in-stages --missing-stats-only, then vacuumdb --all --analyze-only.

solid answer

~40 s

Until 17, `pg_upgrade` transferred **no** optimizer statistics: the new cluster planned on defaults until `vacuumdb --all --analyze-in-stages` finished. In 18, `pg_upgrade` calls the new `pg_dump` with `--statistics`, which restores `pg_statistic` rows and relation-level values such as `reltuples` and `relpages` through `pg_restore_attribute_stats` and `pg_restore_relation_stats`. That works from a 14 source too, because the 18 tools read the old catalogs. Not carried: **extended statistics** from `CREATE STATISTICS`, custom statistics from extensions, and the **cumulative statistics** counters such as `n_dead_tup`. The completion banner says what to run: `vacuumdb --all --analyze-in-stages --missing-stats-only` first, which analyzes only relations missing statistics without replacing good ones with low-target ones, then `vacuumdb --all --analyze-only` so autovacuum has fresh counters. Both accept `--jobs`; `--no-statistics` turns the transfer off.

code

bash · 4 lines
bash
# as a superuser, right after starting the upgraded 18 e-learning cluster
/usr/lib/postgresql/18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only --jobs=8
# then, with traffic back on
/usr/lib/postgresql/18/bin/vacuumdb --all --analyze-only --jobs=8

go deeper

for a junior

Remember that a freshly upgraded cluster needs ANALYZE, and that PostgreSQL 18's pg_upgrade now brings most planner statistics with it.

for a middle

Explain what analyze-in-stages does with its three targets, and which statistics 18 carries and which it leaves behind.

for a senior

Sequence the two vacuumdb runs around reopening traffic, catch empty extended statistics on correlated columns, and know why plain in-stages now hurts.

for a principal

Account for the first hours after an upgrade in the plan: carried statistics shrink the bad-plan window, but extended statistics and autovacuum state still need budgeted work.

## Before 18: an empty pg_statistic The planner estimates row counts from **optimizer statistics**: per-column histograms, most-common values and distinct counts in `pg_statistic`, and per-table sizes in `pg_class`. Until PostgreSQL 17, `pg_upgrade` rebuilt the catalogs from a schema-only dump that carried none of this. The upgraded cluster started with no statistics, every query was planned on default guesses, and the standard step was `vacuumdb --all --analyze-in-stages`. That command runs ANALYZE three times per database: with a statistics target of 1, then 10, then the default, so rough statistics exist quickly and full ones follow. On a 3 TB database the first hours after an upgrade were famous for bad plans. ## What 18 carries over PostgreSQL 18's `pg_upgrade` runs the new `pg_dump` with `--statistics` while capturing the schema. The dump emits calls to two new functions that write the values into the new cluster: - `pg_restore_relation_stats` restores relation-level values: `relpages`, `reltuples`, `relallvisible` and `relallfrozen`; - `pg_restore_attribute_stats` restores the per-column rows of `pg_statistic`, including those of expression indexes. The manual's wording is that it transfers **most** optimizer statistics. Because the 18 `pg_dump` reads the old server's catalogs, this works for a 14 source as well, not only for 18 to 18 upgrades. `--no-statistics` switches it off. ## What it leaves behind | Not transferred | What breaks | Regenerated by | |---|---|---| | Extended statistics (`CREATE STATISTICS`) | The definition comes back but its data does not, so correlated-column estimates fall back to independence | The first command below | | Statistics added by an extension | Whatever the extension's estimates feed | ANALYZE of those relations | | Cumulative statistics (`pg_stat_user_tables` counters such as `n_dead_tup`, `n_mod_since_analyze`) | Autovacuum's triggers start from zero | The second command below | In the e-learning database, a `CREATE STATISTICS` object on `lesson_progress (course_id, lesson_id)` exists after the upgrade, but until it is analyzed the planner multiplies the two columns' selectivities as if they were independent and badly underestimates rows per course. ## The two vacuumdb commands `pg_upgrade` ends by telling you that some statistics are not transferred and printing two commands, both of which take `--jobs`: 1. `vacuumdb --all --analyze-in-stages --missing-stats-only` generates minimal statistics quickly for relations that lack them, meaning a column, index expression or extended statistics object without data. Combined with `--analyze-in-stages`, `--missing-stats-only` stops the low-target first stage from **replacing** the good statistics that came across, which would make plans transiently worse. It needs superuser, because it reads `pg_statistic` and `pg_statistic_ext_data`. 2. `vacuumdb --all --analyze-only` then analyzes everything, so that every relation has updated cumulative statistics for triggering vacuum and analyze. This one can run with traffic back on. If `vacuum_cost_delay` is set, you can override it for these runs with `PGOPTIONS='-c vacuum_cost_delay=0'`. ## Contrast with dump and restore The carry-over is a `pg_upgrade` default only. A plain `pg_dump` in 18 does **not** include statistics (`--no-statistics` is its default; `--statistics` opts in), so a dump-and-restore upgrade still starts empty and needs the old full `vacuumdb --all --analyze-in-stages`. ## Planning the first hour 1. Start the upgraded cluster with application traffic still held back. 2. Run the `--missing-stats-only` pass; on a schema with few extended statistics objects it touches few relations and finishes quickly. 3. Spot-check the plans of the hottest queries, especially those filtering on correlated columns such as course and lesson. 4. Reopen traffic, then run the full `--analyze-only` pass with `--jobs` sized so it does not compete too hard with the workload. 5. Use `--no-statistics` only on purpose, for example when the old statistics are known to be stale and you intend to analyze everything anyway; it brings back the pre-18 empty start. ## Checking what is still missing - Query `pg_stats_ext` for your statistics objects; no row with data means it has not been analyzed yet. - Compare `EXPLAIN` estimates on key queries before and after the first command. - Watch `last_analyze` and `last_autoanalyze` in `pg_stat_user_tables` to see the second pass progress.

  • Why not simply run plain vacuumdb --all --analyze-in-stages after an 18 pg_upgrade, as on older versions?
    Its first stage analyzes every relation with a statistics target of 1, replacing the full statistics `pg_upgrade` carried across with rough ones, so plans get transiently worse until the last stage finishes. `--missing-stats-only` restricts the run to relations missing statistics, keeping the good ones.
  • If statistics were carried, why does pg_upgrade still recommend vacuumdb --all --analyze-only?
    The cumulative statistics system is not transferred, so counters such as `n_dead_tup` and `n_mod_since_analyze` start from zero, and autovacuum decides when to work from those counters. Analyzing every relation once gives each one updated cumulative statistics, as the manual puts it, for triggering vacuum and analyze.

  • Since PostgreSQL 18, pg_upgrade carries every statistic, so no ANALYZE is needed afterwards.
  • Extended statistics created with CREATE STATISTICS survive pg_upgrade like column statistics.
  • Statistics transfer only works when the old cluster is already on PostgreSQL 18.
  • Plain --analyze-in-stages is harmless on tables that already have good statistics.
  • pg_stat_user_tables counters such as n_dead_tup carry across the upgrade.

Assumes PostgreSQL 18 as the target: statistics transfer, pg_upgrade --no-statistics and vacuumdb --missing-stats-only are new in 18; with a 17 target nothing is carried.