---
# === IDENTITY ===
id: software/patterns/database-indexing-strategies/2026
canonical_question: "What are the best database indexing strategies?"
aliases:
  - "How to create database indexes"
  - "Which index type should I use"
  - "Database index performance optimization"
  - "B-tree vs hash vs GIN index comparison"
entity_type: software_reference
domain: software > patterns > database indexing strategies
region: global
jurisdiction: global
temporal_scope: 2020-2026

# === VERIFICATION ===
last_verified: 2026-02-24
confidence: 0.90
version: 1.0
first_published: 2026-02-24

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: "PostgreSQL 17 / MySQL 9.0 / SQL Server 2025 — no breaking index changes"
  next_review: 2026-08-23
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "Every index adds write overhead — INSERT, UPDATE, DELETE all slow down per additional index"
  - "Composite index column order must match query filter/sort order (leftmost prefix rule)"
  - "Hash indexes do not support range queries — use B-tree for >, <, BETWEEN, ORDER BY"
  - "Never index columns wrapped in functions unless you create an expression index"
  - "BRIN indexes only work on physically sorted (correlated) data — random inserts destroy effectiveness"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "You need full-text search, not structured column indexing"
    use_instead: "Use database-specific full-text search (tsvector in PostgreSQL, FULLTEXT in MySQL)"
  - condition: "You need vector similarity search for embeddings"
    use_instead: "Use pgvector (PostgreSQL) or dedicated vector databases"

# === AGENT HINTS ===
inputs_needed:
  - key: database_engine
    question: "Which database engine are you using?"
    type: choice
    options: ["PostgreSQL", "MySQL", "SQL Server", "SQLite", "Oracle"]
  - key: workload_type
    question: "Is your workload read-heavy, write-heavy, or mixed?"
    type: choice
    options: ["read-heavy", "write-heavy", "mixed"]
  - key: data_type
    question: "What data types are you indexing?"
    type: choice
    options: ["scalar (int/text/date)", "JSONB/arrays", "geospatial", "full-text"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/patterns/database-indexing-strategies/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-24)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: software/patterns/sql-query-optimization/2026
      label: "SQL Query Optimization"
    - id: software/patterns/sql-materialized-views/2026
      label: "SQL Materialized Views"
    - id: software/patterns/caching-patterns/2026
      label: "Caching Patterns"
  solves:
    - id: software/patterns/slow-query-diagnosis/2026
      label: "Slow Query Diagnosis"
  often_confused_with:
    - id: software/patterns/full-text-search/2026
      label: "Full-Text Search Configuration"

# === SOURCES (7 authoritative sources) ===
sources:
  - id: src1
    title: "Use The Index, Luke — SQL Indexing and Tuning"
    author: Markus Winand
    url: https://use-the-index-luke.com/
    type: technical_blog
    published: 2024-01-15
    reliability: authoritative
  - id: src2
    title: "PostgreSQL Documentation: Index Types"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/indexes-types.html
    type: official_docs
    published: 2025-11-01
    reliability: authoritative
  - id: src3
    title: "MySQL 8.4 Reference Manual: Multiple-Column Indexes"
    author: Oracle Corporation
    url: https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html
    type: official_docs
    published: 2025-06-01
    reliability: authoritative
  - id: src4
    title: "SQL Server Index Architecture and Design Guide"
    author: Microsoft
    url: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src5
    title: "Understanding Postgres GIN Indexes: The Good and the Bad"
    author: pganalyze
    url: https://pganalyze.com/blog/gin-index
    type: technical_blog
    published: 2024-08-12
    reliability: high
  - id: src6
    title: "A Tour of Postgres Index Types"
    author: Citus Data
    url: https://www.citusdata.com/blog/2017/10/17/tour-of-postgres-index-types/
    type: technical_blog
    published: 2023-03-20
    reliability: high
  - id: src7
    title: "Columnstore Indexes — Design Guidance"
    author: Microsoft
    url: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance
    type: official_docs
    published: 2025-09-01
    reliability: high
---

# Database Indexing Strategies

## TL;DR

- **Bottom line**: Choose index types based on your query patterns — B-tree for range/sort queries, hash for equality-only, GIN for JSONB/arrays, BRIN for time-series, and partial indexes to reduce storage on filtered workloads.
- **Key tool/command**: `CREATE INDEX idx_name ON table (col1, col2) WHERE condition;`
- **Watch out for**: Over-indexing — every unused index wastes storage and slows writes with zero read benefit.
- **Works with**: PostgreSQL 13+, MySQL 8.0+, SQL Server 2019+, SQLite 3.35+ (syntax varies per engine).

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- Every index adds write overhead — INSERT/UPDATE/DELETE operations slow down per additional index. Benchmark write-heavy workloads before adding indexes. [src1]
- Composite index column order must follow the leftmost prefix rule: a `(a, b, c)` index serves queries on `(a)`, `(a, b)`, and `(a, b, c)` but NOT `(b, c)` alone. [src3]
- Hash indexes do not support range queries (`>`, `<`, `BETWEEN`, `ORDER BY`) — only exact equality (`=`). Use B-tree for anything else. [src2]
- Never apply functions to indexed columns in WHERE clauses (`WHERE UPPER(name) = 'FOO'`) unless you create an expression/functional index. The optimizer cannot use the base index. [src1]
- BRIN indexes require physically sorted (correlated) data to be effective. Random inserts destroy correlation and make BRIN useless. [src2]
- Partial indexes must have a WHERE clause that matches your query's filter exactly — the planner won't use a partial index if the query's condition doesn't match. [src2]

## Quick Reference

| Index Type | Best For | Lookup Speed | Write Overhead | Storage | Engine Support |
|---|---|---|---|---|---|
| B-tree | Range queries, sorting, equality | O(log n) | Moderate | Moderate | All engines |
| Hash | Equality-only lookups | O(1) amortized | Low | Low | PG 10+, MySQL (Memory only) |
| GIN (Generalized Inverted) | JSONB, arrays, full-text, trgm | O(1) per element | High (batch-friendly) | High | PostgreSQL only |
| GiST (Generalized Search Tree) | Geospatial, range types, nearest-neighbor | O(log n) | Moderate | Moderate | PostgreSQL only |
| SP-GiST (Space-Partitioned GiST) | Non-balanced structures (quad-trees, k-d trees) | O(log n) | Moderate | Moderate | PostgreSQL only |
| BRIN (Block Range Index) | Time-series, append-only sorted data | O(n) worst case | Very low | Very low | PostgreSQL 9.5+ |
| Partial Index | Filtered subsets (active rows, non-null) | Same as base type | Lower (fewer rows) | Lower | PG, SQL Server (filtered), SQLite |
| Covering Index (INCLUDE) | Index-only scans avoiding heap lookups | O(log n) | Moderate-high | Higher | PG 11+, SQL Server, MySQL 8.0+ |
| Composite Index | Multi-column filters and sorts | O(log n) | Moderate | Moderate | All engines |
| Columnstore | Analytics, aggregation on large tables | Scan-optimized | High (batch insert) | Very high (compressed) | SQL Server 2012+ |
| Expression/Functional Index | Queries on computed values (`LOWER(col)`) | O(log n) | Moderate | Moderate | PG, MySQL 8.0+ (functional) |

## Decision Tree

> Full script: [decision-tree.txt](scripts/decision-tree.txt) (28 lines)

```
START
├── Query uses only equality (=)?
│   ├── YES → Single column?
│   │   ├── YES → Hash index (PG) or B-tree (MySQL/SQL Server)
│   │   └── NO → Composite B-tree index (columns in selectivity order)
# ... (see full script)
```

## Step-by-Step Guide

### 1. Identify slow queries with EXPLAIN

Run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see if the query uses a sequential scan instead of an index scan. [src1]

```sql
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- SQL Server
SET STATISTICS IO ON;
SELECT * FROM orders WHERE customer_id = 42;
```

**Verify**: Look for `Seq Scan` (PG), `ALL` in type column (MySQL), or `Table Scan` (SQL Server) — these indicate missing indexes.

### 2. Create a B-tree index on filtered columns

Start with a standard B-tree index on the column(s) appearing in WHERE clauses. [src2]

```sql
-- PostgreSQL / MySQL
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- SQL Server
CREATE NONCLUSTERED INDEX idx_orders_customer ON orders (customer_id);
```

**Verify**: Re-run EXPLAIN — should now show `Index Scan` (PG), `ref` (MySQL), or `Index Seek` (SQL Server).

### 3. Add composite indexes for multi-column queries

For queries filtering on multiple columns, create a composite index with the most selective column first. [src3]

```sql
-- Query: WHERE status = 'active' AND created_at > '2026-01-01'
-- Equality columns first, range columns last
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
```

**Verify**: `EXPLAIN` should show the composite index being used with both conditions.

### 4. Use partial indexes to reduce index size

If queries consistently filter on a condition, create a partial index. [src2]

```sql
-- Only 5% of orders are 'pending' — index just those
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';
```

**Verify**: `SELECT pg_size_pretty(pg_relation_size('idx_orders_pending'));` — should be much smaller than a full index.

### 5. Create covering indexes to avoid table lookups

Include extra columns so the query can be answered entirely from the index. [src4]

```sql
-- PostgreSQL 11+
CREATE INDEX idx_orders_cover ON orders (customer_id)
  INCLUDE (total, created_at);

-- SQL Server
CREATE NONCLUSTERED INDEX idx_orders_cover ON orders (customer_id)
  INCLUDE (total, created_at);

-- MySQL (covering via composite — no INCLUDE keyword)
CREATE INDEX idx_orders_cover ON orders (customer_id, total, created_at);
```

**Verify**: EXPLAIN shows `Index Only Scan` (PG) or `Covering Index Scan` (SQL Server).

### 6. Monitor and remove unused indexes

Indexes that are never used waste storage and slow writes. Query system catalogs regularly. [src1]

```sql
-- PostgreSQL: find unused indexes
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL: check index usage
SELECT * FROM sys.schema_unused_indexes;

-- Drop unused indexes
DROP INDEX idx_unused_index;  -- PostgreSQL
DROP INDEX idx_unused_index ON table_name;  -- MySQL
```

**Verify**: After dropping, monitor write performance — INSERT throughput should improve.

## Code Examples

### PostgreSQL: GIN Index for JSONB Queries

```sql
-- Input:  Table with JSONB column storing product attributes
-- Output: Fast lookups on any key inside the JSONB document

-- Create a GIN index for containment queries (@>)
CREATE INDEX idx_products_attrs ON products USING gin (attributes);

-- Query: find products where attributes contain {"color": "red"}
SELECT id, name FROM products
WHERE attributes @> '{"color": "red"}'::jsonb;

-- For specific key lookups, use jsonb_path_ops (smaller, faster)
CREATE INDEX idx_products_attrs_path ON products
  USING gin (attributes jsonb_path_ops);
```

### PostgreSQL: BRIN Index for Time-Series Data

```sql
-- Input:  Append-only events table with timestamp column
-- Output: Extremely small index for range queries on time

-- BRIN stores min/max per block range (128 pages default)
CREATE INDEX idx_events_ts ON events USING brin (created_at);

-- Query: filter by time range — BRIN eliminates block ranges
SELECT * FROM events
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

-- Check physical correlation (must be > 0.9 for BRIN to help)
SELECT correlation FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';
```

### MySQL: Composite Index with Sort Optimization

```sql
-- Input:  Orders table queried by status + date range + sort
-- Output: Single index serving filter, range, and ORDER BY

-- Equality columns first, then range, then sort direction
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC);

-- This query uses the index for all three operations:
SELECT id, total, created_at FROM orders
WHERE status = 'shipped'
  AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- Verify with EXPLAIN — should show 'Using index condition'
EXPLAIN SELECT id, total, created_at FROM orders
WHERE status = 'shipped'
  AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;
```

### SQL Server: Filtered Index + Columnstore for Mixed Workloads

```sql
-- Input:  Large orders table with OLTP + reporting queries
-- Output: Filtered rowstore index for OLTP, columnstore for analytics

-- Filtered index: only active orders (OLTP fast path)
CREATE NONCLUSTERED INDEX idx_orders_active
ON orders (customer_id, created_at)
WHERE status = 'active';

-- Nonclustered columnstore: analytics on historical data
CREATE NONCLUSTERED COLUMNSTORE INDEX idx_orders_analytics
ON orders (customer_id, total, product_id, created_at)
WHERE status != 'active';
```

## Anti-Patterns

### Wrong: Indexing every column individually

```sql
-- BAD — creates N separate indexes that mostly go unused
-- The optimizer typically picks only ONE index per table scan
CREATE INDEX idx_col1 ON orders (customer_id);
CREATE INDEX idx_col2 ON orders (status);
CREATE INDEX idx_col3 ON orders (created_at);
CREATE INDEX idx_col4 ON orders (total);
CREATE INDEX idx_col5 ON orders (product_id);
```

### Correct: Create targeted composite indexes matching query patterns

```sql
-- GOOD — one composite index serves the most common query
CREATE INDEX idx_orders_main ON orders (customer_id, status, created_at);
-- Serves: WHERE customer_id = ?, WHERE customer_id = ? AND status = ?,
-- and WHERE customer_id = ? AND status = ? AND created_at > ?
```

### Wrong: Range column before equality column in composite index

```sql
-- BAD — range column first breaks index efficiency for equality filter
CREATE INDEX idx_orders_bad ON orders (created_at, status);

-- This query cannot efficiently use the index for status filtering:
SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-01-01';
```

### Correct: Equality columns first, range columns last

```sql
-- GOOD — equality columns narrow the search before range scan
CREATE INDEX idx_orders_good ON orders (status, created_at);

-- Now the index seeks to status='active' then scans the date range
SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-01-01';
```

### Wrong: Using functions on indexed columns

```sql
-- BAD — wrapping the column in a function prevents index usage
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
```

### Correct: Use expression indexes or rewrite the query

```sql
-- GOOD (PostgreSQL) — expression index on the function result
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- GOOD (MySQL) — rewrite to avoid function on column
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
```

### Wrong: Indexing low-cardinality boolean/status columns alone

```sql
-- BAD — a boolean column has only 2 values, full index has no selectivity
CREATE INDEX idx_users_active ON users (is_active);
-- The optimizer may ignore this index entirely (table scan is cheaper)
```

### Correct: Use a partial index on the rare value

```sql
-- GOOD — partial index on the minority value is tiny and targeted
CREATE INDEX idx_users_active ON users (id) WHERE is_active = true;
-- Only indexes the ~5% of active users, not the entire table
```

### Wrong: Never checking if indexes are actually used

```sql
-- BAD — creating indexes without monitoring leads to index bloat
-- Common in ORMs that auto-generate indexes for every foreign key
-- Result: 50+ indexes on a table, most never queried
```

### Correct: Regularly audit index usage and drop dead indexes

```sql
-- GOOD — query pg_stat_user_indexes to find unused indexes
SELECT indexrelname AS index_name,
       idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Then DROP INDEX for any index with 0 scans over weeks of production traffic
```

## Common Pitfalls

- **Index bloat in PostgreSQL**: Dead tuples from UPDATE/DELETE inflate index size. Fix: schedule regular `REINDEX CONCURRENTLY idx_name;` or tune `autovacuum_vacuum_scale_factor` lower (e.g., 0.05). [src2]
- **Implicit type casting defeats indexes**: `WHERE int_column = '42'` forces a cast, bypassing the index. Fix: ensure query parameter types match column types exactly. [src1]
- **MySQL FORCE INDEX causes plan regression**: Optimizer hints like `FORCE INDEX` become stale as data distribution changes. Fix: remove hints and let the optimizer choose; use `ANALYZE TABLE` to refresh statistics. [src3]
- **Partial index not used due to mismatched WHERE clause**: The planner only uses a partial index when your query's WHERE condition logically implies the index's predicate. Fix: make query condition match or be a subset of the index condition. [src2]
- **BRIN on randomly inserted data is useless**: BRIN relies on physical row order correlation. If rows are inserted in random order, every block range overlaps. Fix: check `pg_stats.correlation` — value must be close to 1.0 or -1.0. [src6]
- **Covering index INCLUDE columns are not searchable**: Columns in INCLUDE are stored in leaf pages only — they cannot be used for filtering or sorting, only to avoid heap lookups. Fix: put searchable columns in the key, non-searchable in INCLUDE. [src4]
- **Too many indexes on write-heavy tables**: Each INSERT updates every index. On a table with 15 indexes, inserts can be 5-10x slower. Fix: keep indexes under 5-7 per table on write-heavy workloads. [src1]

## Diagnostic Commands

> Full script: [diagnostic-commands.sql](scripts/diagnostic-commands.sql) (36 lines)

```sql
-- PostgreSQL: check index usage statistics
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- PostgreSQL: check table index sizes
# ... (see full script)
```

## Version History & Compatibility

| Feature | PostgreSQL | MySQL | SQL Server | Notes |
|---|---|---|---|---|
| B-tree | All versions | All versions | All versions | Universal default |
| Hash index | 10+ (WAL-logged) | Memory engine only | N/A | PG hash indexes crash-safe since v10 |
| GIN | 8.2+ | N/A | N/A | PostgreSQL-specific |
| GiST | 7.0+ | N/A | N/A | PostgreSQL-specific |
| BRIN | 9.5+ | N/A | N/A | PostgreSQL-specific |
| Partial/Filtered Index | All versions | N/A | 2008+ | MySQL lacks partial indexes |
| Covering (INCLUDE) | 11+ | 8.0+ (implicit) | 2005+ | MySQL uses composite for covering |
| Expression/Functional | All versions | 8.0+ | Via computed columns | MySQL added in 8.0 |
| Columnstore | N/A | N/A | 2012+ | SQL Server-specific |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Queries filter on specific columns repeatedly | Table has < 1000 rows | Full table scan (faster for tiny tables) |
| Read/query workload dominates | Write throughput is the bottleneck | Batch inserts, then add indexes after bulk load |
| Query needs sorted output (ORDER BY) | Data is write-once, read-never (audit logs) | Append-only table, no indexes |
| Covering index eliminates heap lookups | You're indexing every column "just in case" | Targeted indexes based on actual query patterns |
| JSONB containment queries are slow | JSON values are small and rarely queried | No index; sequential scan on small data |
| Time-series data with range filters | Data is inserted in random order | B-tree (BRIN requires physical correlation) |

## Important Caveats

- PostgreSQL hash indexes were not WAL-logged before version 10 — they were unsafe for production. Always use PG 10+ for hash indexes. [src2]
- MySQL InnoDB stores the primary key at every secondary index leaf node. Very wide primary keys (e.g., UUID) inflate all secondary indexes. Prefer integer auto-increment PKs on InnoDB. [src3]
- SQL Server clustered index physically reorders table data — you get exactly one per table. Choose it carefully (usually the primary key or most common range query column). [src4]
- Index maintenance (REINDEX, OPTIMIZE TABLE, ALTER INDEX REBUILD) can lock the table. Use CONCURRENTLY (PG) or online rebuild (SQL Server Enterprise) in production. [src2]
- Statistics staleness causes bad plans. PostgreSQL auto-analyzes, but after bulk loads run `ANALYZE table_name;` manually. MySQL: `ANALYZE TABLE`. SQL Server: `UPDATE STATISTICS`. [src1]
- Multicolumn GIN indexes in PostgreSQL do not support the `<@` (contained-by) operator — only `@>`, `?`, `?|`, `?&`. Check operator class support before creating. [src5]

## Related Units

- [SQL Query Optimization](/software/patterns/sql-query-optimization/2026)
- [SQL Materialized Views](/software/patterns/sql-materialized-views/2026)
- [Caching Patterns](/software/patterns/caching-patterns/2026)