Part 8 · 1 chapters · ~8 min

Incident Log: The Shared Database

A composite incident: a reporting migration queues an ACCESS EXCLUSIVE lock and stalls payment writes, connection pools exhaust, and the remediation: single ownership, replicas and CDC for readers, lock timeouts, per-service roles, and splitting the database.

Timeline and lessons

A composite incident; the lock-queue mechanism is real Postgres behaviour (Postgres course P5), the names and numbers are illustrative.

code

-- every migration: fail fast instead of queueing behind a long query and blocking everyone else
SET lock_timeout = '3s';
SET statement_timeout = '15min';
ALTER TABLE payments.transactions ADD COLUMN channel text;     -- retried by the migration tool on timeout

-- per-service roles: reporting cannot touch payment tables at all
REVOKE ALL ON SCHEMA payments FROM reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA payments_public_views TO reporting_role;

Why a shared database couples teams: schema changes need everyone's agreement, one team's slow query consumes everyone's I/O and connections, and nobody can tell which service depends on which column. Splitting it is the strangler fig of part 1 applied to data.

INCIDENT: THE SHARED DATABASE

a reporting query locks out payments

swipe the figure sideways, or tap expand for full screen

1/4

the setup

Payments and reporting shared one database; reporting read payment tables directly, with no contract.

two owners, one databaseno contract between them