Blog

PostgreSQL Index Types: B-tree, Hash, GIN, GiST, SP-GiST, BRIN and Partial/Expression Indexes

In PostgreSQL the shape of the query, not the column type, decides the index type: B-tree for equality and ranges, GIN for arrays, JSONB and ILIKE '%...%', GiST for overlaps, BRIN for tables that grow over time. A query that reads 26 pages instead of 10,328 with 18's skip scan, deduplication, INCLUDE and index-only scans, a 24 kB BRIN instead of a 21 MB B-tree, hash, partial and expression indexes, an INVALID index left by CONCURRENTLY and an index that breaks HOT updates.