You own a large OLTP database with a mix of small hot tables, huge append-only tables, and nightly-loaded reporting tables. How would you design the statistics-refresh policy across them?

## Why a single global policy is wrong Every mainstream engine ships an automatic statistics refresh with a threshold of the form *"refresh when changed rows exceed a constant plus a fraction of the table size"*. That formula has one deeply awkward property: **the bigger the table, the more change it tolerates before reacting**. At a 10% scale factor, a 10,000-row table refreshes after 1,000 changes; a billion-row table waits for 100 million. The tables where a wrong estimate is most expensive are the ones the default protects least. Meanwhile, refreshing costs real resources — a sampling pass and catalog writes — and refreshing too eagerly on a hot table churns plans for no benefit. So the policy is a genuine tradeoff, and it should be made per class of table. ## Class 1 — small, hot, frequently updated tables Characteristics: thousands to low millions of rows, high write rate, queried constantly. - Automatic refresh already fires often, and a sampling pass is cheap. - Leave defaults alone. The real risk here is *plan churn*: statistics moving slightly and flipping a marginal plan back and forth. - If plan instability is observed, the answer is usually to look at whether two plans are genuinely near-equal in cost, not to refresh more. ## Class 2 — very large append-only tables Characteristics: hundreds of millions to billions of rows, continuous inserts, rarely updated. Two distinct problems: 1. **Threshold scaling.** Lower the scale factor for these tables so refresh triggers on a sane absolute number of rows rather than a percentage. 2. **Boundary staleness matters more than volume.** On a monotonically increasing column — timestamp, sequence id — the harmful staleness is not the row count but the histogram's upper bound. Recent-data predicates fall outside the recorded range and estimate near-zero rows, producing plans sized for one row against millions. A relatively cheap, more frequent refresh limited to the increasing columns solves most of this. If the engine supports it, incremental or partition-level statistics avoid re-sampling the whole table on every refresh — but remember that table-level aggregates may still need updating separately. ## Class 3 — nightly-loaded reporting tables Characteristics: bulk-loaded in a batch window, then queried heavily by long-running statements. - **Refresh explicitly, as the last step of the load job.** This is non-negotiable; background refresh cannot be trusted to have run before the dependent workload starts. - Sequence it before the reports, and let the report job depend on the refresh completing. - For partition-wise loads, refresh the touched partitions *and* the table-level statistics. - Because these queries are long-running, a fuller sample is affordable here: the gathering cost is amortized over minutes of execution, so raised targets pay off more than they do in OLTP. ## The sampling-target dimension Separate from *when* to refresh is *how thoroughly*. The statistics target controls sample size and histogram/MCV resolution. - Raise it selectively: heavily-filtered skewed columns, high-cardinality join keys where distinct-value error propagates into join cardinality. - Do not raise it globally. The cost is paid on every refresh of every table, and most columns gain nothing. - Add multi-column/extended statistics only where correlated predicates are demonstrably causing bad estimates — they are not free either. ## Monitoring — the part most teams skip Make staleness observable: - rows changed since last refresh, and time since last refresh, for the top N tables by size or query volume - alert when a table crosses a staleness budget you have chosen deliberately per class - for the queries that matter, periodically compare estimated versus actual rows and alert on large divergence This converts "the report was slow last night" into "table X has not been analyzed in 40 hours", which is actionable before anyone notices. ## The tradeoffs to name explicitly - **Freshness versus gathering cost.** Refresh passes consume I/O and CPU on a live system; the more you refresh, the more you pay continuously to avoid an occasional bad plan. - **Freshness versus plan stability.** Every refresh can change a plan. On a marginal cost decision, more frequent refreshing means more frequent flips. Some workloads value predictability over optimality — batch jobs in particular. - **Automatic versus explicit.** Automatic covers the long tail of tables nobody thinks about; explicit covers the handful that matter and must be right at a specific moment. You want both, not a choice between them. ## The framing that lands *Statistics freshness is an SLO, not a setting. Define it per table class, meet it with automatic refresh where change is proportional and explicit refresh where change is bursty, spend sampling budget only where estimation error demonstrably costs plans, and monitor staleness so it is an alert rather than an incident.*