database-operations

Database Operations

A database is the one component in most systems that cannot simply be redeployed when something goes wrong — it holds state that no other system has a copy of. That changes the math on every operational decision: a connection leak that would be a shrug in a stateless service can take down every service sharing that database, and a "quick" index build can lock a table your whole product depends on.

The job is not keeping the database running — it mostly runs itself. The job is making sure every change to it, and every failure mode around it, has already been thought through before it happens under pressure.

Treat the database as the one thing you cannot roll back to a clean slate — every operation against it should assume that.

1. Treat the connection pool as a shared, finite resource

Every connection costs the database memory and a backend process, and the limit is far lower than the number of application instances that want one. The default failure mode is each service opening its own pool sized for its own convenience, and the sum quietly exceeding what the database can hold.

• Size pools from the database's max-connections budget backward, divided across every service and environment that connects, not from a single service's guess at its own needs.
• Use a connection pooler (PgBouncer, ProxySQL, RDS Proxy) in front of the database when many short-lived clients connect, so the database sees a stable, small number of backends.
• Alert on pool saturation before the database rejects connections, since by the time connections are refused, requests are already failing.

Done when: the sum of every service's max pool size is known and stays under the database's connection limit with headroom.

2. Make schema changes online by default

A migration that locks a table is a maintenance window nobody scheduled. Most modern databases support adding columns, indexes, and constraints without blocking reads and writes, but only if you use the online variant and avoid the naive one.