Stop 06 · Databases (SQL & NoSQL)Topics · 7/8

Indexes & Query Performance

Part of the Full Stack Developer Roadmap

In short

Your API was fast with 100 rows. At 10 lakh rows the same endpoint takes three seconds. Usually the fix isn't a bigger server, it's one well-placed index.

Adding indexes to frequently queried fields to keep your API fast as your data grows — a small effort with a huge payoff on read-heavy endpoints.

The mascot stands with a society guard checking a sorted list on a clipboard.

Think of it like this

The society guard's flat list

A delivery boy asks for Mr Sharma. A new guard walks building to building, door to door. The experienced guard checks his sorted list and says 'C-Wing, 402' in two seconds. An index is that list: extra work to maintain, huge time saved on lookups.

Watch it happen

Finding 'rohan' with an index

aman

bina

dev

isha

kabir

meera

neha

rohan

sara

zoya

1/5Without an index, Postgres reads every row to find WHERE name = 'rohan'. That's a Seq Scan.

The big ideas

What you need to know

Index what you filter and sort

Columns in WHERE, JOIN and ORDER BY on busy endpoints are the candidates. Primary keys get one automatically.

Column order matters

An index on (user_id, created_at) helps 'orders for user 42, newest first'. It doesn't help a filter on created_at alone.

Indexes cost writes

Every INSERT and UPDATE must update each index too. Add them for real queries, not just in case.

EXPLAIN ANALYZE tells the truth

It shows whether Postgres used an Index Scan or a Seq Scan, and how long each step took.

See it in code

Walk through the code

sql

1-- Slow: Postgres reads every row (Seq Scan)2EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;3 4-- One composite index matches both the filter and the sort5CREATE INDEX CONCURRENTLY orders_user_created_idx ON orders (user_id, created_at DESC);6 7-- Run again: the plan should now show an Index Scan on orders_user_created_idx8EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;9 10-- UNIQUE constraints create an index for you11ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

1/4Measure first. EXPLAIN ANALYZE runs the query and prints the plan with real timings.

This is the query behind a 'My orders' page. CONCURRENTLY builds the index without locking out writes on a live table.

Save yourself the pain

Common mistakes

Oops

Adding an index to every column 'to be safe'.

Do this instead

Index the columns your slow queries actually use. Extra indexes slow down writes and eat disk.

Oops

Testing on a table with 50 rows and concluding the index does nothing.

Do this instead

Postgres often skips indexes on tiny tables because a full scan is cheaper. Test with realistic data volume.

Your mission

Make a slow query fast

~30 min

Quick check

You have an index on (user_id, created_at). Which query benefits most?

Go deeper

1Add an index to a field your API filters or sorts by frequently.
2Compare query performance with and without the index using `EXPLAIN`.
3Learn indexing applies in both SQL and MongoDB, with similar underlying trade-offs.