Skip to content
Prompt Words
History

Database index

Also called: index, B-tree index, secondary index.

A sorted copy of one or more columns, each entry pointing to its row, that the database keeps next to a table. A query that filters on that column can jump to the matching rows instead of reading the whole table. Each insert and update also has to update the index, so add indexes for the queries you actually run.

Find one user by email in a 12-row table, first without an index, then with one.

Table rows read
–
Index steps
–

No index on email yet. Postgres can only read the table row by row.

    No index

    Say it in a prompt

    Add a Postgres index on users(email) with CREATE INDEX CONCURRENTLY users_email_idx, so the login query stops scanning the whole table. Show EXPLAIN ANALYZE for SELECT * FROM users WHERE email = $1 before and after (Seq Scan vs Index Scan, rows read, time), and add a unique constraint if emails must be unique.

    Vague vs precise prompt

    Vague prompt

    login is slow, speed up the database

    Typical resultSuggests a bigger database server or a cache in front of the users table. Every login still reads every row, so it slows down again as users sign up.

    Precise prompt

    Login runs SELECT * FROM users WHERE email = $1 and EXPLAIN shows a Seq Scan. Add CREATE INDEX CONCURRENTLY users_email_idx ON users (email) and show EXPLAIN ANALYZE before and after.

    Typical resultThe plan changes from Seq Scan to Index Scan: 1 row read instead of the whole table, and login stays fast at 1,000,000 users.

    Seen on

    • PostgreSQL documentation: Without an index the database has to scan the whole table row by row; with one it can walk a few levels down a search tree. Shows CREATE INDEX test1_id_index ON test1 (id).
    • Use The Index, Luke: A free book on SQL indexing. Explains the B-tree and that real indexes with millions of rows are only four or five levels deep.

    You might describe it as

    • make the database find the row without reading everything
    • the query gets slower as the table grows
    • look up users by email fast

    Not to be confused with

    • Read replica

      An index helps one database find rows faster; a replica adds copies so more reads can run at once.