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
How to Connect Python to a SQL Database
Learn how to connect Python to a SQL database, run queries safely, load results into pandas, and automate reports — a core skill for every data analyst.
Read More Data ScienceWhat Is a Database? A Plain-English Guide
A database is an organized collection of data stored so it can be easily accessed, managed, and updated by software. This guide explains the core types, how databases work, and why nearly every application depends on one.
Read More Data ScienceWhat Does a Database Analyst Do? Role, Skills, and Path
A database analyst designs, maintains, and optimizes the databases that store an organization's data, ensuring it stays accurate, secure, and fast to query. This guide covers the role's daily work, required skills, and how to break into it.
Read More AI & Technology7 Signs You Don't Need a Dedicated Vector Database
You probably do not need a dedicated vector database when your corpus fits comfortably in memory, query volume is low, filtering dominates ranking, or your existing database already offers vector search. This names seven concrete conditions, the failure modes of choosing wrongly, and the signals that should make you reconsider later.
Read More