Index Design 2026

B-tree, GIN, hash.

Overview

Modern index design matches the index type to the query pattern. B-tree, GIN, hash, partial, and covering indexes each fit specific shapes; blanket indexing slows writes without speeding reads where it matters.

• Index type per query pattern. B-tree for range and equality; GIN for JSON and full-text; hash for pure equality at scale.
• B-tree default. Range queries, equality, ORDER BY. The right answer for most production indexes.
• GIN for JSON and full-text. Inverted index for documents, arrays, and tsvector full-text search.
• Partial and covering indexes. Partial indexes (WHERE active=true) reduce size; covering indexes (INCLUDE columns) avoid heap lookups.

The approach

Three habits make index design evidence-based: read EXPLAIN before adding any index, prefer partial indexes when applicable, and monitor unused indexes as a standing signal.

• EXPLAIN-driven. Read the query plan before adding an index. The plan tells you whether the index will actually help.
• Partial indexes where applicable. Many queries hit a small subset of rows; partial indexes shrink the index footprint dramatically.
• Covering indexes for read-heavy. INCLUDE columns let the database answer the query without touching the heap.
• Monitor unused plus document. pg_stat_user_indexes surfaces unused indexes for cleanup; per-index supporting query documented in the migration.

Why this compounds

Each correctly-designed index produces ongoing query benefit. The team learns the optimiser through repeated review; new indexes ship with the right shape from day one.

• Faster queries. Right index for the query produces measurable p99 improvements. Wrong index does nothing.
• Better write performance. Fewer unused indexes mean less write amplification. INSERT and UPDATE throughput improves.
• Storage efficiency. Partial indexes shrink the storage footprint. Cost benefits show up monthly.
• Year-one investment, year-two habit. The first index is investment. By year two, every new query gets a deliberate index decision rather than a reflexive one.