PostgreSQL Performance: Queries That Scale

PostgreSQL is remarkably capable out of the box. But as your data grows from thousands to millions of rows, the queries that worked fine in development start timing out in production. Here's how to prevent that.

EXPLAIN ANALYZE is your most important tool. Not EXPLAIN. EXPLAIN ANALYZE. The difference is that ANALYZE actually executes the query and shows real execution times, not estimates. Every slow query investigation starts here. Look for sequential scans on large tables, nested loops with high row counts, and sort operations that spill to disk.

Index strategy requires understanding your query patterns. A common mistake is adding indexes reactively, a query is slow, so you add an index on the WHERE clause column. Instead, analyze your application's query patterns holistically. Composite indexes (multi-column) often serve multiple queries. A composite index on (user_id, created_at DESC) serves both 'find user's recent orders' and 'find user's order on date' queries.

Partial indexes are underused. If you frequently query WHERE published = true, a partial index. CREATE INDEX idx_published_articles ON articles(published_at) WHERE published = true, is smaller and faster than indexing the entire table. This works especially well when the filtered subset is much smaller than the full table.

Connection pooling is essential for serverless and container-based architectures. Each PostgreSQL connection consumes roughly 10MB of memory. Cloud Run or Lambda can spawn hundreds of instances, each wanting its own connection. PgBouncer in transaction mode sits between your application and PostgreSQL, multiplexing many application connections over a smaller pool of database connections.

The N+1 query problem is the most common performance issue in ORM-based applications. Loading a list of articles, then loading tags for each article individually, produces N+1 queries. Prisma's include directive (or JOIN in raw SQL) loads everything in one or two queries. Always check your ORM's query log in development.

Pagination with OFFSET is a trap. OFFSET 10000 means PostgreSQL reads and discards 10,000 rows before returning your page. Cursor-based pagination. WHERE id > last_seen_id ORDER BY id LIMIT 20, is constant time regardless of how deep into the dataset you are.

Batch operations over loops. Inserting 1,000 rows one at a time means 1,000 round trips. A single INSERT with multiple VALUES clauses is dramatically faster. Prisma's createMany and update with WHERE IN patterns handle this.

Monitor slow queries in production. PostgreSQL's pg_stat_statements extension tracks query performance statistics. Set log_min_duration_statement to capture queries above a threshold. Review these weekly, performance degradation is gradual and easy to miss until it becomes critical.