PostgreSQL Performance Deep Dive

EXPLAIN ANALYZE Is Your Best Friend

You can't optimize what you can't see. EXPLAIN ANALYZE shows you exactly what Postgres did to execute a query — which indexes it used, how many rows it scanned, and where the time went. Read this output before touching any query.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

Reading the Output

Look for Seq Scans on large tables — those are usually index misses. Look for high "actual rows" vs "estimated rows" divergence — that's stale statistics. Look for nested loop joins on large result sets — those are often the real bottleneck.

Index Strategy That Actually Helps

An index on every column is as bad as no indexes at all. Index the columns in your WHERE clauses, JOIN conditions, and ORDER BY. Composite indexes are ordered — (user_id, created_at) helps queries filtering by user and sorting by date, but not queries filtering only by date.

Partial Indexes

Index only the rows you actually query. A partial index on WHERE status = 'pending' is dramatically smaller and faster than a full index when pending orders are 2% of your table.

Connection Pooling Is Not Optional

Postgres has a connection limit. Each connection consumes ~5-10MB of RAM. At 200 concurrent connections you're in trouble without a pooler. PgBouncer in transaction mode is the standard. With Supabase or Neon, it's built in.

Eliminating N+1 Queries

N+1 is the most common performance problem in any ORM-based app. One query to get 100 users, then 100 queries to get their profiles. The fix: JOINs, or the ORM's eager loading. Use pg_stat_statements to find repeated query patterns.

Understanding Autovacuum

Postgres never updates rows in-place. Updates create a new row version and mark the old one dead. Autovacuum reclaims that space. When autovacuum can't keep up (high write load, large tables), you get table bloat and query slowdowns. Monitor it.