Chapter 38 — Key Takeaways

The big idea

Administration is keeping a production database healthy and recoverable — configured well, monitored, backed up, tuned, upgraded. It's Part IV's internals applied operationally, and the job that never ends.

Configuration

  • Tune memory to your hardware/workload (iteratively): shared_buffers ≈ 25% RAM (the buffer pool), effective_cache_size ≈ 50–75% (a planner hint), work_mem per operation (modest — it multiplies with concurrency), maintenance_work_mem larger.
  • Don't blindly copy config; tune and measure.

Monitoring

  • pg_stat_activity — live queries (find the stuck/long transaction).
  • pg_stat_statements — top queries by total time (what to optimize — Ch. 24).
  • pg_stat_user_tables — dead tuples/bloat (Ch. 28); pg_stat_user_indexes — unused indexes (Ch. 23).
  • Plus OS metrics (CPU, RAM, disk space, connections, replication lag). Alert proactively.

Backups — the #1 responsibility

  • pg_dump (logical) · pg_basebackup (physical) · WAL archiving + PITR (restore to any moment — the gold standard).
  • 3-2-1 (3 copies, 2 media, 1 off-site/immutable), automated, and test-restored.
  • A backup you've never restored is a hope, not a backup. Backups fail silently; automated test-restores catch breakage in days, not in a disaster. (Case Study 1.)

Routine maintenance & resources

  • Keep autovacuum keeping up (bloat, long-transaction blockers, fresh stats — Ch. 28).
  • Monitor disk space and alert early — a full disk halts the database (can't write WAL); WAL piles up if archiving stalls. (Case Study 2.) Never manually delete WAL.
  • Pool connections (PgBouncer) rather than over-raising max_connections (Ch. 27).

Upgrades

Minor (16.2→16.3): quick restart, apply promptly (security). Major (16→17): different format → pg_upgrade/dump-restore/logical replication, tested. Stay reasonably current.

Managed vs. self-hosted

Managed services (RDS/Cloud SQL/Aurora) operate backups/HA/patching — the right default for most. Concepts still apply; they don't remove the need to understand them.

You can now…

  • ☐ Tune key config settings to your workload.
  • ☐ Monitor via pg_stat_* and OS metrics.
  • ☐ Back up (logical/physical/PITR) and test-restore (3-2-1).
  • ☐ Keep autovacuum healthy; manage connections and disk space.
  • ☐ Plan upgrades; choose managed vs self-hosted.

Looking ahead

Chapter 39 — Capstone. The whole book together: your designed, queryable, indexed, secured, documented, application-connected database — a portfolio-ready piece.

One sentence to carry forward: Operating a database is monitoring it, tuning it to your workload, and — above all — keeping backups that you have actually restored; an untested backup and an unmonitored disk are the two most preventable disasters.