Database Indexing Deep Dive

Master database indexing. Covers B-tree internals, composite index strategy, covering indexes, partial indexes, index maintenance, and common indexing mistakes that destroy performance.

Database indexing is the foundation of efficient query performance in relational and document databases. It matters when a query takes longer than a single memory access, when data grows beyond a few thousand rows, and when a single query dominates application latency. This page details how to design, implement, and maintain indexes that respond to real workloads—without relying on defaults, without hidden costs, and without failure modes that only emerge under load.

When to Add an Index

Add an index when a query consistently takes more than 10 milliseconds and returns fewer than 10% of the table’s rows. The decision is driven by a query that appears in profiles, logs, or tracing, and is identified by a slow execution pattern: sequential scans, high CPU, and excessive I/O.

For example, after analyzing a production trace, you identify a query:

SELECT id, name, email, created_at
FROM users
WHERE created_at >= '2024-01-01'
  AND status = 'active'
ORDER BY created_at DESC
LIMIT 10;

This query runs at 27ms on a 1.2 million-row users table, scanning all rows. You decide to index it.

How to Create an Index

Create an index using CREATE INDEX, with explicit options. The command is not just a one-liner, but a decision point in the system.

CREATE INDEX idx_users_active_recent
  ON users (status, created_at DESC)
  WHERE status = 'active'
  WITH (fillfactor = 90, parallel_workers = 4);

The index is not built immediately. The command runs asynchronously, and you can monitor progress:

SELECT
  schemaname,
  tablename,
  indexname,
  reltuples,
  relpages,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname = 'idx_users_active_recent';

When idx_tup_read exceeds idx_tup_fetch, the index is not selective enough—many rows are read but not fetched.

How Indexes Are Used in Queries

The database chooses to use an index based on cost, determined by the number of pages scanned, the selectivity of predicates, and the number of rows returned.

A query using the index above will:

This is a index-only scan when the index contains all required columns:

CREATE INDEX idx_users_active_recent_covering
  ON users (status, created_at DESC)
  INCLUDE (id, name, email, created_at)
  WHERE status = 'active';

Now, if the query only needs id, name, email, and created_at, the database can satisfy it entirely from the index, skipping the heap table access.

Choosing the Right Index Type

B-tree indexes are the default and best for equality, range, and sort operations. Use them for WHERE, ORDER BY, and JOIN on columns with high selectivity.

For IN clauses over many values, use bitmap indexes:

CREATE INDEX idx_users_tags_bitmap ON users USING bitmap (tags);

The database builds a bitmap for each tag, then combines them with bitwise operations. The query planner uses bitmap scan and bitmap heap scan to efficiently merge results.

For full-text search, use GIN (Generalized Inverted Index):

CREATE INDEX idx_users_search
  ON users USING gin (to_tsvector('english', concat(name, ' ', email, ' ', bio)));

GIN indexes store inverted lists and allow efficient @@ (contains) and & (and) operators.

For spatial data, use GiST (Generalized Search Tree):

CREATE INDEX idx_locations_gist
  ON locations USING gist (geography_point);

GiST supports approximate and range queries on geometries, with support for ST_DWithin, ST_Contains, and ST_Intersects.

Index Maintenance and Performance

Indexes grow and degrade over time. The database must maintain them during INSERT, UPDATE, and DELETE.

  SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexname)) AS index_size,
    pg_size_pretty(pg_total_relation_size(indexname) - pg_relation_size(indexname)) AS bloat
  FROM pg_indexes
  WHERE schemaname = 'public'
  ORDER BY bloat DESC;
  REINDEX INDEX idx_users_active_recent;

This rewrites the entire index, reducing page splits and improving cache locality.

If id, name, email, and created_at are large and stored in TOAST, the index may need to read TOAST pages during index-only scans. The query planner may fall back to heap scans when TOAST fetches dominate.

When Indexes Fail

Index failure is subtle and often unnoticed until performance degrades.

  EXPLAIN
  SELECT id, name
  FROM users
  WHERE created_at >= '2024-01-01'
    AND status = 'active';

The output shows Seq Scan on users, not Index Scan. The cause is:

Diagnosis:

  SELECT
    i.indexrelid,
    i.indrelid,
    i.indisunique,
    i.indisprimary,
    i.indkey,
    pg_class.relname,
    pg_class.relpages,
    pg_class.reltuples
  FROM pg_index i
  JOIN pg_class ON pg_class.oid = i.indexrelid
  WHERE i.indrelid = 'users'::regclass;

If indkey has a column that does not exist in the table, the index is corrupted.

The solution is to ensure INSERT and UPDATE operations run in the same transaction as the index updates, or to use CONCURRENTLY for index creation:

  CREATE INDEX CONCURRENTLY idx_users_email
    ON users (email);

This allows the table to be available for reads during index creation, but requires that the index is built in the background and updated incrementally.

Tuning Indexes for Workload

Different workloads require different index strategies.

  CREATE INDEX idx_users_report
    ON users (status, created_at DESC)
    INCLUDE (name, email, id)
    WHERE status = 'active';

This index supports SELECT, JOIN, ORDER BY, and LIMIT without touching the heap.

When inserting 10,000 rows per second, fillfactor = 70 reduces page splits, and parallel_workers = 8 allows concurrent index scans during updates.

  VACUUM FULL ANALYZE
    users;

This reclaims space, reduces index bloat, and updates statistics for the query planner.

Indexing Strategies for Common Patterns

Primary Key

Use a B-tree on a unique, non-null column. The primary key is the clustering key for the table.

id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMPTZ DEFAULT NOW() );`

The id column is both the primary key and the clustering key. Rows are stored in id order.

Foreign Key

Use B-tree on the foreign key column to support JOIN operations.

CREATE INDEX idx_orders_user_id
  ON orders (user_id);

This supports JOIN orders ON users.id = orders.user_id.

Composite Keys

Use multiple columns in order of selectivity. The most selective column first.

CREATE INDEX idx_orders_user_status_date
  ON orders (user_id, status, created_at);

This supports:

The index is used in all three cases, and the order of columns matters.

Unique Constraints

Use B-tree on the constraint columns.

CREATE UNIQUE INDEX idx_users_email
  ON users (email);

This ensures no duplicate emails and supports SELECT * FROM users WHERE email = '[email protected]'.

Partial Indexes

Use WHERE to limit the index to a subset of rows.

CREATE INDEX idx_users_active_recent
  ON users (created_at DESC)
  WHERE status = 'active'
    AND created_at >= '2024-01-01';

This index is small and efficient for recent active users.

Indexing JSON Columns

For document-style data, use jsonb and index the fields.

CREATE INDEX idx_users_metadata_gin
  ON users USING gin (metadata);

This supports queries like:

SELECT *
FROM users
WHERE metadata @> '{"preferences": {"theme": "dark"}}';

Indexing Arrays

Use GIST or GIN for array columns.

CREATE INDEX idx_users_tags_gin
  ON users USING gin (tags);

This supports tags @> ARRAY['backend', 'python'].

Common Pitfalls

    CREATE INDEX idx_users_upper_name
      ON users (UPPER(name));

Summary

Indexing is not a one-time setup. It is a continuous process of monitoring, tuning, and maintaining. A good index is not just created—it is verified, tested, and watched. The database does not automatically choose the right index; it relies on accurate statistics, correct selectivity, and consistent data types. When a query is slow, the index is the first place to look. When an index fails, it is not because it was wrong—it was forgotten.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.