What changes for Postgres in production?
A database in development is forgiving; in production it holds data you cannot afford to lose and serves traffic you cannot pause. Running it well means backups you have actually tested, safe schema changes, replication for availability, and respecting connection limits.
Why it matters
The database is usually the hardest part of a system to recover when it goes wrong, because it holds state. A bad migration or an untested backup can mean permanent data loss or extended downtime. Operating Postgres responsibly is what separates a hobby project from a production service.
What to learn
- Automated backups and, crucially, testing restores
- Point-in-time recovery
- Read replicas for availability and read scaling
- Running schema migrations safely with zero downtime
- Connection limits and why a pooler like PgBouncer matters
- Monitoring slow queries and locks in production
- Vacuum and bloat
Common pitfall
Taking backups but never testing a restore. A backup you have not restored is a hope, not a guarantee — and people discover the file is corrupt or incomplete exactly when they need it most. Schedule periodic restore drills so recovery is proven, not assumed.
Resources
Primary (free):
- PostgreSQL — Backup and restore · docs
- PostgreSQL — High availability · docs
- PgBouncer — Documentation · docs
Practice
Take a logical backup of a database, drop a table, and restore it from the backup — proving recovery works end to end. Then write a migration that adds a column without locking the table, and describe how you would run it with zero downtime. Done when the restore brings the data back intact.
Outcomes
- Automate backups and verify them with restore drills.
- Run a schema migration without taking downtime.
- Use read replicas and a connection pooler appropriately.
- Monitor slow queries and locks on a live database.