PostgreSQL Indexing Strategies

Index Type	Best Use Cases	When to Use	Limitations	Performance Impact
B-Tree (Default)	Equality, range queries, sorting	• Most common queries  • WHERE col = value  • WHERE col > value  • ORDER BY operations  • Primary keys, foreign keys	• Not efficient for partial matches  • No support for complex data types  • Can become unbalanced with skewed data	• Fast lookups: O(log n)  • Moderate storage overhead  • Good for most workloads
Hash	Equality queries only	• Simple equality checks  • WHERE col = value  • High-cardinality columns  • When range queries not needed	• Only supports equality (=)  • No range queries  • No sorting support  • Not WAL-logged (unsafe for replication)	• Very fast equality: O(1)  • Lower storage than B-Tree  • Not crash-safe
GIN (Generalised Inverted)	Full-text search, arrays, JSON	• Text search with tsvector  • Array contains operations  • JSONB queries  • Multi-value columns	• Large storage overhead  • Slow updates (can batch)  • Not suitable for simple equality	• Excellent for complex searches  • High storage cost  • Slow writes, fast reads
GiST (Generalized Search Tree)	Geometric data, full-text	• PostGIS spatial queries  • Range types  • Full-text search  • Nearest neighbor searches	• More CPU intensive  • Slower than specialized indexes  • Complex to tune	• Flexible but slower  • Good for complex data types  • Extensible framework
SP-GiST (Space-Partitioned GiST)	Non-balanced data structures	• IP addresses (inet)  • Text patterns  • Quad-trees, k-d trees  • Hierarchical data	• Limited use cases  • Not widely supported  • Complex implementation	• Efficient for specific patterns  • Lower memory usage  • Specialised scenarios only
BRIN (Block Range Index)	Large tables with natural ordering	• Time-series data  • Monotonically increasing IDs  • Date/timestamp columns  • Very large tables (TB+)	• Only effective with sorted data  • Poor performance on random data  • Limited query types	• Extremely small size  • Fast creation  • Good for append-only data

Index Modifiers and Special Cases

Modifier/Type	Description	When to Use	Limitations
UNIQUE	Enforces uniqueness	Primary keys, unique constraints	Additional overhead for uniqueness checks
PARTIAL	Index subset of rows	WHERE conditions in CREATE INDEX	Only helps queries matching the condition
EXPRESSION	Index on computed values	Functions in WHERE clauses	Must match exact expression in queries
MULTICOLUMN	Multiple columns in one index	Compound WHERE conditions	Column order matters, left-prefix rule
CONCURRENT	Non-blocking index creation	Production systems	Takes longer, uses more resources

Column-Based Recommendations

Data Type/Pattern	Recommended Index	Reasoning
Primary Keys	B-Tree (automatic)	Fast lookups, unique constraint
Foreign Keys	B-Tree	Join performance
Timestamps	B-Tree or BRIN	Range queries, BRIN for time-series
Text (exact match)	B-Tree or Hash	B-Tree for flexibility, Hash for pure equality
Text (search)	GIN with tsvector	Full-text search capabilities
JSON/JSONB	GIN	Complex nested queries
Arrays	GIN	Contains and overlap operations
Geographic	GiST (PostGIS)	Spatial queries
IP Addresses	SP-GiST	Network operations
UUIDs	B-Tree	Standard lookups

Performance Guidelines

When to Add Indexes

• Columns frequently used in WHERE clauses

• JOIN conditions

• ORDER BY columns

• Columns with high selectivity (many unique values)

• Foreign key relationships

When to Avoid Indexes

• Small tables (< 1000 rows)

• Columns with low selectivity (few unique values)

• Tables with heavy INSERT/UPDATE workloads

• Temporary or staging tables

Maintenance Considerations

• REINDEX periodically for heavily updated indexes

• Monitor index bloat with pg_stat_user_indexes

• Use ANALYSE after significant data changes

• Consider FILLFACTOR for frequently updated tables

• Drop unused indexes (check pg_stat_user_indexes.idx_scan)