# Summary Diagnosing PostgreSQL performance incidents (lock contention, query regressions, connection storms) requires manual correlation of `pg_stat_activity` snapshots, logs, and metrics — usually under time pressure and dependent on expert knowledge. Active Session History (ASH) solves this by sampling session state at regular intervals, giving a time-series view of what the database was doing at any moment. We validated this approach in a [proof of concept](https://gitlab.com/gitlab-org/gitlab/-/work_items/606635) built on [pg_ash](https://github.com/NikolayS/pg_ash) (pure SQL/PL-pgSQL, no C extension): * Sampling driven by a Sidekiq cron worker (no `pg_cron` dependency) * Reports rendered in a UI and exported as self-describing JSON if needed * JSON reports fed to an LLM for automated diagnosis narratives The PoC works as expected. The main unsolved problem is lifecycle management of the ASH database objects (installation, updates, schema validation) — the PoC keeps them in a single vendored SQL file, which is not production-ready. This epic covers integrating ASH into the product properly. # Business justification Database performance incidents are among the hardest support escalations to resolve. When a self-managed instance slows down, neither the admin nor GitLab support has visibility into what the database was doing at the time — `pg_stat_activity` only shows the current moment, and by the time someone looks, the evidence is gone. Commercial platforms already treat ASH as table stakes (AWS RDS Performance Insights, Oracle ASH); GitLab instances have nothing comparable built in. Built-in ASH gives us: * Faster incident resolution — admins and support can look back at session history instead of trying to reproduce the problem * Reduced support burden — first-pass triage no longer requires a database expert; LLM-generated diagnosis makes it accessible to any engineer * A foundation for database observability — the same sampled data feeds dashboards, alerting, and future Duo-assisted diagnostics # Expected impact * Admins get a built-in, always-on view of database session history — no external tooling or extensions to install * Time to diagnose database performance incidents drops from hours (manual correlation, back-and-forth with support) to minutes (look at the ASH report, read the generated diagnosis) * Support tickets about database slowness arrive with ASH data attached, instead of starting an evidence-gathering loop * A reusable pattern for managing non-schema database objects (functions, procedures, maintenance jobs), which other database health features can build on # Exit criteria * [ ] ASH database objects are installed and updated through a supported lifecycle mechanism (vendored files, gem, any other tool?) * [ ] Sampling runs by default on self-managed instances, with configurable interval and retention * [ ] Admins can view ASH reports in the Web UI (wait-event breakdowns, top queries, session timeline) * [ ] ASH reports are exportable as structured JSON for support tickets and LLM analysis * [ ] Report available from a rake task as well * [ ] Sampling overhead is measured and documented as safe for production workloads * [ ] Documentation for admins and support # Deliverables - [x] Phase 0 — Architecture - [x] Decide the lifecycle mechanism for ASH database objects (dedicated library similar to `labkit`, migrations, or another approach) — the main open question from the PoC - [x] Decide sampling driver (Sidekiq cron vs. alternatives) and default interval/retention - [ ] Phase 1 — Core integration - [x] Package and install the ASH objects through the chosen mechanism, including upgrades - [ ] Sampling and rollup jobs running by default, with configuration for interval and retention - [ ] Benchmark sampling overhead on a production-like workload - [ ] Phase 2 — Reporting - [ ] Web UI for ASH reports (wait-event breakdowns, top queries, session timeline) - [ ] Structured JSON export for support tickets and LLM input - [ ] Phase 3 — LLM diagnosis - [ ] Feed ASH reports to an LLM and surface the generated diagnosis to the admin - [ ] Phase 4 — Documentation - [ ] Admin and support documentation; share with stakeholders