[FF] analyze_partitioned_tables_with_default_interval: whole-table ANALYZE for partitioned tables on the default analyze_interval

Summary

Track the analyze_partitioned_tables_with_default_interval ops flag, introduced by !252300 (merged) for #624175.

!252300 (merged) gives every partitioned table without an explicit analyze_interval a default of 1.week (Gitlab::Database::Partitioning::BaseStrategy::DEFAULT_ANALYZE_INTERVAL). This flag is the kill switch for the extra load that change adds.

What the flag controls

It gates only the whole-table ANALYZE that Database::PartitionManagementWorker runs on tables that rely on the default interval. That ANALYZE targets the parent table, so it covers every partition, and it runs at most once per week per table.

It does not control:

  • Tables that set an explicit analyze_interval (listed below). They are analyzed exactly as before, whatever the flag state.
  • The ANALYZE of newly created partitions. That is controlled separately by analyze_partitioned_tables_on_rotation.

The whole-table ANALYZE for default-interval tables also never runs from gitlab:db:create_dynamic_partitions (the db:migrate path), whatever the flag state. On first run most of these tables have no last-analyze time, so running it there would analyze nearly all of them during an upgrade. The worker picks them up on its next run instead.

What to watch after the deploy

The worker runs every 6 hours (postgres_dynamic_partitions_manager, 21 */6 * * *). It analyzes the affected tables one after another, each under a 1 hour statement timeout.

  • A table whose whole-table ANALYZE hits the timeout records no analyze time, so the worker retries it on every run, not weekly. Look for repeated Failed to run ANALYZE on partitioned table log entries for the same table.
  • If that happens, or the worker's runs get long enough to delay partition creation, disable the flag with /chatops gitlab run feature set analyze_partitioned_tables_with_default_interval false.

Affected tables

These 64 tables have no explicit analyze_interval and so fall back to the default. Derived from Gitlab::Database::Partitioning.registered_models and registered_tables at the head of !252300 (merged).

Table Strategy Model
ai_audit_events MonthlyStrategy AuditEvents::AiAuditEvent
ai_events_counts MonthlyStrategy Ai::EventsCount
ai_usage_events MonthlyStrategy Ai::UsageEvent
background_operation_jobs SlidingListStrategy Gitlab::Database::BackgroundOperation::Job
background_operation_jobs_cell_local SlidingListStrategy Gitlab::Database::BackgroundOperation::JobCellLocal
background_operation_workers SlidingListStrategy Gitlab::Database::BackgroundOperation::Worker
background_operation_workers_cell_local SlidingListStrategy Gitlab::Database::BackgroundOperation::WorkerCellLocal
backup_finding_evidences MonthlyStrategy Vulnerabilities::Backups::FindingEvidence
backup_finding_flags MonthlyStrategy Vulnerabilities::Backups::FindingFlag
backup_finding_identifiers MonthlyStrategy Vulnerabilities::Backups::FindingIdentifier
backup_finding_links MonthlyStrategy Vulnerabilities::Backups::FindingLink
backup_finding_remediations MonthlyStrategy Vulnerabilities::Backups::FindingRemediation
backup_finding_signatures MonthlyStrategy Vulnerabilities::Backups::FindingSignature
backup_findings MonthlyStrategy Vulnerabilities::Backups::Finding
backup_vulnerabilities MonthlyStrategy Vulnerabilities::Backups::Vulnerability
backup_vulnerability_external_issue_links MonthlyStrategy Vulnerabilities::Backups::VulnerabilityExternalIssueLink
backup_vulnerability_issue_links MonthlyStrategy Vulnerabilities::Backups::VulnerabilityIssueLink
backup_vulnerability_merge_request_links MonthlyStrategy Vulnerabilities::Backups::VulnerabilityMergeRequestLink
backup_vulnerability_reads MonthlyStrategy Vulnerabilities::Backups::VulnerabilityRead
backup_vulnerability_severity_overrides MonthlyStrategy Vulnerabilities::Backups::VulnerabilitySeverityOverride
backup_vulnerability_state_transitions MonthlyStrategy Vulnerabilities::Backups::VulnerabilityStateTransition
backup_vulnerability_user_mentions MonthlyStrategy Vulnerabilities::Backups::VulnerabilityUserMention
batched_background_migration_job_transition_logs MonthlyStrategy Gitlab::Database::BackgroundMigration::BatchedJobTransitionLog
ci_test_balancing_assignments DailyStrategy Ci::TestBalancing::Assignment
group_audit_events MonthlyStrategy AuditEvents::GroupAuditEvent
groups_visits MonthlyStrategy Users::GroupVisit
incident_management_pending_alert_escalations MonthlyStrategy IncidentManagement::PendingEscalations::Alert
incident_management_pending_issue_escalations MonthlyStrategy IncidentManagement::PendingEscalations::Issue
instance_audit_events MonthlyStrategy AuditEvents::InstanceAuditEvent
loose_foreign_keys_deleted_records SlidingListStrategy LooseForeignKeys::DeletedRecord
loose_foreign_keys_namespace_deleted_records SlidingListStrategy LooseForeignKeys::NamespaceDeletedRecord
loose_foreign_keys_organization_deleted_records SlidingListStrategy LooseForeignKeys::OrganizationDeletedRecord
loose_foreign_keys_project_deleted_records SlidingListStrategy LooseForeignKeys::ProjectDeletedRecord
loose_foreign_keys_user_deleted_records SlidingListStrategy LooseForeignKeys::UserDeletedRecord
merge_request_commits_metadata IntRangeStrategy MergeRequest::CommitsMetadata
merge_request_diff_commits IntRangeStrategy registered table (no model)
merge_request_diff_commits_b5377a7a34 IntRangeStrategy registered table (no model)
merge_request_diff_files IntRangeStrategy MergeRequestDiffFile
merge_requests_merge_data IntRangeStrategy MergeRequests::MergeData
p_ai_active_context_code_enabled_namespaces IntRangeStrategy Ai::ActiveContext::Code::EnabledNamespace
p_ai_active_context_code_repositories IntRangeStrategy Ai::ActiveContext::Code::Repository
p_batched_git_ref_updates_deletions SlidingListStrategy BatchedGitRefUpdates::Deletion
p_catalog_resource_sync_events SlidingListStrategy Ci::Catalog::Resources::SyncEvent
p_ci_finished_build_ch_sync_events SlidingListStrategy Ci::FinishedBuildChSyncEvent
p_ci_finished_pipeline_ch_sync_events SlidingListStrategy Ci::FinishedPipelineChSyncEvent
p_ci_runtime_environments SlidingListStrategy Ci::RuntimeEnvironment
p_duo_workflows_checkpoint_blobs DailyStrategy Ai::DuoWorkflows::CheckpointBlob
p_duo_workflows_checkpoint_headers DailyStrategy Ai::DuoWorkflows::CheckpointHeader
p_duo_workflows_checkpoints DailyStrategy Ai::DuoWorkflows::Checkpoint
p_generated_ref_commits IntRangeStrategy MergeRequests::GeneratedRefCommit
p_knowledge_graph_code_indexing_tasks DailyStrategy Analytics::KnowledgeGraph::CodeIndexingTask
p_sent_notifications SlidingListStrategy SentNotification
project_audit_events MonthlyStrategy AuditEvents::ProjectAuditEvent
project_daily_statistics MonthlyStrategy ProjectDailyStatistic
projects_visits MonthlyStrategy Users::ProjectVisit
security_findings SlidingListStrategy Security::Finding
user_audit_events MonthlyStrategy AuditEvents::UserAuditEvent
value_stream_dashboard_counts MonthlyStrategy Analytics::ValueStreamDashboard::Count
verification_codes MonthlyStrategy registered table (no model)
vulnerability_archive_exports SlidingListStrategy Vulnerabilities::ArchiveExport
vulnerability_archived_records MonthlyStrategy Vulnerabilities::ArchivedRecord
vulnerability_archives MonthlyStrategy Vulnerabilities::Archive
web_hook_logs_daily DailyStrategy WebHookLog
zoekt_tasks SlidingListStrategy Search::Zoekt::Task

Not affected (explicit 3.days through Ci::Partitionable): p_ci_build_names, p_ci_build_needs, p_ci_build_sources, p_ci_build_trace_metadata, p_ci_builds, p_ci_job_annotations, p_ci_job_artifact_reports, p_ci_job_artifacts, p_ci_job_definition_instances, p_ci_job_definitions, p_ci_job_inputs, p_ci_job_messages, p_ci_job_runtime_environments, p_ci_pipeline_artifact_states, p_ci_pipeline_processing_data, p_ci_pipeline_variables, p_ci_pipelines, p_ci_runner_machine_builds, p_ci_stages, p_ci_workload_variable_inclusions, p_ci_workloads.

What could go wrong?

A weekly ANALYZE on a large partitioned parent, for example merge_request_diff_files, merge_request_diff_commits, security_findings or the audit event tables, is new recurring load on the primary. Each run uses SKIP_LOCKED and the partition manager's 1 hour statement timeout. Watch primary CPU and IO on the main and ci databases, and Failed to run ANALYZE on partitioned table errors in the Sidekiq logs for Database::PartitionManagementWorker. Also watch:

  • Database::PartitionManagementWorker job duration_s in the Sidekiq logs, especially on the first run after deploy, when most default tables get their first whole-table ANALYZE.
  • Plan changes on queries against the listed parent tables. The first run gives most of these parents inheritance statistics for the first time, so plans can change as well as load.
  • Lock waits on the listed tables during the worker window. The ANALYZE holds SHARE UPDATE EXCLUSIVE on the partitions until it finishes, so deploy-time DDL on them can queue behind it.

Kill switch

If the whole-table ANALYZE causes unexpected database load, disable the flag. This stops the whole-table ANALYZE for the tables above. Tables with an explicit interval aren't affected, and the new-partition ANALYZE keeps running under analyze_partitioned_tables_on_rotation, including for the tables above.

/chatops gitlab run feature set analyze_partitioned_tables_with_default_interval false

On self-managed:

Feature.disable(:analyze_partitioned_tables_with_default_interval)

Re-enable with true / Feature.enable.

Tracking

  • !252300 (merged) merged and deployed to GitLab.com
  • One week after deploy, confirm the affected tables show a recent pg_stat_get_last_analyze_time on their first partition and no sustained primary load increase
  • Review the flag within 12 months, per the ops flag guidance, and either keep it (update milestone) or remove it
Edited by Gregory Havenga