Dashboard preview

Top blocked/blocking pairs across the last hour.

• highorders ↔ payments lock contention

  UPDATE orders SET status=$1 WHERE id=$2 blocked 14× by long-running BEGIN; SELECT … FOR UPDATE on payments. Same fingerprint pair across 6 ticks → chronic, not transient.

sqlcommenter tags → where in your code the slow query lives.

• mediumBillingService.computeMonthly is 41% of total exec time

  Sqlcommenter tag `application=billing,controller=BillingService,action=computeMonthly` accounts for 41% of cumulative pg_stat_statements total_exec_time. Top fingerprint: SELECT … FROM invoices i JOIN line_items li ON … WHERE i.month = $1.

Fingerprint-level latency deltas vs the prior 7-day baseline.

• high5.4× p95 regression on orders.list_by_user

  p95 went from 38ms → 205ms over the last 24h on SELECT … FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2. Index orders_user_id_idx is present but the plan flipped to a Bitmap Heap Scan after stats stale.

Structural plan changes (node-type sequence) between snapshots.

• highIndex Scan → Bitmap Heap Scan on orders

  Fingerprint hash a3f1…b9 flipped from Index Scan using orders_user_id_idx to a Bitmap Heap Scan + Recheck Cond after 2026-05-13 08:14. pg_stats n_distinct on user_id went from 4.2M → 800k between snapshots — ANALYZE orders fixes it.

Sustained latency-slope detection across fingerprints.

• mediuminvoices.month_summary trending +18%/wk

  Linear regression over 28 days shows p95 climbing 18% week-over-week on SELECT … FROM invoices WHERE month >= $1. Cross-references RegressionCard.

Unused indexes, missing FKs, denormalized hot spots.

• medium3 unused indexes on hot tables (orders, payments, sessions)

  idx_orders_legacy_status, idx_payments_old_provider, idx_sessions_ua_string have 0 scans in 30 days and consume 41 GiB. Safe to drop on busy tables — confirms via pg_stat_user_indexes.

  DROP INDEX CONCURRENTLY idx_orders_legacy_status;
  DROP INDEX CONCURRENTLY idx_payments_old_provider;
  DROP INDEX CONCURRENTLY idx_sessions_ua_string;

HypoPG-simulated workload_benefit_pct ranking.

• highorders (user_id, created_at DESC) projected −62% workload cost

  HypoPG-simulated. Covers the top 3 fingerprints by p95. EXPLAIN cost on the canonical orders.list_by_user query: 18,420 → 6,995. No write-amp risk: user_id+created_at columns have no UPDATE-heavy workload.

  CREATE INDEX CONCURRENTLY orders_user_id_created_at_idx
    ON orders (user_id, created_at DESC);

Range/list/hash recommendation with key + window.

• mediumRange-partition orders on created_at, monthly window

  Table is 52M rows, 38 GiB. 84% of read workload filters by created_at >= now() − 90d. Range partitioning by month enables pruning + parallel maintenance. Hash partitioning gives parallelism without pruning — not recommended for this access pattern.

Table/column/index churn between snapshots.

• infoADD COLUMN orders.fulfillment_provider TEXT

  Detected 2026-05-12 14:22. Currently NULL on 100% of rows; backfill not yet observed. No referencing query in pg_stat_statements yet.

VACUUM / ANALYZE / REINDEX prioritized by impact.

• highorders has not been ANALYZEd in 11 days

  Drives the PlanFlip and Regression findings on the same table. Run ANALYZE orders now and tighten autovacuum_analyze_scale_factor to 0.02 on this table — default 0.1 is too loose for a 52M-row hot table.

  ANALYZE orders;
  ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

Never-vacuumed / starved / disabled-per-table relations.

• mediumaudit_events: 12M dead tuples, 0 autovac in 6 days

  autovacuum_vacuum_scale_factor=0.2 inherited from default. With 60M live rows, threshold is 12M dead tuples — exactly where we are. Lower per-table to 0.05.

  ALTER TABLE audit_events SET (autovacuum_vacuum_scale_factor = 0.05);

pg_repack-grade bloat ratios with reclaim estimate.

• mediumorders 2.4× live-to-total ratio, ~22 GiB reclaimable

  pg_repack will reclaim ~22 GiB without taking an exclusive lock. VACUUM FULL is faster but holds AccessExclusiveLock and is unsafe on a hot table.

  -- requires pg_repack extension
  pg_repack -t orders -d production_db

REINDEX CONCURRENTLY candidates ranked.

• loworders_pkey 1.6× bloat, ~2.8 GiB reclaimable

  Low priority because the index is still fully usable. Schedule REINDEX CONCURRENTLY in a low-traffic window — it briefly doubles disk footprint.

  REINDEX INDEX CONCURRENTLY orders_pkey;

TOAST storage tuning + column-split candidates.

• mediumevents.payload bytea: avg_width 4.2 KiB, 38× per-row decompress cost

  Currently uses EXTENDED storage (compress + out-of-line). Switching to EXTERNAL skips the LZ compress step — saves CPU at modest disk cost. Safe + idempotent alter.

  ALTER TABLE events ALTER COLUMN payload SET STORAGE EXTERNAL;

Idle-in-xact leaks, saturation, pgbouncer mode advice.

• criticalidle_in_transaction_leak at 13.5% (threshold 5%)

  27 of 200 connections sitting in idle-in-transaction state. This is almost always an application bug — a code path that BEGIN-s and forgets to COMMIT/ROLLBACK. Run pgbouncer in transaction-pool mode + set idle_in_transaction_session_timeout = 60s as a safety net.

  ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
  SELECT pg_reload_conf();

Replay/write lag, inactive slot WAL retention.

• highInactive physical slot replica2 retaining 4.3 GiB WAL

  Slot replica2 has active=false and confirmed_flush_lsn lagging primary by 4.3 GiB. Drop the slot or restart the standby BEFORE the primary disk fills. Replay lag on the remaining active slot is 9.7 s — acceptable but trending up.

  SELECT pg_drop_replication_slot('replica2');

Tick freshness, cert binding, agent error feed.

• infocollector healthy, 1,422 ticks in last 24h

  mTLS cert valid for 84 more days. No agent errors in the last 6h. Per-tick payload size 18–34 KiB.