Phase 10: Destruction Labs & Chaos Engineering Playbook¶

Hands-On Destruction Lab Protocols (0.1 CPU / 64MB RAM Sandbox)¶

This runbook contains step-by-step SQL scripts, diagnostic queries, and emergency recovery commands to reproduce and resolve catastrophic database choke points in our constrained local sandbox.

Lab 01: The "Noisy Neighbor" Misdirection¶

The Scenario:¶

Table A (orders): High-volume e-commerce table receiving 500 fast index lookups/sec.
Table B (analytics_logs): Unindexed analytical table receiving only 2 queries/minute.
The Misdirection: Application monitoring alerts that Table A queries are timing out. The engineering team starts debugging Table A indexes and caching, unaware that Table B is the true culprit.

Step 1: Seed the Schema & Data¶

-- Fast, indexed transactional table
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT,
    amount NUMERIC(10, 2),
    created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO orders (customer_id, amount) 
SELECT (random()*1000)::int, (random()*500)::numeric(10,2) 
FROM generate_series(1, 100000);

-- Unindexed heavy table
CREATE TABLE analytics_logs (
    log_id BIGINT GENERATED ALWAYS AS IDENTITY,
    raw_payload TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO analytics_logs (raw_payload) 
SELECT md5(random()::text) || repeat('DATA', 50) 
FROM generate_series(1, 50000);

Step 2: Trigger the Bombardment¶

In Terminal 1: Run repeated fast queries on Table A:

while true; do psql -d postgres_lab -c "SELECT * FROM orders WHERE order_id = (random()*100000)::int;" > /dev/null; done

In Terminal 2: Launch the "Poison" query on Table B:

-- Full unindexed sequential scan calculating expensive hashes on 0.1 CPU core:
SELECT count(*), md5(raw_payload) 
FROM analytics_logs 
GROUP BY md5(raw_payload);

Step 3: Observe the Misdirection¶

Watch Terminal 1: The fast orders queries that normally take 0.5ms now stall for 15+ seconds!

Step 4: The 10-Year DBA Diagnostic Runbook¶

Under fire, do NOT look at query counts. Inspect CPU consumption and wait states:

-- Find queries holding CPU or active for > 2 seconds:
SELECT pid, now() - query_start AS duration, wait_event_type, wait_event, query 
FROM pg_stat_activity 
WHERE state = 'active' AND (now() - query_start) > interval '2 seconds'
ORDER BY duration DESC;

* The Finding: You see analytics_logs running for 25 seconds with wait_event = NULL (meaning it is 100% pegged on the throttled CPU core), while 50 orders queries are queued in wait_event = CPU or I/O wait!

Step 5: Emergency Hotfix¶

-- Instantly kill the rogue query:
SELECT pg_cancel_backend(<POISON_PID>);

* Permanent architectural fix: Create an index on analytics_logs, restrict analytical queries to a read replica, and set ALTER ROLE reporting_user SET statement_timeout = '5s'.

Lab 02: The DDL Lock Stampede¶

The Scenario:¶

A long-running report query blocks an ALTER TABLE migration, which in turn halts all application web traffic.

Step 1: Simulate the Long Report¶

In Session A:

BEGIN;
SELECT count(*) FROM orders WHERE pg_sleep(30) IS NOT NULL;

Step 2: Simulate the DDL Migration¶

In Session B (immediately while Session A is running):

ALTER TABLE orders ADD COLUMN promo_code VARCHAR(20);
-- This blocks, waiting for AccessExclusiveLock!

Step 3: Simulate Normal Application Reads¶

In Session C:

SELECT * FROM orders WHERE order_id = 42;
-- THIS BLOCKS TOO! The whole website is now down!

Step 4: Diagnostic Query¶

Find the blocking tree:

    FROMJOINJOIN

href="#__codelineno-8-1">SELECT blocked_locks.pid AS blocked_pid, blocked_activity.query AS blocked_statement, blocking_locks.pid AS blocking_pid, blocking_activity.query AS current_statement_in_blocking_process class="w"> pg_catalog.pg_locks blocked_locks class="w"> pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid class="w"> pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;

Step 5: The Emergency Fix¶

Kill the migration or the blocking read:

SELECT pg_terminate_backend(<BLOCKING_PID>);

Lab 03: The Index Miss & Planner Betrayal¶

The Scenario:¶

An indexed query unexpectedly performs a full table sequential scan because of implicit data type casting from an application ORM.

Step 1: Create Table with BIGINT ID¶

CREATE TABLE accounts (
    account_number BIGINT PRIMARY KEY,
    holder_name TEXT,
    balance NUMERIC
);
CREATE INDEX idx_accounts_num ON accounts (account_number);
INSERT INTO accounts SELECT g, 'Holder ' || g, 1000 FROM generate_series(1, 200000) g;

Step 2: The Subtle Trap (Implicit Type Cast)¶

Node.js / Python application queries the database sending a string instead of a bigint:

-- Query with BIGINT literal:
EXPLAIN ANALYZE 
SELECT * FROM accounts WHERE account_number = 150000;
-- Result: Index Scan using idx_accounts_num (Execution time: 0.04 ms)

-- Query with TEXT parameter (The Index Miss!):
EXPLAIN ANALYZE 
SELECT * FROM accounts WHERE account_number::text = '150000';
-- Result: Seq Scan on accounts (Execution time: 45.20 ms -> 1000x SLOWER!)

Why the Planner Betrayed You: The B-Tree index is built on BIGINT. Wrapping the column in ::text forces Postgres to convert every single row to text before comparing, rendering the B-Tree index completely invisible to the optimizer!

Step 3: The Resolution¶

Fix the application ORM parameter binding to cast parameters to BIGINT.

Or create an expression index if the query cannot be altered:

CREATE INDEX idx_accounts_text ON accounts ((account_number::text));