PostgreSQL Performance: Fix the Query Before Scaling the Database
A repeatable workflow for diagnosing slow PostgreSQL requests before buying more compute.
When an application feels slow, increasing database compute is tempting. Sometimes it helps, but it often hides the real problem: a query reading far more rows and columns than the request needs. A faster machine makes inefficient work finish sooner; it does not make the work efficient.
Begin with the exact slow query and run EXPLAIN ANALYZE in a safe environment. Look at estimated rows versus actual rows, sequential scans on large tables, repeated loops, and expensive sorts. The goal is not to eliminate every sequential scan. Small tables are often faster to scan. Focus on operations that consume most of the measured time.
Indexes should follow real access patterns. If requests filter by published status and order by creation date, a composite or partial index may be more useful than separate indexes on each column. Column order matters. An index that cannot support the query's leading conditions may exist without helping.
Reduce transferred data too. Avoid SELECT * on wide records when the page needs only a title, slug, and thumbnail. Limit relationship depth, paginate lists, and remove accidental N+1 queries. Less data improves database time, network cost, serialization, and memory usage at once.
Connection behavior matters in serverless deployments. Use a pooled connection string for application traffic, keep transactions short, and avoid opening a new unbounded pool for every invocation. Monitor active connections and waiting queries before assuming the database has run out of CPU.
Performance work should end with a measurement. Compare latency, rows read, data transferred, and load before and after the change. The most valuable optimization is usually not clever SQL—it is removing work the user never asked the database to do.
