Database Operations

Combine physical and logical backups. Take periodic physical base backups and archive the write-ahead log (WAL) continuously; replaying a base backup plus WAL to a chosen instant — point-in-time recovery (PITR) — restores to just before the bad migration, a low RPO. Keep logical dumps for portable per-table restores. Store backups and WAL off-host so one failure destroys neither, and restore-test them, since an untested backup is not a backup. A replica is not a backup; it copies corruption too.

Opening a database connection is expensive — a TCP handshake, authentication, and a backend process per connection — and each connection costs memory, so a server caps them with max_connections. A connection pool keeps a set of established connections and lends them to many short-lived clients instead of reopening one each time. A pooler like PgBouncer fronts the DB so hundreds of app instances share a bounded number of real connections, avoiding max_connections exhaustion.

Replication keeps copies (followers/replicas) of a database in sync with a primary (leader) by streaming the leader's changes to them. The leader takes writes; each follower applies the same change stream and can serve reads. Teams replicate for read scaling (offload reads onto replicas), high availability (promote a follower if the leader fails), and a warm copy for disaster recovery. A replica is not a backup — it faithfully copies mistakes too.

For high availability a database runs a primary with replicas plus a cluster manager (like Patroni) that health-checks it and, on failure, promotes a replica, repointing clients — automatic failover. The danger is split-brain: if the old primary is only partitioned, not dead, two nodes both accept writes and diverge. To prevent it the cluster needs quorum — a majority must agree who is primary — and fences the old node. Failover is only as safe as the replica is fresh.

With asynchronous replication the leader commits and returns at once, replicating afterward — fast, but a failover can lose transactions not yet shipped to the promoted replica, so the recovery-point objective (RPO) is above zero. Synchronous replication waits for a replica to confirm each commit, so a failover loses nothing (RPO near zero), but every write pays a round-trip and stalls if the replica is slow. Most run async, reserving sync for data they cannot lose.

Run a primary with a synchronous standby (RPO near zero) plus async replicas for reads. A cluster manager health-checks the primary and, on failure, promotes the freshest standby. Require quorum — a majority must agree who is primary — and fence the old node so a partition cannot leave two primaries writing (split-brain). Repoint clients via a proxy, virtual IP, or pooler so they reach the new primary automatically. The sync standby bounds data loss; automation bounds downtime.

Most databases don't reclaim space immediately on UPDATE/DELETE. PostgreSQL's MVCC writes a new row version and marks the old one dead; until cleanup runs, those dead tuples occupy space — this is bloat. Indexes and write-ahead logs add to it. Space is reclaimed by background cleanup (autovacuum/VACUUM), so steady row counts can still mean growing files when cleanup falls behind.

First confirm with df/du what is eating space — data, bloat from dead tuples, WAL, or logs. Immediate relief: free safe space (rotate logs, archive old WAL) to keep the DB writable. Durable fix for the bloat shown: run VACUUM (ANALYZE) — it reclaims dead-tuple space and refreshes planner stats; then investigate the lagging autovacuum and right-size the disk. Never delete files in the data directory by hand.

Never ship one blocking ALTER on a hot table — a NOT NULL column with a default or a type change can take a table-level lock that blocks every read and write. Use expand-contract: add the column nullable (a fast metadata change), backfill in batches to avoid a long lock or replica lag, dual-write from the app, switch reads over, and drop the old one only after nothing reads it. A short lock_timeout with retries stops a blocked DDL queuing all traffic; each step is reversible.