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.
- Partitioned parent tables.
- PostgreSQL autovacuum does not process partitioned tables.
- Only a manual
ANALYZEorDatabase::PartitionManagementWorkersets their statistics. - On PostgreSQL 17,
pg_stat_user_tableshas a row for each partitioned parent. Butn_mod_since_analyzeandn_live_tupstay0, andlast_autoanalyzeis alwaysNULL. - A local PostgreSQL 17.8 instance confirmed this: 104 partitioned parents, none with
n_mod_since_analyze > 0, none withlast_autoanalyzeset. - So an urgency formula always reports "not due" for them.
- Tables that were never analyzed.
pg_class.reltuples = -1means the table was never vacuumed or analyzed (PostgreSQL 14 and later).- This happens after a PostgreSQL major upgrade. Before PostgreSQL 18,
pg_upgradedoes 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 hasreltuples = -1. The message tells the administrator to runANALYZEon it.🟡 partitioned_statistics_stale(warning): a partitioned parent above the size floor has alast_analyzeolder 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.
- For a registered table, the interval is its
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 fromChecks::Base. It returns its payload merged withverdict(findings), likeChecks::TimeoutsandChecks::AutovacuumSettings. - Wire the class into
Gitlab::Database::DatabaseInformationfor 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 reportstatistics_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_analyzeandlast_autoanalyzeare cumulative statistics. PostgreSQL discards them after crash recovery or an immediate shutdown, afterpg_stat_reset(), and afterpg_upgrade.- So a
NULLtimestamp does not mean "no statistics". - Use
pg_class.reltuplesto 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_analyzevalues 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
ANALYZEfrom 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 longANALYZEruns. - 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_autoanalyzeand a highn_mod_since_analyzebe a finding here? Or only in #602130? - Should the first iteration add a Service Ping metric?
- Should
statistics_missingalso check for no rows inpg_stats? A plainVACUUMsetsreltuples, but it does not collect column statistics.
References
- PostgreSQL docs: Updating Planner Statistics
- PostgreSQL docs: The Autovacuum Daemon (partitioned tables are not processed by autovacuum)
- PostgreSQL docs:
pg_class(reltuples = -1) - GitLab docs: Run ANALYZE to regenerate database statistics
- Investigating PostgreSQL performance issues
Related
- Part of &23920 (Database State: Performance and operational checks), under the Database State Framework &20270
- Problem class 1 of #619095 (closed)
- Complements #602130 (auto-vacuum backlog and analyze urgency)
- Detects the effect of #631607 (closed), #627248 (closed), and #276184
- Default
analyze_intervaland kill switch: #630895 (closed)