BRIN vs B-tree Indexes in PostgreSQL: A Definitive Guide

In PostgreSQL indexing, B-tree indexes have long been the default choice for most use cases. However, BRIN indexes are often considered a more space-efficient alternative when dealing with multi-billion-row tables. But do they live up to the hype? Using various query patterns, this post will systematically benchmark B-tree and BRIN indexes in PostgreSQL. We'll also cover essential optimization techniques to maximize the performance of both index types.

Note:

A good friend, Bill Coulam, and I are diving deeper. As we get better results, we will update the benchmark output file.

---

📦 Data Setup: A Multi-Billion Row Web Log Table

For our benchmarks, we generated a synthetic web log table with 100 million rows, mimicking common web server logs:

• created_atTimestamps spanning 180 days

• status_codeHTTP status codes (e.g., 200, 404, 500)

• http_methodHTTP methods (e.g., GET, POST)

• id: UUID values

We created the following indices:

• brin_web_log BRIN index on (created_at)

• btree_web_log_target B-tree index on (status_code, http_method, created_at)

Download the Scripts

• Create a Dataset and Indexes Script

• Benchmark Script

• Benchmark Output

---

🛠️ Benchmarking Queries and Scripts

Query 1: Last 7 Days of GET Requests with Specific Status Codes

SELECT id, user_ip, path, http_method, status_code, created_at
FROM web_log
WHERE created_at >= NOW() - INTERVAL '7 days'
  AND status_code IN (500, 404)
  AND http_method = 'GET'
ORDER BY created_at DESC
LIMIT 100;

• B-tree Execution Time: 28 ms

• BRIN Execution Time: 472 ms

• No Index Execution Time: 11,576 ms

Query 2: Specific Status Code and HTTP Method

SELECT id, user_ip, path, http_method, status_code, created_at
FROM web_log
WHERE status_code = 404
  AND http_method = 'GET';

• B-tree Execution Time: 11,788 ms

• BRIN Execution Time: 9,944 ms

• No Index Execution Time: 5,963 ms

Query 3: Full Range Scan with Sorting

SELECT id, user_ip, path, http_method, status_code, created_at
FROM web_log
ORDER BY created_at DESC
LIMIT 10000;

• B-tree Execution Time: 2,058 ms

• BRIN Execution Time: 18,967 ms

• No Index Execution Time: 15,584 ms

Query 4: Large Range Scan (Last 180 Days)

SELECT id, user_ip, path, http_method, status_code, created_at
FROM web_log
WHERE created_at >= NOW() - INTERVAL '180 days';

• B-tree Execution Time: 37,373 ms

• BRIN Execution Time: 35,337 ms

• No Index Execution Time: 32,690 ms

Benchmark Results:

---

⚡ Optimization Recommendations

• For Targeted Queries: Use B-tree for precise lookups and backward scans.

• For Large Range Scans: Partition by created_at and apply BRIN indexes to each partition.

• For High Cardinality Data: Consider partial indexes or specialized BRIN configurations with smaller pages_per_range values.

• Order/Cluster Data: BRIN indexes do not function well if the data is not ordered. I’ll put together another script that you can download from my Git. This is blog entry 002.

---

📖 Further Reading and Documentation

• BRIN Indexes in PostgreSQL 16+

• B-tree Indexes in PostgreSQL 16+

• Partitioning in PostgreSQL 16+

---

🛠️ Final Thoughts: BRIN or B-tree?

BRIN indexes are a fantastic tool for reducing index size and scan ranges in large, well-ordered datasets. However, when data is not physically ordered or queries are highly targeted, B-tree remains the optimal choice. For mixed workloads, a hybrid approach — combining BRIN for range scans and B-tree for targeted lookups — offers the best balance of performance and storage efficiency.

✅ Credits and References

This post was developed with the assistance of ChatGPT and provides comprehensive insights into PostgreSQL indexing strategies and performance analysis. Stay tuned for more PostgreSQL optimization tips and tricks!