# Database Index

URL: https://softwaredictionary.org/terms/database-index
Category: Databases
Last updated: 2026-09-29
In Turkish: Veritabanı İndeksi

In short: A database index is a data structure that helps a database find rows quickly without scanning a whole table, much like the index at the back of a book.

## What is a database index?

A database index is an extra structure the database maintains alongside a table so it can quickly locate rows by the value of one or more columns. Without an index, a query like finding a user by email forces the database to check every row, which is called a full table scan and gets slower as the table grows.

Most relational databases store indexes as B-trees, sorted tree structures that let the database reach the right value in a few steps, even across millions of rows. Other types exist for special cases, such as hash indexes for exact matches, GIN indexes for full-text search and JSON data, and vector indexes for similarity search on embeddings.

The index at the back of a textbook is a good analogy: instead of reading every page to find 'recursion', you look it up alphabetically and jump to the right page. Databases automatically index primary keys and usually unique columns, and developers add other indexes on columns frequently used in `WHERE`, `JOIN`, and `ORDER BY` clauses.

Indexes are not free. Each one takes storage space, and every `INSERT`, `UPDATE`, or `DELETE` must also update the affected indexes, so too many indexes slow down writes. The goal is to index the columns your important queries actually filter or sort by, and commands like `EXPLAIN` show whether a query uses an index.

## Key takeaways

- Indexes speed up reads by avoiding full table scans.
- Most indexes are B-trees, which keep values sorted for fast lookups.
- Primary keys are indexed automatically.
- Every index uses extra storage and makes writes slightly slower.
- Use `EXPLAIN` to check whether a query uses an index.

## Example: Creating and checking an index

```sql
-- Without an index, this query scans every row in the table
SELECT * FROM users WHERE email = 'ada@example.com';

-- Create an index on the email column
CREATE INDEX idx_users_email ON users (email);

-- Ask the database how it runs the query (it should now use the index)
EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com';

-- A composite index helps queries that filter by both columns
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);
```

## Frequently asked questions

**Why not index every column?**

Each index uses disk space and must be updated on every insert, update, and delete, so too many indexes slow down writes. Index the columns that your frequent queries filter, join, or sort on.

**What is the difference between a clustered and a non-clustered index?**

A clustered index determines the order in which the table's rows are physically stored, so a table can have only one. A non-clustered index is a separate structure that points to the rows, and a table can have many.

**What is a composite index?**

A composite index covers more than one column, such as `(customer_id, created_at)`. It helps queries that filter on the first column, or on the first and second columns together, because the columns are sorted in that order.

## Sources

- [PostgreSQL documentation: Indexes](https://www.postgresql.org/docs/current/indexes.html)

---

Software Dictionary: https://softwaredictionary.org/ · https://softwaredictionary.org/llms.txt