# Unpartitioned table above the live-tuple threshold

- **Finding:** `H15`
- **Severity:** P1
- **Owner:** DBA / Platform Engineer (Cluster GUCs / Vacuum / Pooling / Storage) + Developer (Application SQL / ORM / Migrations)
- **Environment:** cloudrun-showcase (synthetic-demo)
- **Generated:** 2026-10-27 17:23:20 UTC

## 1. Incident Overview & Executive Summary

CircuitSQL detected finding H15 (Unpartitioned table above the live-tuple threshold) in environment cloudrun-showcase (synthetic-demo) at 2026-10-27 17:23:20 UTC. Vacuum cycles and index scans lengthened, and the table became the dominant consumer of shared buffers. The correlated report classifies the incident as P1 with triggers H2, H6, H11, H12, pg_stat_statements. CircuitSQL routes remediation to DBA / Platform Engineer (Cluster GUCs / Vacuum / Pooling / Storage) + Developer (Application SQL / ORM / Migrations). Environment cloudrun-showcase (synthetic-demo) reported 4 breached health check(s) across 2 correlated root-cause incident(s) and 2 query offender(s). CircuitSQL routes remediation to Developer (Application SQL / ORM / Migrations) + DBA / Platform Engineer (Cluster GUCs / Vacuum / Pooling / Storage).

### Document metadata

| Field | Value |
| --- | --- |
| Incident ID | CSQL-INC-H15-202610271723 |
| Date / Time of Incident | 2026-10-27 17:23:20 UTC |
| Duration | 63 min (trigger -25 min to recovery +38 min; see section 4) |
| Severity Level | P1 - Critical (customer-visible outage or data-risk) |
| Affected Systems | circuitsql_monitor@db-primary.internal:5432/circuitsql (ssl_mode=require); orders and customers OLTP paths; environment cloudrun-showcase (synthetic-demo) |
| Database Engine & Version | PostgreSQL 17.2 |
| Lead Investigator(s) | Developer / Marlon Francis Briguera (incident commander); Backend Engineer (query path); DBA / Platform Engineer (cluster controls) |
| Current Status | Mitigated - monitoring for recurrence; permanent remediation tracked in section 7 |

## 2. Technical Environment Context

### System topography

```text
Client / API (FastAPI, app_rw)
        |
        v
[PgBouncer :6432] -- pool_mode=transaction, default_pool_size=4x vCPU
        |
        v
[PostgreSQL primary circuitsql]  << finding H15
        |-- pg_stat_activity / pg_locks / pg_blocking_pids
        |-- pg_stat_statements (offender ranking)
        |-- pg_stat_checkpointer / pg_stat_io (PG 17/18 subsystem counters)
        '-- [Read replica] analytics_ro (reporting, EXPLAIN ANALYZE rehearsals)

[CircuitSQL Monitor] -- H1-H18 probes, CSQL-S01..S07 security scan, RCA export
```

### Privilege and tool prerequisites

- Role membership: pg_monitor and pg_read_all_stats (read-only catalog access).
- pg_stat_statements present in shared_preload_libraries and installed in the database.
- PgBouncer admin console reachable on port 6432 for SHOW POOLS and RELOAD.
- psql 17 client plus a shadow database for EXPLAIN (ANALYZE, BUFFERS) rehearsals; never run write DDL against production to inspect a plan.
- ALTER SYSTEM requires a superuser or a role with the ALTER SYSTEM privilege; credentials come from the secret manager, never URL query strings.
- Incident channel and GitLab issue write access for the fix MR linked in section 8.

## 3. Symptoms & Observed Evidence

### Observed vs. expected behavior

| Aspect | Observed | Expected |
| --- | --- | --- |
| Measured value vs. threshold | H15 evaluated as observed. | Use declarative partitioning and a retention policy for high-volume history tables. Keep each relation below 10M live tuples. |
| Session and lock state | The finding evaluation detected no unexpected lock queue. | Lock depth <= 1, no idle-in-transaction session, and every statement bounded by lock_timeout and statement_timeout. |
| Workload impact | orders and customers traffic shows no customer-visible failure yet. | p95 within the 250 ms OLTP SLO and connection use below 85% of the pool budget. |
| Data integrity and compliance | No data loss observed; the control gap maps to SQLSTATE 54000 handling. | Constraints validated, checksums clean, and CIS PostgreSQL 17 controls satisfied. |

### Application exception log

```text
2026-10-27 17:11:20.812 UTC [api] ERROR orders.views: request failed route=/orders finding=H15
No application exception; p95 latency grows with history size.
Traceback (most recent call last):
  File "app/routes/orders.py", line 88, in create_order
    session.commit()
psycopg.errors.DatabaseError: LOG: automatic vacuum of table "circuitsql.public.orders": index scans: 3
```

### RDBMS error log

```text
2026-10-27 17:11:20.101 UTC [41892] LOG: process 41892 still waiting after 1000.042 ms
LOG: automatic vacuum of table "circuitsql.public.orders": index scans: 3
2026-10-27 17:11:21.204 UTC [41892] STATEMENT: /* finding=H15 */ SELECT s.schemaname, s.relname, s.n_live_tup, c.relkind::text FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s...
2026-10-27 17:11:22.318 UTC [41892] DETAIL: causal_tag=[CSQL-M15 · DIRECT] sqlstate=54000
```

## 4. Timeline of Events

| Phase | UTC Timestamp | Event | Evidence |
| --- | --- | --- | --- |
| Trigger | 2026-10-27 16:58:20 UTC | H15 condition established: Unpartitioned table above the live-tuple threshold | finding=H15; tag=[CSQL-M15 · DIRECT] |
| Alerting | 2026-10-27 17:05:20 UTC | CircuitSQL monitor evaluated the probe and raised the finding on the console. | environment=cloudrun-showcase (synthetic-demo); breached=true |
| Degradation | 2026-10-27 17:11:20 UTC | orders and customers traffic degraded: latency rose and lock or buffer pressure became visible. | The snapshot captured cloudrun-showcase (synthetic-demo) at 2026-10-27 17:23:20 UTC without a live catalogue row for H15. |
| Triage | 2026-10-27 17:23:20 UTC | On-call opened the per-finding RCA page and confirmed the mechanism against pg_stat_activity, pg_locks, and pg_stat_statements. | RCA page /console/rca/H15 |
| Mitigation | 2026-10-27 17:32:20 UTC | On-call terminated blockers and reloaded the connection pool as emergency mitigation (section 6.1). | pg_terminate_backend + PgBouncer RELOAD |
| Recovery | 2026-10-27 18:01:20 UTC | Latency, lock depth, and connection use returned to baseline. On-call monitored the incident for 30 minutes before closing it. | recovery verified at 18:01:20 UTC |

## 5. Technical Root Cause Analysis

### Causal flow diagram

```text
+------------------------------------------------------------------------------------------------+
| Trigger H15: Unpartitioned table above the live-tuple threshold                                |
+------------------------------------------------------------------------------------------------+
                                                |
                                                v
+------------------------------------------------------------------------------------------------+
| Mechanism: The orders history table grew past 10M live tuples without declarative partition... |
+------------------------------------------------------------------------------------------------+
                                                |
                                                v
+------------------------------------------------------------------------------------------------+
| Symptom: Vacuum cycles and index scans lengthened, and the table became the dominant consum... |
+------------------------------------------------------------------------------------------------+
                                                |
                                                v
+------------------------------------------------------------------------------------------------+
| Impact: orders/customers OLTP latency and write availability                                   |
+------------------------------------------------------------------------------------------------+
                                                v
+------------------------------------------------------------------------------------------------+
| Blast radius: checkout writes, orders reads, p95 SLO                                           |
+------------------------------------------------------------------------------------------------+
```

### Primary cause

The orders history table grew past 10M live tuples without declarative partitioning. Vacuum and index maintenance scaled with total history. Correlated verdict: Root Cause (H2 · High): PID 41892 idle in transaction for 312.0s holding RowExclusiveLock on public.orders [CSQL-M05 · DIRECT] -> Proximate Cascading Symptoms (2 suppressed): H11 (Lock blocking chain depth 4 queued behind idle-in-transaction PID 41892 on public.orders [CSQL-M03 · INDIRECT]), H12 (public.orders dead-tuple ratio 34.0% (340,000 dead tuples) amplified by H6 autovacuum stall [CSQL-M01 · INDIRECT]).

### Secondary cause

Retention used ad-hoc DELETE batches instead of partition detach. The batches left bloat behind. Downstream correlation: Root Cause (H6 · Medium): Autovacuum disabled on public.orders while hot updates accumulate dead tuples [CSQL-M01 · DIRECT] (isolated breach, 0 downstream symptoms).

### Tertiary cause

Vacuum cycles and index scans lengthened, and the table became the dominant consumer of shared buffers. Correlated evidence: Check H2 (High, owner DEVELOPER): value=312.0 vs. threshold=60.0 (cited) — PID 41892 idle in transaction for 312.0s holding RowExclusiveLock on public.orders [CSQL-M05 · DIRECT]

### Finding SQL

```sql
SELECT s.schemaname, s.relname, s.n_live_tup, c.relkind::text
  FROM pg_stat_user_tables s
  JOIN pg_class c ON c.oid = s.relid
 WHERE s.n_live_tup >= 10000000 AND c.relkind IN ('r', 'p')
 ORDER BY s.n_live_tup DESC;
```

### Metrics

- PG 17/18 pg_stat_checkpointer: requested ratio 11.2%, write 1840.5 ms, sync 210.2 ms.
- Waiters: LWLock buffer 2, IO 1.
- The snapshot has no catalogue evidence for H15. The exported document records the deterministic probe and the expected threshold.

- **Causal tag:** [CSQL-M15 · DIRECT]

## 6. Step-by-Step Resolution & Remediation Procedures

### Emergency mitigation (immediate stabilization)

### Step 1 Capture the blocker set before touching anything

```sql
SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blockers,
       left(query, 160) AS query
  FROM pg_stat_activity
 WHERE state <> 'idle'
   AND (now() - xact_start > interval '30 seconds'
        OR wait_event_type = 'Lock')
 ORDER BY xact_age DESC;
```

Record the head blocker for H15 in the incident timeline before any termination. The capture is read-only and pg_monitor compatible.

### Step 2 Terminate only the head blocking backend, with a bounded wait

```sql
-- Replace 41892 with the head blocker captured in Step 1.
SELECT pg_terminate_backend(41892, 5000);

-- Confirm the queue drains instead of re-queuing.
SELECT count(*) AS queue_depth
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;
```

Do not terminate blocked sessions first. They re-queue behind the same head blocker and the incident restarts.

### Step 3 Drain and reload PgBouncer so pooled sessions pick up the new limits

```sql
-- PgBouncer admin console (port 6432), not the PostgreSQL port.
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c 'SHOW POOLS;'
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c 'RELOAD;'
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c 'SHOW STATS;'
```

RELOAD applies pgbouncer.ini pool_mode and default_pool_size without dropping the listener. Transaction pooling discards session-level state by design.

### Permanent remediation

### 6.2.1 CREATE INDEX CONCURRENTLY & EXPLAIN (ANALYZE, BUFFERS)

```sql
-- 6.2.1 Build the replacement index without blocking writes.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
    ON public.orders (customer_id, created_at DESC);

-- Prove the planner uses it before dropping or shipping anything else.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents
  FROM public.orders
 WHERE customer_id = $1
 ORDER BY created_at DESC
 LIMIT 50;

-- Finding-specific DDL for H15.
CREATE TABLE public.orders_2026_11 PARTITION OF public.orders
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
```

Partitioning is an expand-and-contract migration; never rewrite the live table in place.

### 6.2.2 ALTER SYSTEM SET statement_timeout = '5000ms' & idle_in_transaction_session_timeout = '15000ms'

```sql
-- 6.2.2 Bound every session at the cluster and role level.
ALTER SYSTEM SET statement_timeout = '5000ms';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '15000ms';
SELECT pg_reload_conf();

-- Verify the effective values for the application role.
SELECT rolname, rolconfig
  FROM pg_roles
 WHERE rolname IN ('app_rw', 'migration_runner', 'analytics_ro');
```

Per-role settings override cluster defaults. For H15, the idle-in-transaction ceiling is the control that prevents recurrence.

### 6.2.3 Before/After @Transactional application refactor

```sql
# Before: network I/O and retries live inside the transaction.
@transactional
def create_order(payload: OrderIn) -> Order:
    order = Order(**payload.model_dump())
    session.add(order)
    session.flush()
    inventory.reserve(order.id)      # remote call holds the write lock
    payments.authorize(order)        # another remote call
    return order

# After: the transaction only covers database work.
@transactional
def create_order(payload: OrderIn) -> Order:
    order = Order(**payload.model_dump())
    session.add(order)
    session.flush()
    outbox.enqueue("order.created", order.id)  # local insert, no I/O
    return order

# Inventory and payment run after COMMIT, with their own short transactions.
```

Keep only database work inside the transaction boundary. Run remote calls and retries after COMMIT.

## 7. Preventative Action Items & Monitoring Controls

| Action Item | Type | Target Date | Owner | Status |
| --- | --- | --- | --- | --- |
| Add a burn-rate alert for H15 with a 5-minute lookback and route it to the Monitor stage dashboard. | Monitoring control | 2026-10-30 | DBA / Platform Engineer | Open |
| Enforce statement_timeout, lock_timeout, and idle_in_transaction_session_timeout for every application role. | Configuration | 2026-10-30 | DBA / Platform Engineer | Open |
| Ship the reviewed remediation for H15 behind Tier 1 static analysis and Tier 2 shadow verification. | Code change | 2026-11-10 | Backend Engineer | Open |
| Add a regression test that fails when the finding reproduces against the showcase snapshot. | Test coverage | 2026-11-06 | Backend Engineer | Open |
| Re-audit the control against CIS PostgreSQL 17 Benchmark v1.0.0 and record the evidence in the Govern stage. | Compliance | 2027-01-25 | Software Architect / Technical Writer | Scheduled |

## 8. Related Resources & References

- **Internal Monitoring Dashboard:** /console?stage=monitor&view=rca&finding=H15
- **Related Merge Request:** !0 - fix MR to be opened from section 6.2 (H15)
- **Runbook Reference:** src/circuitsql/skills/root-cause-analysis/SKILL.md section 4-6; docs/specs/v3-spec.md (finding H15)
- **Vendor Documentation:** https://www.postgresql.org/docs/17/ddl-partitioning.html