Day 10: Indexes and Performance Optimization
Welcome to Day 10! ⚡
Today we’ll supercharge your PostgreSQL skills with indexing strategies and practical performance tuning.
What You'll Learn
- How indexes work (B-tree, Hash, GIN, GiST, BRIN)
- Creating and using indexes effectively
- Covering indexes and partial indexes
- Multi-column indexes and ordering
- Query planning with EXPLAIN/EXPLAIN ANALYZE
- Vacuuming, analyze, and autovacuum
1) How Indexes Work
- Indexes speed up reads by maintaining searchable structures
- Trade-off: faster SELECTs, slower writes (INSERT/UPDATE/DELETE)
- Common types:
- B-tree (default): equality, range
- Hash: equality only
- GIN: arrays, JSONB, full-text
- GiST: geometric, similarity
- BRIN: large, naturally ordered data
2) Creating Indexes
-- Basic B-tree index
CREATE INDEX idx_users_email ON users(email);
-- Unique index (enforces constraint)
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- Partial index (only active users)
CREATE INDEX idx_users_active_email ON users(email) WHERE active = true;
-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Multi-column index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
3) Using Indexes Effectively
- Put most selective column first in multi-column indexes
- Match index order with query predicates and ORDER BY
- Use partial indexes when a predicate is common and stable
- Use expression indexes to support computed predicates
-- Supports WHERE LOWER(email) = 'x'
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Supports WHERE active = true AND last_login > now()-interval '30 days'
CREATE INDEX idx_users_active_recent
ON users(last_login) WHERE active = true;
4) Covering Indexes (INCLUDE)
-- B-tree with INCLUDE for covering queries (Postgres 11+)
CREATE INDEX idx_orders_lookup
ON orders(customer_id, order_date) INCLUDE (status, total_amount);
-- SELECT can be satisfied entirely from the index (fewer heap hits)
5) ORDER BY and Multi-Column Strategy
-- This index helps both filter and sort
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);
-- Order of columns matters for index usage
-- WHERE customer_id = ? ORDER BY order_date DESC
6) Inspecting Plans with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY order_date DESC LIMIT 10;
Look for: Index Scan vs Seq Scan, Sort vs Index Scan, rows estimates vs actual.
7) Maintenance: VACUUM and ANALYZE
- VACUUM reclaims space from dead tuples
- ANALYZE updates planner statistics
- Autovacuum handles this automatically, but tune for heavy workloads
VACUUM (VERBOSE, ANALYZE) orders;
8) Practical Patterns
-- JSONB key lookup with GIN
CREATE INDEX idx_events_data_gin ON events USING GIN (data jsonb_path_ops);
SELECT * FROM events WHERE data ? 'user_id';
-- Full-text search
CREATE INDEX idx_posts_fts ON posts USING GIN (to_tsvector('english', title || ' ' || body));
SELECT * FROM posts WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('postgresql performance');
-- Array containment
CREATE INDEX idx_products_tags ON products USING GIN (tags);
SELECT * FROM products WHERE tags @> ARRAY['featured'];
Performance Checklist
- Add indexes for frequent filters/joins
- Avoid over-indexing; each index costs writes + storage
- Verify with EXPLAIN ANALYZE before/after
- Consider partitioning for very large tables
- Keep stats fresh (ANALYZE) and avoid bloat (VACUUM)
Today's Challenge 🎯
1) Propose indexes to speed up:
- SELECT * FROM orders WHERE customer_id = ? AND status = 'paid' ORDER BY created_at DESC LIMIT 20 2) Create an index strategy for searching blog posts by tags and text 3) Show EXPLAIN (ANALYZE) output improvements for one query after indexing
-- Your solutions here
Summary
You learned index types, creation patterns, and how to use EXPLAIN to verify improvements.
Coming Up Next
Day 11: Stored Procedures and Functions — encapsulate logic inside the database.
Top comments (0)