Bhaki's Engineering Journal
← All weekly notes

2 min read

What PostgreSQL indexes actually cost

A concise explanation of faster reads, slower writes, storage overhead and why the query planner may ignore an index.

PostgreSQL · Databases · Performance

The basic trade-off

An index is a separate data structure that helps PostgreSQL find rows without scanning the entire table. The benefit is faster reads for suitable queries. The cost is additional storage and extra work whenever indexed data changes.

For every relevant INSERT, UPDATE, or DELETE, PostgreSQL must keep both the table and its indexes consistent. More indexes can therefore reduce write throughput and increase maintenance work.

Why a B-tree is the default

PostgreSQL uses B-tree indexes by default. They work well for equality and ordered comparisons such as:

WHERE customer_id = 42WHERE created_at >= '2026-01-01'ORDER BY created_at

A multi-column index follows left-to-right ordering. An index on (customer_id, created_at) is naturally useful when a query filters by customer_id, or by customer_id and then created_at. It is usually not equally useful for a query that only filters by created_at.

Why PostgreSQL may choose a table scan

Having an index does not guarantee that PostgreSQL will use it. The planner estimates the cost of possible plans and chooses the cheapest one.

A sequential scan may be better when:

  • The table is small.
  • The query will return a large percentage of its rows.
  • The indexed column has poor selectivity.
  • Statistics are stale or do not represent the data well.
  • The query transforms the column in a way that does not match the index.

EXPLAIN shows the chosen plan, while EXPLAIN ANALYZE executes the query and reports actual timing and row counts.

Useful variations

  • A unique index also enforces uniqueness.
  • A partial index stores only rows matching a condition.
  • An expression index stores the result of an expression such as lower(email).
  • An index with included columns may allow an index-only scan for some queries.

The correct choice comes from the real query workload, not from indexing every column that appears in a filter.

The takeaway

An index trades write cost and storage for faster reads. Choose indexes from the queries the system must serve, paying attention to selectivity and column order. Verify the result with EXPLAIN ANALYZE because PostgreSQL may prefer a sequential scan when an index would touch too much of the table.