PostgreSQL indexing gets much easier once you stop thinking in terms of “index or no index” and start thinking in terms of workload shape.
BRIN and GIN are a good example because they solve almost opposite problems.
When BRIN Helps
BRIN is useful for very large tables where values are naturally correlated with physical row order, such as:
- timestamps in append-only event tables
- monotonically increasing IDs
- time-series data
It stores lightweight summaries of block ranges rather than indexing every row individually. That is why it can be tiny compared with a B-tree.
When GIN Helps
GIN is useful when you need to search inside composite values:
jsonb- arrays
- full-text documents
It indexes the contained elements rather than treating the entire value as one opaque blob.
A Practical Example
For a large event table:
CREATE INDEX events_created_at_brin_idx
ON events
USING BRIN (created_at);
For a jsonb column:
CREATE INDEX users_preferences_gin_idx
ON users
USING GIN (preferences);
These are both “indexes,” but they improve very different kinds of queries.
Trade-Offs
BRIN is compact and cheap, but depends on ordering characteristics.
GIN is powerful, but usually larger and more expensive to maintain on writes.
That is why the right question is not “which one is faster?” It is “which one matches the access pattern?”
Further Reading