PostgreSQL Index Types: B-tree, GIN, GiST, and BRIN

Create four indexes for four different query shapes

create index orders_created_at_idx
  on orders (created_at);

create index products_attributes_gin_idx
  on products using gin (attributes);

create index bookings_during_gist_idx
  on bookings using gist (during);

create index events_created_at_brin_idx
  on events using brin (created_at);

These are examples, not a recommendation to add all four. Each index must match real operators, data distribution, and workload.

Use B-tree for the common case

B-tree is PostgreSQL's default index. It supports equality and ordered comparisons such as less than, greater than, BETWEEN, IN, IS NULL, and sorting. It can also support anchored prefix matching under compatible operator classes and collations.

Column order matters in a multicolumn B-tree. Equality conditions on leading columns, followed by a range condition, are the conventional useful shape. PostgreSQL 18 can sometimes use skip scan when a leading value is missing, but that does not make column order irrelevant.

Match a multicolumn index to the query

create index orders_account_created_idx
  on orders (account_id, created_at desc);

select id, total
from orders
where account_id = 42
  and created_at >= current_date - interval '30 days'
order by created_at desc
limit 50;

Use GIN for values with components

GIN is an inverted index. It stores entries for components inside a value, which makes it useful for arrays, JSONB containment, and full-text search. The supported operators come from the selected operator class.

GIN can be expensive to update and can consume meaningful space. Index the JSONB or search operations you actually run, then compare write cost and index size as well as read latency.

Use GiST for extensible search strategies

GiST is an index framework used by range types, geometric data, network-address extensions, exclusion constraints, and nearest-neighbor searches when the operator class supports them. It is not simply an alternative spelling of GIN.

Choose between GIN and GiST from the operators and behavior of the data type. For example, a range-overlap query and a JSONB-containment query need different operator classes even though both values look composite.

Use BRIN when physical order helps

BRIN stores summaries for ranges of table pages rather than an entry for every row. It stays small and can skip large parts of an append-heavy table when a value such as created_at is correlated with physical row order.

BRIN is not a tiny B-tree. On a small table or randomly distributed identifier, its summaries may select too many pages to help. Test the correlation and the actual plan.

Prove that the index improves the query

Load representative data and run EXPLAIN (ANALYZE, BUFFERS) before and after the index. Compare execution time, rows, loops, buffer reads, and the chosen scan. Run ANALYZE after a major data load so the planner has current statistics.

Keep indexes that improve important queries enough to justify their storage and write cost. The EXPLAIN ANALYZE walkthrough shows how to read the plan without treating an index scan as an automatic win.

Measure the intended query

analyze orders;

explain (analyze, buffers)
select id, total
from orders
where account_id = 42
order by created_at desc
limit 50;

EXPLAIN ANALYZE executes the query. Use care with writes and expensive production statements.