I’ve been running PostgreSQL in production for seven years. In that time, I’ve turned queries from 30 seconds to 3 milliseconds, recovered databases from near-death disk usage, and learned that most performance problems come from a handful of common mistakes.

This isn’t a DBA guide — it’s a developer’s guide. The things you need to know to build fast applications without waiting for a database team to optimize your queries.

The First Thing: Use EXPLAIN ANALYZE

Before optimizing anything, you need to understand what’s happening. EXPLAIN ANALYZE shows exactly how PostgreSQL executes your query:

EXPLAIN ANALYZE
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 20;

Output:

Limit  (cost=45320.12..45320.17 rows=20 width=72) (actual time=892.341..892.358 rows=20 loops=1)
  ->  Sort  (cost=45320.12..45432.45 rows=44932 width=72) (actual time=892.339..892.352 rows=20 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=43210.56..43659.88 rows=44932 width=72) (actual time=845.234..878.122 rows=44932 loops=1)
              ->  Hash Left Join  (cost=1234.56..38765.43 rows=889026 width=72) (actual time=12.456..567.890 rows=889026 loops=1)
                    Hash Cond: (u.id = o.user_id)
                    ->  Seq Scan on users u  (cost=0.00..1567.89 rows=44932 width=68) (actual time=0.012..23.456 rows=44932 loops=1)
                          Filter: (created_at > '2024-01-01')
                    ->  Hash  (cost=987.65..987.65 rows=543210 width=8) (actual time=11.234..11.234 rows=543210 loops=1)
Planning Time: 0.456 ms
Execution Time: 892.567 ms

What to Look For

Warning Sign Meaning Fix
Seq Scan on large table Full table scan Add an index
High rows removed by filter Index exists but isn’t used Check index columns/order
Nested Loop with many rows O(n²) join Check join conditions, add index
Sort with disk Not enough work_mem Increase work_mem or add index
HashAggregate with many batches Not enough memory Increase work_mem

Pro Tip: Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for the most useful output. The BUFFERS option shows you how many disk pages were read vs cached in memory — crucial for understanding I/O patterns.

Indexing: The 80/20 of Performance

The Most Important Rule

Index the columns you filter and sort by. This single rule solves 80% of performance problems.

-- If you frequently query:
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2025-01-01'
ORDER BY created_at DESC;

-- Create a composite index:
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

Index Types and When to Use Them

Index Type Use Case Example
B-tree (default) Equality, range, sorting CREATE INDEX idx ON t(col)
Hash Equality only (faster for =) CREATE INDEX idx ON t USING hash(col)
GIN Full-text search, JSONB, arrays CREATE INDEX idx ON t USING gin(col)
GiST Geometric, range types, proximity CREATE INDEX idx ON t USING gist(col)
BRIN Large tables with natural ordering CREATE INDEX idx ON t USING brin(col)

Composite Index Column Order Matters

-- For this query:
SELECT * FROM products WHERE category_id = 5 AND price < 100;

-- ✅ Good: Equality first, range second
CREATE INDEX idx_products_cat_price ON products (category_id, price);

-- ❌ Bad: Range first (can't efficiently use second column)
CREATE INDEX idx_products_price_cat ON products (price, category_id);

Partial Indexes: Index Only What You Query

-- If you mostly query active orders:
CREATE INDEX idx_active_orders ON orders (created_at DESC)
WHERE status = 'active';

-- Much smaller than indexing all orders
-- Only useful for queries that include WHERE status = 'active'

Covering Indexes (Index-Only Scans)

-- If your query only needs specific columns:
SELECT id, email, name FROM users WHERE email = '[email protected]';

-- Include extra columns in the index to avoid table lookup:
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (id, name);
-- PostgreSQL can serve this query entirely from the index!

Pro Tip: Check for unused indexes regularly. Every index slows down writes (INSERT/UPDATE/DELETE). Query pg_stat_user_indexes to find indexes that are never scanned:

SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Connection Pooling: Don’t Skip This

Each PostgreSQL connection uses 5-10MB of RAM. Without pooling, a 100-concurrent-user app needs 100 connections. With pooling, you might need 20.

PgBouncer (External Pooler)

; pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction    ; Best for web apps
max_client_conn = 1000     ; Accepts up to 1000 client connections
default_pool_size = 20     ; Uses only 20 actual PostgreSQL connections
min_pool_size = 5
reserve_pool_size = 5

Application-Level Pooling

// With Prisma
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?connection_limit=10&pool_timeout=10'
    }
  }
});

// With pg (node-postgres)
import { Pool } from 'pg';
const pool = new Pool({
  max: 20,                    // Maximum connections in pool
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Error if can't connect within 5s
});

Connection Pool Sizing

Rule of thumb: connections = (CPU cores * 2) + effective_spindle_count

For most web apps:

  • Development: 5 connections
  • Small production: 10-20 connections
  • Medium production: 20-50 connections
  • Large production: Use PgBouncer in front

Query Optimization Patterns

N+1 Query Problem

// ❌ N+1: One query per user for their orders
const users = await db.query('SELECT * FROM users LIMIT 100');
for (const user of users) {
  user.orders = await db.query('SELECT * FROM orders WHERE user_id = $1', [user.id]);
}
// This makes 101 queries!

// ✅ Two queries with a JOIN or IN clause
const users = await db.query('SELECT * FROM users LIMIT 100');
const userIds = users.map(u => u.id);
const orders = await db.query('SELECT * FROM orders WHERE user_id = ANY($1)', [userIds]);
// Group orders by user_id in application code

Efficient Pagination

-- ❌ Offset pagination: Gets slower as offset increases
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;
-- PostgreSQL must scan 10,020 rows and discard 10,000

-- ✅ Cursor pagination: Constant performance regardless of page
SELECT * FROM products WHERE id > $last_seen_id ORDER BY id LIMIT 20;
-- Uses index, always scans exactly 20 rows

For API pagination, cursor-based approaches give you consistent database performance at any depth.

Batch Operations

-- ❌ Individual inserts (slow: 1000 round trips)
INSERT INTO events (type, data) VALUES ('click', '{}');
INSERT INTO events (type, data) VALUES ('view', '{}');
-- ... 998 more

-- ✅ Batch insert (fast: 1 round trip)
INSERT INTO events (type, data) VALUES
  ('click', '{}'),
  ('view', '{}'),
  -- ... up to 1000 rows per statement
  ('scroll', '{}');

-- ✅ Even better with COPY for bulk loads
COPY events (type, data) FROM '/tmp/events.csv' WITH (FORMAT csv);

JSONB: When and How to Use It

PostgreSQL’s JSONB is powerful but often misused:

-- ✅ Good: Semi-structured metadata that varies per row
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price DECIMAL NOT NULL,
  metadata JSONB DEFAULT '{}'  -- Color, size, brand vary by product type
);

-- Index JSONB for queries
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- Query JSONB efficiently
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
SELECT * FROM products WHERE metadata->>'brand' = 'Nike';
-- ❌ Bad: Don't put relational data in JSONB
-- If you're querying by a JSONB field in WHERE clauses frequently,
-- it should probably be a column instead
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  data JSONB  -- ❌ Don't store customer_id, status, total in JSON
);

Pro Tip: Use JSONB for data where the schema varies (user preferences, product attributes, webhook payloads). Use columns for data you filter, join, or aggregate on frequently. The performance difference for indexed columns vs JSONB queries is 5-10x.

Table Partitioning

For tables over 10M rows, partitioning can dramatically improve query performance:

-- Partition by date range (most common)
CREATE TABLE events (
  id UUID DEFAULT gen_random_uuid(),
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2025_q1 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE events_2025_q2 PARTITION OF events
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
CREATE TABLE events_2025_q3 PARTITION OF events
  FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

-- Queries that filter by created_at only scan relevant partitions
SELECT * FROM events WHERE created_at > '2025-06-01';
-- Only scans events_2025_q2 and events_2025_q3!

When to Partition

Condition Partition?
Table > 100M rows Yes
Table > 10M rows with date queries Yes
Small table (<1M rows) No (overhead outweighs benefits)
Need to drop old data quickly Yes (drop partition vs delete)
Random queries across all data Probably not helpful

Configuration Tuning

Default PostgreSQL config is intentionally conservative. Here are the settings I adjust for production:

# postgresql.conf - for a server with 16GB RAM, 8 CPU cores

# Memory
shared_buffers = 4GB              # 25% of total RAM
effective_cache_size = 12GB       # 75% of total RAM
work_mem = 64MB                   # Per-operation sort memory
maintenance_work_mem = 1GB        # For VACUUM, CREATE INDEX

# Write Performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Query Planner
random_page_cost = 1.1            # SSD (default 4.0 is for HDD!)
effective_io_concurrency = 200    # SSD

# Connections
max_connections = 200             # Use pooling, keep this reasonable

# Parallelism
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8

The single biggest impact: Change random_page_cost from 4.0 to 1.1 if you’re on SSD. This tells the query planner that random disk access is cheap, making it prefer index scans over sequential scans.

Common Mistakes

1. Missing Indexes on Foreign Keys

-- PostgreSQL does NOT automatically index foreign keys!
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);

-- You MUST create the index manually:
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Without this index:
-- - JOINs are slow
-- - CASCADE deletes scan the entire table
-- - Referential integrity checks are slow

2. Not Running VACUUM/ANALYZE

-- PostgreSQL needs VACUUM to reclaim dead row space
-- and ANALYZE to update query planner statistics

-- Check for tables that need vacuuming:
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Manual vacuum for critical tables:
VACUUM ANALYZE orders;

3. SELECT * in Production Code

-- ❌ Fetches all 30 columns when you need 3
SELECT * FROM users WHERE id = $1;

-- ✅ Fetch only what you need (enables index-only scans)
SELECT id, name, email FROM users WHERE id = $1;

4. Not Using Prepared Statements

// ❌ String interpolation (SQL injection risk AND no plan caching)
const query = `SELECT * FROM users WHERE email = '${email}'`;

// ✅ Parameterized query (safe AND cached execution plan)
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
);

5. Ignoring Table Bloat

After heavy UPDATE/DELETE operations, tables accumulate dead rows. Monitor and address bloat:

-- Check table bloat
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) as total_size,
  pg_size_pretty(pg_relation_size(tablename::regclass)) as table_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

Monitoring Queries

-- Enable query statistics
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT
  round(total_exec_time::numeric, 2) as total_time_ms,
  calls,
  round(mean_exec_time::numeric, 2) as avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) as pct,
  LEFT(query, 100) as query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

For apps using Docker for database infrastructure, make sure your PostgreSQL container has enough shared memory configured.

FAQ

How do I know if I need an index?

If a query takes >100ms and you see “Seq Scan” in EXPLAIN ANALYZE on a table with >10K rows, you likely need an index on the column(s) in your WHERE clause. Also index foreign key columns, columns in ORDER BY, and columns in JOIN conditions. Start with the slowest queries (check pg_stat_statements) and work down.

When should I use PostgreSQL vs other databases?

PostgreSQL is my default for almost everything. Use it when you need: relational data with complex queries, JSONB for flexible schemas, full-text search, geospatial data, or strong ACID compliance. Consider alternatives when: you need extreme write throughput (Cassandra/ScyllaDB), pure key-value (Redis), or document-oriented with automatic scaling (DynamoDB/MongoDB). In 2025, PostgreSQL handles 90% of use cases well.

How do I handle database migrations safely?

Use a migration tool (Prisma Migrate, Drizzle Kit, or golang-migrate). Key safety rules: never lock tables in production (avoid ALTER TABLE ... ADD COLUMN ... DEFAULT on large tables without NOT NULL), always test migrations against a production-sized dataset, make migrations reversible, and run them in a transaction when possible.

What’s the maximum table size PostgreSQL can handle?

Technically 32TB per table, but practically you’ll want to partition tables over 100M rows and consider archival strategies for tables over 1B rows. With proper indexing, partitioning, and hardware, I’ve seen PostgreSQL handle 5B+ row tables with sub-second queries. The key is ensuring your working set (frequently accessed data) fits in RAM.

Should I use an ORM or raw SQL?

Use an ORM (Prisma, Drizzle) for CRUD operations and basic queries — it’s faster to develop and prevents SQL injection. Use raw SQL for complex queries (window functions, CTEs, recursive queries) where the ORM generates suboptimal SQL. Most apps benefit from both: ORM for 80% of queries, raw SQL for the performance-critical 20%.