PostgreSQL indexing and query tuning in production
Practical steps we took to find and fix slow queries using EXPLAIN, indexes, and a bit of schema refinement.
When our Laravel app started hitting timeouts on a few report and listing pages, we used PostgreSQL’s EXPLAIN (ANALYZE, BUFFERS) to see where time was spent. Most of the pain came from full table scans and missing indexes on filters and joins.
We added indexes based on actual query patterns and avoided over-indexing writes. For a couple of heavy reports, we introduced materialized views refreshed on a schedule so the live path stayed fast.
Monitoring slow query logs and keeping an eye on index usage helped us catch regressions early. The same discipline applied when we later introduced read replicas: knowing which queries are read-heavy made routing decisions straightforward.