PostgreSQL Performance Mastery: Indexes, EXPLAIN ANALYZE, Partitioning & Query Optimization

-- Perfect for: = , <, >, BETWEEN, LIKE 'prefix%'
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_posts_published_at ON posts (published_at DESC);

-- Composite index — order matters!
-- This index supports: WHERE status = ? AND published_at = ?
-- But NOT: WHERE published_at = ? alone
CREATE INDEX idx_posts_status_date ON posts (status, published_at DESC);

-- Full-text search
ALTER TABLE posts ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', title || ' ' || content)
  ) STORED;

CREATE INDEX idx_posts_search ON posts USING GIN (search_vector);

-- Fast full-text query
SELECT * FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'kubernetes docker');

-- JSONB containment queries
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- Find users where metadata contains specific key-value
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

-- Ideal for time-series data where rows are inserted in order
-- Tiny index size: 1 entry per 128 pages (vs B-tree: 1 entry per row)
CREATE INDEX idx_events_created_at ON events USING BRIN (created_at);

-- 1 billion row table: B-tree = 21GB, BRIN = 400KB

-- 95% of posts are published. Queries almost always filter by status='published'
-- Only index published posts — fraction of the rows
CREATE INDEX idx_published_posts ON posts (published_at DESC)
WHERE status = 'published';

-- This index is 95% smaller than a full index and fits in RAM

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY post_count DESC
LIMIT 10;

Limit (cost=1234.56..1234.57 rows=10 width=48) (actual time=45.123..45.131 rows=10 loops=1)
  -> Sort (cost=1234.56..1284.56 rows=20000 width=48) (actual time=45.120..45.124 rows=10 loops=1)
       Sort Key: (count(p.id)) DESC
       Sort Method: top-N heapsort  Memory: 25kB
       -> HashAggregate (cost=789.00..989.00 rows=20000 width=48) (actual time=38.234..43.891 rows=18432 loops=1)
            Group Key: u.id
            -> Hash Left Join (cost=234.00..689.00 rows=40000 width=16) (actual time=5.432..28.901 rows=45231 loops=1)
                 Hash Cond: (p.user_id = u.id)
                 -> Seq Scan on posts p ...   ← ⚠️ FULL TABLE SCAN!
                 -> Hash (cost=184.00..184.00 rows=4000 width=12) (actual time=4.321..4.321 rows=4000 loops=1)
                      -> Index Scan using idx_users_created_at on users u ...
Buffers: shared hit=1234 read=5678   ← read=5678 means disk I/O — needs more caching
Planning Time: 1.234 ms
Execution Time: 45.678 ms            ← Our target: <10ms

CREATE INDEX idx_posts_user_id ON posts (user_id);

-- Parent table
CREATE TABLE events (
  id          BIGSERIAL,
  user_id     UUID NOT NULL,
  event_type  VARCHAR(50) NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- ... continue for each month

-- Index each partition (PostgreSQL 11+ allows global indexes too)
CREATE INDEX ON events_2026_01 (user_id, created_at);
CREATE INDEX ON events_2026_02 (user_id, created_at);

-- Only scans events_2026_08 partition
SELECT * FROM events
WHERE created_at BETWEEN '2026-08-01' AND '2026-08-31'
AND user_id = 'abc-123';

# pgbouncer.ini
[databases]
myapp = host=postgres port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction          # One DB connection per transaction
max_client_conn = 1000           # Handle 1000 app connections
default_pool_size = 20           # With only 20 real DB connections
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600

-- ❌ BAD — prevents index use on email
SELECT * FROM users WHERE LOWER(email) = 'bipin@example.com';

-- ✅ GOOD — use expression index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'bipin@example.com';

-- ✅ EVEN BETTER — enforce lowercase at insert time
ALTER TABLE users ADD CONSTRAINT email_lowercase
  CHECK (email = LOWER(email));

-- ❌ BAD — OFFSET 100000 still reads 100000 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 100000;

-- ✅ GOOD — Keyset/cursor pagination
SELECT * FROM posts
WHERE created_at < '2026-08-01T12:00:00Z'  -- cursor from last page
ORDER BY created_at DESC
LIMIT 10;

-- ❌ BAD — fetches all columns including large TEXT/JSONB fields
SELECT * FROM posts WHERE id = $1;

-- ✅ GOOD — only fetch what you need
SELECT id, title, excerpt, published_at, author FROM posts WHERE id = $1;