Systems Engineering & Incident Response Runbooks

Production Engineering Runbooks

Battle-tested operational procedures for zero-downtime PostgreSQL DDL migrations, pgvector HNSW memory exhaustion, and multi-tenant key rotation.

Production Incident & Operations Runbooks

Engineering Operations & Incident Runbook Library

Battle-tested operational procedures for zero-downtime database migrations, vector memory exhaustion, and multi-tenant key rotation.

Verified Production Procedures

RBK-PG-001High (SEV-2)

Zero-Downtime PostgreSQL DDL Migrations on High-Volume Tables

Target System: PostgreSQL Multi-Tenant Core Cluster • Estimated MTTR: 15 minutes

Operational Summary:

Standard operating procedure for adding columns, dropping constraints, and creating indexes on tables with millions of active rows without acquiring exclusive table locks.

Required Prerequisites & Safety Checks:

Set statement_timeout = 5000ms to avoid prolonged queue stalls.
Ensure connection pooler (PgBouncer) health is green.
Active replica lag is < 500ms.

Step-by-Step Incident Execution Protocol:

Step 01

Set safe lock timeout before transaction

Prevents blocking active read/write traffic if lock acquisition takes longer than 2 seconds.

SET lock_timeout = "2s"; SET statement_timeout = "5s";

Verification: SHOW lock_timeout returns 2s.

Step 02

Execute Concurrent Index Creation

Builds index without exclusive ACCESS EXCLUSIVE lock; allows non-blocking continuous writes.

CREATE INDEX CONCURRENTLY idx_tenants_created_at ON transactions (tenant_id, created_at DESC);

Verification: Query pg_stat_activity for CREATE INDEX status.

Step 03

Validate Index Health & Statistics

Ensures index is marked valid and planner statistics are refreshed.

ANALYZE transactions; SELECT indisvalid FROM pg_index WHERE indexrelid = "idx_tenants_created_at"::regclass;

Verification: indisvalid returns true.

Emergency Rollback Protocol:

DROP INDEX CONCURRENTLY IF EXISTS idx_tenants_created_at;