PostgreSQL Performance: A Practical Checklist for Growing Applications

When an application starts getting slow, the database is usually the first suspect — and it is often the actual culprit. But "the database is slow" is not a diagnosis. You need to know which queries are slow, why they are slow, and what to do about it.

This is the checklist we run through whenever a client's PostgreSQL database starts showing performance problems. It is ordered by impact — start at the top and work your way down. Most of the time, the problem is somewhere in the first three items.

1. Find the slow queries first

Before you tune anything, you need to know which queries are actually causing problems. Enable the pg_stat_statements extension if it is not already enabled. This gives you a ranked list of queries by total execution time, which tells you exactly where the database is spending its effort.

Look at total time, not just average time. A query that takes 50ms but runs 10,000 times per hour is a bigger problem than one that takes 2 seconds but runs once a day.

2. Check for missing indexes

This is the single most common cause of slow queries in applications that have grown past their initial data size. The query worked fine with 10,000 rows. Now you have 2 million rows and the same query is doing a sequential scan across the entire table.

Run EXPLAIN ANALYZE on your slow queries and look for sequential scans on large tables. If a query is filtering on a column that does not have an index, adding one is usually a dramatic improvement — sometimes reducing a 3-second query to under 10 milliseconds.

Common places where indexes are missing:

• Foreign key columns (PostgreSQL does not create these automatically)
• Columns used in WHERE clauses or JOIN conditions
• Columns used in ORDER BY with LIMIT (pagination queries)
• Columns used in unique-together constraints that are checked frequently

3. Look for N+1 query patterns

This is an application-level problem that shows up as database slowness. The classic pattern: you load a list of 50 orders, then for each order you run a separate query to load the customer name, then another query to load the shipping address. Instead of 1-2 queries, you are running 100+.

The fix is usually in the application code — using JOINs, eager loading (if you are using an ORM), or restructuring the query to fetch everything in one or two round trips. This often produces the most dramatic performance improvement of anything on this list.

4. Review connection management

PostgreSQL creates a new process for each connection, which is expensive. If your application opens and closes database connections for every request (or worse, for every query), the connection overhead alone can slow things down significantly.

Use connection pooling. PgBouncer is the standard choice — it sits between your application and PostgreSQL, reuses connections, and dramatically reduces the overhead of connection management. Most web frameworks also have built-in connection pooling, but PgBouncer gives you more control and better visibility.

5. Check your PostgreSQL configuration

The default PostgreSQL configuration is designed to work on a small server and not use much memory. If you are running on a machine with 8GB or more of RAM, the defaults are almost certainly too conservative.

Key settings to adjust:

• shared_buffers — Set to about 25% of total RAM. This is PostgreSQL's internal cache.
• work_mem — Controls memory available for sort operations and hash tables. Increase this if you see disk-based sorts in your EXPLAIN output.
• effective_cache_size — Set to about 75% of total RAM. This helps the query planner make better decisions about when to use indexes.
• random_page_cost — If you are on SSDs (and you should be), lower this to 1.1. The default of 4.0 assumes spinning disks and makes the planner undervalue index scans.

6. Consider query caching for read-heavy workloads

If you have queries that run frequently and return the same results (e.g., "get all active product categories"), caching the results in Redis or Memcached can take significant load off the database. This is especially useful for dashboard queries, navigation menus, and other read-heavy patterns.

The tricky part is cache invalidation — making sure the cache is updated when the underlying data changes. For simple cases, a time-based expiry (cache for 60 seconds) is sufficient. For more complex cases, you may need to invalidate specific cache keys when relevant data is modified.

7. Run VACUUM and ANALYZE regularly

PostgreSQL's MVCC architecture means that deleted and updated rows are not immediately removed — they are marked as dead and cleaned up later by the VACUUM process. If autovacuum is not keeping up (common with high-write workloads), dead rows accumulate and queries slow down because they have to scan past all that dead data.

Check the pg_stat_user_tables view for tables with high dead tuple counts. If autovacuum is not keeping up, you may need to adjust its settings or run manual VACUUM on heavily-written tables during low-traffic periods.

Start with the fundamentals

Most PostgreSQL performance problems are solved by items 1 through 3 on this list: find the slow queries, add missing indexes, and fix N+1 patterns. Only after those are addressed should you start looking at configuration tuning, caching, or more advanced strategies like partitioning or read replicas.

If your application is hitting a wall and you are not sure where to start, reach out — database performance is one of the things we help clients with regularly.