Database Indexing Strategies for High-Performance Queries
Types of Indexes
| Index Type | Best For | Limitations |
|---|---|---|
| B-Tree | Range queries, equality, sorting | Overhead on write-heavy tables |
| Hash | Exact-match lookups | No range/ORDER BY support |
| GIN | Full-text search, arrays | Slower builds, more disk space |
| GiST | Geometric, full-text | Complex implementation |
Composite Index Design
Order columns by selectivity (most selective first):
-- Good: status has few values, created_at is highly selective
CREATE INDEX idx_status_created ON orders (status, created_at);
-- This query uses the index efficiently:
SELECT * FROM orders WHERE status = 'ACTIVE' AND created_at > '2026-01-01';
Covering Indexes
Include all columns needed by a query to avoid table lookups:
CREATE INDEX idx_covering ON employees (department, salary) INCLUDE (name, email);
-- The INCLUDE clause stores extra columns at the leaf level without sorting them.
When NOT to Index
- Small tables (< 1000 rows) — full table scans are faster
- Columns rarely used in WHERE/JOIN/ORDER BY
- Columns with very low cardinality (e.g., boolean flags)
- Heavy OLTP tables with high write volume — each index adds write overhead