Hi Pythonistas!
You have a users table.
100 million rows.
SELECT * FROM users WHERE email = 'afsal@example.com';Without any special structure:database starts at row 1.Checks every row.
Is this the email? No. Next. No. Next...100 million times.
This is called a full table scan. Takes seconds. Maybe minutes.
With an index
database jumps directly to the row.1 lookup.Microseconds. That's what indexing does.
What Is an Index?
A separate data structure.Maintained alongside your table.
Contains:
the indexed column values.
pointers to the actual rows.
Index on email:
email | row pointer
----------------------|------------
afsal@example.com | → row 1
arjun@example.com | → row 45
priya@example.com | → row 892
rahul@example.com | → row 2341- Sorted.
- Searchable in O(log n) Instead of O(n) full scan.
The Real Data Structure - B-Tree
Most database indexes use a B-Tree.
[M]
/ \
[D, H] [R, V]
/ | \ / | \
[A] [E] [J] [N] [S] [W]- Each node holds multiple keys.
- Sorted order maintained.
- All leaf nodes at same depth.
Why B-Tree?
Databases store data on disk.
Disk reads are slow.
B-Tree minimizes disk reads.
Each node = one disk page (8KB or 16KB).
One read → many keys checked.
How B-Tree Search Works
Find email = 'priya@example.com':
Start at root: [M]
priya > M → go right → [R, V]
priya < R → go left → [N]
priya < N → go left → leaf: [priya → row 892]
Found. Jump to row 892.
Instead of scanning 100 million rows:
3-4 lookups.
O(log n).
log2(100,000,000) ≈ 27 steps
vs
100,000,000 steps without index
Creating an Index
-- single column
CREATE INDEX idx_users_email ON users(email);
-- multiple columns
CREATE INDEX idx_name_email ON users(name, email);
-- unique index
CREATE UNIQUE INDEX idx_email ON users(email);
-- partial index
CREATE INDEX idx_active ON users(email)
WHERE active = true;
PostgreSQL automatically creates indexes for:
PRIMARY KEY → always indexed
UNIQUE → always indexed
Everything else → you decide.
Types of Indexes
Single Column
CREATE INDEX ON users(email);
WHERE email = 'afsal@...' → uses index ✅
WHERE name = 'Afsal' → does NOT use index ❌
Composite
Index on multiple columns.
CREATE INDEX ON users(name, email);
Order matters.Leftmost prefix rule: Index usable from leftmost column only.
WHERE name = 'Afsal' → uses index ✅
WHERE name = 'Afsal' AND email... → uses index ✅
WHERE email = 'afsal@...' → does NOT use index ❌Covering
Index contains all columns the query needs.
No need to touch the actual table.
CREATE INDEX ON users(email, name, created_at);
SELECT name, created_at FROM users WHERE email = 'afsal@...';
-- all columns in index
-- never touches table
-- extremely fast ✅Partial
Index only a subset of rows.
CREATE INDEX ON users(email) WHERE active = true;Smaller index.
Faster for queries on active users.
Full-Text
For searching inside text content.
CREATE INDEX ON posts USING GIN(to_tsvector('english', content));
SELECT * FROM posts
WHERE to_tsvector('english', content) @@ to_tsquery('system & design');Inverted index under the hood.
Maps words → documents containing them.
Index Type Selection
Situation Index Type
─────────────────────────────────────────────
Default / most cases B-Tree
Equality + range + sort B-Tree
Exact equality only Hash
Full-text search GIN
Array contains GIN
JSONB column queries GIN
Geographic queries GiSTThe Cost of Indexes
Indexes are not free.
Write slowdown
INSERT a new user:
1. Write to users table
2. Update email index
3. Update name index
4. Update created_at index
More indexes = slower writes.
Storage
Index is a separate data structure.
Takes disk space.
Large table with many indexes:
index size can equal table size.
Memory
Active indexes loaded into memory.
Too many indexes = memory pressure.
More disk reads.
Slower overall.
More indexes → faster reads, slower writes, more storage
Fewer indexes → slower reads, faster writes, less storage
Selectivity
Not all indexes are equally useful.
Selectivity = how unique are the values?
High selectivity → good index:
email → almost every value unique ✅
user_id → every value unique ✅
Low selectivity → poor index:
gender → only 2-3 values ❌
active → only true/false ❌
country → ~200 values ⚠️
EXPLAIN - See If Index Is Used
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'afsal@example.com';
#Without index:
Seq Scan on users
Filter: (email = 'afsal@example.com')
Rows Removed by Filter: 99999999
Seq Scan = full table scan. Bad.
#With index:
Index Scan using idx_users_email on users
Index Cond: (email = 'afsal@example.com')
Index Scan = index used. Good.
What to look for
Good signs:
Index Scan → index used ✅
Index Only Scan → covering index ✅ (fastest)
Bitmap Index Scan → multiple indexes combined ✅
Bad signs:
Seq Scan → full table scan ❌
rows=10000000 → scanning too many rows ❌
Filter: ... → filtering after scan ❌
Common Mistakes and Fixes
Mistake Fix
──────────────────────────────────────────────────────────
Too many indexes Only index queried columns
Remove unused indexes
Function on indexed column Function-based index
WHERE LOWER(email) = '...' CREATE INDEX ON users(LOWER(email))
Type mismatch Match types in query
WHERE phone = 9876543210 WHERE phone = '9876543210'
(phone is VARCHAR)
Wrong composite order Most selective column first
(status, user_id) Use (user_id, status)
Low cardinality alone Combine with high cardinality
WHERE active = true (user_id, active) or partial index
Missing FK index Always index foreign keys
posts.user_id → users.id CREATE INDEX ON posts(user_id)
Query Pattern → Index Pattern
Query Pattern Index to Create
──────────────────────────────────────────────────────────────
WHERE email = '...' (email)
WHERE user_id = 1 ORDER BY created_at (user_id, created_at)
WHERE status = 'active' AND user_id = 1 (user_id, status)
WHERE name LIKE 'Afsal%' (name) B-Tree works
WHERE name LIKE '%afsal%' Full-text GIN
WHERE tags @> ARRAY['python'] GIN on tags
WHERE data->>'city' = 'Kerala' GIN on JSONB
SELECT name,email WHERE email = '...' (email, name) coveringProduction Index Creation
sql
-- NEVER do this in production (locks table)
CREATE INDEX ON users(email);
-- ALWAYS do this in production (no lock)
CREATE INDEX CONCURRENTLY ON users(email);
CONCURRENTLY takes longer. But doesn't block reads or writes. Critical in production.
Index Maintenance
-- find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- find unused indexes
SELECT indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE 'pg_%';
-- rebuild bloated index
REINDEX INDEX CONCURRENTLY idx_users_email;
-- check index usage
SELECT indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename = 'users';
The 5-Step Indexing Process
Step 1: Find slow queries
pg_stat_statements → sort by mean_exec_time
Step 2: Run EXPLAIN ANALYZE
Seq Scan? Needs index.
Step 3: Identify the right index
What column in WHERE/JOIN/ORDER BY?
High selectivity?
Single or composite?
Step 4: Create the index
CREATE INDEX CONCURRENTLY
(doesn't lock table)
Step 5: Verify and monitor
Run EXPLAIN again
Index Scan now?
Check write performance
Quick Reference
Fast lookup by email/id? → Single column index
Fast lookup by user + date? → Composite (user_id, date)
All query columns in index? → Covering index
Search text content? → GIN full-text index
Query arrays or JSONB? → GIN index
Geo queries? → GiST index
Index subset of rows? → Partial index
Query still slow after index? → Run EXPLAIN ANALYZE
Check leftmost prefix rule
Check type mismatch
Check function on column
Mental Model
Index → separate structure for fast lookup
Full scan → check every row, O(n), slow
Index scan → jump directly, O(log n), fast
B-Tree → default, works for most cases
Composite → multiple columns, leftmost rule
Covering → all columns in index, fastest
Partial → index subset of rows
Selectivity → high cardinality = good index
EXPLAIN → shows if index used
Write cost → every write updates all indexes
CONCURRENTLY → create index without locking table
GIN → full-text, arrays, JSONB
GiST → geographic data
What Changed for Me
Before this:
I added indexes randomly.Hoping they'd help.
After this:
I look at the query first.
Understand the access pattern.
Choose the right index type.
Verify with EXPLAIN.
Monitor write performance.
Indexing is not guessing.
It's a systematic process.
What's Coming Next
Now you know how databases store and find data.
But what about data you don't structure at all?
Images. Videos. PDFs. Audio files.
How do systems store and serve billions of files?
Object Storage and Blob Storage.
How S3 works.
How files travel from your phone to a server
and back to someone else's screen.
