Operating a Production Database
The standing duties around the one component you cannot restart your way out of: connections, locks, bloat, replication, and change discipline.
The question, the obvious approach, and why it breaks
Every lesson starts where the work starts: an operational problem, a first attempt that is entirely reasonable, and the way production disagrees with it.
What does owning a production database actually require, beyond it being up?
The database is stateful, singular, and the slowest thing to replace. Every other component can be recreated from an artifact; this one holds the only copy of something.
It is a managed service. The provider handles patching, backups and failover, so operating it means watching the CPU graph.
The managed service manages the infrastructure. Query patterns, index health, lock contention, connection budget, schema change safety and data growth are all still yours (Shared Responsibility in Cloud & Infrastructure).
- The managed service manages the infrastructure. Query patterns, index health, lock contention, connection budget, schema change safety and data growth are all still yours (Shared Responsibility in Cloud & Infrastructure).
- CPU is a late signal. Databases usually degrade through locks, connection exhaustion or a plan change long before CPU is interesting (Saturation: The Reading Utilization Cannot Give You in Observability).
- A managed failover still drops connections and still requires the application to reconnect cleanly, which is application behaviour nobody tested.
- Storage that fills is one of the few database failures with no graceful degradation: writes stop, and the fix takes time you do not have.
What is actually happening
Underneath the tooling, which is the part that survives a change of tool.
- A database is a shared, finite resource with several independent limits — connections, memory, I/O, locks, disk — and it degrades by hitting one of them, not by getting generally slower.
- The limits interact. A slow query holds a connection; held connections exhaust the pool; an exhausted pool queues requests in the application; queued requests time out and retry, adding load to the thing that was already slow (Retry Storms: The Load You Generated Yourself in Backend Engineering).
- Locks are the mechanism behind most surprise outages caused by a deploy: a schema change or a long transaction blocks ordinary traffic while using almost no CPU (Locks and Deadlocks in Database Engineering).
- Replication lag is a correctness signal, not a performance one: an application reading from a lagging replica reads the past, and users see their own writes vanish (Replication and Read Scaling in Database Engineering).
- Growth is the slow failure: disk, index size and vacuum or compaction work all scale with data, so a configuration that was fine last year fails on an ordinary Tuesday.
The signals that lead, and the ones that lag
Operators watch CPU because it is on the default dashboard. It is a lagging signal for the failures that actually happen. This table is the alternative dashboard.
| Signal | What it tells you | What it looks like when it goes wrong |
|---|---|---|
| Active connections vs limit | How close you are to refusing all new work | Total outage while CPU is low (The Connection Budget) |
| Longest running transaction | Whether something is holding locks or blocking cleanup | Migrations hang; dead rows accumulate; disk grows |
| Lock waits | Contention that costs no CPU and all of your latency | Everything slow, database apparently idle |
| Replication lag | How stale replica reads are, and your real recovery point | Users cannot see their own writes (RTO and RPO) |
| Disk headroom and trend | Time until writes stop | No graceful degradation — writes simply fail |
| Cache/buffer hit ratio | Whether the working set still fits in memory | Latency step-change as reads start hitting disk (The Buffer Pool in Database Engineering) |
| Slow query profile | Which statements consume the capacity | One plan flip saturates I/O for everyone |
How a healthy database becomes an outage
Each row starts somewhere other than the database and ends there, which is why the database is blamed and the cause lives elsewhere.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Traffic spike, application scales out | Every request fails; database CPU is low | Instances multiplied by pool size exceeded the connection limit | Enforce a system-wide budget; add a proxy or pooler; cap application scale to the budget |
| Deploy runs a migration | All queries on one table stall | The DDL statement took a lock that queues behind a long-running transaction, and then everything queues behind it | Set a short lock timeout for DDL and retry; kill long transactions before migrating (Zero-Downtime Migrations) |
| A batch job starts | Replication lag climbs; replica reads go stale | A single large write transaction replicating as one unit | Batch with pauses; watch lag between batches; pause the job when lag exceeds threshold |
| Statistics change after a bulk load | One endpoint goes from fast to timing out | Plan flip to a sequential scan | Refresh statistics after bulk loads; alert on slow query profile changes, not just averages (Query Optimization: Finding the Actual Bottleneck in Database Engineering) |
| A replica is stopped or a slot is abandoned | Primary disk fills steadily with no traffic change | Retained write-ahead log the primary is not allowed to discard | Alert on retained log size and slot state; drop abandoned slots deliberately |
| Index created during peak | Latency doubles across unrelated queries | Index build competing for the same I/O as production traffic | Build concurrently where the engine supports it, and prefer a low-traffic window |
The operator's standing duties
These are the tasks that keep a database boring. They are cheap when scheduled and expensive when they become incidents, which is the definition of the work this module covers.
- 1Budget review
Recheck the connection arithmetic against current instance counts and pool sizes.
fails by Autoscaling or a new consumer quietly breaks the budget.
evidence Peak connection count stays under the reserved ceiling (The Connection Budget).
- 2Slow query review
Look at the top consumers of database time and fix the worst one.
fails by Reviewed only during incidents, when the choice is between bad options.
evidence A regular record of what was found and what changed.
- 3Growth projection
Project disk, table and index growth forward to a date.
fails by Discovered at 95% full, when reclaiming space takes longer than you have.
evidence A dated projection and an alert with lead time, not a threshold at 90%.
- 4Migration review
Check pending schema changes against known-blocking operations for this engine and version.
fails by A statement that is instant in development locks a large production table.
evidence An automated check in CI plus a human sign-off for anything touching a large table.
- 5Failover rehearsal
Actually fail over and observe application reconnect behaviour.
fails by The application does not reconnect cleanly, and you learn this during a real failover.
evidence A dated rehearsal with measured impact on the application.
- 6Restore drill
Restore to a separate target and verify the application against it.
fails by Backups are assumed to work (Restore Drills).
evidence A dated drill record with phase timings.
Five of the six are cheap to do quarterly. All six are expensive to do for the first time during an incident.
How to do it properly
Most important first.
- Watch the signals that lead, not the ones that lag: active connections against the limit, longest-running query and transaction, lock waits, replication lag, disk headroom, and cache hit ratio.
- Set a connection budget across all consumers and enforce it, rather than configuring each application independently (The Connection Budget).
- Cap statement and transaction duration at the database, so a runaway query cannot hold a lock indefinitely (Timeouts in Backend Engineering).
- Treat every schema change as a deploy with a blast radius, and know which operations take a blocking lock on your engine and version (Why Migrations Are the Dangerous Change).
- Run bulk work in bounded batches with pauses, watching lag and error rate between batches — never as one large transaction.
- Route reads to replicas deliberately, per query, based on whether that read can tolerate staleness — not globally.
- Rehearse the failover and the restore, because those are the two operations you will need under pressure (Restore Drills).
How much can this affect
Every production change has a blast radius. Stated as a scale so it is comparable between changes rather than adjectival — and paired with what actually contains it, because a wide scope with a real containment mechanism is a different situation from a wide scope with none.
Statement and transaction timeouts, a connection budget, and batched bulk work contain most of it. Nothing contains a destructive schema change except not shipping it.
What can go wrong
- Connection exhaustion from an autoscaling application, presenting as a total outage while the database is nearly idle.
- A long transaction left open by a stuck worker blocks a schema change, which then blocks everything behind it.
- A query plan flips after statistics change and a previously fast query starts scanning, saturating I/O.
- A replica falls behind during a bulk operation and users on replica reads see stale data.
- Disk fills because of retained write-ahead log segments a stalled replication slot will not release.
- "Managed means operated." It means the infrastructure is operated. The workload is not.
- "CPU is low, so the database is fine." Lock waits and connection exhaustion are nearly free in CPU terms and are total in impact.
- "Add a read replica to fix write load." Replicas offload reads. Write load, lock contention and connection pressure from writes are unchanged.
- "The query is slow, so we need a bigger instance." Frequently it needs an index or a bounded batch; the instance size hides the cause until it cannot.
Operating it
Evidence is the signal, not the intention. Rollback is sometimes 'you cannot, and that is the point'.
- A dashboard an operator can act on: connections versus limit, longest transaction, lock waits, replication lag, disk headroom, error rate (Dashboards an Operator Can Act On).
- Alerts fire on symptoms — connection saturation, lag past a threshold tied to the recovery point, disk trending to full — not on raw CPU.
- Slow query reporting is on, and someone reviews it on a schedule rather than during incidents (The Slow Query Workflow in Observability).
- The last failover and the last restore both have dates.
- Most database operations do not roll back cleanly, which is the defining operational fact. A dropped column, a rewritten table and a deleted row are gone.
- Reversibility has to be designed in before the change: expand before contract, additive migrations, soft deletes, and a preserved copy before bulk edits (Expand, Migrate, Contract).
- For anything irreversible, the rollback is a restore, and the restore is only as good as the last drill (Partial and Logical Data Recovery).
- Automate: backups and verification, failover mechanics, patching within an agreed window, connection and lag alerting, slow query capture, and disk headroom projection.
- Automate migration safety checks in CI — refuse the merge for a statement pattern known to take a blocking lock on your engine (Policy as Code).
- Keep human: running a migration during peak, killing a long-running query, promoting a replica, and any bulk data change. Each is a judgement about who gets hurt.
- A managed service removes real operational work and takes away levers — parameters you cannot set, extensions you cannot install, upgrade timing you only partly control (Managed vs Self-Hosted in Cloud & Infrastructure).
- Read replicas add read capacity and add staleness, which becomes an application-level correctness problem rather than an infrastructure one.
- Aggressive statement timeouts protect the database and turn some slow-but-legitimate work into errors.
- Larger instances postpone every one of these problems and postpone the learning too, at a cost that compounds monthly.
Where this applies
This domain is unusually tool- and organisation-dependent. These labels say what each claim is specific to, and what a different platform, provider or organisation does instead.
- DATABASE-SPECIFICWhich operations take a blocking lock, how bloat is reclaimed, and what "connection" costs differ sharply. PostgreSQL uses a process per connection and needs vacuum to reclaim dead tuples; MySQL/InnoDB uses threads and purges differently; adding a column with a default is cheap on recent versions of both and was rewriting the whole table on older ones. Check your engine and your version, not the general claim.
- CLOUD-SPECIFICManaged services set some parameters for you, tie the connection limit to instance size, and control the maintenance window. Failover behaviour and how long it takes are provider-specific, and some providers hide the write-ahead log entirely, which changes what recovery options you have.
- SCALE-SPECIFICAt small data volumes almost every mistake here is survivable: a full table rewrite finishes before anyone notices. The same statement on a large table is an outage, and the transition happens without any warning.
Where the depth lives
This domain teaches delivery and operation, and hands the mechanism off to the domain that owns it.
- — Testing & Reliability Engineering — how to exercise failover and reconnect behaviour as a test rather than as a hope.