Chapter 38 — Exercises

Operational exercises against mercado (and your project DB). (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Configuration

38.1 Find your current shared_buffers, work_mem, effective_cache_size, and max_connections (SHOW <setting>;). For a machine with 16 GB RAM, what would reasonable shared_buffers and effective_cache_size be? (answer in Appendix)

38.2 Why is work_mem set per operation dangerous to raise too high? (Tie to concurrency.)

38.3 ⭐ Why is blindly copying config settings from a blog post a bad idea?

Group B — Monitoring

38.4 Use pg_stat_activity to see currently-running queries and find any long-running transaction. (answer in Appendix)

38.5 Enable/inspect pg_stat_statements and find the top 5 queries by total time. Why is this the key optimization tool?

38.6 Check n_dead_tup and last-autovacuum in pg_stat_user_tables for a table; what would high dead-tuple counts indicate? (answer in Appendix)

38.7 ⭐ Find unused indexes via pg_stat_user_indexes (idx_scan = 0). (Ties to Chapter 23.)

Group C — Backups (the important one)

38.8 pg_dump the mercado database to a file. Then restore it into a new database (mercado_test) and verify the row counts match. (answer in Appendix)

38.9 Explain logical (pg_dump) vs physical (pg_basebackup) backups and when you'd use each.

38.10 What is PITR, and what does it let you recover from that a nightly dump does not? (answer in Appendix)

38.11 ⭐ State the 3-2-1 rule and why "test your restores" is non-negotiable.

Group D — Maintenance & ops

38.12 Why must you ensure autovacuum keeps up, and what blocks it? (Ch. 28.) (answer in Appendix)

38.13 Why monitor free disk space, and what happens when a PostgreSQL disk fills?

38.14 ⭐ Minor vs major version upgrades — what's the difference and the process for each?

Group E — Progressive project

38.15 Back up your project database and test-restore it.

38.16 Set up basic monitoring (top queries, dead tuples, connections) and note baseline values.

38.17 ⭐ Write an operational runbook: how to back up, restore, and what to check when "the database is slow."

Self-check. If you can back up and successfully restore, read pg_stat_* to diagnose, and articulate the 3-2-1/test-restore discipline, you can keep a database alive. Next: the capstone.