Database Reliability Engineering

Everything else in a typical service tier is disposable. A stateless web pod dies, a fresh one starts, and nobody can tell the difference — that's the whole premise reliability patterns is built on. The database is the one component where that premise quietly stops being true: it is the single place your architecture keeps a promise it can't take back, and "kill it and let a fresh copy start" is precisely the move that turns a bad afternoon into a permanent one. Database Reliability Engineering (DBRE) is the name the industry gave to applying SRE's discipline — SLOs, blameless review, automation over toil — specifically to that one stubborn exception. This page goes past the general principles and into four skills a database reliability engineer is expected to have cold: how replication topologies fail, why a backup is a hypothesis until it's been restored, why connection-pool exhaustion quietly causes more outages than almost anything else on this list, and how to change a schema without ever taking the site down to do it.

Why database reliability engineering is its own discipline

☺ Like you're 10: Every other tier of your stack is stateless and disposable — kill it and a fresh copy is identical. A database remembers things, so "just restart it" is exactly the move that can make things worse instead of better.

The term was popularized by Laine Campbell and Charity Majors's 2017 O'Reilly book Database Reliability Engineering, and the argument it makes is narrower and more useful than the title suggests: most of SRE's toolkit — SLOs, error budgets, blameless postmortems, automating away toil — transfers to databases without modification, but a handful of assumptions baked deep into "cloud-native" operational thinking do not, and pretending otherwise is how experienced platform teams still lose data. Three assumptions in particular quietly break:

Instances aren't interchangeable. The "cattle, not pets" framing that justifies killing and replacing any stateless pod without ceremony has a real exception at the database tier: a primary is not a replica, a replica three seconds behind is not the same as one three minutes behind, and the instance holding the only unreplicated copy of the last five seconds of writes is not fungible with any other box in the fleet, no matter how identical their specs look. Capacity planning's usual move of adding more identical nodes doesn't cleanly apply to a primary the way it does to a stateless tier.

A bad deploy is usually reversible; a bad migration or a lost write often isn't. Roll back a stateless service and you're instantly back to the last known-good behavior. Roll back a schema migration that already dropped a column, or a failover that already promoted a replica missing the last few seconds of commits, and there may be nothing to roll back to — the data that would let you undo it is the data that's gone. This is why the migration and failover patterns later on this page are built entirely around never reaching a point of no return until it's provably safe to.

The recovery mechanism is not automatically the disaster-recovery mechanism. Replication keeps a service available through a single-node failure; it does not protect you from a bad write, a dropped table, or a bug that corrupts data and replicates the corruption everywhere in milliseconds. Only a backup — a copy taken at a point in time, held separately, and provably restorable — protects against that class of failure, which is why backup verification gets its own full section below rather than a passing mention.

Those three gaps are exactly the four topics this page covers: topology (because a database is a distributed system in its own right, with its own consensus and failure modes), backups (the one recovery path that has to be actively proven, not assumed), connections (the finite resource almost nobody sizes on purpose), and migrations (the one class of deploy where the blast radius is data, not just code).

Replication topologies and their failure modes

☺ Like you're 10: Copying data to a second machine sounds simple until you ask the follow-up question every topology answers differently: if the two machines briefly disagree, which one is telling the truth?

Replication exists to survive the loss of a single node without losing the service, and every mainstream topology is a different answer to the same trade-off: how much does a write cost in latency and availability, in exchange for how strong a guarantee that a promoted replica never contradicts what clients were already told was durable.

Single-leader (primary/replica), asynchronous. One node accepts writes; one or more replicas stream changes behind it — Postgres streaming replication reading the WAL, MySQL replicas applying the binary log. The primary acknowledges a write and returns control to the client the instant it's durable locally, without waiting for any replica to catch up. This is the default almost everyone runs because it costs nothing in write latency, but it has one sharp edge: the replica is always somewhat behind, and if the primary dies before it catches up, whatever was acknowledged to a client in that gap is gone the moment a replica gets promoted. Check the gap directly rather than guessing at it — on Postgres, SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication; run on the primary, or SELECT now() - pg_last_xact_replay_timestamp(); run on the replica itself; on MySQL 8.0.22+, SHOW REPLICA STATUS\G and read Seconds_Behind_Source (the field was renamed from Seconds_Behind_Master; older servers and some clients still use the old name).

Single-leader, semi-synchronous. A middle ground: the primary waits for at least one replica to acknowledge receipt of a write (not necessarily apply it) before telling the client it's committed. Postgres calls this synchronous_commit = remote_write (or on/remote_apply for progressively stronger guarantees) paired with a synchronous_standby_names list; MySQL's equivalent is the semisync plugin (rpl_semi_sync_source_enabled in current naming). This closes most of the async data-loss window at a real but usually small latency cost — one extra network round trip per commit — and is the setting most production Postgres and MySQL fleets should be running for anything that isn't purely disposable data.

Single-leader, fully synchronous. Every listed synchronous standby must acknowledge before a commit returns. This is the strongest durability single-leader replication can offer, and it comes with the sharpest availability cost: if the synchronous replica is slow, partitioned, or down, writes on the primary block or fail outright, because the whole point is that the primary is no longer allowed to say "durable" on its own authority. Financial ledgers and anything with a genuine zero-data-loss requirement (RPO of zero) reach for this; almost nothing else should, because you've converted a single-node failure into a two-node dependency for every write.

Multi-leader. More than one node accepts writes directly, then the nodes replicate to each other. This buys write availability across regions — every region has a local, writable copy — at the cost of a hard new problem: two clients can write conflicting values to the same row on two different leaders before either replication link catches up, and something has to resolve the conflict when it does (last-write-wins by timestamp, a CRDT merge rule, or hand-written application logic). The failure mode unique to this topology is split-brain: if the link between leaders partitions and each side keeps accepting writes independently, you don't lose availability — you lose a single consistent truth, silently, for as long as the partition lasts, and no automated resolver perfectly undoes an arbitrary conflict after the fact.

Leaderless / quorum. Systems in the Dynamo lineage (Cassandra, Riak, and similar) write to N replicas and consider a write successful once W of them acknowledge, and read from R replicas, merging what comes back. Set W + R > N and every read is guaranteed to overlap with the most recent successful write, which is how these systems get strong-enough consistency without a single leader to bottleneck on. The failure mode here is subtler than split-brain: during a partition or a slow node, the quorum math can still technically hold while individual replicas silently drift, relying on background read repair and hinted handoff to reconcile — mechanisms that themselves become a source of load and lag if a node is down long enough to accumulate a large backlog of hints.

Backup verification: a backup you haven't restored isn't a backup

☺ Like you're 10: A backup job that says "success" only proved that some bytes got written somewhere. It never proved those bytes can turn back into your data — the only test that proves that is actually restoring them.

Replication answers "what happens if one node dies." Backups answer a completely different question: "what happens when the data itself is wrong" — a bad migration, a dropped table, a bug that corrupts rows and replicates the corruption to every replica in milliseconds, or a human who ran a DELETE without a WHERE clause against production. Replication cannot help with any of these, because a faithful replica faithfully replicates the mistake too. A backup — a copy frozen at a point in time and held somewhere the mistake can't reach — is the only mechanism that does.

Three backup strategies cover most production databases, and they trade off differently against two numbers you should size deliberately rather than inherit by accident: RPO (recovery point objective — how much data, measured in time, are you willing to lose) and RTO (recovery time objective — how long are you willing to be down while restoring).

Point-in-time recovery is the strategy most production systems should be running, because it's the only one of the three that lets you restore to "one second before the bad DELETE ran" instead of "whenever last night's dump happened to be taken." But the strategy on paper is not the point of this section — the discipline that makes any of the three trustworthy is.

The clearest public illustration remains GitLab's January 2017 database incident: an on-call engineer, trying to recover from a replication-lag problem during a spam-driven load spike, removed what he believed was an empty directory on a secondary and instead deleted roughly 300GB of live production data from the primary. GitLab's own published postmortem is worth reading directly rather than trusting exact figures repeated secondhand, but the structural lesson is what matters here and is not in dispute: the team discovered, in real time, during the incident, that several independent backup and replication mechanisms they believed they had — regular database dumps, disk snapshots, and a separate sync process — had each been silently failing or misconfigured for some time, in some cases for weeks, without anyone noticing because nothing had ever tried to actually use them. They ultimately recovered from a manually-triggered disk snapshot that happened to have been taken shortly before the incident, more by fortunate timing than by design, and still lost several hours of production data. Every mechanism on that list would have shown "success" on a dashboard that only checked whether the job had run — none of them had ever been through an actual restore.

The fix is to treat "can we restore this" as its own measured, alerted-on signal, not an assumption that rides along with "did the backup job exit zero." Concretely:

• Automate a real restore, on a schedule, not just the backup. Pull the latest backup artifact, restore it to a disposable scratch instance, and run it all the way through — not a syntax check, an actual database that boots and answers queries.
• Verify content, not just success. A restore that completes without error but silently restored last month's schema, or zero rows in a table that should have millions, is a false negative disguised as a green checkmark. Check row counts against expected ranges, checksum a sample of known rows, and run a handful of the application's own read queries against the restored copy.
• Track "time since last verified restore" as its own SLI, distinct from "time since last backup taken." A backup that was written six hours ago but last successfully restored three months ago is not a six-hour-old backup for planning purposes — it's a three-month-old, mostly-unproven promise with some recent bytes attached.
• Run restore game days deliberately, on the same cadence discipline as chaos engineering — schedule a quarter's worth of "restore this backup, from scratch, on the clock, and see if the measured RTO still matches the number written in the runbook" drills rather than waiting to find out during a real incident.

# A minimal nightly restore-verification job — the shape, not a drop-in script.
# Runs against a disposable scratch instance, never against anything live.

LATEST_BACKUP=$(pgbackrest info --output=json | jq -r '.[0].backup[-1].label')

pgbackrest --stanza=main --set="$LATEST_BACKUP" \
  --pg1-path=/scratch/restore-test restore

pg_ctl -D /scratch/restore-test start -w

ROWS=$(psql -tAc "SELECT count(*) FROM orders" -d scratchdb)
if [ "$ROWS" -lt "$MIN_EXPECTED_ORDERS" ]; then
  page_oncall "restore test: orders table has $ROWS rows, expected >= $MIN_EXPECTED_ORDERS"
  exit 1
fi

CHECKSUM=$(psql -tAc "SELECT md5(string_agg(id::text || total, ',' ORDER BY id))
                       FROM (SELECT id, total FROM orders ORDER BY id LIMIT 1000) s" -d scratchdb)
compare_against_known_good_checksum "$CHECKSUM" || page_oncall "restore test: row checksum mismatch"

record_metric "backup_restore_verified_timestamp" "$(date +%s)"
pg_ctl -D /scratch/restore-test stop
rm -rf /scratch/restore-test

See disaster recovery & business continuity for how a database's RPO/RTO numbers roll up into an org-wide DR plan, and multi-region & multi-AZ architecture for why cross-region backup copies and cross-region replicas answer genuinely different failure scenarios, not the same one twice.

Connection-pool exhaustion: the outage hiding in plain sight

☺ Like you're 10: A database can only talk to so many clients at once — run out of open lines and it doesn't matter how fast or healthy the database itself is, because nobody new can even get through to ask it anything.

Every mainstream relational database has a hard ceiling on simultaneous connections — Postgres defaults to max_connections = 100, and even a well-tuned instance rarely runs comfortably past a few hundred to a couple thousand, because each connection is a real, moderately heavyweight resource: Postgres spawns an entire OS backend process per connection, consuming on the order of several megabytes of memory before it's done a single query, plus its own share of shared-buffer and lock-table bookkeeping. This is precisely why connection-pool exhaustion is one of the most common root causes behind real production outages, and one of the least intuitive ones to a team that's only ever reasoned about capacity in terms of CPU and request throughput: the limiting resource isn't compute at all, it's a small integer nobody wrote down anywhere.

The arithmetic that catches teams off guard is simple multiplication nobody did on purpose. Say a service runs 50 pods, each holding a local connection pool of 20 (a default many ORMs and drivers ship with, unexamined) — that's a ceiling of 1,000 possible simultaneous connections from one service alone, against a database that may be configured for 200. Under normal load the pools sit mostly idle and nothing breaks. The exhaustion event is almost always triggered by exactly the kind of stress a database is worst-positioned to absorb calmly:

• A deploy or autoscale event. Rolling out 50 new pods, or scaling out under load, means every fresh pod opens its full pool eagerly on startup, all at once, and the database that comfortably serves a steady-state 150 connections can be handed a burst well past its ceiling in seconds.
• A slow query, anywhere. One query that used to take 20ms now takes 2 seconds — a missing index after a data-growth threshold, a lock wait, a noisy-neighbor query. Every connection running that query is now held 100x longer than usual. Throughput didn't change, but connections-in-use did, because in-use time is exactly what grew.
• Retries amplifying the shortage. Once new requests start timing out waiting for a pool slot, a naive retry policy makes it strictly worse: each failed attempt frees nothing and the retry itself competes for the same starved pool, so the queue grows even as the useful work being completed collapses toward zero — a textbook saturation collapse, not a graceful degradation.

The failure signature is distinctive once you know to look for it, and it's total rather than partial: Postgres returns FATAL: sorry, too many clients already and refuses new connections outright, including the operator's own psql session trying to log in and diagnose the problem — the database is often perfectly healthy internally, CPU and disk both quiet, while every client-facing signal says total outage.

Defending the pool: pooling modes, Little's Law, and break-glass connections

☺ Like you're 10: Instead of giving every single worker their own private phone line to the database, put a receptionist in the middle who quickly hands out a much smaller set of shared lines only for as long as each call actually needs one.

The standard fix is to stop letting application processes hold direct database connections at all, and put a dedicated pooler — PgBouncer for Postgres, ProxySQL for MySQL — between them and the database. The pooler accepts a large number of lightweight client connections and multiplexes them onto a much smaller, fixed set of real database connections, and the multiplexing mode it runs in determines how much sharing you actually get:

Transaction pooling is the mode almost every production PgBouncer deployment runs, because it's the point where you get most of the sharing benefit while a normal application's request-scoped transactions still work correctly.

; pgbouncer.ini — the shape of a production config, not a drop-in file
[databases]
checkout_db = host=primary.internal port=5432 dbname=checkout

[pgbouncer]
listen_port      = 6432
pool_mode        = transaction
max_client_conn  = 4000     ; connections PgBouncer accepts from apps
default_pool_size = 25      ; real DB connections PgBouncer holds per (db,user) pair
reserve_pool_size = 5       ; extra slots PgBouncer can open under sustained pressure
reserve_pool_timeout = 3

Sizing default_pool_size — and, further upstream, max_connections itself — isn't a number to guess at; it falls out of Little's Law, one of the few pieces of pure queueing theory every DBRE ends up using in practice: the average number of items in a system equals the arrival rate multiplied by the average time each item spends in the system. Applied to a connection pool, the number of connections genuinely in use at steady state is approximately the query throughput times the average time each query holds a connection:

connections_in_use ≈ throughput (queries/sec) × avg_time_per_query (sec)

Example: checkout API sustains 800 queries/sec, average query
duration 15ms (0.015s):
    connections_in_use ≈ 800 × 0.015 = 12

A pool sized at exactly 12 has zero headroom — any latency spike
(a slow query, a lock wait, GC pause) instantly queues new requests
behind it. Production pools are sized with real headroom above the
Little's Law steady-state number, not equal to it — commonly 2-4x —
specifically to absorb latency variance without an immediate queue.

The number that comes out of that formula is the number to defend on the database side too, with two guardrails that keep a single runaway client from re-creating the exact problem the pooler exists to prevent: a statement_timeout so one pathological query can't hold a connection indefinitely, and an idle_in_transaction_session_timeout so a client that opened a transaction and then hung — a bug, a paused debugger, a forgotten COMMIT — doesn't sit on a pool slot forever doing nothing. Finally, reserve a small number of connections the pool never touches — Postgres's superuser_reserved_connections, or a dedicated break-glass admin credential routed around the pooler entirely — specifically so that during an exhaustion incident, the one person who needs to get in and diagnose it isn't locked out by the exact failure they're trying to fix. See capacity planning & performance for how this same throughput-times-latency reasoning generalizes past connections to any finite, shareable resource, and queueing theory for SRE for Little's Law's full derivation and its other uses in this discipline.

Expand-contract: schema migrations without downtime

☺ Like you're 10: You can't remodel the room and swap every customer's instructions to match at the exact same instant — so you widen the doorway first, let both the old and new directions work for a while, and only remove the old doorway once nobody's using it anymore.

A rolling deploy — the normal way any reliable service ships code — guarantees a window where old application code and new application code are both running against the database at once, simply because instances update one at a time rather than atomically. A schema migration that isn't compatible with both versions of the app during that window will break one of them, and it's rarely obvious in advance which one. The expand-contract pattern (also called parallel change) resolves this by decoupling the schema change from the code change into three independently-deployable phases, so at every single moment — including the whole rolling-deploy window — the live schema is valid for whichever app version happens to be running against it.

1. Expand. Add the new shape alongside the old one, purely additively: a new nullable column, a new table, a new index — never removing or renaming anything old code still depends on. This migration ships and completes before any new app code goes out, and old code simply ignores the new column it's never heard of.
2. Migrate. Deploy new app code that writes to both the old and new locations (dual-write), then backfill historical rows into the new location in small batches, off-peak, checked against replication lag and load as you go — never as one giant locking UPDATE. Once the backfill is verified complete and dual-writes have run cleanly for a confidence window, flip reads over to the new location behind a feature flag, so the switch is instantly reversible without another migration.
3. Contract. Only once every instance is confirmed running the new code path, dual-writing has been stopped, and the rollback window has fully closed, ship the final migration that drops the old column or table. This is the one irreversible step in the whole pattern, and it's deliberately the very last one.

Worked example: renaming email to email_address on a live users table. Expand: ALTER TABLE users ADD COLUMN email_address text; — this ships alone and is invisible to running app code. Migrate: deploy app code that writes both columns on every insert/update, backfill email_address from email in batched, throttled updates (UPDATE users SET email_address = email WHERE id BETWEEN $1 AND $2 AND email_address IS NULL, walking id ranges rather than scanning the whole table at once), verify the two columns agree for every row, then ship app code that reads from email_address and stops writing to email. Contract: once that's fully rolled out and stable, ALTER TABLE users DROP COLUMN email;

Adding a NOT NULL constraint safely follows the same expand-then-validate shape, because the naive ALTER TABLE users ALTER COLUMN email_address SET NOT NULL; takes an ACCESS EXCLUSIVE lock and does a full-table scan while holding it — on a large table, that's minutes of every query against users blocking behind the migration. The standard safe sequence splits validation from lock duration:

-- Step 1: add the constraint unvalidated — this takes only a brief
-- metadata lock, no table scan, doesn't block concurrent reads/writes.
ALTER TABLE users
  ADD CONSTRAINT email_address_not_null
  CHECK (email_address IS NOT NULL) NOT VALID;

-- Step 2: validate it separately — this does scan the whole table,
-- but takes a much weaker SHARE UPDATE EXCLUSIVE lock that doesn't
-- block concurrent DML while it runs.
ALTER TABLE users VALIDATE CONSTRAINT email_address_not_null;

-- Step 3 (Postgres 12+): SET NOT NULL can now detect the already-
-- validated CHECK constraint and skip its own redundant table scan,
-- so this final step only needs a brief catalog-only lock. Verify
-- this optimization's exact behavior against the docs for your
-- Postgres version before relying on it.
ALTER TABLE users ALTER COLUMN email_address SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT email_address_not_null;

For changes that don't reduce to a lock-free ALTER at all — changing a column's type, or any operation that would otherwise require rewriting the whole table under a long-held lock — reach for a purpose-built online schema-change tool rather than running the raw DDL against a live table: gh-ost (GitHub's tool for MySQL, which reads the binlog to keep a shadow copy in sync rather than using triggers) or pt-online-schema-change (Percona Toolkit's equivalent, trigger-based) both build a new table in the background, keep it continuously in sync with live writes, and cut over with a brief atomic rename, so the table stays fully readable and writable for the entire multi-hour copy. Also worth knowing before reaching for a tool at all: some of the most common migrations are already lock-light natively — Postgres has made ADD COLUMN ... DEFAULT <constant> a metadata-only operation since version 11 (no table rewrite, because it doesn't need one), and MySQL 8.0.12+ supports ALGORITHM=INSTANT for compatible ADD COLUMN operations for the same reason. Check what your specific database version already does for free before building a migration harder than it needs to be.

Where this fits

☺ Like you're 10: Everything on this page protects the one part of the system that can't just be rebuilt from scratch — and every other lesson in this course leans on that part staying trustworthy.

Each of these four skills defends a different failure mode of the same underlying fact: a database is the one place your architecture keeps a promise that can't simply be replayed from a blueprint. Replication topology choices decide what a single-node failure costs you; backup verification decides what a bad write or a human mistake costs you; connection-pool sizing decides what an ordinary traffic spike costs you; and expand-contract decides what shipping code, the most routine action a team takes, costs you. None of these are exotic — they're the specific, learnable places where the general SRE playbook from reliability patterns and incident management & on-call needs a stateful-systems appendix, and this page is that appendix. For the failure-mode reasoning that applies one layer up, across an entire distributed system rather than one datastore, see distributed systems reliability fundamentals; for what happens when a database's data itself, not just its uptime, has to be protected against a full-region loss, see disaster recovery & business continuity.