Secondary Indexes and Index Trade-offs

Hash, B-tree, composite, covering and inverted indexes: what each can answer, where it points, and what it costs every write.

Overview

An index is a second copy of some of your data, sorted or hashed so a query can find rows without reading the whole table. It is derived data: the database can always rebuild it from the table. That is what makes it a trade. Every index speeds up some reads and slows down every write that has to keep it up to date.

The cost of an index

A table with no index is cheap to write and slow to search. Each index added makes the reverse true for the queries it serves.

INSERT — One more index entry to write
DELETE — Entries to clean up (PostgreSQL does this later, in vacuum)
UPDATE of an indexed column — A new index entry
UPDATE of other columns — In PostgreSQL, often no index work at all, thanks to HOT updates, if the new row version fits on the same page
SELECT — Faster, but only for queries the index can serve

So index the columns that appear in frequent filters, joins and sort orders, and nothing else.

Hash indexes

A hash index maps each key to where its row lives. It answers 'find the row with this exact key' in one step, and nothing else: hashing scatters neighboring keys, so it cannot answer a range or sort.

Bitcask, the storage engine written for Riak, is the clean example. It appends every write to a log file and keeps an in-memory hash table, the keydir, from every key to the file, offset and size of its latest value. A read is one hash lookup and at most one disk seek. The price is that the keydir must fit entirely in RAM. PostgreSQL also has hash indexes, which store a 32-bit hash of the value and support only equality.

Secondary B-tree indexes

A secondary index is an index on a column that is not the primary key, such as email or created_at. Many rows can share a value, so each entry has to say which row it belongs to. Engines differ in how.

PostgreSQL — A row's physical location in the heap, the unordered table file — Index descent, then a heap page read
MySQL InnoDB — The row's primary key value — Secondary index descent, then a second descent of the clustered (primary key) index

In InnoDB the table itself is a B-tree ordered by primary key, called the clustered index. Reading by primary key lands directly on the row. InnoDB's docs advise keeping primary keys short because every secondary index stores a copy.

Covering indexes

If an index holds every column a query needs, the database can answer from the index alone and skip the table. PostgreSQL calls this an index-only scan, and the INCLUDE clause adds extra payload columns that are stored but not searched on.

2026-01-02 00:00:00 — Welcome
2026-01-05 00:00:00 — Invoice

Every column in that query is in the index, so it can be an index-only scan. One PostgreSQL detail: the index does not know whether a row version is visible to your transaction. The planner checks the visibility map, and only pages marked all-visible avoid a heap visit. On a table with many recent changes, an index-only scan can still read the heap.

Composite indexes and column order

A composite index is sorted by its first column, then by the second within each value of the first, like a phone book sorted by last name, then first name. That order decides which queries it helps.

WHERE user_id = 7 — Yes: one contiguous range
WHERE user_id = 7 AND created_at > X — Yes: one range, already sorted by time
WHERE user_id = 7 ORDER BY created_at — Yes, and no sort step is needed
WHERE created_at > X — Not directly: matching rows are spread across every user

PostgreSQL's rule: equality conditions on the leading columns, plus a range condition on the first column without an equality, limit the part of the index that is read. Since PostgreSQL 18 there is one exception, skip scan: if the leading column has only a few distinct values, the planner can search once per value and still use the index for a condition on a later column.

Inverted indexes for text

A B-tree sorts whole values, so it can find LIKE 'abc%' but not a word in the middle of a text. For that, an inverted index maps each word to the list of rows that contain it, like the index at the back of a book. PostgreSQL's GIN indexes work this way and are its preferred type for full-text search.

Hash — Exact match — Ranges, sorting
B-tree — Exact match, ranges, sorting, prefix LIKE — Words inside text
Inverted (GIN) — Words in text, array and JSON containment — Ranges and sorting on the column
BRIN — Ranges on huge tables whose values follow the physical row order, such as time — Precise lookups on unordered data

What you will be able to do

Say what each extra index costs on writes, and when an update in PostgreSQL avoids it.
Explain where a secondary index points in PostgreSQL and in InnoDB.
Design a composite index for a query and explain why the column order matters.
Explain covering indexes and when an index-only scan still reads the table.
Pick hash, B-tree, inverted or BRIN indexes for a given query.

Key concepts

Secondary index: An index on a column other than the primary key. Many rows can share a value, so each entry also identifies its row.
Clustered index: An index that stores the rows themselves in key order. Every InnoDB table is a clustered index on its primary key.
Heap: An unordered table file, as in PostgreSQL, where every index points to row locations.
Covering index: An index that contains every column a query needs, so the query can skip the table. PostgreSQL adds payload columns with INCLUDE.
Composite index: An index on several columns, sorted by the first, then the second. Most useful when the query constrains the leading columns.
Hash index: A map from a hash of the key to the row. Exact matches only; no ranges or sorting.
Inverted index: A map from each word or element to the rows that contain it. The basis of full-text search, and of PostgreSQL GIN indexes.
HOT update: A PostgreSQL update that changes no indexed column and fits on the same page, so no new index entries are written.

When to use it

Choosing indexes for a schema in a design or database interview
Explaining why a query does not use an index you expected it to use
Discussing the write cost of adding one more index
Choosing between a B-tree and an inverted index for search features

Interview questions

Which index would you add for a query that filters on user_id and a created_at range, sorted by created_at?
How does a secondary index lookup differ between PostgreSQL and MySQL InnoDB?
Why does every index slow down writes, and how do you decide how many to keep?
When will the planner ignore an index and read the whole table?
How would you support search for words inside a text column?