Indexes and Their Tradeoffs

Build the mental model

An index helps the database find rows quickly without scanning the whole table - like a book's index, jumping straight to the right page.

Indexes are a tradeoff: they speed up reads, but cost extra storage, and every write has to update the index too, making writes slower.

• columns frequently used in WHERE filters
• columns used in joins
• columns used in ORDER BY sorting
• columns needing fast unique lookups (e.g. email)
• don't index every column

A unique index enforces that no two rows share a value while speeding up lookups. A composite index covers multiple columns, and column order changes which queries it helps.

The N+1 query problem: fetching 100 posts then running 100 separate author queries is often far slower than one join fetching everything together. The SQL and PostgreSQL tutorials cover index syntax and EXPLAIN hands-on.

Index

A separate data structure the database maintains to find rows quickly based on one or more columns, without scanning the whole table.

N+1 Query Problem

A pattern where fetching a list (100 posts) is followed by one separate query per item for related data (author), making it far slower than a single join or batch.

INDEX LOOKUP VS FULL SCAN, AND N+1
----------------------------------
WITHOUT INDEX (linear scan)
row1 -> row2 -> row3 -> ... -> row9999 -> MATCH
checks potentially every row until found

WITH INDEX (direct lookup)
index[key] -> MATCH
one direct jump to the row, like a book's index

N+1 PROBLEM
1 query:     SELECT * FROM posts;          (100 rows)
100 queries: SELECT * FROM authors WHERE id=? (x100)
BETTER: 1 query with a JOIN, or 1 batched IN (...)

Connect it to a real scenario

Add an index once you notice a slow query repeatedly filtering, joining, or sorting on the same column - not preemptively on every column.

Tools like PostgreSQL's EXPLAIN exist exactly to inspect which indexes exist and how the query planner uses them.

Watch for N+1 patterns in loops - a query running once per item is usually a sign to batch into a single join or query instead.

Before adding an index, ask whether the write overhead is worth it for that table's read/write ratio. The runnable example below shows the search difference with and without an index.

• An index is a tradeoff - faster reads at the cost of extra storage and slower writes
• Index columns that are frequently used in filters, joins, sorting, or unique lookups - not every column
• The N+1 query problem means running one query per item instead of a single join or batch, and it's usually much slower

Try the working example

function linearSearch(rows, field, value) {
  let comparisons = 0;
  let found = null;
  for (const r of rows) {
    comparisons++;
    if (r[field] === value) {
      found = r;
      break;
    }
  }
  return { found, comparisons };
}

function buildIndex(rows, field) {
  const map = new Map();
  for (const r of rows) map.set(r[field], r);
  return map;
}

function indexedSearch(index, value) {
  return { found: index.get(value) || null, comparisons: 1 };
}

const bigTable = [];
for (let i = 1; i <= 5000; i++) {
  bigTable.push({ id: i, email: `user${i}@example.com` });
}
const target = "user4999@example.com";

const withoutIndex = linearSearch(bigTable, "email", target);
const emailIndex = buildIndex(bigTable, "email");
const withIndex = indexedSearch(emailIndex, target);

console.log("Without index comparisons:", withoutIndex.comparisons);
console.log("With index comparisons:", withIndex.comparisons);

Without index comparisons: 4999
With index comparisons: 1

Searching for the 4999th row out of 5000 takes 4999 comparisons with a linear scan, but only 1 lookup using a Map-based index.

Wikipedia: Database index — How Databases Work