What Is Database Indexing?
A database index is a data structure (usually a B-tree) that speeds up data retrieval by creating a quick lookup path to rows. Without an index, the database scans every row (full table scan). With the right index, it jumps directly to matching rows — turning seconds into milliseconds.
How Database Indexing Works
Think of a database index like a book's index: instead of reading every page to find 'PostgreSQL,' you look it up in the index and jump to page 142. A database index works the same way for queries.
Creating an index: CREATE INDEX idx_users_email ON users(email). Now WHERE email = 'user@example.com' is instant instead of scanning millions of rows. But indexes have costs: they slow down writes and use storage.
Key Concepts
- B-Tree Index — The default index type — balanced tree structure for equality and range queries
- Composite Index — Index on multiple columns — CREATE INDEX idx ON orders(user_id, created_at) speeds up queries filtering on both
- Query Plan — Use EXPLAIN ANALYZE to see how the database executes your query and whether it uses indexes
Frequently Asked Questions
How many indexes should I create?
Index columns used in WHERE, JOIN, and ORDER BY clauses. Don't over-index — each index slows INSERT/UPDATE operations and uses disk space. Start with the queries that are actually slow.
Why is my query slow even with an index?
The index might not match your query pattern. Use EXPLAIN ANALYZE to check. Common issues: wrong column order in composite indexes, functions on indexed columns, or outdated statistics.