Backups, Connections and Migrations

A backup you have never restored is a hope. Take one, restore it to a fresh database, and run the application against it. Do that once deliberately, now, and you will know rather than assume.

pg_dump for a logical backup you can restore selectively. Point-in-time recovery for anything where losing a day matters. Whichever you choose, the restore is the part to rehearse.

Connections are a limited resource. Postgres handles a bounded number, and each one costs memory. An application that opens one per request will exhaust them under load, which appears as the site simply refusing to work.

Use a connection pool. Most hosts provide one, and most frameworks have a setting for persistent connections.

Migrations on a live table need care. Adding a nullable column is instant. Adding a column with a default, adding an index without CONCURRENTLY, or changing a column's type can lock the table while it rewrites, and on a large table that lock is an outage.

The safe pattern for a risky change is several small deploys: add the new thing, backfill in batches, switch the code over, then remove the old thing. Each step is reversible; a single big migration is not.