What do a database's automatic statistics and vacuum/purge maintenance daemons do for you, and what symptoms appear when they cannot keep up with the write rate?

## What these daemons are for Two maintenance duties cannot live on the query path but must happen continuously. **Statistics.** The optimizer picks plans from a statistical model of the data: row counts, distinct values, most-common values, histograms, correlation. Data changes; the model does not, unless something refreshes it. An automatic analyze component watches how many rows have been inserted, updated or deleted since the last sample and, past a threshold, re-samples the table. **Dead-version reclamation.** Under multi-version concurrency control an update does not overwrite in place and a delete does not free space immediately — older versions must remain visible to transactions that started earlier. Once no live transaction can still need a version, its space is reclaimable. A daemon does that sweep, plus associated housekeeping such as index maintenance and internal counters. Both are **threshold-driven** (act when a table has changed enough), **concurrent** (they do not block normal work in the general case) and **throttled** (a cost or budget limit forces them to pause so they consume only a share of the I/O). ## Why they are scheduled rather than immediate Doing this work inline would put a variable, sometimes large cost on random user transactions — the one unlucky DELETE that also has to compact a page. Batching lets one pass clean many versions in a page at once, and lets the engine choose a moment. The throttle exists because unthrottled maintenance on a busy system is itself an outage: it will saturate the storage and starve the queries it exists to protect. ## Symptoms of falling behind **Bloat.** The classic signal. A table holding 2 GB of live rows occupies 20 GB. Every sequential scan reads ten times the pages; indexes bloat too, so even index scans touch more pages. Worse, the buffer pool now caches mostly dead space, so hit ratios drop and previously memory-resident workloads start hitting disk. This is a positive feedback loop: slower queries run longer, longer transactions hold the horizon back further, less becomes reclaimable. **Growing history/undo.** Engines that keep old versions in a dedicated area see it grow, and readers that must walk long version chains to find the version they can see get progressively slower. **Sudden plan regressions.** Stale statistics are silent until they are catastrophic. A table that grew from ten thousand to ten million rows still looks small to the planner, so it keeps choosing a nested loop or a plan without the right join order, and one query goes from milliseconds to minutes with no code change. Newly loaded partitions and freshly bulk-loaded tables are the usual victims, because they change enormously between samples. **Maintenance that never completes.** On the hottest tables the daemon may be repeatedly cancelled or preempted, or its throttle budget may be so small that a pass takes longer than the interval at which the table qualifies again. You see the same table perpetually queued. **Emergency modes.** Engines have last-resort protections when internal housekeeping falls dangerously behind — aggressive, more intrusive maintenance, or refusing new writes. Hitting one is an incident, and it is always preceded by weeks of ignored signals. ## Root causes, in the order I check them 1. **A long-running transaction or an idle-in-transaction session.** This is the most common and the most misdiagnosed. Nothing older than that transaction's snapshot can be reclaimed, so the daemon runs, works hard, and frees nothing. Look for the oldest active transaction before touching any tuning knob. 2. **Throttle too small for the write rate.** The daemon's cost budget determines how much I/O per unit time it may consume. If the workload dirties pages faster than the budget lets it clean them, it will never catch up regardless of how often it starts. 3. **Too few workers, or all workers stuck on huge tables.** A limited pool of maintenance workers, each pinned to a large table, means the rest of the schema is never visited. 4. **Percentage-based thresholds on very large tables.** "Twenty percent changed" is a modest number of rows on a small table and a hundred million on a big one, so the biggest, hottest tables are analyzed and cleaned least often. Set per-table thresholds with a scale factor near zero and an absolute floor for those. 5. **Bulk-load patterns.** A large load followed immediately by queries plans against pre-load statistics. Analyze explicitly at the end of the load rather than waiting for the daemon. 6. **Replicas holding back cleanup.** Configurations that let standby queries delay reclamation on the primary produce the same symptom as a long local transaction. ## What good operation looks like Track, per table, the time since last automatic analyze and last cleanup, an estimate of dead versus live rows, and the age of the oldest running transaction. Alert on the oldest transaction age and on dead-row ratio, not on daemon CPU. Give the largest tables per-table thresholds. Raise the throttle budget deliberately — it is one of the few knobs where the correct move on modern storage is usually "much more aggressive than the default", because defaults were chosen for spinning disks. And treat the daemon working hard as normal; treat it never finishing as the alarm.