Everything was fine at 1M rows. At 5M, some queries slowed. At 10M, three critical paths exceeded 500ms.
section
What We Learned
- Index your RLS predicates: If your policy checks auth.uid() = user_id, index user_id
- Use PgBouncer: Tune pool_size and server_idle_timeout
- Partition early: Time-series data should be partitioned by month from day one
- Materialized views: For complex aggregations, materialize and refresh on schedule
section
After Optimization
- Average query: 520ms → 12ms
- Connection overhead: 180ms → 3ms
- RLS evaluation: 340ms → 0.8ms