Over the past few months I've been doing diving deep into PostgreSQL. I've spent a good bit of my career supporting Oracle and MySQL, but over the past few years I've spent more time managing PostgreSQL. I'm super comfortable getting around a psql prompt, but wanted to really dive into the guts of PostgreSQL to take my knowledge to the next level.
I started with the PostgreSQL internals documentation. Understanding the life of a query, how the system catalog and system views are organized, and how the client/server protocol works helped a ton. I also read Mastering PostgreSQL 17 to fill in any knowledge gaps I had. I especially enjoyed reading the Making Use of Indexes chapter. It helped me understand the cost model, EXPLAIN plans, and clustered tables in more detail.
I'm a visual and hands-on-the-keyboard learner so I went back to the official docs to dig into the six (six that I know of) index types. Each index type is designed for a different shape of query, and choosing the wrong one can take your query from 11 to 0 in a heartbeat. To help me visualize how each index type works I crafted some ASCII art:
B-tree
A B-tree keeps values in a balanced, sorted tree. To find a row, PostgreSQL
walks from the root toward the matching leaf. It takes only a handful of steps
regardless of table size. Because the tree is sorted, it handles much more than
equality: ranges, prefix matches, NULL checks, and ORDER BY all work
without a separate sort pass.
┌──────────────┐
│ 50 │
└──────┬───────┘
┌───────────────┴───────────────┐
┌──────┴──────┐ ┌───────┴──────┐
│ 20 │ │ 70 │
└──────┬──────┘ └───────┬──────┘
┌───────┴───────┐ ┌─────────┴───────────┐
┌────┴────┐ ┌────┴────┐ ┌────┴────┐ ┌─────┴────┐
│ 10 │ │ 30 │ │ 60 │ │ 80 │
└─────────┘ └─────────┘ └─────────┘ └──────────┘
Use when: B-tree handles equality, range comparisons, prefix matching, null checks, and sorting, covering the vast majority of real-world index needs.
Hash
A hash index runs each value through a hash function to produce a bucket number, then stores a pointer directly in that bucket. Lookup is a single step with no tree to traverse. The tradeoff is that it only supports equality checks — ranges, sorting, and prefix matches are off the table. Collisions (two different values landing in the same bucket) are normal and handled automatically.
value hash fn bucket stored rows
─────────── ──────────→ ────── → ─────────────────────
"alice" → 0x4A3F → [ 74 ] → row 42, row 99
"bob" → 0x11B7 → [ 4 ] → row 7
"carol" → 0x9C21 → [ 31 ] → row 101
"dave" → 0x4A3F → [ 74 ] → row 42, row 99 ← same bucket as alice
Use when: the column is only ever queried for exact equality and you have confirmed a measurable performance gain over B-tree.
GIN: Generalized Inverted Index
GIN works like a textbook's back-of-index: each individual element (a word, an array item, or a JSON key) points back to every row that contains it. The index is built inside-out compared to B-tree. Instead of one entry per row, there is one entry per element value.
Row contents:
┌───────┬──────────────────────────────┐
│ row 1 │ ["cat", "dog", "fish"] │
│ row 2 │ ["dog", "bird"] │
│ row 3 │ ["cat", "bird", "fish"] │
└───────┴──────────────────────────────┘
GIN index (element → posting list):
┌────────┬──────────────────┐
│ "bird" │ { row 2, row 3 } │
│ "cat" │ { row 1, row 3 } │
│ "dog" │ { row 1, row 2 } │
│ "fish" │ { row 1, row 3 } │
└────────┴──────────────────┘
Use when: querying inside arrays, JSONB documents, or full-text search
columns. GIN builds and updates more slowly than B-tree, so on write-heavy
columns enable fastupdate to batch insertions.
GiST: Generalized Search Tree
GiST is a framework, not a single fixed structure. Each node stores a bounding predicate (a bounding box, a date range, or a convex hull) that loosely describes all values in its subtree. When PostgreSQL searches, it skips entire branches whose predicate cannot possibly match, rather than inspecting every leaf.
Query: find all points inside the box (30–70, 30–70)
┌─────────────────────────────┐
│ bounds: x(0–100) y(0–100) │ ← might match, descend
└─────────────┬───────────────┘
┌─────────────────────┴───────────────────┐
┌────────┴──────────────┐ ┌────────────┴──────────────┐
│ bounds: x(0–50) y(0–50)│ │bounds: x(51–100) y(51–100)│
│ might match, descend │ │ no overlap — skip ✗ │
└────────┬───────────────┘ └───────────────────────────┘
┌────┴────────┐
(10,20) (40,45)
skip ✗ return ✓
Use when: working with geometric data, range types such as date or integer ranges, or nearest-neighbor queries. It is the go-to choice for PostGIS and any data with overlapping or bounding-box semantics.
SP-GiST: Space-Partitioned GiST
SP-GiST divides the value space into non-overlapping partitions where every value belongs to exactly one cell. The resulting tree can grow lopsided (unlike B-tree), which is actually an advantage when the data itself is lopsided: common prefixes collapse into a short path, rare ones into a long one.
Trie example — routing table of IP prefixes:
┌───┐
│ * │ (root)
└─┬─┘
┌──────┴──────┐
┌─┴──┐ ┌──┴──┐
│192 │ │ 10 │
└─┬──┘ └──┬──┘
┌───┴───┐ ┌──┴──┐
[.0] [.168] [.0]
│ │ │
[.1.0] [.1.1] [.0.1] → row ptr
↓ ↓
row ptr row ptr
Use when: the data has a natural hierarchical structure, such as IP prefixes, phone numbers, or postal codes. On uniformly distributed data the depth advantage disappears and B-tree or GiST will perform as well or better.
BRING: Block Range Index
BRING is the lightest index PostgreSQL offers. Rather than cataloguing every row, it records just the minimum and maximum value for each physical block range of the heap. A query can skip any block range whose min–max cannot contain the target value.
The entire index might be kilobytes on a table with millions of rows, but it is only effective when the physical order of rows on disk matches the query's sort order (i.e. rows were inserted in roughly sorted order and never shuffled by updates).
heap file (one row = one timestamp, written in order)
┌──────────────┬────────────────────────────────────────┐
│ blk 1– 10 │ min: 2024-01-01 max: 2024-01-10 │ ← skip (max < target)
│ blk 11– 20 │ min: 2024-01-11 max: 2024-01-20 │ ← scan ✓
│ blk 21– 30 │ min: 2024-01-21 max: 2024-01-31 │ ← skip (min > target)
└──────────────┴────────────────────────────────────────┘
query: WHERE ts = '2024-01-15'
result: only blocks 11–20 are read from disk
Use when: indexing a naturally ordered, append-only column on a very large table, such as a timestamp on an event log. It is useless if rows are written out of order or frequently updated in place.
I've mostly used B-tree indexes to address performance issues. Occasionally hash indexes if the data and correlation fit. Looking back at some one-off situations BRING indexes would have hit the spot! You gotta love PostreSQL!
Resources: