Database State: Missing and stale planner statistics

Proposal

Add a new check to the Database Diagnostics page (/admin/database_diagnostics) and to the gitlab:db:diagnostics Rake task. The check finds large tables that have no planner statistics. It also finds partitioned tables whose statistics are old. The check is read-only. It never runs ANALYZE.

Background

Missing or stale statistics are the first thing Support checks in a self-managed performance problem. Support note: "Generally if statistics need updating (or creating) performance is awful and the database server will be at 100% CPU." (source) The Support first step is SET statement_timeout = 0; ANALYZE VERBOSE;. A 2000-seat GitLab Premium customer runs ANALYZE manually every month to keep performance acceptable. (source) This is problem class 1 ("Stale or missing planner statistics") in #619095 (closed).

No Database Diagnostics check reports statistics age today. Gitlab::Database::DatabaseInformation collects schema resolution, timeouts, vacuum progress, and autovacuum configuration. It does not read last_analyze, last_autoanalyze, or reltuples.

#602130 (auto-vacuum backlog) plans an "analyze urgency" for each table. It compares n_mod_since_analyze with autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * n_live_tup. This works for regular tables that autovacuum processes. It cannot detect two cases.

  1. Partitioned parent tables.
    • PostgreSQL autovacuum does not process partitioned tables.
    • Only a manual ANALYZE or Database::PartitionManagementWorker sets their statistics.
    • On PostgreSQL 17, pg_stat_user_tables has a row for each partitioned parent. But n_mod_since_analyze and n_live_tup stay 0, and last_autoanalyze is always NULL.
    • A local PostgreSQL 17.8 instance confirmed this: 104 partitioned parents, none with n_mod_since_analyze > 0, none with last_autoanalyze set.
    • So an urgency formula always reports "not due" for them.
  2. Tables that were never analyzed.
    • pg_class.reltuples = -1 means the table was never vacuumed or analyzed (PostgreSQL 14 and later).
    • This happens after a PostgreSQL major upgrade. Before PostgreSQL 18, pg_upgrade does not transfer planner statistics.
    • This happens after a database restore (#276184).
    • This happens on new partitions (#627248 (closed)).

Database::PartitionManagementWorker runs ANALYZE on registered partitioned tables. The interval comes from the partitioning strategy analyze_interval. It is 3.days for CI partitioned tables (Ci::Partitionable). The default for other tables is 1.week (#630895 (closed), ops flag analyze_partitioned_tables_with_default_interval). Known bug #631607 (closed): after gitlab-ctl pg-upgrade, the worker skips parent tables for up to 3 days. The worker checks the last_analyze of the first leaf partition, not of the parent. A Zendesk ticket showed about 40 hours with no parent ANALYZE and bad performance, until a manual ANALYZE. The new check makes this kind of failure visible.

What to surface

Show the data for each database: main, and ci/sec when they are separate databases. Show the top relations by size.

Field Note
schema, relation name
kind table or partitioned table
total size For a partitioned parent, the sum of its leaf partitions (pg_partition_tree). The parent has no storage.
reltuples
last_analyze, last_autoanalyze Also show the age of the most recent one.
n_mod_since_analyze Regular tables only.
expected analyze_interval Partitioned tables registered in Gitlab::Database::Partitioning only.
pg_stat_database.stats_reset One value for each database.

Findings

The check uses the existing Findings::ERROR and Findings::WARNING severities. It uses the verdict helper in Gitlab::Database::Diagnostics::Checks::Base.

  • 🔴 statistics_missing (error): a table or partitioned table above the size floor has reltuples = -1. The message tells the administrator to run ANALYZE on it.
  • 🟡 partitioned_statistics_stale (warning): a partitioned parent above the size floor has a last_analyze older than its expected interval plus a margin.
    • For a registered table, the interval is its analyze_interval.
    • For an unregistered partitioned table, use a constant.

The remediation text must say this: ANALYZE on a partitioned table also samples all of its partitions. It can take a long time on a large table. Run it in a session with statement_timeout = 0.

Technical notes

  • Add a new class under lib/gitlab/database/diagnostics/checks/. It inherits from Checks::Base. It returns its payload merged with verdict(findings), like Checks::Timeouts and Checks::AutovacuumSettings.
  • Wire the class into Gitlab::Database::DatabaseInformation for the admin page.
  • Add a console view under lib/gitlab/database/diagnostics/console/views/ for the Rake task.
  • Optional: register the class in Gitlab::Usage::Metrics::Instrumentations::DatabaseDiagnosticsFindingMetric::CHECKS. Then Service Ping can report statistics_missing.
  • Keep the thresholds as constants: size floor, row limit, margin, and the age limit for unregistered parents.
  • Bound the query cost. Filter by the size floor. Limit the result to the top N relations. Compute partition tree sizes only for partitioned parents.

Signal requirements:

  • last_analyze and last_autoanalyze are cumulative statistics. PostgreSQL discards them after crash recovery or an immediate shutdown, after pg_stat_reset(), and after pg_upgrade.
  • So a NULL timestamp does not mean "no statistics".
  • Use pg_class.reltuples to decide "never analyzed". It is catalog data and PostgreSQL keeps it across restarts.
  • Use the timestamps only to show age.
  • Show pg_stat_database.stats_reset, so the reader knows from when the timestamps are valid.
  • Cumulative statistics are per server. A replica does not have the last_analyze values of the primary.
  • Read on the primary, like the other checks do: Gitlab::Database::LoadBalancing::SessionMap.current(connection.load_balancer).use_primary.

Out of scope

  • Running ANALYZE from GitLab. Automation fixes are in #631607 (closed), #627248 (closed), and #276184.
  • Analyze urgency for regular tables based on n_mod_since_analyze. This is #602130.
  • Extended statistics (CREATE STATISTICS). The GitLab schema does not define any.

Open questions

  • What are the size floor and the row limit? For example, 10 MB and top 50.
  • What is the margin for partitioned_statistics_stale? For example, 2 × analyze_interval. This allows for the 6-hour worker schedule and long ANALYZE runs.
  • What is the constant age limit for partitioned tables that are not registered in Gitlab::Database::Partitioning?
  • Should a regular table with an old last_autoanalyze and a high n_mod_since_analyze be a finding here? Or only in #602130?
  • Should the first iteration add a Service Ping metric?
  • Should statistics_missing also check for no rows in pg_stats? A plain VACUUM sets reltuples, but it does not collect column statistics.

References

Edited by 🤖 GitLab Bot 🤖