Database indexing: Poor indexing is behind most database performance problems. This comprehensive guide covers B-tree internals, composite index strategy, pa... This guide explains Database Indexing Deep Dive: AI-Native Agency | WRKSHP with practical patterns WRKSHP uses on client builds. Use the checklist below to prioritize next steps and avoid common mistakes that waste time and money.

Poor indexing is behind most database performance problems. This comprehensive guide covers B-tree internals, composite index strategy, partial indexes, and the monitoring queries that find missing indexes. This guide explains Database Indexing Deep Dive: AI-Native Agency | WRKSHP with practical patterns WRKSHP uses on client builds.

How Indexes Actually Work

To use indexes effectively, you need to understand what they're doing under the hood. Let's start with the most common index type: B-tree.

B-tree Fundamentals

A B-tree (balanced tree) is a sorted data structure optimized for disk access. Imagine a dictionary: instead of reading every page to find "zebra," you use the alphabetical organization to jump directly to the Z section.

A B-tree works similarly. Data is organized in pages (typically 8KB), with each page containing sorted key values and pointers. Finding a value requires traversing from the root page down through branch pages to leaf pages, with each level narrowing the search.

Key characteristics:

• Logarithmic search time: Finding any value requires O(log n) page reads. Even with billions of rows, you need only ~30 page reads.
• Sorted order: Values are stored in sorted order, making range queries efficient.
• Leaf page links: Leaf pages link to each other, enabling efficient range scans.

-- This query uses the B-tree's sorted structure efficiently
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

-- The index on created_at enables:
-- 1. Find the first matching leaf page (O(log n))
-- 2. Scan forward through linked leaf pages
-- 3. Stop when values exceed the range

What's Stored in an Index

An index entry contains:

1. The indexed column value(s)
2. A pointer to the actual row (typically the primary key or row ID)

When you query using an index, the database:

1. Searches the index to find matching entries
2. Uses the pointers to fetch the actual rows from the table (unless covering index)

This second step, fetching from the table, is called a "heap lookup" or "table access by index rowid." It's often the hidden cost in index usage.

Index Types and When to Use Them

B-tree (Default)

Use for:

• Equality comparisons (=)
• Range comparisons (<, >, BETWEEN)
• Sorting (ORDER BY)
• Prefix matching (LIKE 'abc%')

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);

-- Both of these use the indexes efficiently
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE created_at > '2024-01-01' ORDER BY created_at;

Hash Index

Use for: Equality comparisons only, when you'll never need range queries.

Hash indexes are faster than B-trees for exact matches but can't support range queries or sorting. In PostgreSQL, they're rarely better than B-trees in practice.

CREATE INDEX idx_sessions_token ON sessions USING HASH (token);

-- Efficient
SELECT * FROM sessions WHERE token = 'abc123';

-- Cannot use this index
SELECT * FROM sessions WHERE token LIKE 'abc%';

GIN (Generalized Inverted Index)

Use for: Full-text search, array contains, JSONB containment.

GIN indexes are optimized for values that contain multiple elements (arrays, full-text documents, JSON). They store a mapping from each element to the rows containing it.

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', content));

SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('database & indexing');

-- Array contains
CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT * FROM products WHERE tags @> ARRAY['electronics', 'sale'];

-- JSONB containment
CREATE INDEX idx_events_data ON events USING GIN (data);

SELECT * FROM events WHERE data @> '{"type": "click"}';

GIST (Generalized Search Tree)

Use for: Geometric data, range types, nearest-neighbor searches.

-- PostGIS spatial queries
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(-122.4, 37.8), 1000);

-- Range overlap
CREATE INDEX idx_reservations_dates ON reservations USING GIST (daterange(start_date, end_date));

SELECT * FROM reservations
WHERE daterange(start_date, end_date) && '[2024-06-01, 2024-06-15]';

BRIN (Block Range INdex)

Use for: Very large tables with naturally ordered data (like time-series).

BRIN indexes are tiny, they store only min/max values per block range. They're ideal for append-only tables where data is inserted in order.

-- For time-series data that's inserted in order
CREATE INDEX idx_events_timestamp ON events USING BRIN (timestamp);

-- Efficient for range queries on ordered data
SELECT * FROM events WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-02';

Composite Index Strategy

Composite indexes (multi-column indexes) are where index design gets interesting. The column order matters enormously.

The Leftmost Prefix Rule

A composite index can be used for queries on the leftmost columns. An index on (a, b, c) can be used for queries on:

• a alone
• a and b
• a, b, and c

But NOT for queries on:

• b alone
• c alone
• b and c

CREATE INDEX idx_orders_composite ON orders(customer_id, status, created_at);

-- Uses the index (customer_id is leftmost)
SELECT * FROM orders WHERE customer_id = 123;

-- Uses the index (customer_id + status)
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- Uses the index fully
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending' AND created_at > '2024-01-01';

-- Cannot use this index (status is not leftmost)
SELECT * FROM orders WHERE status = 'pending';

Column Order Guidelines

Order columns by:

1. Equality conditions first: Columns used with = go first
2. Range conditions last: Columns used with <, >, BETWEEN go last
3. Selectivity: Among equality columns, put the most selective (fewest matching rows) first

-- Query: WHERE tenant_id = ? AND status = ? AND created_at > ?

-- Good index order
CREATE INDEX idx_orders ON orders(tenant_id, status, created_at);
-- tenant_id and status are equality → can use index for both
-- created_at is range → goes last, enables index range scan

-- Bad index order
CREATE INDEX idx_orders_bad ON orders(created_at, tenant_id, status);
-- created_at is range → stops index usability for later columns

Covering Indexes

A covering index includes all columns needed by a query, eliminating table lookups. The query can be satisfied entirely from the index.

-- Query
SELECT order_id, status, total FROM orders WHERE customer_id = 123;

-- Regular index (requires table lookup for total)
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Covering index (no table lookup needed)
CREATE INDEX idx_orders_customer_covering ON orders(customer_id) INCLUDE (status, total);

-- PostgreSQL will show "Index Only Scan" in EXPLAIN

Covering indexes trade storage space for query performance. Use them for frequently-executed queries where the extra columns are small.

Partial Indexes

Partial indexes index only a subset of rows, making them smaller and more efficient for specific query patterns.

-- Full index: indexes all 10 million orders
CREATE INDEX idx_orders_status ON orders(status);

-- Partial index: indexes only pending orders (maybe 100k)
CREATE INDEX idx_orders_pending ON orders(status) WHERE status = 'pending';

-- Use case: You frequently query pending orders but rarely query completed ones
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 123;

-- Another example: Index only recent data
CREATE INDEX idx_events_recent ON events(user_id, event_type)
WHERE created_at > NOW() - INTERVAL '30 days';

Partial indexes are especially valuable for:

• Querying rare statuses (pending orders, unread messages)
• Soft-deleted data (WHERE deleted_at IS NULL)
• Active records in a mostly-historical table

Index Anti-Patterns

Function Calls on Indexed Columns

Indexes on column values don't help when you apply functions:

-- Cannot use index on created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Can use index
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- Or create a functional index
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));

Type Mismatches

Implicit type conversion can prevent index usage:

-- If user_id is INTEGER
CREATE INDEX idx_orders_user ON orders(user_id);

-- Cannot use index (string compared to integer)
SELECT * FROM orders WHERE user_id = '123';

-- Can use index
SELECT * FROM orders WHERE user_id = 123;

Leading Wildcards

LIKE patterns starting with % cannot use B-tree indexes:

-- Cannot use index
SELECT * FROM products WHERE name LIKE '%shirt%';

-- Can use index (prefix matching)
SELECT * FROM products WHERE name LIKE 'shirt%';

-- For arbitrary substring search, use full-text search
CREATE INDEX idx_products_name_fts ON products USING GIN (to_tsvector('english', name));

Over-Indexing

Every index has costs:

• Storage space
• Write overhead (every INSERT/UPDATE/DELETE must update all relevant indexes)
• Planner time (optimizer must consider more options)

Don't create indexes speculatively. Create them when you have evidence of need.

Finding Missing Indexes

PostgreSQL: pg_stat_user_tables

Identify tables with sequential scans that might benefit from indexes:

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

High seq_tup_read with low idx_scan suggests missing indexes.

Slow Query Logs

Enable logging of slow queries:

-- PostgreSQL
log_min_duration_statement = 1000  -- Log queries over 1 second

-- MySQL
slow_query_log = 1
long_query_time = 1

Analyze the slow query log to find patterns, queries that appear frequently are index candidates.

EXPLAIN ANALYZE

The most powerful diagnostic tool. Shows exactly how the database executes a query:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

Look for:

• Seq Scan: Sequential table scan, often indicates missing index
• High rows vs actual rows: Poor statistics, consider ANALYZE
• Nested Loop with high loops: May indicate missing index on join column
• Buffers shared read: High numbers indicate cold cache or inefficient access

Index Maintenance

Bloat Management

In PostgreSQL, UPDATE and DELETE operations leave dead tuples. Indexes can become bloated with pointers to dead tuples.

Monitor bloat:

-- Check index bloat (simplified)
SELECT
  indexrelid::regclass AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan AS scans
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Rebuild bloated indexes:

-- Blocking rebuild
REINDEX INDEX idx_orders_customer;

-- Non-blocking rebuild (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_customer;

Statistics Updates

The query planner relies on statistics to choose indexes. Stale statistics lead to poor plans.

-- Update statistics for a table
ANALYZE orders;

-- Check when statistics were last updated
SELECT
  relname,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables;

Consider increasing statistics targets for columns with skewed distributions:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Production Optimization Workflow

When facing index-related performance issues:

1. Identify slow queries: Use slow query logs, APM tools, or pg_stat_statements
2. Analyze execution plans: EXPLAIN ANALYZE suspicious queries
3. Identify patterns: Group similar queries, one index might help many queries
4. Design the index: Consider column order, partial indexes, covering indexes
5. Test in staging: Verify the index helps without breaking other queries
6. Create with CONCURRENTLY: Avoid blocking production writes
7. Monitor: Verify the index is being used, check for regression in other queries
8. Cleanup: Remove unused indexes that add write overhead

-- Create without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_new ON orders(customer_id, status);

-- Check if index is being used
SELECT
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';

Conclusion

Effective indexing isn't about creating more indexes, it's about creating the right indexes. Understand your query patterns, analyze execution plans, and design indexes that match how your application actually accesses data.

The key principles:

• Understand B-tree structure and the leftmost prefix rule
• Order composite index columns by equality first, ranges last
• Use partial indexes for queries on subsets of data
• Monitor for unused indexes and remove them
• Keep statistics current
• Always test changes before production

Indexing is an ongoing practice, not a one-time setup. As your data grows and query patterns evolve, your indexing strategy must evolve too.

Work With WRKSHP

WRKSHP builds AI-native software, operated growth systems, and governance layers for teams that sell outcomes, not billable hours.

Explore enterprise software, Growth OS, GaaS governance, contact, or start a conversation about your next build.

Frequently Asked Questions

What is the main takeaway from "Database Indexing Deep Dive: AI-Native Agency | WRKSHP"?

Database indexing: Poor indexing is behind most database performance problems. Use it as a production playbook for operators and engineers, not slide-deck theory.

How does "How Indexes Actually Work" fit into Database Indexing Deep Dive: AI-Native Agency | WRKSHP?

How Indexes Actually Work is a core section of this guide. Apply it after you pick one measurable KPI, then instrument the path that moves that KPI before expanding scope.

What should I watch when working through "Index Types and When to Use Them"?

Treat "Index Types and When to Use Them" as a decision checkpoint: name an owner, define success metrics, and refuse to automate steps that spend money or change production data without an audit trail.

When should I bring in a partner on Database Indexing Deep Dive: AI-Native Agency | WRKSHP?

Bring in help when you need a fixed-outcome delivery model, governance for agent actions, or a single platform spanning build and growth, patterns WRKSHP uses on enterprise software and Growth OS engagements.