What is Database Indexing?

Database indexing uses additional data structures to avoid scanning every row in a table when a query searches for specific values. Like the index at the back of a book, a database index points the database engine toward the rows that matter.

How It Works

The most common index type is a B-tree. For equality and range queries such as customer_id = 42 or created_at > ..., the database reads the index first and then visits the matching rows. Systems such as PostgreSQL also provide GIN, GiST, BRIN, and hash indexes for different data types and query patterns.

When It Helps

Columns that are frequently filtered, sorted, or used in joins are index candidates. In an e-commerce table, fields such as order_id, customer_id, status, and created_at are common examples. A primary key usually creates an index automatically, but reporting and search queries may need additional indexes.

Tradeoffs

Every index has a write cost. INSERT, UPDATE, and DELETE operations must update the index as well, so unnecessary indexes consume storage and add maintenance overhead. Index decisions should be based on actual query plans, table size, and the balance between reads and writes.

In SQL systems, choosing the right index is one of the main parts of query optimization.