Database Index

A database index is a lookup structure that speeds up queries on specific columns at the cost of extra write overhead.

Diagram

  Without index:  Scan ALL 10M rows ──▶ ~5000ms
  
  With index on email:  B-tree lookup ──▶ ~5ms

In Depth

A database index is an auxiliary data structure (usually a B-tree) that maps column values to row locations, allowing the database to find matching rows without scanning the entire table.

Code Example

Creating and using an index

-- Slow: full table scan on 10M rows
SELECT * FROM users WHERE email = 'rohan@test.com';

-- Fast: add index first
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'rohan@test.com'; -- uses index

Related Terms

sql postgres latency

← All Terms Next: TTL →