PostgreSQL 18 pg_upgrade Statistics: Safer Major Upgrades

PostgreSQL major upgrades are usually judged by downtime, extension compatibility, and application test results. Query plans often get less attention until the first busy hour after cutover, when a familiar endpoint suddenly chooses a bad join order because the new cluster has not collected enough planner statistics yet.

PostgreSQL 18 changes that upgrade day risk. The official release notes list a practical improvement for pg_upgrade: it now retains optimizer statistics. That does not remove the need for post-upgrade ANALYZE work, but it narrows one of the most common performance gaps between the old cluster and the new one.

For teams running managed PostgreSQL, this is good news because it makes major-version upgrades less dependent on a long statistics warm-up window. The useful question is not whether ANALYZE goes away. It does not. The useful question is how to adjust the upgrade runbook so the retained statistics help without creating a false sense of safety.

What changed in PostgreSQL 18

PostgreSQL's query planner depends on statistics about tables and columns. ANALYZE stores those estimates in system catalogs so the planner can decide whether to use an index scan, sequential scan, nested loop, hash join, or another strategy. If those estimates are missing or badly out of date, the SQL text can be correct while the plan is still expensive.

Before PostgreSQL 18, a pg_upgrade workflow commonly included a very visible post-upgrade statistics step. The upgraded cluster could start quickly, but many relations needed fresh optimizer statistics before the planner had enough information to make stable decisions. On large databases, that made the period immediately after cutover operationally awkward: the database was online, but query quality could be uneven until analysis completed.

In PostgreSQL 18, pg_upgrade transfers most optimizer statistics from the old cluster unless the --no-statistics option is used. The pg_upgrade documentation is careful about the word "most." It does not transfer every kind of statistic, including statistics created explicitly with CREATE STATISTICS, custom statistics added by extensions, or cumulative statistics. PostgreSQL still instructs operators to regenerate missing and cumulative information after the upgrade.

Why retained statistics matter

The benefit is mostly about the first minutes and hours after a major upgrade. A planner with usable inherited statistics is less likely to treat large production tables like unknown objects. That can reduce the chance of avoidable plan regressions during the same window when engineers are already watching replication, application errors, migration scripts, connection pools, and rollback criteria.

This matters most for applications with large tables, skewed values, and expensive joins. A small CRUD app may finish post-upgrade analysis quickly enough that the change is barely visible. A multi-tenant SaaS database with several high-cardinality tables, partial indexes, and hot customer-specific access patterns is more sensitive. The retained estimates give the planner a better starting point while the new cluster settles.

It also changes how teams should talk about upgrade readiness. In older runbooks, "run ANALYZE" could become a blunt instruction that hid the real goal: get the planner enough accurate information before full traffic returns. With PostgreSQL 18, the question becomes more specific. Which statistics came across, which did not, and which relations changed enough during the upgrade window that a fresh analysis is still important?

What to keep in the upgrade checklist

The safest PostgreSQL 18 runbook still includes an explicit statistics phase. The pg_upgrade documentation recommends using vacuumdb --all --analyze-in-stages --missing-stats-only first to quickly generate minimal optimizer statistics for relations that do not have them. It then recommends vacuumdb --all --analyze-only so relations have updated cumulative statistics for vacuum and analyze behavior. The documentation also notes that --jobs can speed this up, and that PGOPTIONS can override a non-zero vacuum_cost_delay when the maintenance window should favor speed.

In practice, treat those commands as part of the upgrade, not as optional cleanup. Retained statistics help user traffic survive the early window, but missing extended statistics and newly active tables can still produce bad plans. If the application has a small set of revenue-critical queries, capture their plans before the upgrade and compare them after the upgrade under realistic parameter values. The fastest way to catch a regression is to know which plans would be unacceptable before the cutover starts.

For managed PostgreSQL users, the operational shape depends on the provider. Some providers hide pg_upgrade and expose major upgrades as a button or scheduled maintenance operation. Others support dump/restore or logical replication paths instead. The feature discussed here specifically applies to pg_upgrade. If your provider performs major upgrades through another path, ask how planner statistics are handled and whether any post-upgrade analysis is performed for you.

ArmorDB users should still think in the same terms even when the platform handles the database operations: major upgrades are safest when application owners know their critical queries, maintenance window, and rollback expectations. A managed service can remove server administration, but it cannot automatically know which slow report or checkout query matters most to your product. For related operational planning, see the ArmorDB guide to PostgreSQL migration strategies.

Where the improvement helps most

The change is especially useful for databases that cannot afford a long period of planner uncertainty after cutover. Large SaaS applications often have tables where the distribution matters more than the row count: one tenant may own a large share of rows, a status column may be heavily skewed, or a date range query may behave differently for recent and historical data. Statistics are how PostgreSQL models that shape.

It also helps teams that use short maintenance windows. If the application must return to service quickly, the ability to carry most optimizer statistics forward can reduce the amount of performance risk that remains after the cluster starts. That does not mean full traffic should return blindly. It means smoke tests and query checks begin from a better default state.

The improvement is less meaningful when the upgrade path is not pg_upgrade, when the database is small enough to analyze quickly, or when schema and data distribution change substantially during the migration. For example, a migration that rewrites large tables, changes partitioning, or backfills major columns can invalidate assumptions from the old cluster. In those cases, fresh analysis remains central.

A practical PostgreSQL 18 upgrade flow

Start by inventorying the queries that would hurt if they regressed: login, checkout, dashboard loading, background job dispatch, billing exports, and customer-facing search. Capture their pre-upgrade EXPLAIN output with representative parameters, not just synthetic values. The goal is not to freeze every plan forever; it is to know whether the new cluster chooses obviously worse access paths.

During the upgrade, confirm whether the path is pg_upgrade and whether the default statistics transfer is enabled. Avoid using --no-statistics unless there is a specific reason. After the upgraded cluster starts, run the recommended vacuumdb analyze sequence, with parallel jobs where appropriate for the maintenance window. Then compare the critical plans and watch latency, buffer reads, and lock behavior as traffic returns.

Finally, keep the observation window longer than the first successful smoke test. Autovacuum and auto-analyze continue to refine statistics as rows change. If a workload has daily or weekly patterns, some regressions only appear when the less common path runs. A short list of watch queries in the first week after upgrade is often more valuable than a broad dashboard nobody reads.

Takeaway

PostgreSQL 18's pg_upgrade statistics retention is a quiet but meaningful production feature. It reduces one source of post-upgrade plan instability and makes major upgrades friendlier for large databases. It does not eliminate ANALYZE, extended-statistics checks, provider-specific upgrade questions, or application-level plan validation.

The right runbook is simple: use the retained statistics as a safer starting point, run the documented post-upgrade analysis anyway, and verify the few queries your product cannot afford to get wrong.

Sources and further reading

• PostgreSQL 18 release notes: https://www.postgresql.org/docs/18/release-18.html
• PostgreSQL 18 pg_upgrade documentation: https://www.postgresql.org/docs/18/pgupgrade.html
• PostgreSQL ANALYZE documentation: https://www.postgresql.org/docs/18/sql-analyze.html
• PostgreSQL planner statistics documentation: https://www.postgresql.org/docs/18/planner-stats.html