Statistics Best Practices

Planner quality depends on fresh statistics. Treat ANALYZE as part of every data migration and performance review on PostgreSQL 18.4.

How to Use This List

Run after bulk DML before benchmarking indexes.
Pair with EXPLAIN estimate vs actual row checks.
Document extended statistics in migration PRs when added.

A - Refresh Cadence

Manual ANALYZE after bulk COPY, delete purges, or backfills. Autoanalyze may lag hours.
Analyze new partitions when attached. Empty stats mislead pruning.
Re-analyze after CREATE INDEX CONCURRENTLY on huge tables. Confirm planner picks it.
Monitor n_mod_since_analyze on hot tables. Alert before plans rot.
Include analyze step in ETL runbooks. Last job before BI opens.

B - Depth and Correlation

Raise STATISTICS target only on proven skew columns. Not globally by default.
Add extended stats for correlated AND filters. dependencies or ndistinct as appropriate.
Create expression stats for immutable reporting predicates. date_trunc, lower where indexed.
Validate with pg_stats and EXPLAIN before/after. Prove estimate fixes.
Drop unused extended stats to shorten analyze. Periodic catalog audit.

C - Operations Safety

Never disable autovacuum/analyze globally. Per-table tuning instead.
Schedule heavy analyze off peak when possible. Large tables still sample heavily.
Keep stats in logical migration repos. CREATE STATISTICS is schema.
Test plans as application roles with production GUCs. Role-level random_page_cost matters.
Snapshot masked data at production scale for plan review. Empty dev DB lies.

D - Team Process

Reject index PRs without post-analyze plans. Prevent false negatives.
Track plan regressions in CI with JSON EXPLAIN baselines. Stats drift detection.
Document tenant skew assumptions in ADRs. Partial indexes plus stats pairs.
Train app teams: ORM deploy != analyze. Data change triggers DBA notify.
Review pg_stat_user_tables monthly on top 20 tables. Operational hygiene.

FAQs

How soon after ETL to analyze?

Before any user or BI traffic hits new data.

Default statistics target enough?

Often for uniform columns; rarely for status enums with skew.

Analyze all columns vs some?

Target changed or skewed columns to save time.

Standby stats?

Replicas inherit primary stats; analyze runs on primary.

pg_stats security?

May reveal distribution; restrict sensitive environments.

Autovacuum analyze scale factor?

Lower on hot fact tables (0.01-0.02).

When extended stats mandatory?

When AND filters mis-estimate by orders of magnitude post-analyze.

Cloud autovacuum?

Managed presets exist; still tune per-table for facts.

Histogram vs index?

Stats guide plans; indexes enable paths.

Next section?

Index design maintenance for physical structures stats should use.

Statistics Basics - Catalog fundamentals
ANALYZE & Autoanalyze - Timing
Extended Statistics - Correlation tools
EXPLAIN Best Practices - Plan capture

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Statistics Best Practices

Planner quality depends on fresh statistics. Treat ANALYZE as part of every data migration and performance review on PostgreSQL 18.4.

How to Use This List

Run after bulk DML before benchmarking indexes.
Pair with EXPLAIN estimate vs actual row checks.
Document extended statistics in migration PRs when added.

A - Refresh Cadence

Manual ANALYZE after bulk COPY, delete purges, or backfills. Autoanalyze may lag hours.
Analyze new partitions when attached. Empty stats mislead pruning.
Re-analyze after CREATE INDEX CONCURRENTLY on huge tables. Confirm planner picks it.
Monitor n_mod_since_analyze on hot tables. Alert before plans rot.
Include analyze step in ETL runbooks. Last job before BI opens.

B - Depth and Correlation

Raise STATISTICS target only on proven skew columns. Not globally by default.
Add extended stats for correlated AND filters. dependencies or ndistinct as appropriate.
Create expression stats for immutable reporting predicates. date_trunc, lower where indexed.
Validate with pg_stats and EXPLAIN before/after. Prove estimate fixes.
Drop unused extended stats to shorten analyze. Periodic catalog audit.

C - Operations Safety

Never disable autovacuum/analyze globally. Per-table tuning instead.
Schedule heavy analyze off peak when possible. Large tables still sample heavily.
Keep stats in logical migration repos. CREATE STATISTICS is schema.
Test plans as application roles with production GUCs. Role-level random_page_cost matters.
Snapshot masked data at production scale for plan review. Empty dev DB lies.

D - Team Process

Reject index PRs without post-analyze plans. Prevent false negatives.
Track plan regressions in CI with JSON EXPLAIN baselines. Stats drift detection.
Document tenant skew assumptions in ADRs. Partial indexes plus stats pairs.
Train app teams: ORM deploy != analyze. Data change triggers DBA notify.
Review pg_stat_user_tables monthly on top 20 tables. Operational hygiene.

FAQs

How soon after ETL to analyze?

Before any user or BI traffic hits new data.

Default statistics target enough?

Often for uniform columns; rarely for status enums with skew.

Analyze all columns vs some?

Target changed or skewed columns to save time.

Standby stats?

Replicas inherit primary stats; analyze runs on primary.

pg_stats security?

May reveal distribution; restrict sensitive environments.

Autovacuum analyze scale factor?

Lower on hot fact tables (0.01-0.02).

When extended stats mandatory?

When AND filters mis-estimate by orders of magnitude post-analyze.

Cloud autovacuum?

Managed presets exist; still tune per-table for facts.

Histogram vs index?

Stats guide plans; indexes enable paths.

Next section?

Index design maintenance for physical structures stats should use.

Statistics Basics - Catalog fundamentals
ANALYZE & Autoanalyze - Timing
Extended Statistics - Correlation tools
EXPLAIN Best Practices - Plan capture

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Statistics Best Practices

Search Documentation

Statistics Best Practices