PostgreSQL Performance: Indexes, Query Plans, and Connection Pooling

Editorial Team · · 3 min read

PostgreSQL Isn't Slow — Your Queries Are

I can't count the number of times I've heard "PostgreSQL is slow, we need to switch to [insert NoSQL database]." Every single time, the problem turned out to be missing indexes, bad query patterns, or connection pool misconfiguration. PostgreSQL can handle enormous workloads — Instagram ran on it until well past 100 million users. Your SaaS app with 50,000 users isn't going to outgrow it.

But you do need to tune it. Out-of-the-box PostgreSQL settings are intentionally conservative, designed to run on a machine with 512MB of RAM. Your production server has a lot more than that.

Index Types and When to Use Them

The default B-tree index covers 90% of use cases. It's great for equality and range queries on scalar values. But PostgreSQL offers several specialized index types that are worth knowing:

B-tree — Default. Use for =, <, >, BETWEEN, IN, ORDER BY, LIKE 'prefix%'. Handles NULLs. Supports unique constraints.

Hash — Only useful for exact equality (=). Slightly faster than B-tree for equality-only lookups, but doesn't support range queries or ordering. Rarely worth using since PostgreSQL 10+ B-trees are very competitive.

GIN (Generalized Inverted Index) — For composite values: arrays, JSONB, full-text search (tsvector). If you're querying JSONB fields with @> or ? operators, you want a GIN index.

-- JSONB containment queries
CREATE INDEX idx_orders_metadata ON orders USING gin (metadata);
SELECT * FROM orders WHERE metadata @> '{"priority": "high"}';

-- Array containment
CREATE INDEX idx_tags ON articles USING gin (tags);
SELECT * FROM articles WHERE tags @> ARRAY['python', 'backend'];

GiST (Generalized Search Tree) — For geometric data, ranges, and full-text search. If you're doing PostGIS spatial queries, you're using GiST.

BRIN (Block Range Index) — For very large tables where the indexed column correlates with physical row order. Think timestamps on append-only tables. A BRIN index on a 100GB table might be 100KB instead of the 2GB a B-tree would need.

Partial Indexes

One of PostgreSQL's best features that people underuse:

-- Instead of indexing all orders...
CREATE INDEX idx_orders_status ON orders(status);

-- Index only the ones you actually query
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

-- This index is tiny and fast because 95% of orders
-- aren't pending

Reading EXPLAIN ANALYZE

Every developer working with PostgreSQL should be comfortable reading query plans. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on any query you're optimizing:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, count(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > '2026-01-01'
GROUP BY u.name
ORDER BY count DESC
LIMIT 20;

Key things to look for:

  • Seq Scan on large tables — usually means a missing index
  • Nested Loop with high row counts — the inner loop runs once per outer row; consider if a Hash Join would be better
  • Actual rows vs estimated rows — if these diverge by 10x+, your statistics are stale (run ANALYZE table_name)
  • Buffers: shared hit vs shared read — reads from disk are slow; if shared read is high, you might need more shared_buffers

Connection Pooling with PgBouncer

PostgreSQL creates a new process for every connection. Each process uses about 5-10MB of RAM. A hundred connections? That's manageable. A thousand? You're burning a gigabyte of RAM just on connection overhead, and the process scheduler starts struggling.

PgBouncer sits between your app and PostgreSQL, multiplexing many client connections onto fewer database connections:

# pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction    # Most common and safest
max_client_conn = 1000     # Accept up to 1000 app connections
default_pool_size = 25     # Only 25 actual PG connections
reserve_pool_size = 5
reserve_pool_timeout = 3

The transaction pool mode releases the connection back to the pool after each transaction completes. This means 1,000 application connections can share 25 database connections, as long as they're not all running queries simultaneously. For most web applications, this works perfectly.

Essential Configuration Tuning

For a server with 16GB RAM and SSD storage, these settings are a good starting point:

# Memory
shared_buffers = 4GB            # 25% of RAM
effective_cache_size = 12GB     # 75% of RAM
work_mem = 64MB                 # Per-sort/hash operation
maintenance_work_mem = 1GB      # For VACUUM, CREATE INDEX

# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Planner
random_page_cost = 1.1          # SSD (default 4.0 is for spinning disk)
effective_io_concurrency = 200  # SSD

# Connections
max_connections = 100           # Use PgBouncer for more

And please run VACUUM ANALYZE regularly — either through autovacuum (which should be enabled by default) or a cron job for large tables that see heavy UPDATE/DELETE traffic.