What is a B-tree index in PostgreSQL and how does it work?

Q: How much space will a B-tree index take?

As a rule of thumb, an index on a single costs roughly 16 bytes per row plus page and tuple overhead, so a 100 million row table is in the low gigabytes. Do not plan from the rule of thumb — build it on a copy, or check an existing one with . Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026 09 17.

2026-10-03

A B-tree index in PostgreSQL is a balanced, multi-level tree whose leaf pages hold sorted index tuples pointing at heap row locations (ctids). It is the default index type, and it serves equality, range, BETWEEN, IN, IS NULL, prefix LIKE, sorting and MIN/MAX on any data type with a total order.

Key facts

| Fact | Value | |---|---| | Default access method | btree — CREATE INDEX without USING builds one (CREATE INDEX) | | Operators served | <, <=, =, >=, >, plus BETWEEN, IN, IS NULL, IS NOT NULL (B-tree) | | Max columns per index | 32 (CREATE INDEX) | | Default fillfactor | 90 for B-tree (index storage parameters) | | Deduplication | On by default since PostgreSQL 13 (btree implementation) | | Rough size on a bigint | ~16 bytes per row plus page overhead — measure with pg_relation_size | | Across 31 monitored instances | 58.1% have at least one unused index flagged; 67.7% have duplicate indexes |

Why this happens

A B-tree is a balanced tree of 8 kB pages. The root and internal pages hold separator keys that route a search downwards; the leaf pages hold the actual index tuples, each of which is an indexed key plus a ctid — the physical (block, offset) address of a row in the heap. Because every leaf sits at the same depth, a lookup costs the same number of page reads regardless of which key you ask for, and on real table sizes that depth is usually three or four levels. Leaf pages are also linked to their siblings, which is what makes a range scan a sequential walk rather than a repeated descent from the root (B-tree implementation).

That structure explains the operator list. Anything the planner can express as "start at this point in the sorted order and walk" works: =, the four inequality operators, BETWEEN, IN (a set of starting points), and IS NULL/IS NOT NULL, since NULLs are stored in the index and sort to one end. Prefix LIKE 'abc%' also works, but only when the index's collation supports it — in any non-C collation you need a text_pattern_ops operator class on the index for the planner to use it for pattern matching (operator classes).

Because the leaves are already in key order, a B-tree can also supply ordering. A query with ORDER BY created_at DESC can read the index backwards and skip the sort node entirely. The catch is that the NULL placement has to match: an index built the default way (ASC, so NULLS LAST) cannot satisfy ORDER BY created_at ASC NULLS FIRST without a sort, and you would need CREATE INDEX ... (created_at ASC NULLS FIRST) instead (indexes and ORDER BY). The same property gives you MIN/MAX for free — PostgreSQL rewrites them into a one-row index scan of the relevant end of the tree.

Sequential keys — a bigserial primary key, an inserted_at timestamp — always insert at the rightmost leaf, so the tree grows to the right and older pages are never touched again. That is efficient, but it also means fillfactor does almost nothing useful there, while on a randomly distributed key a full leaf page must split when a new tuple lands in it. A lower fillfactor leaves room in each leaf at build time and postpones those splits at the cost of a larger index.

Multicolumn B-trees and the leftmost prefix

An index on (a, b, c) sorts by a, then b within equal a, then c. The planner can use it efficiently only when your predicates constrain a leading prefix of those columns (multicolumn indexes):

| Predicate | Uses (a, b, c) well? | |---|---| | a = 1 | Yes — leading column | | a = 1 AND b = 2 | Yes — leading two | | a = 1 AND b = 2 AND c = 3 | Yes — full key | | a = 1 AND c = 3 | Partly — scans all b under a = 1, filters c | | b = 2 AND c = 3 | No useful prefix — full index scan at best | | a > 1 AND b = 2 | Only a restricts; b becomes a filter |

That last row is the one people trip on: a range on the leading column stops the following columns from narrowing the scan, because the tuples matching a > 1 are not sorted by b overall. Put equality columns first and the range column last.

Index-only scans and the visibility map

If every column a query needs is in the index, PostgreSQL can answer from the index alone. It still has to prove each row is visible to your snapshot, and it does that by checking the table's visibility map — a two-bits-per-page bitmap saying whether all tuples on a heap page are visible to everyone (index-only scans). Pages only get marked all-visible by VACUUM, so on a recently written table the map is stale, the planner falls back to visiting the heap for those pages, and EXPLAIN (ANALYZE) reports a non-zero Heap Fetches:. An "index-only scan" with high heap fetches is not cheap; it is a regular index scan wearing a better name.

Deduplication

Before PostgreSQL 13, every duplicate key value stored its own full index tuple, so an index on a low-cardinality column such as status or tenant_id was mostly repeated keys. Since 13, B-tree deduplication stores one copy of the key with a compressed list of the ctids that share it (deduplication). Low-cardinality leading columns are still not free, and a BRIN index may fit better for correlated data, but the old advice that they waste enormous space no longer holds. It is disabled automatically for unique indexes on tables where it would not pay off, and can be turned off per index with deduplicate_items = off.

How to detect it

Start with usage against size — the single most useful index query there is (pg_stat_user_indexes):

SELECT s.schemaname,
       s.relname            AS table_name,
       s.indexrelname       AS index_name,
       s.idx_scan           AS scans,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       pg_relation_size(s.indexrelid)                 AS index_bytes
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique
ORDER BY s.idx_scan ASC, index_bytes DESC
LIMIT 25;

Rows at the top with scans = 0 and a large index_size are candidates for removal. Two cautions: idx_scan is cumulative since the last pg_stat_reset(), so check pg_stat_get_db_stat_reset_time(oid) before concluding an index is idle, and statistics are per node — an index unused on the primary may be the one holding up reporting on a replica.

To confirm an index is actually being chosen, and whether the scan is index-only:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE tenant_id = 42 AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

Read three things in the output (using EXPLAIN): the node type (Index Scan, Index Only Scan, Bitmap Index Scan), whether a separate Sort node survived above it, and Heap Fetches: on any index-only scan. A surviving Sort usually means your ORDER BY direction or NULL placement does not match the index.

How MyDBA shows this

Index scans over time for the busiest B-tree indexes on this instance - the counter pg_stat_user_indexes only shows as a single cumulative total.

Index size distribution for one database, so the cost side of every B-tree sits next to its scan count.

MyDBA's Index Advisor keeps an Index Usage Trends chart of scans per index over time, which is the part pg_stat_user_indexes cannot give you — that view exposes one cumulative counter, so an index that stopped being used three months ago still reads as busy. The Storage page's Index Size Distribution and Top Indexes (by size) sections put the cost beside it, which is the pairing that makes a drop decision defensible. Across 31 monitored instances, 58.1% had at least one unused index flagged and 67.7% had duplicate indexes; across 50 instances, 44.0% failed the index-bloat check. If you want to see the same two views against your own database, the free health check covers it.

How to fix it

1. Measure before you guess. Run the pg_stat_user_indexes query above and record the scan count and byte size of each candidate. 2. Reorder multicolumn indexes so equality predicates come first and the range column last. Build the replacement first, then drop the old one — see creating indexes. 3. Match the index ordering to the query, including NULLS FIRST/NULLS LAST, when you are trying to eliminate a Sort node. 4. Add the payload columns a query needs, as an INCLUDE clause rather than extra key columns, so the tree stays narrow — see covering indexes. 5. Vacuum the table if Heap Fetches: is high: VACUUM (ANALYZE) orders; refreshes the visibility map so the index-only scan can stay index-only. 6. Rebuild without blocking when an index has bloated: REINDEX INDEX CONCURRENTLY idx_name;. 7. Drop what is genuinely unused, DROP INDEX CONCURRENTLY, after confirming the stats window is long enough and that no replica depends on it.

How to prevent it

Set fillfactor deliberately: leave the default 90 for right-growing sequential keys, and consider 70-80 for a randomly distributed key on a write-heavy table to reduce page splits. Check index size growth rather than assuming it — pg_relation_size on the index, compared week over week, is a better signal than a rule of thumb. Create indexes with CREATE INDEX CONCURRENTLY on live tables so you do not hold a write lock.

Most importantly, know when a B-tree is the wrong shape. Containment (@>), array membership, JSONB key lookups and full-text search need GIN; geometric and range overlap need GiST; very large, naturally ordered tables are often better served by BRIN. The PostgreSQL index types guide compares all of them side by side.

FAQ

Does a B-tree index help with `LIKE '%abc%'`?

No. A leading wildcard has no sorted prefix to seek to, so the tree cannot be descended. Prefix patterns (LIKE 'abc%') do work, and in a non-C collation need a text_pattern_ops operator class on the index. For substring matching, use a trigram or full-text index instead.

Should I index a boolean or other low-cardinality column?

Sometimes. Since PostgreSQL 13, deduplication makes the storage far less wasteful than it used to be, but a B-tree that matches half the table will still lose to a sequential scan. A partial index — CREATE INDEX ... WHERE status = 'pending' — is usually the better answer when the interesting value is rare.

Why is my `Index Only Scan` still reading the heap?

Because the visibility map has not been updated for those pages. VACUUM is what marks a heap page all-visible; until it runs, the executor must check each tuple's visibility in the heap. Watch Heap Fetches: in EXPLAIN (ANALYZE) — a large value means the scan is index-only in name only.

How much space will a B-tree index take?

As a rule of thumb, an index on a single bigint costs roughly 16 bytes per row plus page and tuple overhead, so a 100-million-row table is in the low gigabytes. Do not plan from the rule of thumb — build it on a copy, or check an existing one with SELECT pg_size_pretty(pg_relation_size('idx_name'));.

Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026-09-17.