What is Database Index?

What Is a Database Index?

A database index keeps selected column values from a table in a separate data structure for faster lookup. It is similar to an index at the back of a book: instead of reading the whole book, you use the index to jump to the relevant page.

Indexes are commonly implemented with B-tree structures, while some databases also support hash, GiST, GIN, or full-text index types. They can make a major difference for WHERE, JOIN, ORDER BY, and uniqueness checks. For example, a unique index on users.email both speeds up lookup and prevents creating a second user with the same email.

What It Costs

Indexes improve read performance but add write overhead. INSERT, UPDATE, and DELETE operations must update indexes as well as table data. Indexes also consume disk space, and a poorly chosen index may not be used by the query planner.

Practical Use

When analyzing a slow query, start with the columns used for filtering, sorting, and joins. Column order matters in composite indexes, and adding a separate index to every column is not always useful. SQL queries and PostgreSQL EXPLAIN output should be reviewed together to confirm which index is actually used.