Jan Mikoláš
Glossary
Database index
An index is not a general-purpose performance button. It helps a specific filter, sort, or join while increasing the cost of every write.
Short definition
A shortcut to selected rows, not a copy of the entire table.
A database index stores the values of a selected column or combination of columns in an ordered or otherwise specialised structure, along with references to the corresponding rows. For a query such as “all new orders for this store, ordered by time,” the optimizer can scan only the relevant part of the index instead of the whole table.
An index is created for actual access patterns, not from a list of columns in the table. Every INSERT, UPDATE, or DELETE may also have to update the index. Unnecessary, poorly ordered, or unused indexes therefore do not make an application faster for free and can instead make writes and database maintenance worse.
What it is used for
For filters, relationships, and ordering the application actually uses
A well-chosen index corresponds to a specific WHERE, JOIN, ORDER BY, or database constraint.
- finding an order by a unique external identifier
- listing one store’s orders filtered by status and ordered by creation time
- joining order items to an order through a foreign key
- quickly finding unsent outbox events for a worker
- querying a flexible JSONB payload only once such queries are actually used
Practical example
A store’s new-order overview
An administration interface frequently displays the latest new orders for a particular store_id. The query filters by store and status, orders by created_at descending, and returns only the first page. A composite index in the same order supports this read pattern better than three unrelated individual indexes.
Before deployment, the plan is compared against a representative table size. If the order status changes very frequently, the write impact is checked as well. The index is then a separate migration change, not an invisible entity detail.
CREATE INDEX order_store_status_created_idx
ON customer_order (store_id, status, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, number, created_at
FROM customer_order
WHERE store_id = 42
AND status = 'new'
ORDER BY created_at DESC
LIMIT 50;How it works
From a query to an execution plan
The database does not have to use an index every time. It considers the conditions, statistics, table size, and cost of multiple possible plans.
- The query describes the need WHERE, JOIN, ORDER BY, and LIMIT determine which data is needed and in what order.
- The planner evaluates options It compares a sequential scan, index scan, bitmap scan, and other plans based on statistics and estimated cost.
- The index narrows the candidates A suitable index leads the database to fewer rows or the correct order; the remainder of the condition can be evaluated over those rows.
- The table provides the required data If the index does not contain everything needed and an index-only read is not possible, the database loads the corresponding rows from the table.
- Writes maintain the structure When indexed data changes, the table and every relevant index are updated.
Main components and concepts
The index type and column order have specific consequences.
Read the query and plan first; only then add an index.
B-tree
B-tree is a common default index for equality, ranges, and often ordering. It is the right starting point for many ordinary relational queries, but not the automatic answer for every data type.
Composite index
Column order matters. An index on (store_id, status, created_at) naturally supports filtering by store and status with time-based ordering; a reversed need may require a different design.
UNIQUE, primary keys, and foreign keys
Primary and unique constraints usually need an index as part of enforcing the rule. A foreign-key index can matter for joins and deleting the parent record, but the actual workload determines whether it is needed.
Selectivity and partial indexes
A column with two values may not narrow the rows enough on its own. A partial index can hold only a frequently searched subset, such as unprocessed events.
EXPLAIN and measurement
EXPLAIN (ANALYZE) shows the actual plan, row counts, and time. It is better evidence than guessing that “a column in WHERE deserves an index.”
Benefits and limitations
Faster reads, more expensive writes and maintenance.
Benefits
- finding a small subset of a large table more quickly
- returning data in the required order without a separate sort
- supporting unique rules and common joins
- a measurable way to address a specific slow query
Limitations and common mistakes
- indexing every column slows writes and increases storage requirements
- the wrong column order in a composite index does not help the intended query
- an index cannot be assessed without a real plan and data volume
- an index does not fix N+1 queries, a poor filter, or loading unnecessary data
- creating an index on a large production table can have operational impact
When it makes sense
Only once we know the question the database must answer.
An index makes sense for a frequent or sensitive query on a growing table, especially when a filter substantially narrows the data, a join follows a relationship, or the application needs stable ordering with a limit. On a small table, a sequential scan is often cheaper and more appropriate than going through an index.
The write ratio also matters. A table with intensive imports and only an occasional report should not have the same indexes as a catalogue frequently filtered by users. First remove N+1 behaviour, narrow the SELECT, and inspect EXPLAIN; then the index has a clear purpose.
What to keep in mind
An index is a design decision verified against data.
Its name and migration should explain which query the index exists for.
- measure specific SQL with EXPLAIN (ANALYZE) against representative data
- choose the composite-index order based on filters, ordering, and selectivity
- verify the index cost during inserts, updates, and batch imports
- plan index creation on large tables around concurrency and deployment
- regularly remove or reassess unused indexes based on operational data
Common questions
Indexes without misleading shortcuts
Does an index speed up every query?
No. The planner may determine that a sequential scan is cheaper for a small table or a large proportion of its rows. The benefit must be verified against the actual plan.
Should I index every foreign key?
It is often useful for joins and operations on the parent table, but it is not a universal rule. The way the data is read and deleted determines whether an index is needed.
Why don’t three separate indexes always work like one composite index?
A composite index has an order and can directly match a combination of filtering and sorting. The database can sometimes combine indexes, but the result is not equivalent for every query.
Does an index replace fixing an N+1 problem?
No. N+1 means there are too many queries. An index may speed up each one, but the unsuitable data-loading pattern remains.
How I work with databases in practice
I approach database performance through the actual query and its plan.
For e-commerce and integration applications, I design indexes, constraints, and queries around real data, workloads, and a safe schema-change process.