PostgreSQL Indexing & Query Optimization
02 // Simple Explanation (Mental Model)
An index is like a book's index at the back. Instead of scanning every single page (Sequential Scan), the database looks up the keyword in the index to jump directly to the exact row (Index Scan).
03 // Technical Deep Dive & Internals
PostgreSQL defaults to B-Tree indexes, which maintain sorted balanced trees allowing O(log N) searches, range queries, and sorting operations. For large tables, missing indexes result in full table sequential scans that cause disk I/O bottlenecks.
-- Bad: scans millions of rows
SELECT * FROM job_applications WHERE user_id = 'usr_123' AND status = 'INTERVIEW_SCHEDULED';
-- Optimal Index:
CREATE INDEX idx_apps_user_status ON job_applications(user_id, status);
-- Verify with EXPLAIN ANALYZE:
EXPLAIN ANALYZE SELECT * FROM job_applications WHERE user_id = 'usr_123' AND status = 'INTERVIEW_SCHEDULED';05 // Real-World Production Architecture
Speeding up dashboard metric queries, user-specific data filtering, and foreign key join lookups from 1500ms down to 2ms.
06 // Common Pitfalls & Mistakes to Avoid
Over-indexing write-heavy tables (slowing INSERT/UPDATE/DELETE), or indexing low-cardinality columns (like boolean flags).
09 // Quick Revision Notes (Pre-Interview Spec Sheet)
B-Tree for equality and range queries. Left-prefix rule applies to composite indexes (colA, colB).