PostgreSQL interview question · Most asked
What index types does PostgreSQL support, and when do you use each?
Summary
B-tree (default: equality, ranges, sorting), Hash (equality only), GIN (inverted index for JSONB, arrays, full-text, trigrams), GiST (geometric data, ranges, nearest-neighbour, exclusion constraints), SP-GiST (partitioned search trees such as quadtrees), and BRIN (tiny block-range summaries for huge, naturally ordered tables such as time-series).
Longer Answer
- B-tree: almost everything; supports multicolumn, unique,
INCLUDEcolumns and index-only scans. - GIN: "does this document contain key X / this array contain element Y / this text match these words"; slower to update (uses a pending list), fast to search.
- GiST: PostGIS geometries,
tsrangeoverlaps,<->distance ordering (k-nearest neighbour). - BRIN: stores min/max per range of pages; a few kilobytes for billions of rows, effective only when values correlate with physical order (append-only
created_at). - Extensions add more, for example HNSW and IVFFlat indexes in
pgvectorfor similarity search.
Key Points
- B-tree default.
- GIN for JSONB/arrays/full-text.
- GiST for geometry, ranges, KNN.
- BRIN for huge ordered tables.
More most-asked PostgreSQL questions
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?
- What does VACUUM do, and how does autovacuum work?
- What isolation levels does PostgreSQL provide, and how do they behave?
- How does MVCC work in PostgreSQL?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- How do you build a job queue on PostgreSQL with SELECT ... FOR UPDATE SKIP LOCKED?