Database Indexing
Database indexing is the technique of creating auxiliary data structures — most commonly B-trees or hash tables — that let a database engine locate rows matching a query condition without scanning every row in a table.
Definition
Database indexing is the technique of creating auxiliary data structures — most commonly B-trees or hash tables — that let a database engine locate rows matching a query condition without scanning every row in a table.
Overview
Without an index, a database must perform a full table scan to answer a query, reading every row to check whether it matches the requested condition — acceptable for small tables, but prohibitively slow as tables grow into millions of rows. An index on a column (or set of columns) maintains a sorted or hashed lookup structure pointing back to the corresponding rows, letting the engine jump directly to matching data, similar in spirit to how a book's index lets a reader skip straight to a page rather than reading cover to cover. The most common index type is the B-tree (or B+ tree), a balanced tree structure well suited to range queries (`WHERE age BETWEEN 18 AND 65`) and equality lookups alike, used by default in PostgreSQL, MySQL, and most relational databases. Hash indexes offer faster exact-match lookups but can't support range queries; specialized index types like GIN and GiST (in PostgreSQL) support full-text search and geospatial queries, while vector databases use approximate nearest-neighbor indexes for similarity search. Indexes are not free: every index a table has must itself be updated on every INSERT, UPDATE, and DELETE, so over-indexing can slow down write-heavy workloads even as it speeds up reads. Composite indexes (spanning multiple columns) are order-sensitive — an index on `(last_name, first_name)` speeds up queries filtering by `last_name` alone or by both columns together, but not queries filtering by `first_name` alone — which makes index design an important part of broader query optimization work. Choosing which columns to index typically starts with examining a query's `WHERE`, `JOIN`, and `ORDER BY` clauses and consulting the database's query planner (via `EXPLAIN` in PostgreSQL and MySQL) to confirm an index is actually being used, a core practical skill taught in PostgreSQL Mastery.
Key Concepts
- Avoids full table scans by maintaining a searchable lookup structure
- B-tree indexes support both equality and range queries efficiently
- Hash indexes optimize exact-match lookups but not range queries
- Specialized indexes (GIN, GiST, full-text, vector) support non-standard query types
- Composite (multi-column) indexes are order-sensitive
- Every index adds write overhead on INSERT, UPDATE, and DELETE
- Query planners (EXPLAIN) reveal whether an index is actually being used
Use Cases
Frequently Asked Questions
From the Blog
Project: Build a Full-Stack To-Do App with React, Node.js and MongoDB
A full-stack to-do app is the perfect first MERN project — it covers every concept you'll use in production: REST APIs, database CRUD operations, JWT authentication, and deploying a frontend and backend separately. Build it once, understand the full stack.
Read More Projects & Case StudiesProject: Build a REST API with Python and FastAPI
FastAPI is the fastest-growing Python web framework — and for good reason. In this hands-on project you'll build a fully functional REST API with auto-generated documentation, database persistence, and deployment on Render, all in a single afternoon.
Read More ProgrammingAsync Python: asyncio Explained for Beginners
Async Python lets a single thread handle hundreds of concurrent I/O operations — making it essential for web APIs, database calls, and AI integrations. This guide explains coroutines, the event loop, await, gather, and real patterns you'll use in FastAPI, httpx, and LLM streaming.
Read More