A reporting-heavy product was timing out at month-end: dashboards would spin, the connection pool would exhaust, and everyone blamed "the database".
How we approach this kind of workload:
- Measure first. EXPLAIN ANALYZE on the offenders, plus pg_stat_statements to find what actually hurts — which is regularly not what the team suspects.
- Fix the plans. Missing indexes here, predicates that defeat partition pruning there.
- Partition the big table. Monthly partitions with a retention policy turned a monolithic table into something the planner can reason about.
- Pool the connections. PgBouncer in transaction mode absorbed the connection storm from dashboard refreshes.
Month-end reports went from minutes to seconds — with no application rewrite. If end-of-period is something your team dreads, tell us about it.