Close Idle transaction holds row locks for 312 s Idle transaction holds row locks for 312 s
M02 · H2 · F1 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Idle transaction holds row locks for 312 s
PID 48211 (orders#checkout) · xact_start 09:42:18 UTC · 312 s (5 min 12 s) · blocks 4 sessions · Related setting ENV-03
312 s / limit 30 s
Critical threshold: 30 s Primary anti-pattern: The request path scans more buffers than it returns; the composite index is missing. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Idle transaction holds row locks for 312 s moved past its safe budget.Where: PID 48211 (orders#checkout) · xact_start 09:42:18 UTC · 312 s (5 min 12 s) · blocks 4 sessions · Related setting ENV-03.Why: The request path scans more buffers than it returns; the composite index is missing.Measured: 312 s / limit 30 s against 30 s.Evidence: queryid 88412093412 · source tag F1 · Idle transaction holds row locks for 312 s.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Backfill holds 41% of DB time and drives WAL Backfill holds 41% of DB time and drives WAL
M10 · H18 · F1 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Backfill holds 41% of DB time and drives WAL
backfill#2026_10_wal (queryid 44109823109) · 88.5 MB/s WAL vs max_wal_size 1 GB · Related setting ENV-08
41% / limit 20%
Critical threshold: 100 MB/s Primary anti-pattern: The request path scans more buffers than it returns; the composite index is missing. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Backfill holds 41% of DB time and drives WAL moved past its safe budget.Where: backfill#2026_10_wal (queryid 44109823109) · 88.5 MB/s WAL vs max_wal_size 1 GB · Related setting ENV-08.Why: The request path scans more buffers than it returns; the composite index is missing.Measured: 41% / limit 20% against 100 MB/s.Evidence: queryid 88412093412 · source tag F1 · Backfill holds 41% of DB time and drives WAL.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Checkout lookup reads 920 blocks per row Checkout lookup reads 920 blocks per row
M01 · H4 · F1 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Checkout lookup reads 920 blocks per row
orders#customer_orders (queryid 88412093412) · sequential scan on orders
920 / limit 100 blocks
Critical threshold: 1.5 × 8 cores Primary anti-pattern: The request path scans more buffers than it returns; the composite index is missing. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Checkout lookup reads 920 blocks per row moved past its safe budget.Where: orders#customer_orders (queryid 88412093412) · sequential scan on orders.Why: The request path scans more buffers than it returns; the composite index is missing.Measured: 920 / limit 100 blocks against 1.5 × 8 cores.Evidence: queryid 88412093412 · source tag F1 · Checkout lookup reads 920 blocks per row.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Inserts rely on unique-violation errors Inserts rely on unique-violation errors
M04 · H12 · F1 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Inserts rely on unique-violation errors
orders#insert_payment (queryid 31094827104) · transaction rollback ratio
6.2% / limit 5%
Critical threshold: 5% Primary anti-pattern: The request path scans more buffers than it returns; the composite index is missing. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Inserts rely on unique-violation errors moved past its safe budget.Where: orders#insert_payment (queryid 31094827104) · transaction rollback ratio.Why: The request path scans more buffers than it returns; the composite index is missing.Measured: 6.2% / limit 5% against 5%.Evidence: queryid 88412093412 · source tag F1 · Inserts rely on unique-violation errors.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Backends write 72% of dirty buffers Backends write 72% of dirty buffers
M12 · H11 · F1 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Backends write 72% of dirty buffers
pg_stat_bgwriter · backend-written buffers exceed checkpoint share
72% / limit 20%
Critical threshold: 5 sessions Primary anti-pattern: The request path scans more buffers than it returns; the composite index is missing. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Backends write 72% of dirty buffers moved past its safe budget.Where: pg_stat_bgwriter · backend-written buffers exceed checkpoint share.Why: The request path scans more buffers than it returns; the composite index is missing.Measured: 72% / limit 20% against 5 sessions.Evidence: queryid 88412093412 · source tag F1 · Backends write 72% of dirty buffers.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Dead-tuple ratio on orders is 24% Dead-tuple ratio on orders is 24%
M15 · H7 · F1 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Dead-tuple ratio on orders is 24%
public.orders · saturates autovacuum workers (3/3 busy)
24% / limit 10%
Critical threshold: 1% (OLTP) Primary anti-pattern: The request path scans more buffers than it returns; the composite index is missing. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Dead-tuple ratio on orders is 24% moved past its safe budget.Where: public.orders · saturates autovacuum workers (3/3 busy).Why: The request path scans more buffers than it returns; the composite index is missing.Measured: 24% / limit 10% against 1% (OLTP).Evidence: queryid 88412093412 · source tag F1 · Dead-tuple ratio on orders is 24%.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Queries per second (QPS) Queries per second (QPS)
M06Q · H9 · H9 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Queries per second (QPS)
pg_stat_statements sum(calls)/s
6,420 qps
Critical threshold: ≤ 8,000 qps Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Queries per second (QPS) moved past its safe budget.Where: pg_stat_statements sum(calls)/s.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 6,420 qps against ≤ 8,000 qps.Evidence: queryid 88412093412 · source tag H9 · Queries per second (QPS).Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Transactions per second (TPS) Transactions per second (TPS)
M06 · H9 · H9 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Transactions per second (TPS)
pg_stat_database commits + rollbacks
2,880 tx/s
Critical threshold: floor 2,000 (2× baseline) Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Transactions per second (TPS) moved past its safe budget.Where: pg_stat_database commits + rollbacks.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 2,880 tx/s against floor 2,000 (2× baseline).Evidence: queryid 88412093412 · source tag H9 · Transactions per second (TPS).Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Active sessions Active sessions
M01 · H4 · H4 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Active sessions
pg_stat_activity state=active
42 sessions
Critical threshold: 1.5 × 8 cores Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Active sessions moved past its safe budget.Where: pg_stat_activity state=active.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 42 sessions against 1.5 × 8 cores.Evidence: queryid 88412093412 · source tag H4 · Active sessions.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Idle in transaction, longest Idle in transaction, longest
M02 · H2 · H2 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Idle in transaction, longest
pg_stat_activity
312 s
Critical threshold: 30 s Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Idle in transaction, longest moved past its safe budget.Where: pg_stat_activity.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 312 s against 30 s.Evidence: queryid 88412093412 · source tag H2 · Idle in transaction, longest.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Lock wait, longest Lock wait, longest
M03 · H11 · H11 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Lock wait, longest
pg_stat_activity + pg_blocking_pids()
4.2 s
Critical threshold: 1 s Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Lock wait, longest moved past its safe budget.Where: pg_stat_activity + pg_blocking_pids().Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 4.2 s against 1 s.Evidence: queryid 88412093412 · source tag H11 · Lock wait, longest.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Rollback ratio Rollback ratio
M04 · H12 · H12 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Rollback ratio
pg_stat_database
6.2 % of tx
Critical threshold: 5% Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Rollback ratio moved past its safe budget.Where: pg_stat_database.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 6.2 % of tx against 5%.Evidence: queryid 88412093412 · source tag H12 · Rollback ratio.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Mean execution time, worst query Mean execution time, worst query
M05 · H3 · H3 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Mean execution time, worst query
pg_stat_statements · p99 not collected
148 ms
Critical threshold: 1,000 ms Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Mean execution time, worst query moved past its safe budget.Where: pg_stat_statements · p99 not collected.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 148 ms against 1,000 ms.Evidence: queryid 88412093412 · source tag H3 · Mean execution time, worst query.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Oldest XID age Oldest XID age
M07 · H1 · H1 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Oldest XID age
age(datfrozenxid)
19.2 % of wraparound
Critical threshold: 50% Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Oldest XID age moved past its safe budget.Where: age(datfrozenxid).Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 19.2 % of wraparound against 50%.Evidence: queryid 88412093412 · source tag H1 · Oldest XID age.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Deadlocks Deadlocks
M08 · H18 · H18 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Deadlocks
pg_stat_database
0 /15 min
Critical threshold: limit 0 Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Deadlocks moved past its safe budget.Where: pg_stat_database.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 0 /15 min against limit 0.Evidence: queryid 88412093412 · source tag H18 · Deadlocks.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Temp file spill, worst query Temp file spill, worst query
M09 · H8 · H8 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Temp file spill, worst query
temp_blks_written × 8 kB
0.09 MB/query
Critical threshold: 100 MB Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Temp file spill, worst query moved past its safe budget.Where: temp_blks_written × 8 kB.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 0.09 MB/query against 100 MB.Evidence: queryid 88412093412 · source tag H8 · Temp file spill, worst query.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close WAL generation WAL generation
M10 · H18 · H18 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius WAL generation
pg_stat_wal
88.5 MB/s
Critical threshold: 100 MB/s Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: WAL generation moved past its safe budget.Where: pg_stat_wal.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 88.5 MB/s against 100 MB/s.Evidence: queryid 88412093412 · source tag H18 · WAL generation.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close MultiXact consumption MultiXact consumption
M11 · H17 · H17 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius MultiXact consumption
age(datminmxid)
2,741 /s
Critical threshold: 10,000 /s Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: MultiXact consumption moved past its safe budget.Where: age(datminmxid).Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 2,741 /s against 10,000 /s.Evidence: queryid 88412093412 · source tag H17 · MultiXact consumption.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Lock manager waiters Lock manager waiters
M12 · H11 · H11 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Lock manager waiters
wait_event_type = Lock
7 sessions
Critical threshold: 5 sessions Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Lock manager waiters moved past its safe budget.Where: wait_event_type = Lock.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 7 sessions against 5 sessions.Evidence: queryid 88412093412 · source tag H11 · Lock manager waiters.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close I/O wait share I/O wait share
M13 · H8 · H8 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius I/O wait share
wait_event_type = IO
12 % of active
Critical threshold: 20% Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: I/O wait share moved past its safe budget.Where: wait_event_type = IO.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 12 % of active against 20%.Evidence: queryid 88412093412 · source tag H8 · I/O wait share.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Buffer content waiters Buffer content waiters
M14 · H4 · H4 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Buffer content waiters
wait_event = buffer_content
1 sessions
Critical threshold: 3 sessions Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Buffer content waiters moved past its safe budget.Where: wait_event = buffer_content.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 1 sessions against 3 sessions.Evidence: queryid 88412093412 · source tag H4 · Buffer content waiters.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Shared buffer miss ratio Shared buffer miss ratio
M15 · H7 · H7 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Shared buffer miss ratio
1 - hit ratio, pg_stat_database
1.6 %
Critical threshold: 1% (OLTP) Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Shared buffer miss ratio moved past its safe budget.Where: 1 - hit ratio, pg_stat_database.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 1.6 % against 1% (OLTP).Evidence: queryid 88412093412 · source tag H7 · Shared buffer miss ratio.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Requested checkpoints Requested checkpoints
M16 · H6 · H6 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Requested checkpoints
pg_stat_checkpointer
14 % of total
Critical threshold: 20% Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Requested checkpoints moved past its safe budget.Where: pg_stat_checkpointer.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 14 % of total against 20%.Evidence: queryid 88412093412 · source tag H6 · Requested checkpoints.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Backend buffer writes Backend buffer writes
M17 · H8 · H8 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Backend buffer writes
pg_stat_io
2.1 % of allocs
Critical threshold: 5% Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Backend buffer writes moved past its safe budget.Where: pg_stat_io.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 2.1 % of allocs against 5%.Evidence: queryid 88412093412 · source tag H8 · Backend buffer writes.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Autovacuum workers saturated Autovacuum workers saturated
M18 · H6 · H6 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Autovacuum workers saturated
active = max_workers
20 min
Critical threshold: 15 min Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Autovacuum workers saturated moved past its safe budget.Where: active = max_workers.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 20 min against 15 min.Evidence: queryid 88412093412 · source tag H6 · Autovacuum workers saturated.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Top query share of DB time Top query share of DB time
M19 · H8 · H8 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Top query share of DB time
pg_stat_statements total_exec_time %
41 %
Critical threshold: 20% Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Top query share of DB time moved past its safe budget.Where: pg_stat_statements total_exec_time %.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 41 % against 20%.Evidence: queryid 88412093412 · source tag H8 · Top query share of DB time.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Blocks read per row returned Blocks read per row returned
M20 · H7 · H7 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Blocks read per row returned
(blks_hit + blks_read) / rows
920 blocks/row
Critical threshold: 100 blocks Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Blocks read per row returned moved past its safe budget.Where: (blks_hit + blks_read) / rows.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 920 blocks/row against 100 blocks.Evidence: queryid 88412093412 · source tag H7 · Blocks read per row returned.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Execution jitter, worst query Execution jitter, worst query
M21 · H3 · H3 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Execution jitter, worst query
max / mean exec time
38 ×
Critical threshold: 50× Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Execution jitter, worst query moved past its safe budget.Where: max / mean exec time.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 38 × against 50×.Evidence: queryid 88412093412 · source tag H3 · Execution jitter, worst query.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Disk reads per call, worst query Disk reads per call, worst query
M22 · H7 · H7 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Disk reads per call, worst query
shared_blks_read / calls
640 blocks
Critical threshold: 1,000 blocks Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Disk reads per call, worst query moved past its safe budget.Where: shared_blks_read / calls.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 640 blocks against 1,000 blocks.Evidence: queryid 88412093412 · source tag H7 · Disk reads per call, worst query.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Plan time ÷ exec time, worst query Plan time ÷ exec time, worst query
M23 · H10 · H10 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Plan time ÷ exec time, worst query
mean_plan / mean_exec
0.04 ratio
Critical threshold: 1.0 Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Plan time ÷ exec time, worst query moved past its safe budget.Where: mean_plan / mean_exec.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to Developer.Measured: 0.04 ratio against 1.0.Evidence: queryid 88412093412 · source tag H10 · Plan time ÷ exec time, worst query.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close System CPU share System CPU share
M24 · H9 · H9 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius System CPU share
pg_stat_kcache system_time
8 % of CPU
Critical threshold: 20% Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: System CPU share moved past its safe budget.Where: pg_stat_kcache system_time.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 8 % of CPU against 20%.Evidence: queryid 88412093412 · source tag H9 · System CPU share.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close Statement stats evictions Statement stats evictions
M25 · H10 · H10 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Statement stats evictions
pg_stat_statements_info.dealloc
0 /15 min
Critical threshold: limit 0 Primary anti-pattern: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Statement stats evictions moved past its safe budget.Where: pg_stat_statements_info.dealloc.Why: Sustained pressure on a shared PostgreSQL resource. Route the fix to DBA.Measured: 0 /15 min against limit 0.Evidence: queryid 88412093412 · source tag H10 · Statement stats evictions.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close SELECT ... FROM orders WHERE customer_id = ? ORDER BY created_at DESC Top SQL offender
pg_stat_statements 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Top SQL offender
orders#customer_orders · queryid 88412093412
1,840 shared blocks / call (43.8x vs. prior · 75% of offender I/O)
Critical threshold: 100 blocks / call Primary anti-pattern: Sequential scan or unbatched write; add the composite index or batch by primary-key range. Blast radius: shared buffers, lock waits, and plan time for query 88412093412; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id,
o.customer_id,
o.status,
o.total_cents,
o.created_at
FROM public.orders AS o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC
LIMIT 50; Execution plan (EXPLAIN ANALYZE, BUFFERS)
Limit (cost=14820.40..14820.52 rows=50 width=48) (actual time=184.2..184.6 ms rows=50 buffers: shared hit=1620 read=220)
-> Sort (Sort Key: o.created_at DESC)
-> Seq Scan on public.orders o (Filter: (customer_id = $1) · Rows Removed by Filter: 91850) Recommended fix
-- Add composite covering index matching WHERE + ORDER BY:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at
ON public.orders (customer_id, created_at DESC)
INCLUDE (status, total_cents); 3. Root cause (6-part) What: Top SQL offender moved past its safe budget.Where: orders#customer_orders · queryid 88412093412.Why: Sequential scan or unbatched write; add the composite index or batch by primary-key range.Measured: 1,840 shared blocks / call (43.8x vs. prior · 75% of offender I/O) against 100 blocks / call.Evidence: queryid 88412093412 · source tag pg_stat_statements · Top SQL offender.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H1 · Transaction ID (xmin / XID) Horizon Lag Transaction ID (xmin / XID) Horizon Lag
H1 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Transaction ID (xmin / XID) Horizon Lag
SELECT datname, age(datfrozenxid) FROM pg_database
412000000 against 1073741824
Critical threshold: 1073741824 Primary anti-pattern: datfrozenxid age 412M XIDs (19.2% of 2B horizon; healthy). Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Transaction ID (xmin / XID) Horizon Lag moved past its safe budget.Where: SELECT datname, age(datfrozenxid) FROM pg_database.Why: datfrozenxid age 412M XIDs (19.2% of 2B horizon; healthy).Measured: 412000000 against 1073741824 against 1073741824.Evidence: queryid n/a · source tag H-probe · Transaction ID (xmin / XID) Horizon Lag.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H2 · idle in transaction idle in transaction
H2 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius idle in transaction
SELECT pid, usename, now() - xact_start FROM pg_stat_activity WHERE state = 'idle in transaction'
312.0 against 60.0
Critical threshold: 60.0 Primary anti-pattern: PID 41892 idle in transaction for 312.0s holding RowExclusiveLock on public.orders [CSQL-M05 · DIRECT] Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: idle in transaction moved past its safe budget.Where: SELECT pid, usename, now() - xact_start FROM pg_stat_activity WHERE state = 'idle in transaction'.Why: PID 41892 idle in transaction for 312.0s holding RowExclusiveLock on public.orders [CSQL-M05 · DIRECT]Measured: 312.0 against 60.0 against 60.0.Evidence: queryid n/a · source tag H-probe · idle in transaction.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H3 · Long-Running Active Query Long-Running Active Query
H3 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Long-Running Active Query
SELECT pid, now() - query_start FROM pg_stat_activity WHERE state = 'active'
not sampled
Critical threshold: not sampled Primary anti-pattern: Active queries are running longer than the SLO performance contract allows — usually because they scan far more rows than the covering index returns. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Long-Running Active Query moved past its safe budget.Where: SELECT pid, now() - query_start FROM pg_stat_activity WHERE state = 'active'.Why: Active queries are running longer than the SLO performance contract allows — usually because they scan far more rows than the covering index returns.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Long-Running Active Query.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H4 · High Active Sessions High Active Sessions
H4 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius High Active Sessions
SELECT count(*) FROM pg_stat_activity
42 against 85
Critical threshold: 85 Primary anti-pattern: 42 backends vs 4x21=84 rule of thumb (max_connections=100, 2 in LWLock wait); active_non_idle=18 client backends, active_session_ratio=18.0% of max_connections (budget 25%) Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: High Active Sessions moved past its safe budget.Where: SELECT count(*) FROM pg_stat_activity.Why: 42 backends vs 4x21=84 rule of thumb (max_connections=100, 2 in LWLock wait); active_non_idle=18 client backends, active_session_ratio=18.0% of max_connections (budget 25%)Measured: 42 against 85 against 85.Evidence: queryid n/a · source tag H-probe · High Active Sessions.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H5 · Idle Connection Accumulation Idle Connection Accumulation
H5 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Idle Connection Accumulation
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle'
not sampled
Critical threshold: not sampled Primary anti-pattern: Idle (not in transaction) connections waste the max_connections budget; a transaction pooler reuses them instead of leaving sessions parked. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Idle Connection Accumulation moved past its safe budget.Where: SELECT count(*) FROM pg_stat_activity WHERE state = 'idle'.Why: Idle (not in transaction) connections waste the max_connections budget; a transaction pooler reuses them instead of leaving sessions parked.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Idle Connection Accumulation.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H6 · Autovacuum Disabled / Bloat Stall Autovacuum Disabled / Bloat Stall
H6 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Autovacuum Disabled / Bloat Stall
SHOW autovacuum
off (orders) against on
Critical threshold: on Primary anti-pattern: Autovacuum disabled on public.orders while hot updates accumulate dead tuples [CSQL-M01 · DIRECT] Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Autovacuum Disabled / Bloat Stall moved past its safe budget.Where: SHOW autovacuum.Why: Autovacuum disabled on public.orders while hot updates accumulate dead tuples [CSQL-M01 · DIRECT]Measured: off (orders) against on against on.Evidence: queryid n/a · source tag H-probe · Autovacuum Disabled / Bloat Stall.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H7 · Unused Non-Unique Index Unused Non-Unique Index
H7 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Unused Non-Unique Index
SELECT indexrelname FROM pg_stat_user_indexes s WHERE idx_scan = 0 AND NOT indisunique
not sampled
Critical threshold: not sampled Primary anti-pattern: Non-unique indexes exist that no query has used since the stats window opened; they slow every INSERT/UPDATE and may be safely droppable. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Unused Non-Unique Index moved past its safe budget.Where: SELECT indexrelname FROM pg_stat_user_indexes s WHERE idx_scan = 0 AND NOT indisunique.Why: Non-unique indexes exist that no query has used since the stats window opened; they slow every INSERT/UPDATE and may be safely droppable.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Unused Non-Unique Index.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H8 · Repeated Sequential Table Scans Repeated Sequential Table Scans
H8 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Repeated Sequential Table Scans
SELECT relname, seq_scan, seq_tup_read FROM pg_stat_user_tables ORDER BY seq_tup_read DESC
not sampled
Critical threshold: not sampled Primary anti-pattern: Large hot tables are repeatedly read with full sequential scans where a selective index or predicate could remove most of the I/O. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Repeated Sequential Table Scans moved past its safe budget.Where: SELECT relname, seq_scan, seq_tup_read FROM pg_stat_user_tables ORDER BY seq_tup_read DESC.Why: Large hot tables are repeatedly read with full sequential scans where a selective index or predicate could remove most of the I/O.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Repeated Sequential Table Scans.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H9 · Database Size Growth Database Size Growth
H9 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Database Size Growth
SELECT pg_database_size(current_database())
not sampled
Critical threshold: not sampled Primary anti-pattern: On-disk database size is growing beyond its storage budget; check retention/partitioning and replication-slot WAL pinning first. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Database Size Growth moved past its safe budget.Where: SELECT pg_database_size(current_database()).Why: On-disk database size is growing beyond its storage budget; check retention/partitioning and replication-slot WAL pinning first.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Database Size Growth.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H10 · Missing pg_stat_statements Extension Missing pg_stat_statements Extension
H10 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Missing pg_stat_statements Extension
SELECT count(*) FROM pg_extension WHERE extname = 'pg_stat_statements'
not sampled
Critical threshold: not sampled Primary anti-pattern: Shared memory omits pg_stat_statements. Per-query buffer analysis (the monitor decision metric) requires preloading and creating the extension. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Missing pg_stat_statements Extension moved past its safe budget.Where: SELECT count(*) FROM pg_extension WHERE extname = 'pg_stat_statements'.Why: Shared memory omits pg_stat_statements. Per-query buffer analysis (the monitor decision metric) requires preloading and creating the extension.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Missing pg_stat_statements Extension.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H11 · Exclusive Lock Blocking Chain Exclusive Lock Blocking Chain
H11 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Exclusive Lock Blocking Chain
SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0
4 against 1
Critical threshold: 1 Primary anti-pattern: Lock blocking chain depth 4 queued behind idle-in-transaction PID 41892 on public.orders [CSQL-M03 · INDIRECT] Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Exclusive Lock Blocking Chain moved past its safe budget.Where: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0.Why: Lock blocking chain depth 4 queued behind idle-in-transaction PID 41892 on public.orders [CSQL-M03 · INDIRECT]Measured: 4 against 1 against 1.Evidence: queryid n/a · source tag H-probe · Exclusive Lock Blocking Chain.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H12 · Dead-Tuple Bloat Ratio Dead-Tuple Bloat Ratio
H12 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Dead-Tuple Bloat Ratio
SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables
0.34 against 0.2
Critical threshold: 0.2 Primary anti-pattern: public.orders dead-tuple ratio 34.0% (340,000 dead tuples) amplified by H6 autovacuum stall [CSQL-M01 · INDIRECT] Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Dead-Tuple Bloat Ratio moved past its safe budget.Where: SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables.Why: public.orders dead-tuple ratio 34.0% (340,000 dead tuples) amplified by H6 autovacuum stall [CSQL-M01 · INDIRECT]Measured: 0.34 against 0.2 against 0.2.Evidence: queryid n/a · source tag H-probe · Dead-Tuple Bloat Ratio.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H13 · 32-bit Sequence Exhaustion 32-bit Sequence Exhaustion
H13 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius 32-bit Sequence Exhaustion
SELECT schemaname, sequencename, data_type FROM pg_sequences
not sampled
Critical threshold: not sampled Primary anti-pattern: Primary keys or sequences still use 32-bit integers and are approaching exhaustion; plan a supervised migration to bigint. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: 32-bit Sequence Exhaustion moved past its safe budget.Where: SELECT schemaname, sequencename, data_type FROM pg_sequences.Why: Primary keys or sequences still use 32-bit integers and are approaching exhaustion; plan a supervised migration to bigint.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · 32-bit Sequence Exhaustion.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H14 · Unindexed Foreign Key Unindexed Foreign Key
H14 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Unindexed Foreign Key
SELECT conname, conkey FROM pg_constraint WHERE contype = 'f'
not sampled
Critical threshold: not sampled Primary anti-pattern: Foreign keys exist whose columns are not covered by a leftmost-prefix index, slowing joins, cascades, and Referenced-action lookups. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Unindexed Foreign Key moved past its safe budget.Where: SELECT conname, conkey FROM pg_constraint WHERE contype = 'f'.Why: Foreign keys exist whose columns are not covered by a leftmost-prefix index, slowing joins, cascades, and Referenced-action lookups.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Unindexed Foreign Key.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H15 · Unpartitioned Large Table Unpartitioned Large Table
H15 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Unpartitioned Large Table
SELECT relname, n_live_tup FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid WHERE relkind = 'r'
not sampled
Critical threshold: not sampled Primary anti-pattern: Large tables lack partitioning. Maintenance tasks and sequential scans read whole-table data on every pass. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Unpartitioned Large Table moved past its safe budget.Where: SELECT relname, n_live_tup FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid WHERE relkind = 'r'.Why: Large tables lack partitioning. Maintenance tasks and sequential scans read whole-table data on every pass.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Unpartitioned Large Table.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H16 · Non-UTF8 Database Encoding Non-UTF8 Database Encoding
H16 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Non-UTF8 Database Encoding
SELECT datname, pg_encoding_to_char(encoding) FROM pg_database
not sampled
Critical threshold: not sampled Primary anti-pattern: A database is not encoded UTF8, a silent corruption/mojibake risk for multilingual SaaS/FinTech text data. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Non-UTF8 Database Encoding moved past its safe budget.Where: SELECT datname, pg_encoding_to_char(encoding) FROM pg_database.Why: A database is not encoded UTF8, a silent corruption/mojibake risk for multilingual SaaS/FinTech text data.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Non-UTF8 Database Encoding.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H17 · Subtransaction & MultiXact SLRU Pressure Subtransaction & MultiXact SLRU Pressure
H17 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Subtransaction & MultiXact SLRU Pressure
SELECT name, blks_read, blks_hit FROM pg_stat_slru WHERE name LIKE '%MultiXact%' OR name = 'Subtrans'
0 against 0
Critical threshold: 0 Primary anti-pattern: Subtransaction SLRU hit ratio 100.0%; TLS/SSL posture maps to CIS PostgreSQL 17 Benchmark v1.0.0. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Subtransaction & MultiXact SLRU Pressure moved past its safe budget.Where: SELECT name, blks_read, blks_hit FROM pg_stat_slru WHERE name LIKE '%MultiXact%' OR name = 'Subtrans'.Why: Subtransaction SLRU hit ratio 100.0%; TLS/SSL posture maps to CIS PostgreSQL 17 Benchmark v1.0.0.Measured: 0 against 0 against 0.Evidence: queryid n/a · source tag H-probe · Subtransaction & MultiXact SLRU Pressure.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close H18 · Replication Slot WAL Retention Replication Slot WAL Retention
H18 · H-probe 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius Replication Slot WAL Retention
SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) FROM pg_replication_slots
not sampled
Critical threshold: not sampled Primary anti-pattern: Inactive replication slots (or failed data checksums) pin WAL and conceal corruption; review slots, retention, and checksum state. Blast radius: shared buffers, lock waits, and plan time for query n/a; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (customer_id, total);
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; 3. Root cause (6-part) What: Replication Slot WAL Retention moved past its safe budget.Where: SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) FROM pg_replication_slots.Why: Inactive replication slots (or failed data checksums) pin WAL and conceal corruption; review slots, retention, and checksum state.Measured: not sampled against not sampled.Evidence: queryid n/a · source tag H-probe · Replication Slot WAL Retention.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close ENV-05 · work_mem per sort / hash operation work_mem per sort / hash operation
ENV 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius work_mem per sort / hash operation
pg_settings · temp spill ceiling
4 MB (default) (recommended: 32 MB (OLTP) / 64 MB (OLAP))
Critical threshold: 32 MB (OLTP) / 64 MB (OLAP) Primary anti-pattern: 4 MB work_mem causes ORDER BY, GROUP BY, and Hash Join nodes to spill temporary files to disk. Blast radius: shared buffers, lock waits, and plan time for query ENV-05; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf(); 3. Root cause (6-part) What: work_mem per sort / hash operation moved past its safe budget.Where: pg_settings · temp spill ceiling.Why: 4 MB work_mem causes ORDER BY, GROUP BY, and Hash Join nodes to spill temporary files to disk.Measured: 4 MB (default) (recommended: 32 MB (OLTP) / 64 MB (OLAP)) against 32 MB (OLTP) / 64 MB (OLAP).Evidence: queryid ENV-05 · source tag ENV · work_mem per sort / hash operation.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.
Close ENV-10 · OS huge_pages, vm.swappiness & preload libraries OS huge_pages, vm.swappiness & preload libraries
ENV 1. Triage & blast radius 2. SQL & fix 3. Root cause (6-part) 4. GitLab & MR gate
1. Triage & blast radius OS huge_pages, vm.swappiness & preload libraries
Linux sysctl + shared_preload_libraries
huge_pages=try · swappiness=1 · pg_stat_statements, pgaudit (recommended: huge_pages=try/on · swappiness<=1)
Critical threshold: huge_pages=try/on · swappiness<=1 Primary anti-pattern: Huge pages minimize kernel TLB pressure, and shared memory preloads observability and audit extensions. Blast radius: shared buffers, lock waits, and plan time for query ENV-10; read-only, so no WAL. 2. SQL & fix Current SQL
SELECT o.id, o.total, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
ORDER BY o.created_at DESC
LIMIT 100; Execution plan (EXPLAIN ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
Limit (cost=1420.00..1420.25 rows=100 width=48) (actual time=41.2..41.3 rows=100)
-> Sort (cost=1420.00..1430.00 rows=4000 width=48) (actual time=41.1..41.2)
Sort Key: o.created_at DESC
Buffers: shared hit=812 read=96
-> Hash Join (cost=1.09..1320.00 rows=4000 width=48)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=812 read=96 Recommended fix
-- Already aligned with production OS & preload baseline 3. Root cause (6-part) What: OS huge_pages, vm.swappiness & preload libraries moved past its safe budget.Where: Linux sysctl + shared_preload_libraries.Why: Huge pages minimize kernel TLB pressure, and shared memory preloads observability and audit extensions.Measured: huge_pages=try · swappiness=1 · pg_stat_statements, pgaudit (recommended: huge_pages=try/on · swappiness<=1) against huge_pages=try/on · swappiness<=1.Evidence: queryid ENV-10 · source tag ENV · OS huge_pages, vm.swappiness & preload libraries.Owner: Developer and DBA / Platform Engineer review the fix MR before any merge.4. GitLab & MR gate Export the Root Cause Analysis (RCA) and open the Supervised Merge Request (MR) gate.
Markdown PDF DOCX
Gate: Tier 1 → Tier 2 → Policy Gate → Human Approval. The agent never merges without a human.