An index helps PostgreSQL find rows faster by maintaining a separate structure that points to table rows. PostgreSQL supports several index types; each is best for certain kinds of queries and data.
B-tree (default)
B-tree is the default index type. PostgreSQL uses it when you write CREATE INDEX without specifying a type. It keeps keys in sorted order and supports equality (=), range (<, >, BETWEEN), and ORDER BY efficiently. Use it for most columns: IDs, dates, numbers, text. It’s the go-to choice unless you have a reason to use another type.
Hash
Hash indexes store a hash of the key and support only equality (=). They are smaller and can be faster than B-tree for simple “find by key” lookups when you don’t need ranges or sorting. In older PostgreSQL versions they had drawbacks (e.g. not WAL-logged); from PostgreSQL 10 onward they are crash-safe and often a good fit for exact-match queries on a single column.
GiST (Generalized Search Tree)
GiST is a framework for building index structures that can represent many different kinds of data and queries. It’s used for geometric types (e.g. points, boxes), full-text search, ranges, and custom types. GiST supports “nearest neighbor” and containment queries. Choose GiST when you need spatial data, ranges, or full-text search with a single index type that can do more than simple equality or range.
GIN (Generalized Inverted Index)
GIN is optimized for values that contain multiple components or keys: arrays, full-text search (tsvector), JSONB. It builds an “inverted” structure: for each key (e.g. a word or array element), it stores which rows contain it. GIN is excellent for “contains,” “overlaps,” and full-text queries. Use GIN for @>, ?, @@ on arrays and tsvector, and for JSONB containment.
BRIN (Block Range INdex)
BRIN stores summary information (e.g. min/max) for ranges of table blocks instead of indexing every row. It’s very small and fast to build and maintain. BRIN works best when data is naturally ordered on disk (e.g. time-series data by timestamp). It’s less precise than B-tree but ideal for very large, ordered tables where range scans are common. Use BRIN when the table is huge and values are correlated with physical order.
Quick comparison
- B-tree – Default; use for most columns (equality, range, sort).
- Hash – Equality only; good for simple key lookups.
- GiST – Geometric, full-text, ranges, custom types.
- GIN – Arrays, full-text, JSONB (containment, overlap).
- BRIN – Very large, naturally ordered tables (e.g. time-series).
Pick the index type that matches your query pattern and data; when in doubt, start with B-tree.