Topic: PostgreSQL

Indexes follow query shape, not a column checklist

A PostgreSQL index earns its cost only when a real query's filters, ordering, result size, and write workload justify another maintained data structure.

When a query gets slow, it is tempting to scan a table’s columns and add an index to every one that looks important. That is like filing every book under every possible category, then discovering readers use only two of them.

An index is not a free speed button. PostgreSQL maintains it during INSERT, UPDATE, and DELETE; it also consumes storage and cache. Keep one when a frequent query can avoid reading many rows or return a small result in the needed order.

Write the query before naming the index

Suppose an author dashboard often loads the 20 most recent published articles for one author:

SELECT id, title, created_at
FROM articles
WHERE author_id = $1
  AND status = 'published'
ORDER BY created_at DESC
LIMIT 20;

This query shape has three signals: author_id and status are equality filters, created_at supplies the order, and LIMIT 20 asks for very few results. A composite B-tree such as (author_id, status, created_at DESC) answers that use case. It is not a label attached independently to three columns.

PostgreSQL B-trees support equality, range conditions, and ordering. For a multicolumn B-tree, leading columns usually matter most: put equality conditions first, then the first column that needs a range or ordering role. A query that uses only created_at does not automatically benefit. That is another workload, and an independent index needs evidence from its frequency and plan.

An existing index does not force the planner to use it. When a query returns most table rows, a sequential scan can cost less. An index should make a specific read avoid unnecessary work.

A composite index is not two single-column indexes glued together

With separate (team_id) and (priority) indexes, WHERE team_id = $1 AND priority = 'high' can use a bitmap AND path that intersects both results. That can be useful when each condition also serves other queries.

However, bitmap paths normally visit table rows in physical location order. They do not preserve either index’s ordering. They are not equivalent to (team_id, priority, created_at DESC). If a hot screen needs both filters, newest-first ordering, and a small LIMIT, a targeted composite B-tree can be a closer fit.

The opposite mistake is creating an index for every rare column combination. List frequent queries first, then compare read benefit with write cost. That is usually less work than trying to cover every permutation.

Other index types have narrow jobs

Some query shapes do not fit a general B-tree, but each alternative has a condition.

• A partial index can keep an index small when failed payments are only 1% of orders, for example with WHERE payment_status = 'failed'. The query must state or imply that condition. IN ('failed', 'paid') does not establish the same premise.
• An expression index should match a stable expression used by the query, such as lower(email) for WHERE lower(email) = $1, not the original email. Index expressions and predicates also have immutability constraints.
• GIN suits element and containment lookups in jsonb or arrays. It does not replace a general ordering index.
• BRIN suits a large append table whose time or sequence value closely follows physical block order. It stays small by ruling out impossible blocks, not by navigating precisely to every row like a B-tree.

INCLUDE has a narrower job too. It can store values needed only in SELECT as B-tree payload so a qualifying query may avoid revisiting the table. It is not a WHERE or ORDER BY search key. Adding wide fields indiscriminately still enlarges the index and its write cost.

Creating an index is also a write decision

On a large live table, ordinary CREATE INDEX blocks writes. CREATE INDEX CONCURRENTLY can avoid that write block, but it takes longer and has its own constraints. It changes deployment risk and rollout time.

Before adding an index, identify the query’s filters, ordering, return ratio, frequency, and write workload. After adding it, validate with the actual plan and latency. The next EXPLAIN lesson will unpack why the database chose a path. For now, keep one rule: indexes serve observed queries, not column checklists.

What I learned

• I can write down frequent query filters, ordering, and LIMIT before choosing key order, rather than adding an index for each column.
• B-tree, partial, expression, GIN, and BRIN indexes serve different data shapes. The query’s real need comes before the index type.
• Read benefit must be compared with storage, cache, and write-maintenance cost. A sequential scan is not a failure when the planner expects to return many rows.
• INCLUDE adds return values, not search keys. A composite index and a bitmap combination do not provide the same ordering ability.

External references

• PostgreSQL: Indexes Introduction
• PostgreSQL: Index Types
• PostgreSQL: Multicolumn Indexes
• PostgreSQL: Indexes and ORDER BY
• PostgreSQL: Partial Indexes
• PostgreSQL: Combining Multiple Indexes
• Reddit: PostgreSQL users discuss high-impact performance improvements

Further learning

• PostgreSQL: Index Types maps B-tree, GIN, and BRIN to the operators they can handle.
• PostgreSQL: Multicolumn Indexes explains leading columns, range conditions, and composite-key limits.
• PostgreSQL: CREATE INDEX documents expression indexes, partial indexes, INCLUDE, and CONCURRENTLY precisely.
• CS50 SQL Week 5: Optimizing is a public hands-on course covering B-trees, partial indexes, query plans, and space/write trade-offs.