PostgreSQL Performance: Indexes, Query Plans and Connection Pooling

PostgreSQL is the most popular database among professional developers, and for good reason: it's reliable, feature-rich and fast. But when an application slows down as data grows, the cause is usually in how it's queried. These are the techniques that solve most performance problems.

Measure first, change one thing, measure again.

Find the Slow Queries First

Don't guess. Enable the pg_stat_statements extension to see which queries consume the most total time:

SELECT query, calls, round(mean_exec_time::numeric, 1) AS mean_ms, round(total_exec_time::numeric) AS total_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

A query taking 5 ms but called a million times a day often matters more than a 2-second report run once.

Read the Query Plan

EXPLAIN ANALYZE runs the query and shows how PostgreSQL executed it:

EXPLAIN ANALYZE
SELECT * FROM attendance
WHERE tenant_id = '…' AND class_id = '…' AND date >= '2026-06-01';

Look for:

• Seq Scan on a large table — often a missing index.
• Estimated vs actual rows far apart — outdated statistics; run ANALYZE.
• Nested loops over many rows or large sorts spilling to disk.

Design Indexes for Your Queries

Indexes should match how you filter and sort:

CREATE INDEX CONCURRENTLY attendance_tenant_class_date_idx
  ON attendance (tenant_id, class_id, date);

• Column order matters: equality columns first (tenant_id, class_id), then range or sort columns (date).
• In multi-tenant tables, start indexes with the tenant column.
• Use partial indexes for common filters (WHERE deleted_at IS NULL).
• Create indexes concurrently in production to avoid locking writes.
• Don't over-index: every index slows inserts and updates and uses memory.

Eliminate N+1 Queries

ORMs make it easy to load a list and then query each item's relations one by one — 1 query becomes 101. Load relations in a single query with joins or batched IN (…) lookups, and watch query counts per request in development.

Paginate Properly

OFFSET 50000 still reads and discards 50,000 rows. For large lists, use keyset (cursor) pagination:

SELECT * FROM students
WHERE tenant_id = $1 AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pool Your Connections

Each PostgreSQL connection consumes server memory, and connection limits are reached quickly — especially with serverless functions or many application instances.

• Use a connection pooler such as PgBouncer (or your provider's pooler) in transaction mode.
• Keep application pool sizes modest; more connections rarely means more throughput.
• Set statement timeouts so one runaway query can't hold connections indefinitely.

Key Takeaways

• Find expensive queries with pg_stat_statements before optimising.
• Read EXPLAIN ANALYZE to see what the database actually does.
• Build composite indexes that match filters and sort order — tenant column first in SaaS.
• Fix N+1 queries, use keyset pagination and pool connections.

Sources

• PostgreSQL — Using EXPLAIN
• PostgreSQL — Indexes
• PostgreSQL — pg_stat_statements
• PgBouncer — Documentation