SUMMARY:
Explore when not to use B-Tree indexes in PostgreSQL and how GIN, GiST, SP-GiST, and BRIN can deliver better performance for specialized workloads.
Table of contents
Introduction
When PostgreSQL performance is discussed, B-Tree indexes are usually the first recommendation — and for good reason. B-Trees handle equality, range conditions, joins, sorting, and uniqueness extremely well. However, PostgreSQL provides several index types because not every query represents an ordered scalar search.
Choosing the right index means understanding what the query is actually asking the database to find.
1. B-Tree: The Default Workhorse
B-Tree is the standard choice for most OLTP workloads:
CREATE INDEX idx_orders_customer
ON orders(customer_id);It works efficiently for:
WHERE customer_id = 100
WHERE order_date >= '2026-08-01'
ORDER BY order_dateB-Trees maintain keys in sorted order, allowing PostgreSQL to navigate directly to a value and efficiently scan a range.
They are especially powerful with composite indexes:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);This is ideal for queries such as:
WHERE customer_id = 100
AND order_date >= '2026-08-01'
ORDER BY order_date;The order of columns matters because PostgreSQL can exploit equality conditions on leading columns before navigating a range.
Use B-Tree when: your workload involves scalar equality, ranges, joins, sorting, or unique constraints.
2. GIN: Searching Inside Composite Data
GIN, or Generalized Inverted Index, is designed for finding rows containing particular elements.
It is particularly useful for:
- JSONB
- Arrays
- Full-text search
For example:
CREATE INDEX idx_products_tags
ON products USING gin(tags);This can accelerate:
WHERE tags @> ARRAY['postgres'];For JSONB:
CREATE INDEX idx_events_payload
ON events USING gin(payload);For example, if the payload column contains JSONB documents such as:
- Row 1 → {“database”: “postgres”}
- Row 2 → {“database”: “mysql”}
- Row 3 → {“database”: “postgres”}
Conceptually, a GIN index maintains mappings between indexed elements and the rows that contain those elements:
"postgres" → Row 1, Row 3
"mysql" → Row 2The trade-off is write overhead. One row containing many indexed elements can generate many index entries, making GIN considerably more expensive to maintain than a simple B-Tree.
Use GIN when: the query searches for membership or containment inside arrays, JSONB, or documents.
3. GiST: Ranges, Spatial Data, and Relationships
GiST, or Generalized Search Tree, is designed for more complex relationships than simple ordering.
A classic example is PostgreSQL range types:
CREATE INDEX idx_reservations_period
ON reservations USING gist(reservation_period);It can accelerate queries involving:
WHERE reservation_period && requested_periodwhere && means the ranges overlap.
GiST is also heavily used for geospatial workloads and nearest-neighbor searches.
An important advanced use is exclusion constraints:
EXCLUDE USING gist (
room_id WITH =,
reservation_period WITH &&
)This can enforce that two reservations for the same room cannot overlap.
Use GiST when: the problem involves ranges, spatial relationships, overlap, containment, or distance.
4. SP-GiST: Partitioning the Search Space
SP-GiST is specialized for space-partitioned structures, including structures such as quadtrees, k-d trees, and tries.
Unlike GiST’s generalized balanced-tree approach, SP-GiST recursively partitions the search space.
It can be useful for specialized spatial and hierarchical data where partitioning is more natural than maintaining a globally ordered structure.
Use SP-GiST when: the data naturally maps to partitioned search structures and the available operator class supports the required workload.
5. BRIN: The Index for Massive, Correlated Tables
BRIN, or Block Range Index, is fundamentally different.
Instead of indexing individual rows, BRIN stores summaries for ranges of physical table blocks.
For a huge append-only table:
CREATE INDEX idx_events_time
ON events USING brin(created_at);If created_at closely follows physical insertion order, PostgreSQL can determine that entire block ranges cannot contain the requested timestamp and skip them.
This makes BRIN extremely attractive for:
- Time-series data
- Audit tables
- Log tables
- Large append-only tables
The critical requirement is physical correlation. A BRIN index on randomly distributed data may provide very little benefit.
Use BRIN when: the table is very large, and the indexed column correlates strongly with physical row location.
Choosing the Right Index
The decision should start with the query — not the column:
| Query pattern | Starting point |
|---|---|
| Equality/range | B-Tree |
| Sorting | B-Tree |
| JSONB/array containment | GIN |
| Full-text search | GIN |
| Range overlap | GiST |
| Spatial/nearest-neighbor | GiST/SP-GiST |
| Massive correlated table | BRIN |
| Specialized equality-only workload | Hash |
The most important lesson is that indexing is not simply about creating a B-Tree on every frequently queried column.
An index introduces storage, WAL, cache pressure, VACUUM work, and write-maintenance overhead. A GIN index may transform JSONB performance while hurting a write-heavy workload. A BRIN index may be nearly useless without physical correlation. A covering B-Tree may eliminate heap reads, but only when PostgreSQL’s visibility map allows an index-only scan.
Ultimately, good PostgreSQL indexing means matching the query operator, data structure, physical data distribution, selectivity, and workload characteristics to the right access method.
B-Tree may be the default — but understanding when not to use B-Tree is what separates basic indexing from advanced PostgreSQL performance engineering.