Database Optimization Template for PMs

Enable the `pg_stat_statements` extension and query it: `SELECT query, mean_exec_time, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20`. This shows the queries that consume the most total database time (frequency x duration). A query that runs in 5ms but executes 1M times per day may cost more total time than a query that runs in 2 seconds but executes 100 times per day.

Optimize queries first. A read replica adds complexity (replication lag, connection routing, consistency concerns) and cost. If the slow queries are due to missing indexes or inefficient SQL, fixing them is cheaper and more effective. Add a read replica when you have already optimized queries and the primary cannot handle the read volume, or when you need to isolate analytics queries from transactional workload.

In Postgres: `SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE schemaname = 'public' ORDER BY idx_scan ASC`. Indexes with zero scans over a meaningful period are unused and can be dropped to save disk and write overhead. Check over at least 30 days of production traffic before dropping.

Start with `(CPU cores * 2) + number of disks` per application instance. For a 4-core database server and SSD storage, that is roughly 9 connections per app instance. Multiply by the number of app instances and ensure the total is below the database's `max_connections` with 20% headroom. If you need more client connections than the database can handle, add a connection pooler like PgBouncer.

PMs should track the user-facing impact, not the database metrics directly. If the engineering team reports that "the events query went from 840ms to 8ms," the PM should ask how that translates to dashboard load time for users. Include database optimization work in sprint capacity planning and celebrate it as user experience investment, not just infrastructure maintenance.