postgres-impl-indexing-strategy

Quick Reference :

PostgreSQL ships six built-in index access methods. The choice is not stylistic : the wrong access method makes a query unindexable for the operator it uses. Pick the method by the operator class the query needs, not by habit.

• B-tree (default) : equality + range + BETWEEN/IN + IS NULL + LIKE 'prefix%' + sorted output. The ONLY method that backs UNIQUE, primary keys, and ORDER BY sort-avoidance.
• GIN : multi-value columns , jsonb, arrays, full-text tsvector, pg_trgm. Fast lookup, slow update.
• GiST : ranges, geometry/PostGIS, exclusion constraints, k-nearest-neighbour (ORDER BY col <-> point), FTS. Updatable.
• SP-GiST : non-balanced data , quadtrees, k-d trees, IP-prefix hierarchies, text-prefix. Single-column only.
• BRIN : very large tables whose physical row order correlates with the column (append-only time-series). Tiny on disk, block-range summaries.
• Hash : equality only. WAL-logged + replicated since v10. Rarely beats B-tree , default to B-tree unless measured.

Three orthogonal modifiers apply on top of the method : partial (WHERE predicate , index a row subset), expression ((lower(email)) , index a computed value), and INCLUDE (v11+ , non-key payload columns for index-only scans). Build production indexes with CREATE INDEX CONCURRENTLY to avoid an ACCESS EXCLUSIVE lock.

ALWAYS index every foreign-key column on the child side. ALWAYS make an expression index match the query expression byte-for-byte. NEVER create an index whose key columns are already a prefix of another index.

When To Use This Skill :