> Term

Database Index

A data structure that improves the speed of data retrieval operations on a database table.

Detailed Explanation

A database index functions like an index in a book, allowing the query engine to find data without scanning every single row (a full table scan). It uses structures like B-Trees or Hash tables to optimize lookups, sorting, and filtering.

While indexes massively speed up read operations, they introduce overhead for write operations (INSERT, UPDATE, DELETE), because the index itself must be updated every time the data changes.

Why It Matters

It is the difference between a query returning in 10 milliseconds versus taking down the entire database under load.

Common Failure Mode

A developer forgets to add an index to a frequently queried foreign key column, causing slow full table scans that eventually lock up production.

Practical Example

Creating an index on the user_id column to speed up user-specific queries.

Production Manifestation

B-Tree indexes for standard equality and range queries, GIN/GiST indexes for full-text search or JSONB in Postgres.

Frequently Asked Questions

What is Database Index in short?

A data structure that improves the speed of data retrieval operations on a database table.

What is the most common failure mode?

A developer forgets to add an index to a frequently queried foreign key column, causing slow full table scans that eventually lock up production.

AI Summary

A data structure that improves the speed of data retrieval operations on a database table. It is the difference between a query returning in 10 milliseconds versus taking down the entire database under load.