PostgreSQL Performance Tuning That Developers Can Actually Do

Most PostgreSQL performance problems I encounter are caused by application developers, not by database misconfiguration. Missing indexes, N+1 queries, unoptimized joins, and scanning entire tables when an index lookup would suffice. The good news: these are problems developers can fix without becoming database administrators.

Here are the optimizations that have the highest impact for the least effort.

Start With EXPLAIN ANALYZE

Before optimizing anything, measure. PostgreSQL's EXPLAIN ANALYZE shows you exactly what the database does to execute your query: which indexes it uses, how many rows it scans, where time is spent.

Run: EXPLAIN ANALYZE SELECT * FROM tools WHERE category_id = 5 AND is_active = true ORDER BY rating DESC LIMIT 20;

The output tells you: whether it is using a sequential scan (bad for large tables) or an index scan (good), how many rows it expects vs actually finds, the total execution time, and where time is spent (sorting, filtering, joining).

The key metrics: "Seq Scan on tools" means it is reading every row in the table. If the table has a million rows and you want 20, that is wasteful. "Index Scan using idx_tools_category" means it is using an index — much faster.

The Index Checklist

If EXPLAIN ANALYZE shows sequential scans on queries that filter or sort, you likely need indexes:

Index columns used in WHERE clauses: CREATE INDEX idx_tools_category ON tools(category_id). Now queries filtering by category use the index instead of scanning all rows.

Index columns used in ORDER BY: if you frequently sort by rating, add an index. The database can read rows in sorted order directly from the index without a separate sort step.

Index columns used in JOIN conditions: every foreign key should have an index. Without it, every join scans the entire related table.

Composite indexes for multi-column filters: if you always filter by category AND is_active, a composite index CREATE INDEX idx_tools_cat_active ON tools(category_id, is_active) is more efficient than two separate indexes.

Column order in composite indexes matters. Put the highest-selectivity column first (the one that eliminates the most rows). category_id likely has 20-30 distinct values, while is_active has 2. Put category_id first.

The N+1 Query Problem

The single most common performance problem in web applications. It happens when you load a list of items and then make a separate query for each item's related data:

Query 1: SELECT * FROM tools (returns 50 tools)

Query 2-51: SELECT * FROM categories WHERE id = ? (one per tool)

Total: 51 queries. With 600 tools displayed on a page, that is 601 queries.

Fix with eager loading (JOIN or subquery): SELECT t.*, c.name FROM tools t JOIN categories c ON t.category_id = c.id. One query returns everything.

In Prisma: use include to eager-load relations. db.tool.findMany({ include: { category: true } }) generates a single JOIN query instead of N+1 separate queries.

Detect N+1 queries by monitoring query counts per request. If a single page load triggers 50+ queries, you almost certainly have an N+1 problem.

Pagination Done Right

The naive approach: SELECT * FROM tools ORDER BY created_at DESC LIMIT 20 OFFSET 1000. This works but gets slower as offset increases because PostgreSQL must scan and discard the first 1000 rows before returning 20.

For deep pagination (page 50+), use cursor-based pagination: SELECT * FROM tools WHERE created_at < '2026-05-01' ORDER BY created_at DESC LIMIT 20. Use the last item's timestamp as the cursor for the next page.

Cursor-based pagination has constant performance regardless of how deep into the result set you go. It does not need to count skipped rows. The trade-off: you cannot jump to "page 47" directly — you can only go forward and backward.

For count queries (showing "page 1 of 50"): count queries on large tables are expensive. Consider using approximate counts (SELECT reltuples FROM pg_class WHERE relname = 'tools') or caching the exact count with periodic refresh.

Partial Indexes for Filtered Queries

If you frequently query a subset of rows (only active tools, only published blog posts), a partial index eliminates rows that will never match:

CREATE INDEX idx_tools_active ON tools(rating DESC) WHERE is_active = true;

This index is smaller (only includes active tools) and faster (fewer entries to scan). Queries like SELECT * FROM tools WHERE is_active = true ORDER BY rating DESC use this smaller, faster index.

We use partial indexes at AIDToolStack for all our "active only" queries. Most listing pages only show active items, so the partial index perfectly matches our query patterns.

VACUUM and Table Bloat

PostgreSQL uses MVCC (Multi-Version Concurrency Control). When you update or delete a row, the old version remains in the table until VACUUM removes it. Without regular vacuuming, tables bloat — they contain many dead rows that queries must skip.

In managed PostgreSQL (RDS, Neon, PlanetScale), auto-vacuum handles this. In self-hosted PostgreSQL, ensure autovacuum is enabled and tuned.

Monitor bloat with: SELECT schemaname, relname, n_dead_tup FROM pg_stat_all_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC. Tables with many dead tuples might need manual vacuum or autovacuum threshold tuning.

Connection Pooling

PostgreSQL creates a new process for each connection. With 200 concurrent connections, you have 200 PostgreSQL processes consuming memory and CPU for context switching. Most web applications open far more connections than necessary.

Use a connection pooler: PgBouncer (standalone), Prisma's built-in connection pooling, or Neon's serverless proxy. The pooler maintains a smaller pool of actual database connections and multiplexes application requests across them.

Configure your pool size based on your PostgreSQL server's capacity. A rule of thumb: set pool size to (2 * CPU cores) + number of disks. A server with 4 cores and 1 disk should have a pool of about 9 connections.

In serverless environments (Vercel, Lambda), connection pooling is essential. Without it, each serverless function invocation opens a new database connection, potentially exhausting the connection limit during traffic spikes.

Query Optimization Patterns

Avoid SELECT * — select only the columns you need. This reduces the data transferred from disk to memory and from database to application. If your tool table has a 10KB description column but you only need the name and slug for a listing, selecting all columns wastes bandwidth.

Use EXISTS instead of COUNT for existence checks: WHERE EXISTS (SELECT 1 FROM reviews WHERE tool_id = t.id) is faster than WHERE (SELECT COUNT(*) FROM reviews WHERE tool_id = t.id) > 0. EXISTS stops at the first match; COUNT scans all matches.

Avoid functions in WHERE clauses on indexed columns: WHERE LOWER(name) = 'json' cannot use an index on name. Either create a functional index (CREATE INDEX idx_tools_name_lower ON tools(LOWER(name))) or store the lowercased value in a separate indexed column.

Batch operations: instead of 100 individual INSERTs, use a single INSERT with multiple value sets. Instead of 100 individual UPDATEs, use UPDATE with a CTE or a temporary table join. Batch operations reduce round trips and transaction overhead.

Monitoring Query Performance

Enable pg_stat_statements extension to track query performance over time. It records execution counts, total time, mean time, and rows affected for each distinct query pattern. Review the top 10 slowest queries weekly and optimize them.

Set log_min_duration_statement = 1000 to log all queries taking over 1 second. This catches slow queries without the noise of logging every fast query.

In application code, log query durations. Prisma can be configured with middleware that logs query time. Alert if any query exceeds your acceptable threshold (typically 500ms for web requests, 5s for background jobs).

The 80/20 Rule

80% of database performance problems come from 3 sources: missing indexes on WHERE/JOIN columns, N+1 queries from ORM usage, and selecting more data than needed (SELECT * on tables with large text columns).

Fix these three issues and your database performance will be dramatically better than most applications. The remaining 20% — advanced query optimization, table partitioning, read replicas, and database parameter tuning — only matters at significant scale.

For a typical web application with millions of rows, proper indexes and efficient queries keep response times under 50ms. You do not need a DBA or exotic database features. You need EXPLAIN ANALYZE, appropriate indexes, and awareness of N+1 queries.