PostgreSQL optimization for web developers.

Last year I inherited a Node.js application whose main dashboard query took eleven seconds. The table had four million rows and zero indexes beyond the primary key. After a week of targeted optimization, the same query ran in 80 milliseconds. No infrastructure changes, no caching layer — just PostgreSQL fundamentals applied correctly.

The uncomfortable truth is that most database performance problems are self-inflicted. We reach for Redis, read replicas, and elaborate caching architectures when the real problem is a missing index, a reckless ORM query, or a pagination strategy that doesn't scale.

EXPLAIN ANALYZE is your best friend

Stop guessing why queries are slow. Prefix any query with EXPLAIN ANALYZE and PostgreSQL will tell you exactly what it's doing: which indexes it's using (or not), how many rows it's scanning, and where the time goes.

Three things to look for in the output:

• Sequential scans on large tables. If you see "Seq Scan" on a table with more than 10,000 rows, you probably need an index.
• High estimated vs. actual rows. A mismatch means stale statistics — run ANALYZE on the table.
• Nested loops where hash joins should be. This often means the planner doesn't have enough memory. Increase work_mem for complex queries.

Make EXPLAIN ANALYZE a habit, not a last resort. I run it on every new query before it hits production. The five minutes it takes saves hours of debugging later.

Indexing strategies that matter

B-tree indexes are the default and right for 90% of use cases. But knowing which columns to index — and in what combination — is where most developers go wrong.

1. Index columns in your WHERE clauses. If you filter by status frequently, index it. If you filter by status and created_at together, create a composite index — and put the more selective column first.
2. Partial indexes for filtered queries. If 95% of your queries filter for WHERE status = 'active', a partial index on that condition is smaller and faster than a full index.
3. GIN indexes for JSONB and full-text search. If you're querying inside JSONB columns or doing text search, B-tree won't help. GIN indexes are purpose-built for these operations.
4. Don't over-index. Every index speeds up reads but slows down writes. A table with twelve indexes pays a tax on every INSERT, UPDATE, and DELETE. Index what you query, remove what you don't.

Connection pooling is not optional

PostgreSQL forks a new process for every connection. At 20 connections, this is fine. At 200, you're burning memory on process overhead instead of query execution. In serverless environments, it's worse — every function invocation opens a new connection, and you'll hit max_connections within minutes under load.

Use a connection pooler like PgBouncer (or Supabase's built-in pooler if you're on their platform). Configure it in transaction mode for serverless workloads. Set your application's pool size to match available connections divided by the number of application instances.

A common mistake: setting max_connections to 500 because "more is better." Each connection uses roughly 5–10MB of RAM. At 500 connections, that's 2.5–5GB of RAM just for connection overhead. For most web applications, 50–100 connections through a pooler handles thousands of concurrent requests.

N+1 queries: the silent killer

ORMs make N+1 queries embarrassingly easy to write. Fetch a list of 100 users, then loop through each one to fetch their posts — that's 101 database round-trips instead of one JOIN or two batched queries.

How to spot them:

• Log query counts per request in development. If a single API endpoint fires more than 10 queries, investigate.
• Use eager loading in your ORM (include in Prisma, joinedload in SQLAlchemy, includes in ActiveRecord).
• For GraphQL APIs, use DataLoader to batch and cache database lookups within a single request.

The performance difference is not subtle. I've seen API endpoints go from 3 seconds to 50 milliseconds just by fixing N+1 queries. It's the single highest-ROI optimization in most web applications.

The fastest query is the one you don't run. Cache aggressively, denormalize strategically, and question every database round-trip.

Pagination at scale

OFFSET/LIMIT is simple, but it has a dirty secret: PostgreSQL still scans all the skipped rows. OFFSET 100000 LIMIT 10 scans 100,010 rows to return 10. At page 10,000, your "simple" pagination is doing a sequential scan of the entire table.

The fix is keyset pagination (also called cursor-based pagination): instead of "skip N rows," you say "give me rows after this ID." The query becomes WHERE id > $last_seen_id ORDER BY id LIMIT 10 — and it's constant-time regardless of page depth, because it uses the index directly.

The tradeoff: you can't jump to page 500 directly. For most UIs — infinite scroll, "load more" buttons, API pagination — that's fine. For the rare case where you need random page access, consider a hybrid approach with estimated counts.

Closing thought

Database optimization isn't about memorizing PostgreSQL configuration parameters. It's about developing three habits: measuring before optimizing, indexing based on actual query patterns rather than guesses, and treating the database as a first-class part of your architecture rather than an afterthought.

The developers who write fast applications aren't the ones who know every database trick. They're the ones who run EXPLAIN before shipping, question every ORM query, and refuse to add complexity until they've proven the simple approach is too slow.