Practical PostgreSQL Performance Tuning

Start with EXPLAIN, not with config

PostgreSQL performance tuning almost always starts in the wrong place. People reach for postgresql.conf and start changing memory settings before they have looked at a single slow query. Reverse that. Ninety percent of the database performance problems we fix for clients are query and schema problems, not server-configuration problems.

The single most valuable habit is reading EXPLAIN (ANALYZE, BUFFERS) output. ANALYZE runs the query and reports real timing; BUFFERS shows how much data was read from cache versus disk. Together they tell you what the planner actually did, not what you assumed it did.

Reading a plan without drowning in it

You do not need to parse every line. Look for a few specific things:

• Seq Scan on a large table when you expected an index to be used. The planner either has no useful index or decided the index was not worth it.
• Rows estimates that are wildly off from actual rows. If the planner expects 10 rows and gets 100,000, its whole plan is built on bad statistics — run ANALYZE on the table.
• Nested Loop joins over large row counts. Fine for small sets, painful when both sides are big.
• A high Buffers read count, which means the query is hitting disk rather than cache.

Indexes: the highest-leverage fix

Most dramatic speedups come from indexing. A few rules that pay off repeatedly:

• Index the columns in your WHERE, JOIN, and ORDER BY clauses — not every column, just the ones the database filters and sorts on.
• Composite index column order matters. An index on (tenant_id, created_at) serves a query filtering by tenant and sorting by date; the reverse order does not serve it nearly as well.
• Use partial indexes for skewed data. If you constantly query only rows where status is active, an index WHERE status = 'active' is smaller and faster than indexing the whole column.
• Covering indexes via INCLUDE let an index-only scan answer a query without touching the table heap at all.
• Drop unused indexes. Every index slows writes and consumes space. Query pg_stat_user_indexes to find ones that are never scanned.

The N+1 query problem hides in your app, not your database

The slowest thing we routinely find is not one bad query — it is hundreds of small fast queries fired in a loop. An ORM loads 50 orders, then loads each order's customer one at a time: 1 query becomes 51. The database is healthy; the round trips are killing you.

The fix is at the application layer: load related data in a single query with a JOIN, or batch the lookups with a WHERE id = ANY(...) instead of looping. Turn on query logging in development and watch the count per page load. If a single screen fires dozens of near-identical queries, you have found your problem.

Configuration that is genuinely worth touching

Once queries and indexes are sane, a small number of settings matter. Defaults are conservative and assume a tiny machine:

• shared_buffers — commonly set around 25% of system RAM as a starting point.
• effective_cache_size — an estimate of OS cache available, which guides planner decisions; often set to roughly 50-75% of RAM.
• work_mem — memory per sort or hash operation. Raising it speeds large sorts but multiplies across concurrent queries, so raise it carefully.
• autovacuum — keep it on and tuned. Bloat from dead tuples is a slow, silent cause of decay, especially on high-update tables.

Tune settings last, measure after every change, and change one thing at a time. A config you cannot attribute a result to is a config you cannot trust.

Connection handling is a performance problem too

PostgreSQL uses a process per connection, which makes connections relatively expensive. An application that opens a fresh connection per request, or a fleet of serverless functions each holding one open, can exhaust the server long before CPU or disk is the bottleneck. The symptom looks like a slow database; the cause is connection exhaustion.

• Pool connections at the application layer so a bounded set is reused rather than constantly opened and closed.
• Put a pooler like PgBouncer in front when many clients or many serverless instances connect, so the database sees a small, stable number of backends regardless of how many callers exist.
• Size the pool to the database, not the app. More connections is not faster past the point the server can schedule them; an oversized pool adds contention rather than throughput.

Watch the right signals over time

Performance is not a one-time fix. Enable the pg_stat_statements extension so you can see which queries consume the most total time across all calls — the worst offender is often a moderately slow query run thousands of times, not the one query that takes ten seconds once. That aggregate view is where ongoing tuning earns its keep.

Beyond query stats, keep an eye on a few health signals: the cache hit ratio (a sudden drop means queries are spilling to disk), table and index bloat on high-update tables, replication lag if you run replicas, and the count of long-running or idle-in-transaction sessions that can hold locks and block others. A weekly glance at these catches slow decay before it becomes an incident, and it turns tuning from a panic into a routine.

How BSH can help

BSH Technologies builds and operates PostgreSQL-backed SaaS and data systems for clients across India and beyond. When a database feels slow, we profile it against real workload, fix the queries and indexes that matter, and tune only the settings that earn their place — then leave you with the dashboards to keep it healthy. Reach out if your Postgres has started to drag.