PostgreSQL Indexing & Performance Tuning: B-Tree vs. Hash vs. GiST Indexes
Database query performance directly governs application latency. In PostgreSQL, choosing the proper indexing strategy can transform a 10-second sequential scan into a 2-millisecond index scan.
1. B-Tree: The Universal Workhorse
B-Tree indexes are the default for equality and range queries (<, <=, =, >=, >). They maintain balanced tree depth, guaranteeing O(log N) lookup time even across millions of rows.
2. Advanced Indexes: GIN and GiST for JSON and Text
When querying JSONB columns, array memberships, or full-text search vectors, Generalized Inverted Indexes (GIN) provide orders of magnitude faster lookups by mapping keys directly to row pointers.
| Index Type | Optimal Use Case | Query Operators |
|---|---|---|
| B-Tree | Primary keys, foreign keys, timestamps, numbers | =, <, >, BETWEEN, ORDER BY |
| GIN (Generalized Inverted) | JSONB fields, array containment, full-text search | @>, ?, @@ |
| Hash | Exact equality matching only | = |
| BRIN (Block Range) | Naturally ordered append-only time-series data | Range scans on massive tables |