The dashboard broke at nine in the morning and the database had done nothing wrong overnight, which was exactly the problem. Every night a bulk load copied a few hundred million fresh rows into the fact table, and every morning the planner, working from statistics collected before the load, produced plans for a table it believed was a fraction of its real size. A join that should have been a hash over yesterday's partitions became a nested loop expecting fifty rows and meeting five million. The fix took one command, ANALYZE on the loaded table, and about twenty seconds. The diagnosis took longer, because from the outside stale statistics look like everything except stale statistics: a slow query, a suspicious plan, an index that suddenly seems ignored.
Autoanalyze exists precisely so this does not happen, and yet in every busy shop I have worked in, it eventually does, because the default thresholds were designed for steady-state OLTP tables and not for tables whose shape changes in bulk. Understanding when the daemon will run, and when it will not, is the difference between trusting it and being woken up by it.
How autoanalyze decides a table needs it
Autoanalyze is one half of the autovacuum daemon's job, the half nobody writes panicked blog posts about. Where autovacuum reclaims dead tuples, autoanalyze refreshes the planner's statistics: row counts, value distributions, most-common values, histograms, null fractions, per column, per table. The daemon tracks how many rows have been inserted, updated, or deleted since the last analyze, and it fires when that count crosses a threshold computed from two settings:
SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;The defaults are a threshold of 50 rows plus a scale factor of 0.1, meaning ten percent of the table. The formula is simple: analyze when modifications since the last analyze exceed 50 plus 0.1 times the row count. For a table of ten thousand rows, that is about a thousand changes, which is reasonable. For a fact table of four hundred million rows, it is forty million changes before the daemon considers the statistics worth refreshing. A two-hundred-million-row nightly load does cross that line, and the daemon will come around on its own, but only once a worker picks the table up after the load commits, and on a system whose workers are already busy elsewhere that queue is where minutes stretch into much longer. Until it finishes, morning queries run against distribution statistics collected from a table that no longer exists. My nine-in-the-morning incident lived exactly in that window.
There is a second, sneakier version of the same trap: a table loaded from empty. Here the planner's size picture is less broken than you might expect, because COPY refreshes the table's pg_class size counters when it commits, and the planner scales stale row estimates by the current page count anyway. What does not refresh is the part that usually matters: the column distributions. The histograms, most-common values, and distinct-count estimates still describe an empty or tiny table until something analyzes, and selectivity estimates built on those are how you get a nested loop expecting fifty rows and meeting five million. The insert-triggered threshold will fire eventually, but "eventually" is not a plan, and the queries that run in between are reliably terrible.
Why stale statistics break plans
The planner's entire cost model is downstream of these numbers. Row count estimates decide join order and join strategy: nested loops are wonderful when the inner side is fifty rows and catastrophic when it is five million. Selectivity estimates decide whether an index is used or scanned past. Distribution statistics decide how much memory a hash aggregate thinks it needs. When the statistics are stale, every one of those decisions is made with confidence and made wrong, and the maddening part is that nothing in the error output says so. The query just runs slowly, and the plan just looks plausible, until you put estimated row counts next to actual ones and watch them diverge by orders of magnitude.
The companion setting here is default_statistics_target, which controls how much detail ANALYZE gathers per column: the histogram bins and most-common-value entries. The default is 100, and it governs the tradeoff between estimate quality and the cost of planning. For most columns 100 is fine. For columns with skewed distributions, the ones where a handful of values carry most of the rows, raising the target on that specific column buys visibly better estimates:
ALTER TABLE fact_sales ALTER COLUMN store_id SET STATISTICS 500;
ANALYZE fact_sales;The order matters: the new target applies to analyze runs that come after it, so set it first and then re-analyze, or the existing statistics stay at the old detail level. And when the misestimate comes not from skew but from correlation, two columns the planner multiplies together as if independent, per-column statistics cannot fix it no matter the target. That is what extended statistics with CREATE STATISTICS are for, and recognizing which failure you have, skew versus correlation, is most of the craft.
When to run ANALYZE by hand
The rule I operate by is short: after any event that changes a table's shape faster than the daemon will notice, analyze it yourself, immediately, as part of the same job. That means after a bulk load with COPY or a large INSERT SELECT, after a backfill or large UPDATE, after a big DELETE, after restoring a dump, and after every major-version upgrade, because pg_upgrade does not carry statistics across and the fresh cluster starts with none. The post-upgrade case has its own tooling: vacuumdb with the analyze-in-stages options builds statistics in passes, cheap fast ones first so the planner has something to work with, then full-detail ones. Skipping that step is how an upgrade that tested perfectly in staging spends its first production day planning blind.
Two properties make manual ANALYZE easy to reach for. It is cheap relative to the table: it samples rather than reading every row. And it is gentle with concurrency, taking a lock level that does not block ordinary reads and writes, so running it in business hours after a load is routine, not a maintenance-window event. The only discipline required is remembering it, which is why the load job itself should end with ANALYZE rather than relying on anyone's memory. For the autovacuum side of the same daemon, the PostgreSQL vacuum guide covers the dead-tuple half of this story.
Tuning autoanalyze per table
When the defaults are wrong for a table, do not tune the daemon globally; tune the table. Both the threshold and the scale factor are available as per-table storage parameters, which is where they belong, because the table that loads in bulk and the table that trickles are different animals:
ALTER TABLE fact_sales SET (autovacuum_analyze_scale_factor = 0.01);Dropping the scale factor from 0.1 to 0.01 on a four-hundred-million-row table moves the tripwire from forty million changes to four million, which for a heavily loaded table is the difference between statistics that are hours stale and statistics that are minutes stale. The floor of 50 from autovacuum_analyze_threshold still applies, which is fine. The cost of analyzing more often is real but modest: sampling work and planning-time overhead, both small next to the cost of one bad plan on a hot query path. Meanwhile, leave the tiny, quiet tables alone; the defaults serve them well, and per-table parameters on every table become their own maintenance burden. Tune the tables whose load pattern you can name.
Monitoring staleness before the planner notices
Everything you need to watch this lives in pg_stat_user_tables. The view exposes when each table was last analyzed, by hand and by the daemon, and how many modifications have accumulated since:
SELECT schemaname,
relname,
n_mod_since_analyze,
last_analyze,
last_autoanalyze,
n_live_tup
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 0
ORDER BY n_mod_since_analyze DESC
LIMIT 15;Read this query with the threshold formula in mind and it becomes a staleness report: any table whose n_mod_since_analyze is large relative to its own tripwire, or whose last_autoanalyze predates a known bulk event, is a table whose plans are currently gambling. In my experience the healthiest pattern is to alert on exactly that condition, modifications far past threshold with no recent analyze, because it fires before the morning dashboard does. Pair it with plan-level evidence and the picture is complete: when a query family suddenly changes shape, the first question worth asking is when its tables were last analyzed, and this view answers it in one round trip.
Where MonPG fits
Stale-statistics incidents have a signature: query latency that degrades with no deploy, no lock storm, no resource saturation, and a plan diff that makes no sense until you check the statistics timestamps. Connecting that signature quickly requires query history and table-level statistics in the same place. That correlation is the workflow MonPG gives you for PostgreSQL today: pg_stat_statements history, plan context, and table statistics side by side, so the jump from "this query family regressed at 9:05" to "its fact table was last analyzed before the nightly load" is minutes, not a morning of spelunking. The PostgreSQL monitoring page shows what that workflow collects. And if you take one practice from this article, make it this: end every bulk load with ANALYZE, and let autoanalyze be the safety net rather than the plan.