---
title: "B-tree Index"
slug: "btree-index"
category: "glossary"
tags: ["glossary", "database", "postgres", "indexing", "btree", "sql"]
status: "stable"
last_updated: 2026-08-14
summary: "B-tree index is the balanced tree PostgreSQL uses by default for equality, range, and ordering queries on sortable column types."
related:
  [
    "[[glossary/query-plan]]",
    "[[glossary/gin-index]]",
    "[[glossary/sequential-scan]]",
    "[[backend/postgres]]",
    "[[cheatsheets/postgres-explain]]",
    "[[glossary/vacuum]]",
  ]
---

## Overview

This page is the atomic definition. Indexing strategy and `EXPLAIN` analysis live at [[backend/postgres]] and [[cheatsheets/postgres-explain]]. A B-tree index keeps keys sorted in a balanced tree, making it the default choice for equality, range, and ordering queries.

## Definition

A B-tree (Balanced tree) index stores keys in sorted order across a balanced tree of fixed-size pages, with leaf pages holding key values and heap tuple identifiers (TIDs). Internal pages hold routing keys that guide searches to the correct leaf. The tree self-balances on insert and delete, keeping all leaf pages at equal depth.

B-tree indexes are useful for:
- Equality: `WHERE col = $1`
- Range: `WHERE col BETWEEN $1 AND $2`
- Prefix match on strings: `WHERE col LIKE 'prefix%'`
- Ordering: `ORDER BY col` (index-only or index scan avoids a sort node)
- IS NULL: `WHERE col IS NULL` (Postgres B-trees store NULL keys)

B-tree indexes are not useful for full-text search, JSONB containment, array overlap, or nearest-neighbor vector search. Use [[glossary/gin-index|GIN]] for those.

Index size grows linearly with cardinality. A partial index (`WHERE active = true`) covers a subset and stays smaller. A covering index (`INCLUDE (col1, col2)`) enables Index Only Scans for queries that read only the included columns.

## When it applies

Create a B-tree index on any column used in `WHERE`, `JOIN ON`, or `ORDER BY` that results in a [[glossary/sequential-scan|sequential scan]] on a table with more than a few thousand rows. Create composite indexes with the equality column(s) first and the range or sort column last: `(user_id, created_at DESC)`. Monitor index bloat with `pg_stat_user_indexes`; rebuild with `REINDEX CONCURRENTLY` if `idx_tup_read / idx_tup_fetch` diverges widely.

## Example

```sql
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';
CREATE INDEX idx_orders_covering ON orders (user_id) INCLUDE (status, total_cents);
```

## Related concepts

- [[glossary/query-plan]] - the query planner decides whether to use the B-tree or a sequential scan.
- [[glossary/gin-index]] - GIN is the alternative for full-text, JSONB, and array operators.
- [[glossary/sequential-scan]] - what the planner falls back to when B-tree is not cost-effective.
- [[backend/postgres]] - the Postgres deep-dive.
- [[cheatsheets/postgres-explain]] - reading `EXPLAIN` output to confirm index usage.

## Citing this term

> See [[glossary/btree-index|B-tree Index]] (llmbestpractices.com/glossary/btree-index).

## Related

- [[glossary/query-plan]]
- [[glossary/gin-index]]
- [[glossary/sequential-scan]]
- [[backend/postgres]]
- [[cheatsheets/postgres-explain]]