Cruxly › System design › Databases: Choosing One

Operating a database: backups, migrations, multi-region, cost

the part of the choice candidates forget

Picking a database is half the answer. The other half is running it: restoring it after a mistake, changing its schema while it serves traffic, surviving a failover, spanning regions, and paying for it. An interviewer who hears only "Postgres, because relational" will ask what happens at 2 a.m.

Backups, RPO, RTO and point-in-time recovery

Two numbers define every recovery plan. RPO (recovery point objective) is how much data you can afford to lose, measured in time. RTO (recovery time objective) is how long you can be down. RPO is set by how often changes leave the primary. RTO is set by how long a restore takes, and that grows with data size.

A replica is not a backup. A replica protects against losing a machine. A DELETE or DROP TABLE is a committed change, so it streams to every replica within moments. Backups protect against changes you wish had not happened. (A deliberately delayed replica, via recovery_min_apply_delay, can shorten recovery, but it supplements PITR rather than replacing it.)

What point-in-time recovery (PITR) needs, in PostgreSQL:

• A base backup (a full copy, for example from pg_basebackup).
• Continuous WAL archiving: every write-ahead log segment copied somewhere safe (archive_mode, archive_command, wal_level of replica or higher).
• To recover: restore the base backup, set restore_command and recovery_target_time, and start with a recovery.signal file. The target must be later than the end of the base backup.
• Only completed 16 MB WAL segments are archived. archive_timeout forces a switch, so with archiving alone your RPO is roughly that setting (the PostgreSQL docs call about a minute reasonable), not zero. pg_dump cannot do PITR, and recovery restores the whole cluster, not one table.

On RDS the mechanics are managed: transaction logs are uploaded to S3 about every 5 minutes, and a point-in-time restore always creates a new instance. The restored volume loads lazily from S3, so it can be slow at first. The automated-backup retention period is 0 to 35 days (1 to 35 for a Multi-AZ DB cluster). If you create an instance through the API or CLI the default is 1 day; through the console it is 7. Automated backups are deleted with the instance unless you choose to retain them. Manual snapshots are independent of that window and are not deleted with the instance.

:::key An untested backup is a hope Run a restore drill on a schedule: restore into a scratch instance, run a checksum or row-count query, and time it. If the restore takes 6 hours (an assumption for your data size) and the RTO is 2 hours, you have found the problem before the incident did. Write the measured time next to the RTO. :::

Worked example (times are assumptions). At 14:03 someone deletes a customer's orders. Restore the nightly base backup into a new instance, replay archived WAL to 14:02:59, check the rows, then copy them back into production (or cut over if the damage is wide). The replica did not help: it replayed the delete.

Schema changes: lock modes and the queue

A schema change is dangerous because of the lock it asks for, not because of what it changes. In PostgreSQL (current docs):

The queue hazard. The PostgreSQL manual says a transaction seeking a lock waits for conflicting locks; it does not spell out the queueing effect, which is how the lock manager behaves in practice and is described in vendor engineering posts. A waiting ACCESS EXCLUSIVE request conflicts with every lock mode, so reads that arrive after it queue behind it, even though they would have coexisted with the long transaction alone. Reads pile up, the connection pool fills, and the site looks down, though the ALTER has changed nothing yet. Even a metadata-only change can do this.

:::key Set lock_timeout on every migration session The default lock_timeout is 0, which waits forever. Set it to a couple of seconds (a common convention, not a default) on the migration session, and retry with backoff when it fails. OpenAI describes a strict 5-second timeout on schema changes for its main Postgres cluster. Set it per session or role, not in postgresql.conf (the manual advises against a global setting), and keep it below any statement_timeout. A failed attempt is cheap. A stalled table is an outage. :::

Rewrite or metadata-only? Know which one you are running:

• ADD COLUMN with a constant (non-volatile) default is metadata-only since PostgreSQL 11. A volatile default, a stored generated column, an identity column or a constrained domain type rewrites the table.
• ALTER COLUMN TYPE usually rewrites the table and its indexes (so plan for room for the new copy), unless the types are binary coercible.
• ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables. The low-lock path is ADD ... NOT VALID then VALIDATE CONSTRAINT (SHARE UPDATE EXCLUSIVE, writes continue).
• SET NOT NULL scans the table unless a valid CHECK constraint already proves no NULL exists (the shortcut arrived in PostgreSQL 12). So add CHECK (col IS NOT NULL) as NOT VALID, validate it, then set the column.
• Build indexes with CREATE INDEX CONCURRENTLY. It is slower and can fail, leaving an invalid index to drop, but it does not block writes.

Follow-up an interviewer asks: "Add a NOT NULL column with a default to a billion-row table?" Say: constant default is metadata-only on modern Postgres but still needs a brief exclusive lock, so I set a lock timeout and retry; anything that rewrites goes through expand and contract.

Expand and contract, backfills, and online-change tools

When a change cannot be done in place (a rename, a type change, splitting a column, moving data to a new table), do it as expand and contract. Every step is its own deploy and can be undone:

1. Expand. Add the new column or table, nullable and empty.
2. Dual write. New code writes old and new.
3. Backfill. Copy existing rows in small batches.
4. Switch reads to the new column, ideally after comparing old and new results on a slice of traffic.
5. Switch writes so the new column is the source of truth.
6. Contract. Drop the old column.

Stripe's account of moving Subscription objects has the same shape: dual write plus backfill, switch reads with comparison experiments, switch writes, remove the old path. They kept each change small, a few hundred lines at a time.

Backfills are where migrations die. Avoid one giant UPDATE: it holds locks, bloats the table, and floods replication. Loop in batches (assumed batch size shown):

-- repeat until 0 rows updated; sleep between batches; watch replica lag
UPDATE users SET full_name = first || ' ' || last
WHERE id IN (SELECT id FROM users WHERE full_name IS NULL ORDER BY id LIMIT 1000);

Throttle on replication lag and lock waits. Stripe's arithmetic: 100 million objects at 1 second each is over 3 years if run one at a time, so the job must run in parallel, and throttled.

Tools, and what they do (all read from their own docs):

:::warn Dual write has two meanings A temporary, backfilled and verified dual write between two copies of the same data (this section) is safe. A permanent application-level dual write to a database and a search index or message broker is not: it is not atomic. For that, use the transactional outbox with change data capture, so one transaction is the truth and the other stores are derived from it. :::

Connections and pooling

PostgreSQL starts one process per connection, and max_connections is typically 100 by default. Each connection is a process, so hundreds of busy ones are expensive. Uber's 2016 account of leaving Postgres 9.2 reported significant problems past a few hundred active connections (a version-dated complaint). OpenAI named connection limits as a constraint on its Postgres fleet and reported that PgBouncer cut connection setup time from about 50 ms to 5 ms.

The arithmetic interviewers like: pods x pool size. 40 pods x 20 connections each = 800, against a limit near 100 (assumed numbers). Autoscaling makes it worse: scale-out adds connections exactly when load is highest. Serverless fan-out is the extreme case.

Pooler options:

• PgBouncer: a lightweight proxy. Pool modes: session (the default, server connection kept until the client disconnects), transaction (released when the transaction ends), statement (released after each query; multi-statement transactions not allowed). Defaults worth knowing: default_pool_size 20, max_client_conn 100.
• RDS Proxy: a managed pooler for RDS and Aurora that multiplexes connections and queues or sheds load above the database's limits.

The catch with transaction pooling: the next transaction may run on a different server connection, so session state does not carry over: a SET command and other session-level features break; SQL-level PREPARE is passed straight to Postgres and not tracked, and PgBouncer tracks protocol-level prepared statements only when max_prepared_statements is non-zero (the default is 200 in current docs; check your version). RDS Proxy has a related behavior called pinning, where some statements, such as SET commands or any statement over 16 KB, lock a session to one connection and defeat the multiplexing.

Failover: what clients actually see

Failover is not invisible. In order:

1. Detection. The platform notices the primary is unhealthy.
2. Promotion. A standby becomes the writer.
3. Redirect. On RDS, the database's DNS name is changed to point at the standby, so clients must reconnect. A runtime that caches DNS for a long time keeps trying the dead address. For Java, AWS recommends a DNS cache TTL of 60 seconds or less.
4. Recovery of work. In-flight transactions are gone, and a commit that was on the wire has an unknown outcome: the client cannot tell whether it landed just before the crash.

So the application needs retries that are safe to run twice: an idempotency key, or a unique constraint that turns the second attempt into a no-op. "Zero-downtime failover" is a claim you should not make.

How long? For an RDS Multi-AZ DB instance, AWS documents typical failover times of 60 to 120 seconds, longer with large transactions or lengthy recovery (RDS docs, checked October 2026). For a Multi-AZ DB cluster AWS documents typically under 35 seconds, but failover completes only after the readers apply outstanding transactions, so lag matters. Other platforms differ; check yours.

What is lost? It depends on the replication mode:

Split brain and fencing, briefly. If the old primary is only partitioned, not dead, and comes back accepting writes, two nodes believe they are the leader and the data diverges. The defence is fencing: make sure the old leader cannot write before the new one starts, using a lease, a token that storage checks, or by shutting the old node down. Managed services do this for you, which is a good reason to use them. If you run your own failover tooling, say how it fences.

:::key Multi-AZ is not disaster recovery Multi-AZ survives the loss of a zone. It does not survive a bad change (it replicates), and it does not survive losing the region. Say which failures your design covers. :::

Multi-region designs and what each one costs

Multi-region is three different designs with three different bills:

Single writer. Simplest, and often right. Say plainly that async replication means a region loss can lose acknowledged writes, and that read-your-writes needs routing a user's reads to the primary for a short while after they write.

Multi-leader. Pick it only when each region must keep accepting writes during a partition and you can define merge rules (last-write-wins loses data silently; the Dynamo paper's shopping cart merges versions on read and accepts that deleted items can reappear).

Distributed SQL. Google documents Spanner regional configurations at 99.99% availability (three read-write replicas in three zones) and dual-region or multi-region configurations at 99.999% on the Enterprise Plus edition, with a leader region that handles writes and witness replicas that help form quorums. If the leader region is lost, leadership moves to another region. Cross-region writes cost a quorum round trip; in CockroachDB, follower reads trade freshness for local latency, and global tables trade slower writes for fast reads.

Illustrative numbers (assumptions): if two regions are 80 ms apart, a quorum commit across them adds roughly that much to every write, while a local async commit adds none. Whether that is acceptable depends on the data: a payment ledger may pay 80 ms for zero loss; a "like" counter should not.

:::warn Multi-region is not free availability More regions mean more cost (replicas, transfer), more operational surface, and slower writes. If the requirement is "survive a zone", Multi-AZ is enough. Ask for the RPO and RTO per region failure before choosing. :::

Cost drivers and capacity signals

The cheapest store on paper is rarely the cheapest to run. Cost the whole bill. For RDS (AWS pricing page, October 2026; check the current page for rates):

Provisioned or on-demand? Provisioned capacity wins at steady, high utilization. On-demand wins when load is spiky or low. The wrong choice either way means paying for idle capacity or paying a premium per request.

Assumed example. An app reads 20 MB/s across zones: about 52,000 GB a month, so at $0.01 per GB charged in one direction the transfer line is roughly $500 a month (about $1,000 if the same volume were also charged in the other direction), which can rival the instance cost of a small database. Pinning readers to the same zone as the replica is a design decision, not a footnote.

Pay-per-request stores surprise differently. In DynamoDB a read unit covers up to 4 KB, so a 20 KB item costs 5 units per strongly consistent read; a transaction costs double; a Scan reads, and bills, the whole table in 1 MB pages. A key-value store that looks cheap per item can be expensive for the access pattern you actually have.

Capacity signals worth alerting on, before the ceiling:

• CPU and IOPS against the instance limit. Notion re-sharded after shards passed 90% CPU and neared their IOPS limits.
• Connections against max_connections (or the pooler's cap).
• Replica lag: lag at failover is data at risk.
• Disk growth and autovacuum keeping up. Notion's first sharding trigger was a stalled VACUUM and a looming transaction-ID wraparound; Figma noted multi-TB tables hurting vacuum.
• Restore time against RTO, measured, not guessed.

These are also your "when do we shard or scale up" answers: a numbered signal, not a feeling.

What to say, and the wrong statements to avoid

Add an operations paragraph to your database choice. Five beats, about 30 seconds:

1. Targets. "RPO of X minutes, RTO of Y, because Z" (the business reason).
2. Backup. "Base backup plus archived log for point-in-time recovery, retention of N days, and a timed restore drill."
3. Change safety. "Expand and contract, a lock timeout on migrations, backfills in throttled batches."
4. Connections and failover. "A pooler in transaction mode; failover takes tens of seconds to a couple of minutes depending on the setup, so retries are idempotent; for multi-region, one writer plus replicas unless we need zero loss across regions, and then I'd name the latency."
5. Cost and signals. "The bill is compute plus IOPS plus transfer plus backups; I'd alert on CPU, connections, lag, disk growth and restore time."

Follow-ups to prepare: how long does your restore take, and how do you know? What if the migration is half done and must be reverted? What happens to in-flight transactions on failover? What is the RPO if the region dies?

How to say it in the interview

"I'd run this on managed Postgres with a primary and replicas. For recovery I want point-in-time restore, which needs a base backup plus archived write-ahead log, and I'd time a restore drill against our R T O. Replicas are not backups, because a bad delete replicates. Schema changes go through expand and contract with a lock timeout and batched backfills. A pooler sits in front so pods times pool size cannot exhaust connections. Failover takes from tens of seconds to a couple of minutes depending on the setup, and clients see errors, so retries carry idempotency keys. For multi-region I'd start with one writer and remote replicas, and pay for distributed SQL only if we need zero data loss across regions. And I'd watch CPU, I O P S, connections, lag and disk growth before they hit the ceiling."

Checklist

• State RPO and RTO as numbers with a reason before naming a backup mechanism.
• Explain PITR as base backup + archived WAL replayed to a target time, restored into a new instance, and say replicas are not backups.
• Describe a timed restore drill and compare the measured time with the RTO.
• For any schema change, say whether it rewrites or is metadata-only, set a lock timeout, and use expand and contract with batched, throttled backfills when it rewrites.
• Do the connection arithmetic (pods x pool size vs max connections) and put a transaction-mode pooler in front, noting the session-state catch.
• Describe failover as a short outage with dropped connections and unknown commit outcomes, and require idempotent retries.
• Choose a multi-region design and name its cost in latency and RPO: single writer + replicas, multi-leader, or distributed SQL.
• Cost the whole bill (compute, storage, IOPS, transfer, backups) and name the capacity signals you would alert on.

Related lessons

• How to choose a database
• ACID, precisely
• Isolation levels, in depth
• Data models: what each family is built for, and where it fails
• Storage engines: B-trees, LSM-trees, row vs column
• NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB