How Database Indexes Actually Work Under the Hood

Database indexing is one of the most fundamental concepts to master when building high-performance systems. But what exactly happens when you write CREATE INDEX?

1. The Analogy of a Book Index

Imagine trying to find the definition of "B-Tree" in a 1,000-page book. If you start from page 1 and read every page, you're performing what database engineers call a Full Table Scan. If the book has an alphabetical index at the back, you look up "B-Tree", find page 742, and jump straight there. That is exactly what a database index does.

-- Creating an index on the email column for fast lookups
CREATE INDEX idx_users_email ON users(email);

-- Query using the index
SELECT * FROM users WHERE email = 'alex@company.com';

2. B-Tree Indexes under the hood

Most modern relational databases like PostgreSQL, MySQL, and SQLite use a B-Tree (Balanced Tree) data structure for indexing by default. The key benefits of B-Trees are:

• Balanced Height: All leaf nodes are at the same depth, guaranteeing logarithmic search time O(log N).
• Range Queries: Leaf nodes are linked sequentially, allowing fast range scans (e.g. WHERE age BETWEEN 20 AND 30).

3. The Cost of Indexing

Indexes are not free. While they make SELECT queries run in milliseconds, they slow down INSERT, UPDATE, and DELETE operations because the database must keep the B-Tree updated alongside the raw data. Choose your indexed columns wisely!