Database Indexing Explained: How Databases Find Data Faster

A database can store millions of rows and still return a result quickly.

But how?

One of the most important reasons is indexing.

A database index helps the database locate relevant rows without scanning every row in a table.

If you understand indexes, you can write faster queries, diagnose slow endpoints, and make better database design decisions.

Quick idea: An index is an additional data structure that helps a database find rows more efficiently.

Quick Example

Imagine a table containing 10 million users:

users
--------------------------------
id
name
email
country
created_at

Suppose your application frequently runs:

SELECT *
FROM users
WHERE email = 'alex@example.com';

Without an appropriate index, the database may need to inspect a large number of rows.

With an index on email, the database can use that structure to locate the matching row much more efficiently.

CREATE INDEX idx_users_email
ON users(email);

The important point is:

Indexes improve data lookup, but they also have costs.

Database Indexing at a Glance

Why Databases Need Indexes

Consider a simple table:

users

1   Rahul
2   Priya
3   Arjun
4   Sneha
5   Karan
...
10,000,000   Alex

Now imagine searching for:

SELECT *
FROM users
WHERE name = 'Alex';

If there is no useful index, the database may scan rows looking for the matching value.

Conceptually:

Row 1 → Check
Row 2 → Check
Row 3 → Check
Row 4 → Check
...
Row 10,000,000 → Check

That can become expensive as the table grows.

An index provides another structure that can help narrow down the search.

The database can often avoid scanning the entire table.

A Simple Analogy

Think about a physical book.

Imagine a 1,000-page programming book.

You want to find the section about databases.

You could start at page 1 and read every page.

Or you could use the index at the back of the book.

Without Index
Page 1
  ↓
Page 2
  ↓
Page 3
  ↓
...
Page 1000


With Index
Topic
  ↓
Page Number
  ↓
Relevant Section

A database index serves a similar purpose.

It provides a structure that helps the database locate information more efficiently.

Creating Your First Index

Suppose we have:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    country TEXT
);

We frequently search users by email.

Create an index:

CREATE INDEX idx_users_email
ON users(email);

Now the database has an index associated with the email column.

You can verify indexes in PostgreSQL with:

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users';

Primary Keys and Indexes

Primary keys are commonly backed by an index.

For example:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT
);

The primary key provides:

• Uniqueness
• Identification of rows
• Efficient lookup support

So a query such as:

SELECT *
FROM users
WHERE id = 5000;

can efficiently use the primary-key index.

This is one reason primary keys are important for both data integrity and database access patterns.

What Happens Without an Index?

Consider:

SELECT *
FROM orders
WHERE customer_id = 42;

Suppose orders contains 20 million rows.

If customer_id is not indexed, the database may choose a sequential scan.

Conceptually:

20 million rows
       ↓
Check customer_id
       ↓
Keep matching rows

The exact execution strategy depends on the database engine, statistics, query, and data distribution.

What Happens With an Index?

Create:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Now the database has another structure it can consider.

Conceptually:

Query
  ↓
customer_id index
  ↓
Matching row locations
  ↓
Rows from table

This can dramatically reduce the amount of data that needs to be examined.

But an index is not automatically faster for every query.

The query planner decides whether using it is worthwhile.

The Query Planner

Modern relational databases have query planners.

The planner evaluates possible execution strategies and chooses one.

For example:

SQL Query
   ↓
Parse Query
   ↓
Analyze Available Paths
   ↓
Estimate Costs
   ↓
Choose Execution Plan
   ↓
Execute

A planner might decide between:

Sequential Scan

and:

Index Scan

The choice depends on factors such as:

• Table size
• Number of matching rows
• Available indexes
• Statistics
• Query conditions
• Estimated cost

Seeing a Query Plan

In PostgreSQL, you can use:

EXPLAIN
SELECT *
FROM users
WHERE email = 'alex@example.com';

For deeper analysis:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alex@example.com';

EXPLAIN shows the planned execution strategy.

EXPLAIN ANALYZE executes the query and provides actual execution information.

Be careful with EXPLAIN ANALYZE on queries that modify data.

Sequential Scan vs Index Scan

A simplified comparison:

An index is not magic.

If a query needs most of the table, scanning the table may actually be cheaper.

Selectivity Matters

Suppose you have 10 million users.

Column:

country

Possible values:

India
USA
UK
Canada
Germany
...

If 4 million users are from India, an index on country may not always provide a major benefit for:

SELECT *
FROM users
WHERE country = 'India';

Why?

Because a very large portion of the table matches.

Now consider:

SELECT *
FROM users
WHERE email = 'alex@example.com';

If email values are unique, the condition is highly selective.

That can make an index much more useful.

B-Tree Indexes

B-tree indexes are among the most common general-purpose index types in relational databases.

They work well for many operations such as:

=
<
>
<=
>=
ORDER BY

For example:

CREATE INDEX idx_users_created_at
ON users(created_at);

This can support queries involving date ranges and ordering.

Example:

SELECT *
FROM users
WHERE created_at >= '2026-01-01'
ORDER BY created_at;

The exact plan still depends on the database and query.

Composite Indexes

Sometimes a query filters using multiple columns.

Example:

SELECT *
FROM orders
WHERE customer_id = 42
AND status = 'completed';

You could create:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

This is called a composite index or multi-column index.

The order of columns matters.

(customer_id, status)

is not automatically equivalent to:

(status, customer_id)

Index design should reflect actual query patterns.

The Leftmost Column Principle

Consider:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

The first column is:

customer_id

The second is:

status

Queries beginning with the leading indexed column can often make better use of this index.

For example:

WHERE customer_id = 42

is aligned with the first column.

A query using only:

WHERE status = 'completed'

may not benefit in the same way.

The exact behavior depends on the database engine and query planner.

Indexes and ORDER BY

Indexes can also help with sorting in suitable situations.

Example:

SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 20;

An index such as:

CREATE INDEX idx_products_created_at
ON products(created_at DESC);

may help the database efficiently access rows in the required order.

This is particularly useful for common application patterns such as:

• Latest posts
• Recent orders
• New users
• Activity feeds
• Job listings

Indexes and Pagination

Consider a large job board.

A basic pagination query might use:

SELECT *
FROM jobs
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

Large offsets can become increasingly expensive.

A different approach is keyset pagination.

For example:

SELECT *
FROM jobs
WHERE created_at < '2026-09-12T10:00:00'
ORDER BY created_at DESC
LIMIT 20;

An appropriate index can make this pattern efficient.

This is especially useful for:

• News feeds
• Job boards
• Activity feeds
• Large content lists

Unique Indexes

A unique index prevents duplicate values.

For example:

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

Now the database can enforce that two users cannot have the same email value, subject to the database's handling of NULL.

This provides both:

• Fast lookup support
• Data integrity

In many applications, uniqueness constraints are preferable when the goal is data integrity because they express the rule directly in the database schema.

Partial Indexes

Some databases support indexes that cover only rows matching a condition.

For example, imagine a jobs table:

jobs
--------------------------------
id
title
status
expires_at

Your application frequently queries active jobs:

SELECT *
FROM jobs
WHERE status = 'published';

A partial index in PostgreSQL can be:

CREATE INDEX idx_active_jobs
ON jobs(created_at)
WHERE status = 'published';

This can reduce index size and focus the index on the rows that matter for that query pattern.

Indexes Are Not Free

It is easy to think:

More indexes = faster database.

That is incorrect.

Every index has costs.

Storage

Indexes consume disk space.

Write overhead

When rows are inserted, updated, or deleted, relevant indexes may also need to be maintained.

Maintenance

Indexes can require monitoring and maintenance depending on the database system and workload.

Query planning

A database with many indexes has more possible access paths to consider.

So indexes should be created based on actual workload rather than added randomly.

The Read vs Write Trade-Off

Think about a table that receives millions of writes.

Suppose you create ten indexes.

Every insert may need to update those indexes.

More indexes can improve read performance while increasing write work.

This creates an important engineering trade-off:

Optimize the database for the workload you actually have.

When Should You Add an Index?

Good candidates often include columns used frequently in:

• WHERE
• JOIN
• ORDER BY
• Range conditions
• Unique lookups

For example:

SELECT *
FROM applications
WHERE job_id = 100;

An index on:

job_id

may be useful if this query pattern is common.

Another example:

SELECT *
FROM posts
WHERE category_id = 5
ORDER BY created_at DESC;

A composite index may be considered:

CREATE INDEX idx_posts_category_created
ON posts(category_id, created_at DESC);

The right design should be validated using real query plans and workload data.

When Should You Avoid an Index?

Do not automatically index every column.

An index may be unnecessary when:

• The table is very small
• The column is rarely queried
• The query returns a large portion of the table
• The index duplicates another useful index
• Write performance is more important
• The workload does not justify the storage and maintenance cost

Database optimization should be evidence-driven.

Indexing a Real Web Application

Imagine Karyvio has a posts table:

posts
--------------------------------
id
title
slug
category_id
status
created_at
published_at

The website may frequently run queries like:

SELECT *
FROM posts
WHERE slug = 'database-indexing-explained';

An index on slug makes sense if slugs are used for page lookup.

CREATE UNIQUE INDEX idx_posts_slug
ON posts(slug);

Another common query could be:

SELECT *
FROM posts
WHERE category_id = 5
AND status = 'published'
ORDER BY published_at DESC;

A suitable composite index could potentially support this access pattern:

CREATE INDEX idx_posts_category_status_published
ON posts(category_id, status, published_at DESC);

The exact index should be validated against the application's real queries.

This is the important practical lesson:

Design indexes around how your application actually reads data.

How to Find Slow Queries

If your application is becoming slow, don't immediately create indexes.

First identify the problem.

Useful questions include:

1. Which query is slow?
2. How frequently does it run?
3. How many rows does it process?
4. What execution plan does it use?
5. Is the query returning too much data?
6. Is the database missing a useful index?
7. Is an existing index being ignored for a good reason?

A useful workflow is:

Optimization should be a cycle of measure → change → measure.

Common Indexing Mistakes

1. Indexing Everything

Adding an index to every column creates unnecessary storage and write overhead.

2. Ignoring Query Patterns

An index should exist for a reason.

Look at the queries your application actually runs.

3. Using the Wrong Composite Index Order

For:

CREATE INDEX idx_example
ON orders(customer_id, status);

the order is meaningful.

Do not assume the database can treat every column order identically.

4. Forgetting Write Performance

A read-heavy system and a write-heavy system may need very different indexing strategies.

5. Never Checking Execution Plans

You should not assume an index is being used simply because it exists.

Use tools such as:

EXPLAIN

and:

EXPLAIN ANALYZE

where appropriate.

6. Creating Duplicate Indexes

You may accidentally create indexes that overlap heavily.

For example:

idx_customer
idx_customer_status

might both be useful, but the decision should be based on actual queries and database behavior.

A Practical Indexing Checklist

Before creating an index, ask:

• Is this column used frequently in queries?
• Is the query slow enough to justify optimization?
• How selective is the column?
• Does another index already cover the query?
• Is the table large?
• Is the application read-heavy or write-heavy?
• Have I checked the execution plan?
• Will the additional storage be acceptable?
• Did performance actually improve after the change?

Beginner Database Optimization Project

A useful project is to build a small application with:

Users
Orders
Products
Applications

Start with several thousand or more generated rows.

Then create queries such as:

SELECT *
FROM orders
WHERE customer_id = 500;

Measure the query.

Then create:

CREATE INDEX idx_orders_customer
ON orders(customer_id);

Measure again.

Next, test:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 500;

Compare the execution plans.

This teaches something much more useful than memorizing definitions:

how database design affects real application performance.

Database Indexing Interview Questions

What is a database index?

An index is a data structure that helps a database locate rows more efficiently.

Does an index always make a query faster?

No. For some queries, especially those returning a large percentage of a table, a sequential scan may be cheaper.

Why can too many indexes be bad?

They consume storage and add work to inserts, updates, and deletes.

What is a composite index?

An index built from multiple columns.

Example:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

What is a query execution plan?

It describes how the database intends to execute a query, including operations such as scans, joins, sorting, and index access.

How do you investigate a slow query?

Start with the actual query, inspect its execution plan, identify the bottleneck, make a targeted change, and measure the result again.

Final Takeaway

Database indexes are one of the most important tools for improving application performance.

But the goal is not to create as many indexes as possible.

The goal is to create the right indexes for the queries your application actually runs.

A strong developer should understand:

Query
  ↓
Execution Plan
  ↓
Index or Scan
  ↓
Rows
  ↓
Application Response

When a database becomes slow, don't guess.

Measure the query, inspect the plan, understand the workload, make one targeted change, and measure again.

That mindset is more valuable than memorizing a list of indexing rules.