Database Indexing

Status: draft · Confidence: low (0.43) · Basis: verified_sources

Quality notes: generic_source_homepage, no_verified_sources, partial_source_verification

## TL;DR

Database indexes are data structures that speed up data retrieval — like a book's index. Common types: B-tree (default, range queries, sorting), Hash (equality only), GIN (full-text, arrays, JSONB in PostgreSQL), GiST (geometric, full-text), BRIN (block range, large append-only tables). Without index: sequential scan (read every row).

## Core Explanation

B-tree: O(log n) lookups, supports =, <, >, BETWEEN, ORDER BY. Multi-column index: order matters — (a, b) supports a=1 AND b=2, a=1 only, but NOT b=2 only. Partial index: index subset of rows — WHERE active = true. Covering index: includes all columns needed by query — index-only scan (fastest). Index maintenance cost: writes slow down because indexes must update. Monitor unused indexes and remove them.

## Further Reading

- [SQL Performance Explained (Markus Winand)](undefined)

## Related Articles

- [Database Indexing: B-Trees, Hash Indexes, and Query Optimization](../database-indexing-b-trees-hash-indexes-and-query-optimization.md)
- [Learned Database Systems: AI-Driven Query Optimization, Learned Indexes, and Cardinality Estimation](../../ai/learned-database-systems.md)
- [Text-to-SQL: Natural Language Database Querying with Large Language Models](../../ai/text-to-sql.md)