Nobody talks about the leftmost prefix rule until their database grinds to a halt.
You add a composite index on (status, user_id, created_at).
You run your query, assuming you’re covered.
But your WHERE clause filters on user_id and created_at. Not status.
The index is right there. PostgreSQL ignores it completely.
Full table scan. On 50 million rows.
A B-Tree index on (A, B, C) serves queries on A, A+B, or A+B+C.
It does nothing for B alone, C alone, or B+C.
The columns must match from the left. Skip one, and the index is invisible.
MySQL, Oracle, SQL Server — they all follow the exact same rule.
And none of them warn you when your query bypasses the index.
COLUMN ORDER MATTERS
Putting a low-cardinality column first means the index barely narrows anything.
If status only has 3 possible values, the database still scans millions of rows after the first filter.
Flip it. Put the high-cardinality column first.
A composite index on (user_id, status, created_at) lets the engine jump straight to a handful of rows.
The difference on a 100M row table: from minutes to under 100ms.
THE WRITE PENALTY
Every index you add is another B-Tree that every INSERT and UPDATE must maintain.
A table with 15 indexes means every single write updates 15 separate data structures.
A team once added an index to speed up a read.
Their API response time went from 200ms to over 2 seconds.
The index fixed the read but destroyed the write throughput.
The solution isn’t adding more indexes. It’s adding the right ones.
Run EXPLAIN ANALYZE on your slowest queries.
Verify your column order matches your access patterns.
What’s the worst indexing mistake you’ve caught in production?
#SystemDesign #PostgreSQL #BackendEngineering #DatabaseOptimization

You can share you thought with this squad 

Backend Developer 

The leftmost prefix rule in B-Tree indexes means a composite index on (status, user_id, created_at) is completely ignored when your WHERE clause filters on user_id and created_at without status. Column order in composite indexes matters enormously: placing high-cardinality columns first lets the engine skip to a small subset of rows, potentially reducing query time from minutes to under 100ms on large tables. There's also a write penalty to consider — each additional index adds overhead to every INSERT and UPDATE, and over-indexing can turn a 200ms API response into 2+ seconds. The recommended approach is to run EXPLAIN ANALYZE on slow queries and verify column order matches actual access patterns.

How to build the right database index