Things you didn't know about indexes

The textbook trick

It has been reported that a recent Lobsters-linked blog post uses a Pokémon table to do what good tech explainers do best: make something abstract suddenly obvious. Think back to the index in your science textbook — sorted topics, page numbers, quick lookup. Databases do the same thing. With an index on name, Postgres stores a B-tree and can binary-search for "Pikachu" instead of scanning ten million rows. Who doesn’t love a good analogy? Short, sharp, and instantly comforting.

The cost of the convenience

Indexes speed reads and slow writes. Insert a new Pokémon and the database must find the right slot in every relevant index and insert it there. Update or delete? Same story. They also live on disk and compete for cache — eight indexes means nine things to keep warm, not one. And don’t forget the planner: more indexes, more options, more planning time; sometimes planning can cost more than the query itself. The thrill of speed can quickly curdle into frustration. It’s a double-edged sword.

When indexes disappoint

So you add an index and—surprise—not all queries get faster. Composite indexes are ordered: (type_1, type_2) helps queries on type_1 and on type_1+type_2, but won’t magically speed up searches on type_2 alone. And wrapping a column in a function? Lower(name) is not the same as name. If you run WHERE lower(name) = 'pikachu' the database can’t use an index on name; it will scan and lowercase every row, painfully. Why isn’t it working? Because indexes are literal about what they index. The takeaway: indexing is a craft, not a checkbox — know your queries, and build the index the query can actually use.

Sources: jon.chrt.dev, Lobsters