PostgreSQL Performance Optimization for Web Developers

PostgreSQL is the world's most advanced open-source database, powering everything from small startups to massive enterprise applications. But as your application grows, queries that once took milliseconds can slow to seconds — and slow queries are the most common cause of poor application performance.

This guide covers practical PostgreSQL performance optimization techniques for web developers. You will learn indexing strategies, query optimization, connection pooling, and monitoring — all with real-world examples you can apply to your Supabase or PostgreSQL database immediately.

The Most Important Performance Tool: EXPLAIN ANALYZE

Before optimizing anything, learn to read query plans. PostgreSQL's EXPLAIN ANALYZE shows exactly how a query executes:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = '123' AND status = 'completed';

Look for these red flags:

• Sequential scans on large tables (Seq Scan)
• High estimated vs actual row counts (bad statistics)
• Sort operations on unindexed columns
• Nested loop joins on large datasets

Indexing Strategies

Indexes are the single most effective optimization for read-heavy workloads. But too many indexes hurt write performance.

Single-Column Indexes

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

Composite Indexes for Multi-Column Queries

-- For queries filtering by both columns
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Column order matters: put high-selectivity columns first
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Partial Indexes for Common Filters

-- Only index active users (most queries filter by active=true)
CREATE INDEX idx_users_active ON users(id) WHERE active = true;

-- Index only completed orders for reporting queries
CREATE INDEX idx_orders_completed ON orders(created_at)
  WHERE status = 'completed';

Covering Indexes (Include)

When a query only needs specific columns, include them in the index to avoid table lookups:

CREATE INDEX idx_orders_list ON orders(user_id, status)
  INCLUDE (total, created_at);

-- Now this query is covered by the index:
SELECT total, created_at FROM orders
  WHERE user_id = '123' AND status = 'completed';

Query Optimization Patterns

Avoid SELECT * in Production

-- Bad: fetches all columns
SELECT * FROM users WHERE email = 'test@example.com';

-- Good: fetches only needed columns
SELECT id, name, email FROM users WHERE email = 'test@example.com';

Use LIMIT for Pagination

-- Offset pagination (works for small datasets)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- Cursor pagination (better for large datasets)
SELECT * FROM orders
  WHERE created_at < '2024-01-01'
  ORDER BY created_at DESC
  LIMIT 20;

Use EXISTS Instead of COUNT for Existence Checks

-- Bad: counts all matching rows
SELECT COUNT(*) FROM orders WHERE user_id = '123';

-- Good: stops at first match
SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = '123');

Connection Pooling

PostgreSQL has a limited number of connections (default 100). Each connection consumes memory. Connection pooling is essential for serverless and high-traffic applications:

// lib/db.ts with PgBouncer-compatible connection
import { Pool } from 'pg'

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Connection pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
})

// In serverless functions, use a singleton
let globalPool: Pool

export function getPool() {
  if (!globalPool) {
    globalPool = new Pool({
      connectionString: process.env.DATABASE_URL,
      max: 5, // Smaller pool for serverless
    })
  }
  return globalPool
}

Supabase-Specific Optimizations

If you are using Supabase (which uses PostgreSQL), these additional tips apply:

• Use `.maybeSingle()` instead of `.single()` to avoid errors when no rows match
• Use `.select('column1, column2')` instead of `.select('*')`
• Add indexes on foreign key columns that Supabase RLS policies reference
• Use `explain()` on Supabase queries to debug performance

// Before: full table scan
const { data } = await supabase
  .from('orders')
  .select('*')
  .eq('user_id', userId)

// After: with proper indexes and column selection
const { data } = await supabase
  .from('orders')
  .select('id, total, status, created_at')
  .eq('user_id', userId)
  .order('created_at', { ascending: false })
  .limit(20)

Monitoring Slow Queries

Enable query logging to find slow queries:

-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the slowest queries
SELECT
  query,
  mean_exec_time,
  calls,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

The Bottom Line

PostgreSQL performance optimization is an ongoing process, not a one-time task. Start with proper indexing, write efficient queries, use connection pooling, and monitor slow queries regularly. The PostgreSQL Performance Starter template demonstrates all these patterns with a production-ready Supabase configuration.