PostgreSQL on RDS: query tuning, backup verification and a real failover test

Two incidents in the last quarter convinced us to stop treating the database as a black box. Our marketplace runs on a single RDS PostgreSQL instance (about 300 GB) and we have slow dashboards, a nightly batch job that locks tables for 20 minutes, and - worst of all - backups nobody has ever actually restored from. The work splits into three parts. First, performance: analyze pg_stat_statements, fix the ten worst queries and the batch job locking (indexes, query rewrites, whatever it takes - our developers will apply application-side changes you specify). Second, resilience: verify backups by performing a full restore drill into an isolated environment, document the procedure, and script it in Python so it can run monthly. Third, run a supervised failover test with us during a planned maintenance window. Everything gets written down - the deliverable is as much the documentation as the fixes. Access via a scoped IAM role; no production credentials leave our systems.