On an SME advisory platform I worked on, used by more than 1,000 business owners, the database CPU spiked every time traffic peaked. Real-time advisory sessions lagged, and the first instinct on the team was "we need a bigger database."
We didn't need one. Rebuilding the indexes around how the application actually queried data cut query time by 80% and removed the CPU spikes. This is the process I used, and it works on most slow-Postgres problems.
1. Find the queries that matter
Don't guess. Under load, find out which queries take up the database's time. The pg_stat_statements extension is the easiest way:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements ;
SELECT query , calls , round ( total_exec_time :: numeric , 1 ) AS total_ms , round ( mean_exec_time :: numeric , 2 ) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10 ;
Enter fullscreen mode
Exit fullscreen mode
Sort by total time, not mean time. A 5 ms query called 200,000 times hurts more than one 2-second report.
2.…