PostgreSQL Slow After an Upgrade? Check Statistics First

A major PostgreSQL upgrade can finish cleanly and still leave the application slower the next morning. The most common cause is not the new version's planner. It is that the upgraded cluster starts with no optimizer statistics, so the planner guesses row counts until ANALYZE runs. This guide reproduces that on a real pg_upgrade from PostgreSQL 14 to 17, shows how to detect it, and lists the checks to run before and after an upgrade.

The lab used generated data in Docker: PostgreSQL 14.24 upgraded to 17.11 with pg_upgrade --link. It is not a client system, and the timings are for illustration.

What pg_upgrade tells you, and what it does not do

pg_upgrade copies or links the data files and recreates the catalog. Before PostgreSQL 18, it does not carry over optimizer statistics. It ends with this reminder, which is easy to miss in an automated run:

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/17/bin/vacuumdb -U postgres --all --analyze-in-stages

Amazon RDS and Aurora use pg_upgrade for in-place major version upgrades. The RDS major upgrade guide tells you to run ANALYZE on every database afterwards, because optimizer statistics are not transferred.

The lab: same query, same data, different plan

The lab database had 3 million orders and 200,000 customers, with indexes on orders(status) and orders(customer_id). 90% of orders have status paid. The query reports paid revenue by customer region:

SELECT c.region, count(*), sum(o.total)
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.region;

Without statistics, the planner assumed status = 'paid' was selective, chose a nested loop, and then performed 2.7 million index lookups on customers. On a larger table or a busier server, the same mistake turns a sub-second report into a timeout, and many such plans at once look like a CPU or I/O incident.

Do not wait for autovacuum to fix it

Autovacuum analyzes a table after enough rows change since the last analyze. After pg_upgrade, the activity counters start at zero. In the lab, with autovacuum_naptime lowered to 10 seconds, neither table had been analyzed two minutes after start-up and n_mod_since_analyze was still 0. A large, mostly-read table can stay without statistics for a long time. Check directly:

-- Tables with no column statistics at all
SELECT t.schemaname, t.relname, t.n_live_tup, t.last_analyze, t.last_autoanalyze
FROM pg_stat_user_tables AS t
WHERE NOT EXISTS (
  SELECT 1 FROM pg_stats AS s
  WHERE s.schemaname = t.schemaname AND s.tablename = t.relname
)
ORDER BY t.n_live_tup DESC;

-- Planner size estimates: -1 means the table has never been analyzed or vacuumed
SELECT relname, reltuples::bigint, relpages
FROM pg_class
WHERE relkind IN ('r', 'p') AND relnamespace = 'public'::regnamespace;

Straight after the upgrade, the lab returned no rows from pg_stats and reltuples = -1 for both tables.

Rebuild statistics in the right order

vacuumdb --all --analyze-in-stages

--analyze-in-stages runs three passes: statistics target 1, then 10, then the configured default. The first pass is quick and removes the worst guesses, so the application can be let back in sooner. On RDS and Aurora, run it from a client host with the master user, or run ANALYZE VERBOSE; in each database. Two details matter:

• Partitioned tables: autovacuum does not analyze the partitioned parent. Run ANALYZE on each parent so the planner has statistics for queries across partitions.
• Extended statistics created with CREATE STATISTICS need ANALYZE too, including on PostgreSQL 18.

PostgreSQL 18 changes this, partly

From PostgreSQL 18, pg_upgrade retains optimizer statistics. Extended statistics are not preserved, and --no-statistics turns the new behavior off. vacuumdb also gains --missing-stats-only, which computes only missing statistics; it requires a superuser and works with --analyze-only or --analyze-in-stages. If you are leaving PostgreSQL 14 for 18, still run the missing-statistics check above. Managed services can lag behind community behavior, so confirm what your provider documents for the target version.

Extensions are not upgraded for you

The upgrade keeps each extension at its old version. In the lab, pg_stat_statements created at 1.9 stayed at 1.9 on PostgreSQL 17, where 1.11 is available. Older extension versions can lack columns your monitoring expects. AWS states that a PostgreSQL upgrade does not upgrade extensions. Find and update them:

SELECT e.extname, e.extversion AS installed, a.default_version AS available
FROM pg_extension AS e
JOIN pg_available_extensions AS a ON a.name = e.extname
WHERE e.extversion <> a.default_version;

ALTER EXTENSION pg_stat_statements UPDATE;

Read each extension's release notes first. PostGIS, pgvector and similar extensions can change behavior or need an index rebuild between versions.

A checklist that prevents the morning-after regression

Before the upgrade

• Capture a baseline: the top statements from pg_stat_statements by total time, and EXPLAIN (ANALYZE, BUFFERS) for the 10–20 most important queries. Cumulative statistics views start empty on the new cluster, so this is your only comparison point.
• Rehearse on a snapshot restore or clone, then run the same queries against the upgraded copy before and after ANALYZE.
• List extensions and their target versions. On RDS, also check the provider's prerequisites: open prepared transactions, logical replication slots and unsupported data types block the upgrade.
• Plan the time to analyze. Measure it on the rehearsal copy; it grows with table count and the statistics target, not just data size.

Straight after the upgrade

• Run vacuumdb --all --analyze-in-stages (or ANALYZE per database) before opening full traffic if the window allows.
• Run the missing-statistics query and analyze partitioned parents.
• Update extensions after reading their notes.

Validation

• Compare the top statements by total time and the baseline plans with the pre-upgrade capture.
• Watch for plans that changed shape, not just time: a nested loop where there was a hash join is the signature of a missing or stale estimate. PlanReader flags estimate errors per node if you paste in the before and after plans.
• Keep the rollback path documented: on RDS the pre-upgrade snapshot; self-managed with --link, the old cluster cannot be started once the new one has run, so take a backup first.

The full order of work, including the blockers pg_upgrade and RDS check for, is in the PostgreSQL 14 upgrade checklist. If CDC reads from the database, read what happens to logical replication slots before the window.

The PostgreSQL 14 deadlines make this timely. See PostgreSQL 14 EOL planning and the RDS minor-version dates. An upgrade-readiness review does the baseline, rehearsal comparison and post-cutover checks with you.