Part 134: Specialized Indexes: Hash, GIN, GiST & Full-Text

Beyond standard B-Trees, modern databases provide specialized index engines:

1. Specialized Index Types in PostgreSQL

• Hash Index: $O(1)$ equality lookups only (=).
• GIN (Generalized Inverted Index): Indexes composite items like JSONB arrays, full-text search tsvectors, and tags.
• GiST (Generalized Search Tree): Geometric, geospatial (PostGIS), and range types.
• BRIN (Block Range Index): For massive multi-terabyte append-only timeseries tables.

Key Takeaways

• Use GIN for JSONB and search; use BRIN for multi-gigabyte append-only logs.

In Part 135, we explore Covering Indexes and Index-Only Scans!