These are study notes from working through PostgreSQL performance patterns. I use Postgres in production (SlotTurf via Supabase, Advor.ai) but haven't applied all of this yet some is ahead of my current scale. Marking it here so I can find it when I need it.

Index Types

B-Tree is the default and covers most cases equality checks, range queries, sorting.

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_bookings_slot ON bookings(slot_id, created_at DESC);

GIN for array columns, JSONB, and full-text search:

CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
CREATE INDEX idx_events_metadata ON events USING GIN(metadata jsonb_path_ops);

Partial indexes index only rows matching a condition smaller index, faster scans:

-- Only index active users, not the full table
CREATE INDEX idx_active_users_email ON users(email)
WHERE status = 'active';

EXPLAIN ANALYZE

Always use EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN. The plain version shows the planner's estimate; ANALYZE shows what actually happened.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM bookings
WHERE slot_id = 42
AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

What to look for in the output:

• Seq Scan on a large table probably missing an index
• High "Rows Removed by Filter" the index exists but isn't selective enough
• Sort node check if an index on the ORDER BY column can eliminate it
• Nested Loop on large sets may want a Hash Join or Merge Join instead

Connection Pooling

At production scale, opening a new database connection per request is expensive. Supabase handles pooling internally via PgBouncer (transaction mode by default), so for projects using Supabase this is managed for you. On a raw Postgres instance you'd configure this yourself.

Key settings if you're managing it:

• Keep max_connections low (100-200). More isn't better each connection uses memory.
• Transaction mode pooling for web apps connections return to the pool between transactions.
• Set idle_in_transaction_session_timeout to kill connections that hold a transaction open without doing work.

Rules I'm Keeping Track Of

• Foreign keys need indexes. Postgres doesn't create them automatically.
• Composite indexes: put the most selective column first.
• EXPLAIN ANALYZE before adding an index, not after verify the planner is doing what you think.
• pg_stat_statements extension tracks slow queries in production. Worth enabling early.