Module 7: Statistics, Autovacuum & Planner

You reached the module where you open PostgreSQL's black box. Until now you solved problems from the outside in: end-to-end measurement (module 1), plans and EXPLAIN (module 2), indexes the planner actually uses (module 3), N+1 from the ORM (module 4), query profiling (module 5), and connection pooling (module 6). There's one case none of those tools solves: the planner ignores an obvious index and decides to do a Seq Scan over 10 million rows, even though you have a perfect index, the query is well written, and the pool isn't saturated.

This isn't a bug or magic. It's a symptom that the planner is flying with wrong information: stale statistics, autovacuum falling behind, cost parameters calibrated for hardware that no longer exists (HDD), or missing extended statistics on correlated columns. Until you understand what information the planner uses to decide, optimizing queries is an expensive trial-and-error process.

This module teaches you the planner's mental model — what inputs it consumes, how it picks a plan, where it breaks — and the operational tools (ANALYZE, autovacuum, CREATE STATISTICS, VACUUM/pg_repack, cost parameters) to make sure those inputs are correct. By the end you'll be able to diagnose a planner that decides poorly and fix it in minutes instead of hours.

Where are we? Where are we going?

What you already know (modules 1 through 6):

Measure a baseline with wrk/locust, distinguish p50/p95/p99 (module 1).
Read plans with EXPLAIN (ANALYZE, BUFFERS) and understand cost, scan types, JIT (module 2).
Design advanced indexes (composite, covering, partial, expression) that the planner actually uses (module 3).
Eliminate N+1 with selectinload/joinedload and know the special async rules (module 4).
Identify expensive queries in production with pg_stat_statements and auto_explain (module 5).
Configure pooling correctly with SQLAlchemy + PgBouncer in transaction mode (module 6).

What you're going to build this time:

The planner's mental model: understanding that it's a deterministic component that makes decisions from two controllable inputs — statistics about the data and cost parameters about the hardware. If the chosen plan is bad, one of those two is wrong. You're going to learn to inspect both, update them, and tune them.

Why this module comes here:

Module 6 closed the application side: tuned pool, controlled connections, PgBouncer gotchas resolved. The only thing left in the chain is the PostgreSQL engine itself. A module earlier would have been premature — you'd have to mentally set aside pooling and N+1 problems to understand the planner. Now that you've ruled out everything on the outside, the next logical focus is the inside.

And this module comes before module 8 (anti-patterns + final project) because several classic anti-patterns (a slow COUNT(*) that seems to make no sense, a planner that ignores a perfect index) require understanding statistics to diagnose them correctly. Without this module, module 8 would be a list of "do this, not that" without the theory that justifies it.

Professional objective

By the end of this module you'll be able to:

Explain the planner's mental model: statistics (pg_stats, pg_class.reltuples) + cost parameters → estimated cost per candidate plan → winning plan.
Detect stale statistics by comparing estimated rows vs actual rows in EXPLAIN ANALYZE, and fix them with a manual ANALYZE when it applies.
Create extended statistics with CREATE STATISTICS for correlated columns (typical case: country and city), which the planner assumes independent by default.
Distinguish VACUUM from ANALYZE and understand what each one does inside autovacuum.
Tune autovacuum per-table (not globally), lowering autovacuum_vacuum_scale_factor for large tables where the defaults let bloat accumulate before cleaning.
Decide between VACUUM FULL and pg_repack to reclaim bloat. Know why VACUUM FULL is almost always the wrong answer in production.
Adjust cost parameters for SSD: random_page_cost = 1.1 and effective_cache_size = 75% RAM, instead of the defaults calibrated for HDD that distort the chosen plan.

Why does this module matter?

A poorly informed planner is the root cause of the most confusing performance incidents in production. This isn't theoretical:

After a massive bulk insert (loading historical data, migrating a provider), the planner thinks the table still has its old size and picks a Seq Scan over the new 50M-row table. Latency explodes from 20ms to 12 seconds.
After a massive DELETE (cleaning up old data), the pages are left full of dead tuples. Queries keep touching those pages because autovacuum couldn't clean them in time. Throughput drops by half for no apparent reason.
Migrating from a bare-metal instance with HDD to a cloud instance with SSD, queries that ran fine on HDD start doing Seq Scan instead of Index Scan, because random_page_cost = 4.0 (default) lies to the planner about the cost of random access on SSD.
After months without touching autovacuum, an events table with 200M rows has 80M dead tuples. The table physically weighs 4x more than its live data. Queries touching that table get progressively slower.

You prevent all of those cases (or diagnose and resolve them in under an hour) if you master the content of this module.

In the real role of a senior backend dev, this is the module that separates you from anyone who only "knows SQL." Any developer writes queries. Only people with real operational experience know that ANALYZE isn't optional after a bulk load, that random_page_cost = 4.0 is a legacy of the HDD era, and that pg_repack exists.

For senior interviews, this topic shows up in questions like: "you have a query that runs in 50ms in staging and takes 30 seconds in production over the same data — what do you check?" Without this module, you don't have a systematic answer; with this module, your first check is statistics (EXPLAIN ANALYZE to see estimated vs actual, pg_stat_user_tables to see last_analyze), and your second is cost parameters.

A scenario that illustrates the module

Your team loaded historical order data over the weekend: 5 million rows inserted into the orders table, which previously had 500 thousand. Monday morning, support tells you that the /orders/by-customer/{customer_id} endpoint is responding in 8 seconds. Before the weekend it ran in 40 milliseconds.

Your first reflex (after modules 1 through 6) is:

Run EXPLAIN ANALYZE over the exact query the endpoint uses. This rules out pool/N+1 problems.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;

The plan shows:

Seq Scan on orders  (cost=0.00..98123.45 rows=420 width=128)
                   (actual time=2341.23..7892.56 rows=18 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 5499982
Planning Time: 0.18 ms
Execution Time: 7892.84 ms

You spot something odd: the planner estimates rows=420 but reality is rows=18. The estimate is asking for a Seq Scan because it thinks the query returns 420 rows (which would be too unselective to use an index). But the bigger problem is that it's reading 5.5 million rows to return 18.

You have an obvious index: CREATE INDEX idx_orders_customer ON orders(customer_id);. You confirm it with \d orders. Why does the planner ignore it?

Without what you learn in this module, you'd try things blindly: force enable_seqscan = off (a patch, not a solution), recreate the index, rewrite the query. With this module, the debugging goes like this:

Capsule 02 (how the planner decides): you go to pg_stats and see that the selectivity estimate is based on a distribution from a week ago, before the bulk insert.
Capsule 03 (manual ANALYZE): you run ANALYZE orders;. The planner now has fresh statistics. You re-run EXPLAIN. Now it picks Index Scan. Latency drops from 8 seconds to 12 milliseconds.
Capsule 04 (extended statistics): you realize your typical query is WHERE customer_id = X AND status = 'pending', where customer_id and status are correlated. You create extended statistics so the planner stops underestimating.
Capsule 05 (MVCC and bloat): investigating, you find that the orders table has 1.2M dead tuples because the bulk load was done with INSERT ... ON CONFLICT UPDATE (which creates dead tuples on each update). You're going to understand why that happened.
Capsule 06 (autovacuum): you adjust autovacuum_vacuum_scale_factor per-table to 0.05 so autovacuum runs sooner on orders (a large table with many updates).
Capsule 07 (VACUUM FULL vs pg_repack): you recover the accumulated space with pg_repack (no downtime) instead of VACUUM FULL (which locks the table for 20 minutes).
Capsule 08 (cost parameters): you set random_page_cost = 1.1 (you're on SSD), which makes the planner prefer indexes with more confidence on similar queries.

The Monday incident happens once. After the module, on future bulk loads your deploy script runs ANALYZE automatically, autovacuum has the right config, and the cost parameters are already aligned with your hardware. It doesn't happen again.

Module map

Capsule	Topic	What you'll learn
01	Module introduction	You're here. Map, expectations, scenario.
02	How the planner decides	`pg_stats`, `pg_class.reltuples`, cost estimation, the chosen plan as a deterministic function.
03	Manual `ANALYZE` and stale stats	When to run it (after bulk operations, schema changes), how to detect the problem with `EXPLAIN`.
04	Extended statistics	`CREATE STATISTICS` for correlated columns, the 3 types (`dependencies`, `ndistinct`, `mcv`).
05	MVCC and bloat	Why UPDATE/DELETE don't free space immediately, how to detect bloat with `pg_stat_user_tables`.
06	Autovacuum	What it does exactly, defaults, per-table tuning for large tables.
07	`VACUUM FULL` vs `pg_repack`	Operational differences, when to use each, why `pg_repack` is the right answer in production.
08	Cost parameters SSD + Project	`random_page_cost`, `effective_cache_size`, integrative mini-project with a bookstore.

Narrative flow: first you understand how the planner decides (02). Then you make sure the inputs it uses are correct: fresh statistics (03), extended statistics for correlations (04). Then you understand the other connected operational problem: bloat from MVCC (05), autovacuum as the system that prevents it (06), pg_repack to repair it once it's happened (07). You close with cost parameters for modern hardware (08) plus a mini-project that ties everything together.

Connection with the capstone project

The guide's final project (module 8) is a bookstore API with five performance problems planted on purpose. Two of those problems are within this module's scope:

Recent bulk insert without ANALYZE run: the orders table has one million rows added the night before. The planner estimates 100 thousand rows (the old ones) and picks a Seq Scan. Solution: ANALYZE orders; and validate with EXPLAIN.
Correlated columns without extended statistics: the bookstore queries filter by (order_status, shipped_at) — correlated columns (shipped orders always have shipped_at IS NOT NULL, pending ones never do). Without CREATE STATISTICS, the planner underestimates by a factor of 10.

The mini-project of this module (capsule 08) is a more focused version of the problem: a bookstore with a planner that ignores an obvious index, and you have to diagnose and fix it. You reach the module 8 project with the pattern already internalized.

What is NOT covered in this module

EXPLAIN, scan types, indexing: modules 2-3.
Client-side tuning (pool, ORM): modules 4 and 6.
pg_stat_statements, auto_explain: module 5.
Replication, hot standby, read replicas: belongs to future scaling and HA guides.
Tuning shared_buffers, work_mem, maintenance_work_mem from the DBA's perspective: we mention it when it comes up but we don't go deep into server sizing. That belongs to DBA guides.
Partitioning as an alternative to giant tables with bloat: belongs to guide #14 (Advanced PostgreSQL for Backend), partitioning module.
ALTER STATISTICS ... SET STATISTICS to raise the histogram sample size per column: we mention it briefly, without going deep into extreme tuning cases.

Traps to avoid while taking the module

1. "The planner is magic, it optimizes on its own." The planner is deterministic. Given the same statistics and the same configuration, it always picks the same plan. If it picks badly, it's not because "it made a mistake" — it's because the statistics or the configuration are wrong. Internalizing this changes how you debug: instead of guessing, you move to inspecting concrete inputs.

2. "I'll run VACUUM FULL to clean up bloat, I saw it on a blog." VACUUM FULL rewrites the table and takes an exclusive lock. On large tables it can take hours and blocks all queries (reads and writes). In production this means downtime. The right answer is pg_repack, which does the same thing online. Capsule 07 shows you the exact pattern.

3. "Autovacuum works on its own, I don't need to touch it." The autovacuum defaults (autovacuum_vacuum_scale_factor = 0.2) are calibrated for small tables. On a 100-thousand-row table, vacuum runs when there are 20 thousand dead tuples — manageable. On a 100-million-row table, vacuum runs when there are 20 million dead tuples — massive accumulated bloat. For large tables with many updates, you have to adjust per-table. Capsule 06 teaches you when and how.

4. "The default cost parameters are already fine." random_page_cost = 4.0 is the default and it's calibrated for HDD, where random access is 4x more expensive than sequential. On SSD that ratio is ~1.1x. If you don't adjust, the planner overestimates the cost of Index Scan and prefers Seq Scan when it shouldn't. This is one of the settings that in 2026 almost nobody should still have at its default — but it is, in thousands of installations.

5. "If the stats are wrong, I'll fix them each time with ANALYZE." Running a manual ANALYZE is a reactive patch. The right thing is to understand why the stats got stale — slow autovacuum, schema changes without re-analyze, bulk operations in deployment scripts without a following ANALYZE — and attack the root cause. If you end up running a manual ANALYZE every week, there's a process that needs adjustment.

6. "Extended statistics are an exotic feature I don't need." CREATE STATISTICS (PostgreSQL 10+) seems advanced but it solves a very common case: any pair of correlated columns (country and city, status and related timestamps, category and price in e-commerce). Without extended statistics, the planner assumes independence between columns and underestimates by factors of 10x-100x. Detecting this problem and solving it is one of the cleanest wins in the module.

Self-assessment question

Before starting this module, can you answer these questions?

What information exactly does the planner use to decide between Seq Scan and Index Scan?
What does ANALYZE do that's different from what VACUUM does?
When should you run ANALYZE manually instead of leaving it to autovacuum?
What is a "dead tuple" and why does it exist in PostgreSQL?
Why is VACUUM FULL dangerous in production?
Which PostgreSQL default is calibrated for HDD and needs adjustment for SSD?
If you have a query that filters by two correlated columns (country = 'Mexico' AND city = 'Mexico City'), why does the planner underestimate how many rows it returns?

If you hesitate on more than three, don't worry — this is exactly what we explain in the next capsules. If you hesitate on all of them, consider reviewing the EXPLAIN section of module 2 and the profiling section of module 5 before continuing (they're base context that we're going to go deeper on).

Evidence of success

By the end of the module, you'll know you succeeded if:

You can look at an EXPLAIN ANALYZE and detect in under 30 seconds whether the statistics are stale (comparing estimated vs actual rows).
You know which query to run (SELECT * FROM pg_stats WHERE tablename = '...') to inspect what the planner sees, and you can interpret the output.
You understand the difference between VACUUM, ANALYZE, autovacuum, VACUUM FULL, and pg_repack, and you can choose among them depending on the situation.
You can audit the autovacuum configuration on a specific table with SELECT relname, reloptions FROM pg_class WHERE relname = '...' and propose per-table adjustments when the global defaults don't fit.
Your postgresql.conf (or its equivalent in deploy scripts) has random_page_cost = 1.1 and effective_cache_size set to 75% of the available RAM.
In the module's mini-project (capsule 08) you take a query from 8 seconds to under 50 milliseconds with three documented interventions: ANALYZE, CREATE STATISTICS, and adjusting random_page_cost.

We start in the next capsule

We start with capsule 02: how the planner decides. You're going to open the black box: which PostgreSQL catalog tables store statistics, how the planner consults them, what formula it uses to choose between two candidate plans. It's the conceptual base you need before touching ANALYZE, autovacuum, or cost parameters — without understanding what's consuming that information, adjusting it is done blindly.

Before moving on, make sure you have PostgreSQL 16 running locally (Docker or native) with permissions to run ANALYZE and read from the catalog (pg_stats, pg_class, pg_stat_user_tables). If you followed the guide's setup up to here, you already have it.

Resources for the module

PostgreSQL Docs — Statistics Used by the Planner — official reference on pg_stats and how the planner consumes them.
PostgreSQL Docs — Routine Vacuuming — official reference for VACUUM, ANALYZE, and autovacuum.
PostgreSQL Docs — CREATE STATISTICS — reference for extended statistics.
pg_repack — Documentation — online alternative to VACUUM FULL for reclaiming bloat.
Bruce Momjian — "Internals of the Query Planner" — talks and notes on how the planner makes decisions.
Hubert "depesz" Lubaczewski — Posts on ANALYZE and autovacuum — deep analysis of real production behavior.
Tomas Vondra — "Extended Statistics in PostgreSQL" — the feature's main author, talks with real cases.

Module 7 — Database Performance & Query Tuning Guide

← PreviousModule 6 project: tuning the bookstore's pool Next →How the planner decides