What Is Database Indexing?

By Oleksandr Andrushchenko — Published on Sep 30

Tags: database-indexing database-performance sql

Category: Databases & Data

0 Likes

0 Dislikes

Database indexing is a technique that creates an additional data structure to help a database locate rows without scanning an entire table.

An index can make queries dramatically faster, especially on large tables, but it is not free. Every index consumes storage and adds work to inserts, updates, deletes, and maintenance operations. Good indexing is therefore about choosing the right indexes for real query patterns rather than indexing every column.

Why Database Indexes Exist
How a Database Index Works
B-Tree Indexes
Index Selectivity
Composite Indexes
- Column Order Matters
Indexes and Range Queries
Indexes for ORDER BY
Covering Indexes
Unique Indexes
Partial Indexes
The Write Cost of Indexes
Indexing and Partitioning
Reading Query Plans
Production Design Example
When an Index Does Not Help
Common Indexing Mistakes
Frequently Asked Questions
Conclusion
- More Articles to Read

Why Database Indexes Exist

Consider a table containing 500 million users:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    country TEXT,
    created_at TIMESTAMPTZ NOT NULL
);

The application frequently searches by email:

SELECT id, email, name
FROM users
WHERE email = 'alice@example.com';

Without a useful index, the database may need to examine a very large portion of the table:

Row 1
Row 2
Row 3
...
Row 499,999,999
Row 500,000,000

This is conceptually a sequential scan or full table scan.

An index creates a searchable structure associated with the indexed values:

alice@example.com
       ↓
Index
       ↓
Location of matching row
       ↓
Users table

Instead of searching hundreds of millions of rows one by one, the database can navigate the index and locate the relevant data much more efficiently.

How a Database Index Works

A simplified index can be imagined as an ordered mapping between indexed values and rows.

Index on email

alice@example.com   → Row 482
bob@example.com     → Row 8,241
john@example.com    → Row 91,452
sarah@example.com   → Row 182,932

The actual implementation depends on the database and index type, but the basic idea is the same: maintain an additional structure optimized for finding data according to particular access patterns.

An index can be created with:

CREATE INDEX idx_users_email
ON users (email);

The previous query can now use that structure:

WHERE email = 'alice@example.com'
             ↓
       Search index
             ↓
       Find row location
             ↓
       Fetch matching row

The performance difference becomes increasingly important as tables grow.

However, the index is a separate physical structure that must remain synchronized with the table. That creates the fundamental indexing trade-off:

Faster Reads
    ↕
More Storage + More Write Work

Database Index Internals - Key Data Structures

B-Tree Indexes

B-tree indexes are the default general-purpose index type in many relational databases.

A B-tree keeps keys ordered and organized into a balanced tree structure.

A simplified representation looks like:

                 [50]
               /      \
          [20, 35]    [70, 90]
          /   |  \     /   |  \
        ...  ... ...  ... ... ...

Searching does not require checking every value. Each comparison narrows the section of the tree that needs to be visited.

B-tree indexes work well for common predicates such as:

WHERE id = 123

WHERE price > 100

WHERE created_at BETWEEN $1 AND $2

They can also help with sorting because index entries are maintained in an ordered structure.

Databases may provide additional index types for specialized workloads, but B-tree indexes cover a large percentage of ordinary application queries.

Index Selectivity

An index is most useful when a predicate narrows the table to a relatively small number of rows.

Consider 100 million users with unique email addresses:

WHERE email = 'alice@example.com'

The predicate may match exactly one row.

An email index is highly selective.

Now consider:

WHERE active = true

If 95 million of the 100 million users are active, the predicate matches almost the entire table.

Using an index may require millions of index entries followed by millions of table lookups. A sequential scan can sometimes be cheaper.

Highly selective
100,000,000 rows → 1 row
                  → index often useful

Low selectivity
100,000,000 rows → 95,000,000 rows
                  → full scan may be cheaper

This is why creating an index does not guarantee that the query optimizer will use it.

Composite Indexes

A composite index contains multiple columns.

Consider an orders query:

SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = $1
  AND status = 'paid'
ORDER BY created_at DESC;

A composite index might be:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

Conceptually, the index is ordered by:

customer_id
    ↓
  status
    ↓
created_at

The database can narrow the index to one customer, then to paid orders for that customer, while already having the results ordered by creation time.

A good composite index often replaces several less useful single-column indexes when the application consistently filters by the same combination of columns.

Column Order Matters

The order of columns in a composite index is important.

Consider:

INDEX (customer_id, status, created_at)

This index naturally groups entries like:

customer_id
    └── status
          └── created_at

It is well suited to queries beginning with customer_id:

WHERE customer_id = $1

and:

WHERE customer_id = $1
  AND status = $2

But a query using only:

WHERE status = 'paid'

may not be able to use the index nearly as effectively because status is not the leading column.

Column order should follow real query predicates, selectivity, range conditions, and sorting requirements rather than a generic rule such as always putting the most selective column first.

Indexes and Range Queries

Ordered indexes are particularly useful for range queries.

Consider:

SELECT *
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-09-01'
  AND created_at < '2026-10-01';

An index such as:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

allows the database to find the section for customer 42 and then scan only the required date range.

customer_id = 42
      ↓
2026-08-30
2026-08-31
2026-09-01 ← start
2026-09-02
...
2026-09-30
2026-10-01 ← stop

Once a range condition is encountered inside a composite index, columns after that range often become less useful for narrowing the index scan. Exact behavior depends on the database and query.

This makes equality and range predicate placement important when designing multi-column indexes.

Indexes for ORDER BY

Indexes can sometimes eliminate an explicit sorting step.

Consider:

SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

Without an appropriate index, the database may need to:

Find customer rows
       ↓
Sort by created_at
       ↓
Take first 20

An index designed for the query:

CREATE INDEX idx_orders_customer_created_desc
ON orders (customer_id, created_at DESC);

can make the path closer to:

Find customer in index
       ↓
Rows already ordered newest first
       ↓
Read first 20
       ↓
Stop

This can be especially valuable for pagination and recent-item queries where the application needs only a small number of ordered rows.

Pagination, filtering, and sorting patterns are explored further in API Pagination, Filtering, and Sorting Explained.

Covering Indexes

Normally an index identifies matching rows and the database then accesses the table to retrieve additional columns.

Index
  ↓
Matching row locations
  ↓
Table
  ↓
Required columns

A covering index contains enough information to satisfy a query without fetching all required data from the underlying table pages.

Consider:

SELECT created_at, total_cents
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

Depending on the database, an index could include all the required columns:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC)
INCLUDE (total_cents);

The query may then be satisfied largely or entirely from the index.

Query
  ↓
Index
  ↓
customer_id
created_at
total_cents
  ↓
Result

Covering indexes can reduce table-page reads, but they make indexes larger and increase write costs.

Adding every selected column to an index simply to create an index-only path is usually a poor trade-off.

Unique Indexes

A unique index does more than improve query performance. It also enforces a data constraint.

For example:

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

The database prevents two rows from containing the same indexed value.

alice@example.com → allowed

second alice@example.com
        ↓
Unique constraint violation

This is stronger than checking uniqueness only in application code.

A pattern such as:

SELECT email
      ↓
No row found
      ↓
INSERT email

contains a race condition because two concurrent requests can both observe that the value does not yet exist.

A database uniqueness constraint provides the final concurrency-safe enforcement boundary.

Partial Indexes

Some databases support indexes containing only rows that satisfy a condition.

Suppose an orders table contains hundreds of millions of historical rows but the application frequently searches only pending orders:

SELECT *
FROM orders
WHERE status = 'pending'
  AND created_at < $1;

If only 1% of orders are pending, indexing every row may be unnecessary.

A PostgreSQL partial index could be:

CREATE INDEX idx_orders_pending_created
ON orders (created_at)
WHERE status = 'pending';

The index contains only the relevant subset:

All Orders
100,000,000 rows
      ↓
Pending only
1,000,000 rows
      ↓
Partial Index

This can reduce index size and write overhead while providing an efficient path for an important query.

Partial indexes are especially useful for states such as:

pending jobs;
unprocessed events;
active subscriptions;
unread notifications;
non-deleted records.

The Write Cost of Indexes

Every index must remain synchronized with table changes.

An insert into a table with no secondary indexes primarily writes the table data and required database metadata.

An insert into a table with eight indexes also needs to update those index structures.

INSERT row
   │
   ├──→ Table
   ├──→ Index 1
   ├──→ Index 2
   ├──→ Index 3
   ├──→ Index 4
   ├──→ Index 5
   ├──→ Index 6
   ├──→ Index 7
   └──→ Index 8

Indexes therefore affect:

insert latency;
update latency;
delete latency;
storage usage;
memory pressure;
transaction-log volume;
replication traffic;
maintenance operations.

Updates can be particularly expensive when indexed columns change because corresponding index entries must also change.

This leads to an important production rule: an index should justify its ongoing write and storage cost through an important access pattern or constraint.

Indexing and Partitioning

Indexing and partitioning solve different problems and are frequently used together.

Suppose a large events table is partitioned monthly by created_at.

events
 ├── events_2026_07
 ├── events_2026_08
 └── events_2026_09

A query requests one account's September events:

SELECT id, event_type, created_at
FROM events
WHERE account_id = $1
  AND created_at >= '2026-09-01'
  AND created_at < '2026-10-01'
ORDER BY created_at DESC;

Partition pruning can eliminate July and August:

Partition pruning
       ↓
events_2026_09 only

An index can then efficiently locate the account's rows inside that partition:

September partition
       ↓
(account_id, created_at) index
       ↓
Matching events

Partitioning answers which physical data segment should be searched. Indexing answers how matching rows should be found efficiently inside the relevant data.

The broader partitioning strategy is covered in Partitioning Large Tables for Production Systems.

Reading Query Plans

Indexes should be validated using the database query planner rather than assumed to work.

In PostgreSQL, EXPLAIN shows the selected execution plan:

EXPLAIN
SELECT id, email
FROM users
WHERE email = 'alice@example.com';

A plan may contain an index operation:

Index Scan using idx_users_email on users
  Index Cond: (email = 'alice@example.com')

Or the optimizer may choose a sequential scan:

Seq Scan on users
  Filter: (active = true)

EXPLAIN ANALYZE executes the query and provides actual runtime information:

EXPLAIN ANALYZE
SELECT id, email
FROM users
WHERE email = 'alice@example.com';

Useful information includes:

scan type;
estimated rows;
actual rows;
execution time;
loops;
sorting operations;
join strategies;
filtering behavior.

A large difference between estimated and actual row counts can indicate poor statistics or unusual data distribution, which may lead the optimizer toward inefficient plans.

Production indexing should therefore be driven by observed query plans and workload metrics rather than by the existence of columns that seem likely to need indexes.

Production Design Example

Consider an e-commerce platform with 800 million orders.

The table begins with:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status TEXT NOT NULL,
    total_cents BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

The most important customer query is:

SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

An index is created around that access pattern:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

The execution becomes conceptually:

customer_id
     ↓
Index lookup
     ↓
Newest rows already first
     ↓
Read 20 entries
     ↓
Stop

The operations team also runs a worker that finds old pending orders:

SELECT id
FROM orders
WHERE status = 'pending'
  AND created_at < $1
ORDER BY created_at
LIMIT 1000;

Only a small percentage of orders are pending, so a partial index is created:

CREATE INDEX idx_orders_pending_created
ON orders (created_at)
WHERE status = 'pending';

Another API frequently loads all orders for one customer with one specific status:

SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

Measurements show this endpoint is important enough to justify:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

At this point the team does not automatically create individual indexes on every remaining column.

Instead, slow-query data and execution plans are monitored for:

high-frequency sequential scans;
large numbers of rows scanned versus returned;
expensive sorting;
slow joins;
high database CPU;
index hit patterns;
unused indexes;
duplicate or overlapping indexes;
index size;
write latency.

Suppose an engineer proposes:

CREATE INDEX idx_orders_status
ON orders (status);

Before deploying it, the production data distribution is checked:

completed → 91%
pending   → 3%
failed    → 3%
cancelled → 3%

A query for all completed orders would match hundreds of millions of rows, so the standalone status index may provide little value for that workload.

The existing partial index already handles the operationally important pending case with a much smaller structure.

This illustrates the central production indexing workflow:

Observe slow query
       ↓
Understand predicates + ordering
       ↓
Inspect data distribution
       ↓
Inspect execution plan
       ↓
Design index
       ↓
Measure improvement
       ↓
Measure write/storage cost
       ↓
Keep or remove

Index design should remain connected to schema design rather than being treated as an afterthought. Designing High-Performance Database Schemas covers that broader relationship.

When an Index Does Not Help

An index is not automatically faster than scanning the table.

The optimizer may avoid an index when:

the query returns a large percentage of the table;
the indexed column has low selectivity;
the table is very small;
the query applies an incompatible function or expression;
the composite index begins with different columns;
statistics suggest a sequential scan is cheaper;
the query must access so many table pages that index lookups add overhead.

For example:

SELECT *
FROM users
WHERE country = 'US';

If most users are in the United States, an index on country may not provide much benefit.

Likewise, consider:

WHERE LOWER(email) = 'alice@example.com'

A normal index on email may not support the expression as expected. Depending on the database, an expression index may be required:

CREATE INDEX idx_users_lower_email
ON users (LOWER(email));

The correct conclusion when an index is ignored is not automatically that the optimizer is wrong. The execution plan and data distribution need to be inspected first.

Common Indexing Mistakes

Indexing every column. Storage and write costs grow while many indexes provide little value.
Creating indexes without inspecting queries. Indexes should support real predicates, joins, ordering, or constraints.
Ignoring composite column order. An index with the right columns in the wrong order may be ineffective.
Creating many overlapping indexes. Similar indexes increase write costs and complicate maintenance.
Assuming low-cardinality columns always need indexes. Queries matching most rows may prefer sequential scans.
Ignoring ORDER BY. A well-designed index can sometimes avoid expensive sorting.
Ignoring LIMIT. An ordered index can make top-N queries dramatically cheaper.
Using SELECT * unnecessarily. Fetching many columns can prevent efficient index-only access and increase I/O.
Ignoring write-heavy workloads. Every additional index makes modifications more expensive.
Never removing unused indexes. Old application behavior can leave expensive indexes with no meaningful reads.
Assuming an index guarantees usage. The query optimizer chooses the execution plan.
Optimizing development-sized data. Plans on thousands of rows may behave differently when production contains hundreds of millions.
Changing indexes without production-safe deployment planning. Building a large index can consume significant CPU, I/O, locks, or replication capacity.

Frequently Asked Questions

Indexes are one of the most important database performance tools, but their behavior depends on query structure, data distribution, and the database optimizer.

Does a Primary Key Create an Index?

In common relational databases, a primary key is backed by an index or equivalent indexed structure so the database can efficiently enforce uniqueness and locate rows.

The exact implementation depends on the database engine.

Should Every Column Be Indexed?

No. An index should normally support an important query pattern, join, sorting requirement, or data constraint.

Columns that are rarely searched or that have poor selectivity may not justify the storage and write overhead of an additional index.

Why Does the Database Ignore an Index?

The optimizer estimates the cost of different execution plans. If it expects a query to read a large percentage of the table, a sequential scan may be cheaper than traversing an index and repeatedly fetching table pages.

Other reasons include unsuitable composite-index order, stale statistics, expressions that do not match the index, data-type mismatches, or a query whose access pattern simply does not fit the index.

Can a Table Have Too Many Indexes?

Yes. Every additional index consumes storage and must be maintained as data changes.

A table with many unnecessary indexes can experience slower inserts, updates, deletes, migrations, replication, and maintenance. Production systems should periodically identify unused and redundant indexes.

Is an Index the Same as a Cache?

No. An index is a database data structure that provides a more efficient path to stored rows. A cache keeps reusable data closer to the caller so the database query may not need to execute at all.

Both can reduce latency, but they operate at different layers. The broader caching architecture is explained in Caching Explained: Improving Performance Without Overloading Databases.

Conclusion

Database indexes accelerate queries by maintaining additional structures that allow the database to locate, filter, and sometimes order data without scanning the entire table.

Effective indexing requires understanding actual query predicates, column order, selectivity, sorting, range conditions, data distribution, and execution plans. Every index also increases storage and write costs, so more indexes do not automatically mean better performance.

The core principle is: design indexes for measured access patterns, verify them with query plans, and keep only the indexes whose read or constraint benefits justify their ongoing cost.