# PostgreSQL Indexing: When BRIN Is a Better Choice Than B-Tree

If you’re indexing *huge, append-only* tables with *naturally ordered* data — **BRIN** can give you massive performance and space benefits compared to **B-Tree**.

## What is a BRIN Index?

**BRIN** = **Block Range Index**

A BRIN index **doesn't index every row**, unlike B-Tree. Instead, it stores **summary data (min, max)** about *physical blocks* of rows.

Think of BRIN as a **metadata guide**: it narrows down **where** to search, not **what** to return.

## How BRIN Works Internally

When you create a **BRIN index** on a column, PostgreSQL organizes the index **by summarizing values over physical block ranges** in the table.

For **each block range**, the BRIN index stores **summary metadata**, including:

* **Minimum value** in the range
    
* **Maximum value** in the range
    
* And other summary data, depending on the BRIN operator class
    

### During Index Creation

* PostgreSQL **scans the table** in fixed-size block ranges.
    
* For each range, it **stores a tuple** in the BRIN index with the min/max values (and possibly other stats).
    

### During Query Execution

1. When a query has a **search condition** (e.g., `WHERE column = 42`):
    
2. PostgreSQL consults the BRIN index to **check the min/max values for each range**.
    
3. If the search value **falls outside** a block range’s min/max → **the range is skipped**.
    
4. If it **falls inside** the range → PostgreSQL **performs a heap scan** on that range (similar to a sequential scan but limited to that range).
    

> BRIN does **not** point to individual rows—just ranges. It’s extremely space-efficient, but depends heavily on **data correlation** (e.g., time-ordered inserts).

## BRIN vs. B-Tree: Feature Comparison

| Feature | B-Tree | BRIN |
| --- | --- | --- |
| Index size | Large (grows with rows) | Tiny (constant per block) |
| Query performance | Fast, consistent | Depends on data distribution |
| Best for | Random access | Sequential, append-only data |
| Build time | Slow on big tables | Very fast |
| Maintenance | Higher | Minimal |
| Can be used for =, >, < | Yes | only helps if the data is ordered |

## Benchmark: BRIN vs. B-Tree with Real Data

### Dataset: 100 million records

```sql
CREATE TABLE sensor_data (
    id BIGSERIAL PRIMARY KEY,
    device_id INT,
    temperature NUMERIC,
    event_time TIMESTAMP
);
```

Populate with realistic data:

```sql
INSERT INTO sensor_data (device_id, temperature, event_time)
SELECT
  (random()*1000)::int,
  round(random()*50, 2),
  now() - (random() * interval '365 days')
FROM generate_series(1, 100000000);
```

### Create Indexes

```sql
-- BRIN
CREATE INDEX brin_sensor_event_time ON sensor_data USING brin (event_time);

-- B-Tree
CREATE INDEX btree_sensor_event_time ON sensor_data USING btree (event_time);
```

### Performance Test

Query:

```sql
SELECT * FROM sensor_data
WHERE event_time BETWEEN '2023-01-01' AND '2023-01-10';
```

| Index Type | Index Size | Query Time |
| --- | --- | --- |
| B-Tree | ~15 GB | 80ms |
| BRIN | ~60 MB | 500ms–1.5s |

> BRIN is **~250x smaller**, but **slower for highly selective queries**

## When to Use BRIN (Best Use-Cases)

* **Append-only** or **time-series data**
    
* Large tables: 100M+ rows
    
* Columns with **monotonic** or **naturally ordered** data:
    
    * Timestamps
        
    * IDs
        
    * Dates
        
* Use with **partitioned tables** for cheap indexing
    
* Excellent for **archival** or **rarely queried** historical data
    

## When **Not** to Use BRIN

* Highly **randomized** or **non-sequential** data
    
* High **update/delete** activity (BRIN won’t self-tune)
    
* You need **fast, pinpoint lookups** (use B-Tree instead)
    

## Creating & Tuning BRIN

### Basic Syntax

```sql
CREATE INDEX idx_brin_column
ON your_table USING brin(your_column);
```

### Tuning `pages_per_range`

```sql
-- Lower = finer granularity (but larger index)
CREATE INDEX idx_brin_custom
ON your_table USING brin(your_column)
WITH (pages_per_range = 32);
```

Default = 128; use lower for sparse data, higher for dense data

## Measuring BRIN Effectiveness

### Check index size

```sql
SELECT pg_size_pretty(pg_relation_size('idx_brin_column'));
```

### View BRIN summary info

```sql
SELECT * FROM pg_brin_bloom_summary;
-- Or use pg_visibility module for deeper inspection
```

### REINDEX or VACUUM

If data distribution changed a lot:

```sql
REINDEX INDEX idx_brin_column;
VACUUM ANALYZE your_table;
```

## Pairing BRIN with Other Indexes

Yes! Combine strategies:

* BRIN on `event_time`
    
* B-Tree on `device_id`
    

Allows PostgreSQL to **combine filters** effectively.

```sql
SELECT * FROM sensor_data
WHERE event_time > now() - interval '30 days'
AND device_id = 42;
```

Use **multicolumn BRIN** only if both columns are sequentially increasing.

## Other BRIN Index Types

Available operator classes:

* `brin_minmax` (default)
    
* `brin_bloom` (Postgres 14+): bitset-based
    
* `brin_inclusion` (Postgres 15+): for ranges
    
* Custom extensions like `brin_lov` (local dictionary)
    

## Pros and Cons

### Pros

* **Tiny disk footprint**
    
* **Fast to build**
    
* Great for **bulk time-based ingestion**
    
* Easy maintenance
    

### Cons

* Slower than B-Tree for **pinpoint** queries
    
* Sensitive to data **distribution**
    
* No enforcement of uniqueness
    
* Doesn't help much on **UPDATE-heavy** workloads
    

## Final Thoughts

**BRIN indexes are an absolute gem** for large-scale, time-based, or append-only tables where:

* You care about index **size and creation time**
    
* Queries are **range-based** (e.g., logs, sensor data)
    
* You don’t need millisecond response times for every query
    

For more details refer the official doc at [PostgreSQL: Documentation: 18: 65.5. BRIN Indexes](https://www.postgresql.org/docs/current/brin.html)