Every time a service got slow, my reflex was to add a cache. This week I finally ran the numbers on one of those services and found that the cache was hiding a bad query plan.
A quick EXPLAIN ANALYZE showed a sequential scan on a table that had grown 10x. Adding one composite index cut p99 from 900ms to 80ms β no cache needed.
Not saying Redis is bad (it's great, and we still use it). The lesson is just: measure before caching.
We're on Postgres 16 now, and the planner has been excellent once the statistics are fresh. Curious how others handle index review β manual, or automated in CI?
Top comments (2)
Good reminder that a cache can mask a bad plan. For index review, pg_stat_statements sorted by total time plus auto_explain with a threshold has worked well for catching these regressions before users do. In CI, running EXPLAIN against a realistic data snapshot is more useful than an empty test database, since the planner picks seq scans on tiny tables anyway. A sequential scan that only appears after a table grows 10x is exactly what an empty schema won't show. Do you track plan changes over time, or just react to latency alerts?
Your example makes the case for measuring before adding a cache. Did the query slow down gradually as the table grew, or did the latency jump after one particular change?