GIN: Generalized Inverted Index
GIN (Generalized Inverted Index) is built for columns holding composite values, where each row can contain multiple indexable keys, such as an array, a JSONB document, or a tsvector produced by full-text search. Internally GIN maps each distinct key to a sorted list of the row TIDs that contain it, much like the index at the back of a textbook maps each term to every page number where it appears, which makes containment operators like @> (contains), <@ (is contained by), and && (overlaps) fast even though a plain B-tree has no concept of 'one row, many keys'. GIN indexes are typically slower to update than B-trees because a single row change can touch many key entries, so PostgreSQL buffers pending inserts and periodically merges them via the pending list, controllable with the gin_pending_list_limit storage parameter.
Cricket analogy: It is like a wicket-analysis index in a stats book mapping each bowler's name to every match where they took a five-wicket haul; looking up 'Bumrah' instantly returns every relevant match instead of scanning the entire tournament history.
-- GIN index for JSONB containment queries
CREATE INDEX idx_products_attrs ON products USING gin (attributes);
SELECT * FROM products
WHERE attributes @> '{"color": "red", "in_stock": true}';
-- GIN index for full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search ON articles USING gin (search_vector);
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & indexing');For full-text search, generate the tsvector as a STORED generated column rather than computing to_tsvector() on the fly in every WHERE clause. This lets a single GIN index defined on the generated column be reused by any query, and it means the expensive tokenization work happens once per write, not once per read.
GiST: Generalized Search Tree
GiST (Generalized Search Tree) is a balanced, tree-based framework, unlike GIN's flat inverted structure, that supports a broader family of operators including nearest-neighbor (<->) ordering, overlap, and containment, which makes it the natural choice for geometric types, PostGIS geography/geometry columns, and range types like tsrange or int4range where 'do these two ranges overlap' is the core question. Each internal GiST node stores a bounding summary (for example, a bounding box for geometric data) of everything beneath it, so a query can prune entire subtrees whose bounding summary cannot possibly satisfy the predicate, similar in spirit to a B-tree's pruning but generalized to non-linear, multi-dimensional data that has no single natural sort order.
Cricket analogy: It is like a fielding coach dividing the outfield into overlapping zones (deep midwicket, long-on) and instantly ruling out zones a ball's trajectory couldn't reach, rather than tracking every blade of grass individually.
GIN and GiST are not interchangeable for every data type. GIN generally gives faster lookups but slower, larger-footprint updates and is the default (and usually best) choice for JSONB and full-text search; GiST is generally better for geometric/range data with nearest-neighbor queries and for exclusion constraints (EXCLUDE USING gist), since GIN has no native concept of 'nearest' or spatial overlap pruning. When in doubt, benchmark both with representative data rather than assuming one is universally faster.
SP-GiST and RUM: Related Alternatives
PostgreSQL also ships SP-GiST (Space-Partitioned GiST), which trades GiST's balanced, overlapping bounding boxes for a non-balanced, non-overlapping partitioning scheme better suited to data with natural clustering or skew, such as IP address ranges (inet), quad-tree-style point data, or text with common prefixes indexed via a radix tree. The contrib-adjacent RUM index extension goes further than GIN for full-text search by additionally storing lexeme positions, which lets it support fast phrase search and ORDER BY relevance ranking (ts_rank) directly from the index, something plain GIN cannot do because it discards positional information to keep entries compact.
Cricket analogy: It is like scouting systems splitting players not into evenly balanced tiers but into natural clusters by playing style (pace bowlers, spinners, top-order bats), a partitioning that fits the data's real shape better than forcing equal-sized bins.
- GIN is an inverted index mapping each key inside a composite value (array element, JSONB key, lexeme) to the rows containing it.
- GIN excels at containment (@>, <@) and full-text search (@@) queries on arrays, JSONB, and tsvector columns.
- GIN updates are batched via a pending list (gin_pending_list_limit) because a single row can touch many keys.
- GiST is a balanced tree using bounding summaries per node to prune subtrees for non-linear, multi-dimensional data.
- GiST supports nearest-neighbor ordering (<->), geometric/PostGIS types, range overlap, and EXCLUDE USING gist constraints.
- Generated STORED tsvector columns avoid recomputing full-text tokenization on every query.
- Choosing between GIN and GiST should be validated by benchmarking against the actual data and query shape.
Practice what you learned
1. What is the fundamental structural difference between GIN and a B-tree index?
2. Why are GIN index updates typically more expensive than B-tree updates?
3. Which query type is GiST particularly well suited for that GIN is not?
4. Why is it recommended to store a tsvector as a generated STORED column rather than computing to_tsvector() inline in every query?
5. What PostgreSQL feature commonly relies on a GiST index to prevent overlapping data, such as double-booked date ranges?
Was this page helpful?
You May Also Like
B-Tree Indexes Explained
Learn how PostgreSQL's default B-tree index works internally and why it is the right choice for equality and range queries.
Indexing Strategies
Practical guidance for deciding what to index, how to order multicolumn indexes, and how to validate index effectiveness in PostgreSQL.
Partial and Expression Indexes
Use partial indexes to index only a relevant subset of rows and expression indexes to index computed values, keeping indexes smaller and more targeted.
Index Maintenance and Bloat
Understand why indexes accumulate dead space over time, how to detect bloat, and the tools PostgreSQL provides to reclaim it safely.