PostgreSQL Performance at Scale: The Tweaks That Move the Needle

What you need to know

• The highest-impact PostgreSQL optimizations are almost always indexing and query rewrites, not configuration tuning. Fix the queries before tuning the server.
• EXPLAIN ANALYZE is the starting point for every performance investigation. It reveals whether PostgreSQL is using the right plan, whether indexes are being used, and where query time is being spent.
• Connection pooling with PgBouncer or equivalent is essential for applications with high connection churn (serverless, web applications with per-request connection creation).
• The default PostgreSQL configuration is conservative: shared_buffers, work_mem, and effective_cache_size defaults are appropriate for small servers and wrong for production workloads on modern hardware.
• Regular ANALYZE and autovacuum configuration keep query planner statistics accurate. Stale statistics cause PostgreSQL to choose wrong query plans that are orders of magnitude slower than optimal.

The core argument

PostgreSQL performance problems at scale follow predictable patterns. The starting point for almost every investigation is the same: identify the slowest queries using pg_stat_statements or slow query logs, run EXPLAIN ANALYZE on each, and look for sequential scans on large tables. In nine out of ten cases, the fix is an index. The index adds a lookup structure that PostgreSQL uses to find matching rows without scanning the entire table. This single change can reduce query time from seconds to milliseconds.

The N+1 query pattern is the second most common cause of PostgreSQL performance problems in web applications. An ORM that loads a list of users and then queries for each user's recent orders produces one query for the user list and one query per user for their orders. A list of 100 users produces 101 queries. The fix is eager loading: one query that joins users and orders and returns all the data needed. ORMs provide eager loading mechanisms (includes in Rails, eager_load in Sequelize, join in SQLAlchemy) that collapse 101 queries into one. Identifying N+1 patterns in production requires query logging: if the same query template runs many times in a single request, it is likely an N+1.

Configuration tuning matters after the queries are optimized. A PostgreSQL server with correct queries and correct indexes running on a machine with 32GB of RAM but shared_buffers set to 128MB is wasting most of that RAM. Setting shared_buffers to 8GB allows PostgreSQL to cache more of the working dataset in memory, reducing disk I/O for repeated queries. Setting work_mem higher allows complex sort operations to complete in memory rather than spilling to disk. These configuration changes improve performance for already-optimized queries; they do not compensate for missing indexes or inefficient query patterns.

Common mistakes

1. Tuning configuration parameters before addressing query and index problems. Configuration tuning is the last step of PostgreSQL optimization, not the first. A slow query caused by a sequential scan is not fixed by increasing shared_buffers; it is fixed by adding an index. Start with queries.

1. Not running ANALYZE after bulk data loads. ANALYZE updates the statistics that PostgreSQL uses to choose query plans. After a large data load or a bulk delete, statistics may be stale enough to cause wrong plan choices. Run ANALYZE on affected tables after significant data changes.

1. Creating indexes without verifying they are used. An index on a low-cardinality column (a boolean column, an enum with three values) may not be used by PostgreSQL because a sequential scan is cheaper for columns where the index does not significantly reduce the rows scanned. Use EXPLAIN ANALYZE to verify that new indexes are actually used by the queries they are intended for.

1. Not using connection pooling in high-connection environments. Applications that create a new database connection per request or per serverless function invocation exhaust PostgreSQL's connection limit under moderate concurrency. PgBouncer in transaction mode allows thousands of application connections to be served by dozens of Postgres connections.

1. Not monitoring query performance over time. A query that is fast today may become slow as data grows. The index that works for a 100k row table may not work as well for a 10 million row table. Set up pg_stat_statements monitoring and review the slowest queries monthly rather than only during incidents.

Where to start

1. Enable pg_stat_statements and identify the top 10 slowest queries by total time. pg_stat_statements is a Postgres extension that tracks query execution statistics across all queries. The query SELECT query, total_exec_time, calls FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10 shows the queries consuming the most total database time.

1. Run EXPLAIN ANALYZE on each slow query and look for sequential scans. A sequential scan on a table with more than 10,000 rows is a candidate for an index. Identify the columns in the WHERE clause and join conditions of the query and add an index on those columns.

1. Review and update PostgreSQL configuration parameters. Set shared_buffers to 25 percent of available RAM, set effective_cache_size to 75 percent of available RAM, and increase work_mem to 64MB or higher if complex sorts are shown in query plans. These changes require a PostgreSQL restart and take effect immediately after restart.

Related reading

• Pagination Patterns: Cursor vs Offset and Why It Matters
• JSON Columns in Postgres: When They Make Sense
• Multi-Region Deployments: Decision Framework and Cost Math
• Point in Time Recovery: A Founder's Insurance Policy