Database indexes I got wrong

> An index is free speed, I thought. Then came write overhead, the redundant index, and the composite that was never used.

dateNov 11, 2025

read7 m · read

tagsPostgreSQLperformanceSQL

For a long time I thought an index was pure win: add it, things get faster. Reality is more nuanced, and I paid dearly for my mistakes.

##Too many indexes

Every index slows down writes and takes space. I had a table with seven indexes, of which the query planner used two. The rest was pure overhead.

##Column order in a composite index

sql

-- bad: status has low selectivity
CREATE INDEX ON orders (status, user_id);
-- good: the more selective column first
CREATE INDEX ON orders (user_id, status);

Column order in a composite index matters. Put the more selective column first, or the planner will often just ignore it.

Lesson: indexes need maintenance too. Check pg_stat_user_indexes now and then, and drop what no one uses.