SESKStuff Everyone Should Know
SESK: Stuff Everyone Should Know · sesk.info/tech-index-tradeoff

Database indexes speed up searches. What is their cost?

Extra work on writes, because every index must stay synchronized.

Start playing free

  1. They make all queries slower
  2. They add overhead to data changes ✔
  3. They delete old rows

Why: An index works like the index at the back of a book, letting the database jump to matching rows. But every insert, update and delete must also update each index, so adding indexes everywhere backfires.

Huh, didn't know that: PostgreSQL automatically creates a unique B-tree index for a declared primary key.

The dad joke
I added an index to my diary. Now I can find my bad decisions in O(log n).
Like this card?
Play the game

References

Answer: They add overhead to data changes. An index works like the index at the back of a book, letting the database jump to matching rows. But every insert, update and delete must also update each index, so adding indexes everywhere backfires.

  1. PostgreSQL Documentation: Indexes www.postgresql.org/docs/current/indexes.html
    But indexes also add overhead to the database system as a whole, so they should be used sensibly.
    Checked 2026-10-10.

Think this answer is wrong? Tap "Challenge this answer" on the card in the game and tell us why.

Test yourself in the game

Tech & GadgetsIn a relational database, what two rules does a primary key enforce?Tech & GadgetsSQL is called a declarative language. What does that mean?Tech & GadgetsA bank transfer takes money from Alice and gives it to Bob. Why wrap it in a transaction?

That's stuff everyone should know.