How Database Indexes Actually Work

Introduction#

Imagine you have an orders table.

When your application is new, it contains only:

5,000 rows

You run this query:

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

The query feels instant.

A few years later, the same table contains:

80,000,000 rows

The SQL has not changed.

But now the query takes much longer.

Why?

Without a useful index, PostgreSQL may need to inspect a large number of rows to find the orders belonging to one customer.

Then it may need to sort those orders by:

created_at DESC

before returning only the newest 20.

This is where database indexes become important.

But an index is not simply:

Add index
   ↓
Query becomes fast

Indexes have their own structure, rules, and costs.

To use them properly, you need to understand what PostgreSQL is actually doing.

What Is a Database Index?#

Think about a large book.

Suppose the book contains 1,000 pages and you want to find information about:

PostgreSQL

Without an index, you might have to start from page 1 and keep reading until you find it.

Page 1
  ↓
Page 2
  ↓
Page 3
  ↓
...
  ↓
Page 850

That would be slow.

Instead, books usually contain an index.

PostgreSQL → Page 850
Redis      → Page 720
SQL        → Page 410

Now you can jump close to the information you need.

Database indexes work on a similar idea.

Instead of searching every table row, PostgreSQL can use a separate data structure to quickly locate relevant rows.

For example:

CREATE INDEX orders_customer_idx
ON orders (customer_id);

PostgreSQL now maintains an index based on:

customer_id

Conceptually:

customer-1  → matching rows
customer-2  → matching rows
customer-3  → matching rows
customer-4  → matching rows

So when you ask:

SELECT *
FROM orders
WHERE customer_id = 'customer-42';

PostgreSQL may use the index instead of scanning the entire table.

The Table Still Exists#

An important point is that an index does not replace your table.

You now have:

Orders Table
 
id
customer_id
total_amount
status
created_at

and separately:

Customer Index
 
customer_id
    ↓
references to table rows

Conceptually:

             Index
               |
               ↓
        Find customer-42
               |
               ↓
      References matching rows
               |
               ↓
          Orders Table

The index helps PostgreSQL find where relevant data is located.

That is why an index requires additional:

Storage
 
Memory/cache
 
Write work
 
Maintenance

You are trading additional storage and maintenance for faster reads.

Without an Index: Sequential Scan#

Suppose we have:

SELECT *
FROM orders
WHERE customer_id = 'customer-42';

If PostgreSQL does not have a useful index, it may perform a:

Sequential Scan

Conceptually:

Row 1    → customer-10   ✗
Row 2    → customer-91   ✗
Row 3    → customer-42   ✓
Row 4    → customer-15   ✗
Row 5    → customer-42   ✓
...
Row 80M

PostgreSQL reads through table pages and checks rows against the condition.

For a small table, this can be perfectly fine.

For example:

Table rows = 500

Scanning 500 rows may be cheaper than using an index.

But when the table becomes very large:

Table rows = 80,000,000

scanning a large portion of it for a small result can become expensive.

With an Index: Narrow the Search#

PostgreSQL supports several index types.

The most common is the:

B-tree

In fact, when you run:

CREATE INDEX orders_customer_idx
ON orders (customer_id);

PostgreSQL uses a B-tree by default.

You do not need to write:

USING BTREE

for normal B-tree indexes.

How a B-Tree Works#

A B-tree keeps keys in an ordered structure.

You do not need to understand every internal implementation detail to use it effectively.

The important idea is that PostgreSQL does not need to search every entry from beginning to end.

Imagine customer IDs were numeric.

A simplified structure might look like:

                    Root
                      |
        -----------------------------
        |             |             |
     1 - 499       500 - 999      1000+
        |             |             |
        ↓             ↓             ↓
      Pages         Pages         Pages

Suppose PostgreSQL needs:

customer_id = 742

It can navigate toward the section containing that value instead of scanning everything.

Conceptually:

Start
  ↓
Which range contains 742?
  ↓
500 - 999
  ↓
Find 742
  ↓
Locate matching rows

This is why indexes can dramatically reduce the amount of work needed for selective queries.

B-Tree Indexes Are Useful for More Than Equality#

B-tree indexes work well for queries involving:

=
<
<=
>
>=
BETWEEN
ORDER BY

For example:

SELECT *
FROM orders
WHERE customer_id = 'customer-42';

or:

SELECT *
FROM orders
WHERE created_at >= '2026-09-01';

or:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

Whether PostgreSQL actually chooses the index still depends on the query and the data.

Creating an index does not guarantee PostgreSQL will use it.

Composite Indexes#

Indexes can contain more than one column.

These are commonly called composite indexes.

Consider our original query:

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

We could create:

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

The index is organized first around:

customer_id

and then within each customer around:

created_at DESC

Conceptually:

customer-41
   ├── Sep 22
   ├── Sep 21
   └── Sep 20
 
customer-42
   ├── Sep 22
   ├── Sep 21
   ├── Sep 19
   └── Sep 15
 
customer-43
   ├── Sep 22
   └── Sep 18

Now PostgreSQL can locate:

customer-42

and then read that customer's newest orders directly from the relevant part of the index.

This matches our query very well:

WHERE customer_id = $1
        +
ORDER BY created_at DESC
        +
LIMIT 20

PostgreSQL may be able to avoid reading a large number of unrelated rows and may also avoid a separate sort.

Column Order Matters#

These two indexes are not equivalent:

CREATE INDEX idx_one
ON orders (customer_id, created_at);

and:

CREATE INDEX idx_two
ON orders (created_at, customer_id);

The order matters because the index is organized according to the indexed columns.

For:

(customer_id, created_at)

queries starting with customer_id fit naturally.

For example:

WHERE customer_id = $1

and:

WHERE customer_id = $1
AND created_at >= $2

Both can make good use of the index structure.

Think About the Left Side First#

A useful way to think about a composite index is:

(customer_id, created_at)

means:

First organize by customer_id
 
Then organize each customer's entries by created_at

Suppose you only ask:

WHERE created_at >= $1

Now PostgreSQL does not know which customer_id section to start with.

The index may therefore be much less useful for this query.

That is why:

(customer_id, created_at)

and:

(created_at, customer_id)

can support different query patterns.

Do not choose column order randomly.

Start from the queries your application actually runs.

Equality Before Range Is a Useful Starting Point#

Consider:

SELECT *
FROM orders
WHERE customer_id = $1
AND created_at >= $2;

We have:

customer_id = equality
 
created_at = range

A useful index could be:

CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at);

PostgreSQL can first narrow the search to one customer and then search that customer's date range.

A common starting rule is:

Equality columns
      ↓
Range / ordering columns

But treat this as a useful design principle, not a rule that replaces checking the actual execution plan.

Index the Query, Not Just the Column#

A common mistake is looking at your schema and saying:

customer_id looks important.
 
Let's index it.

Instead, start with the real query.

For example:

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

Now ask:

What does the query filter by?
 
customer_id
 
 
What does it order by?
 
created_at DESC
 
 
How many rows does it need?
 
20

That leads naturally toward:

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

The important mindset is:

Design indexes around important query patterns, not isolated columns.

What Is Selectivity?#

Not every column is a good index candidate.

Suppose your orders table contains:

80,000,000 rows

and you have:

is_paid = true
is_paid = false

Imagine the values are approximately:

true  = 40 million rows
false = 40 million rows

Now you create:

CREATE INDEX orders_paid_idx
ON orders (is_paid);

Then run:

SELECT *
FROM orders
WHERE is_paid = true;

The query asks for roughly half the table.

Using the index could mean PostgreSQL finds millions of matching entries and then accesses a huge number of table rows.

At that point, PostgreSQL may decide:

Scanning the table is cheaper.

This relates to selectivity.

A highly selective condition returns a relatively small part of the table.

For example:

customer_id = 'customer-42'
 
80 matching rows
out of
80,000,000 rows

That is very selective.

Compare it with:

is_paid = true
 
40,000,000 matching rows
out of
80,000,000 rows

That is not very selective.

Indexes are especially useful when they help PostgreSQL eliminate a large amount of unnecessary work.

A Sequential Scan Is Not Always Bad#

Developers sometimes see:

Seq Scan

inside EXPLAIN and immediately assume something is wrong.

That is not always true.

Suppose your table contains:

300 rows

Reading the whole table may be extremely cheap.

Or suppose your query needs:

70% of the table

Using an index to jump around between many table pages may cost more than simply reading the table sequentially.

PostgreSQL's planner tries to estimate which approach will cost less.

So the goal is not:

Never use sequential scans.

The goal is:

Avoid unnecessary work for important queries.

Partial Indexes#

Sometimes you do not need to index every row.

Imagine an order system where:

95% orders = completed
 
5% orders = pending

A worker frequently runs:

SELECT *
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

You could create a normal index:

CREATE INDEX orders_status_idx
ON orders (status);

But perhaps your important operational query only cares about pending orders.

You can create a partial index:

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

Now the index only contains rows matching:

status = 'pending'

Conceptually:

Orders Table
 
80,000,000 rows
      |
      ↓
Partial Index
      |
      ↓
Only pending orders

This can make the index:

Smaller
 
Cheaper to scan
 
More focused

Partial indexes are especially useful when your application frequently accesses a small, important subset of a much larger table.

Covering Indexes#

Consider this query:

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

We could create:

CREATE INDEX orders_customer_covering_idx
ON orders (customer_id, created_at DESC)
INCLUDE (total_amount, status);

The important search columns are:

customer_id
created_at

Additional values are stored using:

INCLUDE (total_amount, status)

These included columns do not define the search ordering of the index.

They are additional data PostgreSQL may be able to read directly from the index.

Conceptually:

Index Entry
 
customer_id
created_at
total_amount
status

In suitable conditions, PostgreSQL may perform an:

Index Only Scan

and avoid fetching some data from the main table.

This can reduce table-page reads.

However, it does not mean every query using an INCLUDE index will automatically become an index-only scan. PostgreSQL also considers visibility information and other execution details.

Bigger Indexes Are Not Free#

It might sound tempting to include every useful column:

CREATE INDEX huge_index
ON orders (
  customer_id,
  created_at,
  status,
  payment_method,
  country,
  currency
)
INCLUDE (
  total_amount,
  tax,
  shipping_cost
);

But large indexes have costs.

They require more:

Disk space
 
Memory/cache
 
Write work
 
Maintenance

So covering indexes should be used when the read benefit justifies the additional cost.

Every Index Makes Writes More Expensive#

This is one of the most important things to understand about indexes.

Suppose your table has:

Primary key index
 
Customer index
 
Status index
 
Created-at index

When you insert an order:

INSERT INTO orders (...)
VALUES (...);

PostgreSQL does not only write the table row.

Conceptually:

INSERT Order
     |
     ├── Write table row
     |
     ├── Update primary-key index
     |
     ├── Update customer index
     |
     ├── Update status index
     |
     └── Update created-at index

Every additional index creates additional work.

The same idea applies when indexed values are updated or rows are deleted.

So this strategy:

Create indexes on every column

is usually a bad idea.

Too Many Indexes Create Production Problems#

Imagine an orders table with:

25 indexes

Every write may need to maintain several index structures.

This can increase:

Write latency
 
Storage usage
 
WAL generation
 
Memory/cache pressure
 
Vacuum work
 
Migration time

You might improve one rarely used query while slowing down thousands of writes.

That is why an index should earn its place.

A useful index supports an important and frequent query pattern strongly enough to justify its maintenance cost.

Duplicate Indexes Are Also a Problem#

Over time, teams often create similar indexes.

For example:

CREATE INDEX idx_orders_customer
ON orders (customer_id);

Later:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

Depending on your query patterns and constraints, the second index may cover some of the same use cases as the first.

That does not automatically mean the first index should be deleted.

But it does mean you should investigate whether both are still necessary.

Production databases often collect indexes that were created years ago for queries that no longer exist.

Those indexes still consume resources on every write.

How Do You Know Whether PostgreSQL Uses Your Index?#

Do not guess.

Ask PostgreSQL.

Use:

EXPLAIN
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 'customer-42'
ORDER BY created_at DESC
LIMIT 20;

For real performance investigation, you can use:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 'customer-42'
ORDER BY created_at DESC
LIMIT 20;

You may see something such as:

Index Scan

or:

Index Only Scan

or:

Bitmap Index Scan

or:

Seq Scan

The important question is not simply:

Did PostgreSQL use my index?

The better question is:

Did this execution plan reduce the amount of work
for this query?

What Should You Look for in EXPLAIN ANALYZE?#

When investigating an index, look at things such as:

Scan type
 
Actual rows
 
Execution time
 
Loops
 
Buffer hits
 
Buffer reads
 
Sort operations

Suppose you add an index and the query changes from:

Sequential Scan
      ↓
Large number of rows inspected
      ↓
Sort
      ↓
Return 20 rows

to:

Index Scan
      ↓
Read relevant entries in order
      ↓
Return 20 rows

That is a strong sign that the index matches the query well.

The query execution guide goes deeper into reading PostgreSQL execution plans.

Production behavior can also differ from local testing because of data size, cache state, and concurrent traffic. The slow production queries guide covers those differences.

Test With Realistic Data#

Suppose your local development database contains:

2,000 orders

Almost every query is going to look fast.

Then production contains:

80,000,000 orders

Now index design matters much more.

When testing indexes, try to use data that resembles production in:

Row count
 
Value distribution
 
Query patterns
 
Data relationships

Value distribution is especially important.

For example:

status = completed → 95%
 
status = pending   → 4%
 
status = failed    → 1%

behaves differently from:

completed → 33%
 
pending   → 33%
 
failed    → 34%

even though both databases use the same columns.

The planner makes decisions based partly on statistics about your actual data.

Indexes Must Be Built Carefully in Production#

Creating an index on a small development table may take:

100 ms

Creating one on a production table containing hundreds of millions of rows is a different operation.

A normal command:

CREATE INDEX orders_customer_idx
ON orders (customer_id);

can interfere with production writes while the index is being created.

PostgreSQL provides:

CREATE INDEX CONCURRENTLY orders_customer_idx
ON orders (customer_id);

to reduce write blocking during index creation.

However, concurrent index creation has trade-offs.

It generally:

Takes longer
 
Performs more work
 
Has operational considerations

and it cannot run inside a transaction block.

A Practical Index Design Process#

Instead of randomly adding indexes, follow the query.

Start with:

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

Step 1: Understand the Query#

Ask:

Filter:
 
customer_id = $1
 
 
Ordering:
 
created_at DESC
 
 
Result:
 
20 rows

Step 2: Check the Existing Plan#

Run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 'customer-42'
ORDER BY created_at DESC
LIMIT 20;

Maybe PostgreSQL currently performs:

Sequential Scan
      ↓
Find customer rows
      ↓
Sort by created_at
      ↓
Return 20

Step 3: Design an Index for the Query#

A reasonable candidate is:

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

Why?

Because the query uses:

customer_id
      ↓
Equality filter
 
created_at
      ↓
Ordering

Step 4: Test Again#

Run the same:

EXPLAIN (ANALYZE, BUFFERS)

again.

Compare:

Execution time
 
Rows processed
 
Buffers
 
Sort operations
 
Scan type

Step 5: Measure the Write Cost#

Do not stop after confirming that the read became faster.

Also check:

INSERT latency
 
UPDATE latency
 
Index size
 
Storage usage

The index is valuable only when its overall benefit is worth its ongoing cost.

Common Indexing Mistakes#

Indexing Every Column#

This:

id
customer_id
status
country
currency
is_paid
payment_method
created_at
updated_at

does not mean you should automatically create nine separate indexes.

Start from important queries.

Ignoring Composite Column Order#

Do not assume:

(a, b)

is equivalent to:

(b, a)

The order changes how the index is organized and which query patterns it supports well.

Assuming an Index Must Be Used#

You create:

CREATE INDEX orders_status_idx
ON orders (status);

but PostgreSQL still chooses:

Seq Scan

That does not automatically mean PostgreSQL is wrong.

If the query returns a large percentage of the table, a sequential scan may be cheaper.

Testing Only With Small Data#

A query against:

1,000 rows

does not tell you much about its behavior against:

100,000,000 rows

Test important queries with realistic data.

Keeping Every Index Forever#

Applications change.

Queries change.

Old indexes can remain behind.

Monitor index usage and investigate indexes that are:

Unused
 
Redundant
 
Overlapping
 
Very large

before carefully deciding whether they can be removed.

Production Checklist#

Before adding an index, ask:

• Which real query am I trying to improve?
• Which columns are used for filtering?
• Which columns are used for ordering?
• Is the condition selective?
• Would a composite index match the query better?
• Does composite column order match the query pattern?
• Would a partial index target the important subset?
• Would a covering index meaningfully reduce table reads?
• What does EXPLAIN (ANALYZE, BUFFERS) show?
• Is the test dataset realistic?
• How large will the index become?
• What write overhead will the index introduce?
• Does a similar index already exist?
• How will the index be safely created in production?

Final Mental Model#

Think about a table without a useful index like this:

Query
  |
  ↓
Large Table
  |
  ↓
Inspect Many Rows
  |
  ↓
Find Matches
  |
  ↓
Possibly Sort
  |
  ↓
Return Result

A well-designed index can change the path:

Query
  |
  ↓
Index
  |
  ↓
Narrow Search
  |
  ↓
Locate Relevant Rows
  |
  ↓
Return Result

But that shortcut has a permanent cost:

Faster Reads
     |
     ├── Extra Storage
     ├── Extra Write Work
     └── Extra Maintenance

That is the trade-off.

Conclusion#

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

But an index is not magic.

It is a separate data structure that PostgreSQL maintains so it can find certain data more efficiently.

A good index starts with a real query:

Query
   ↓
Understand filters
   ↓
Understand ordering
   ↓
Understand selectivity
   ↓
Design index
   ↓
Check EXPLAIN ANALYZE
   ↓
Measure improvement
   ↓
Measure write cost

Do not create indexes simply because a column looks important.

Do not assume every sequential scan is bad.

Do not assume more indexes always mean better performance.

And do not forget that every index that speeds up reads also creates work when data changes.

The goal is not:

Maximum number of indexes

The goal is:

Minimum amount of database work
for the queries that matter.

Remember:

A good index is not just an index PostgreSQL can use. It is an index that removes enough work from an important query to justify its permanent write and storage cost.

References#

• Indexes — PostgreSQL
• Multicolumn indexes — PostgreSQL
• Partial indexes — PostgreSQL