Database indexing problems rarely begin with an obvious outage.
They usually appear as a checkout API that moves from 150 milliseconds to 700 milliseconds during peak traffic, a customer dashboard that becomes progressively slower as transaction history grows, or a database cluster that needs more compute every quarter even though application traffic has not increased proportionally.
For engineering leaders, the difficult question is no longer whether a database needs indexes. Almost every production system already has them. The harder question is whether the indexes still match the way the application queries data.
At enterprise scale, adding another index can solve one problem while creating another. An index may reduce read latency but increase write amplification, storage consumption, cache pressure, replication overhead, and maintenance work.
Modern PostgreSQL and SQL Server documentation reflects this trade-off. PostgreSQL 18 explicitly advises using multicolumn indexes sparingly, while Microsoft recommends evaluating the query benefit of included columns against increased storage and data-modification cost.
The practical decision therefore comes down to query shape.
When should a composite index replace multiple single-column indexes?
A composite index becomes useful when production queries repeatedly filter, join, or sort using the same combination of columns.
Consider an enterprise order platform running a query such as:
SELECT id, total_amount, created_at
FROM orders
WHERE tenant_id = 841
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;Creating separate indexes on tenant_id, status, and created_at may appear flexible. The database can sometimes combine them, but combining indexes introduces additional work and may lose useful ordering.
An index designed around the actual access pattern can be more effective:
CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);Column order matters.
For a B-tree index, the leading columns largely determine how efficiently the engine can narrow the search. PostgreSQL 18 documentation states that multicolumn B-tree indexes are most efficient when queries constrain the leading columns. PostgreSQL can now use skip-scan optimization in some cases where a leading column lacks a constraint, but that does not eliminate the need for deliberate column ordering.
Engineering teams should therefore design composite indexes from real query patterns rather than applying simplistic rules such as “put the most selective column first.”
Equality predicates, range predicates, sorting requirements, cardinality, and query frequency all matter.
A composite index also should not become a catch-all index containing every column frequently seen in a query. PostgreSQL’s documentation notes that indexes containing more than three key columns are unlikely to help unless the table has a highly specific access pattern.
When workload patterns vary significantly, separate indexes can still be preferable. PostgreSQL, for example, can combine indexes through bitmap operations for some AND and OR conditions.
When does a partial index make more sense than indexing the entire table?
Many enterprise tables contain millions or billions of rows while applications repeatedly query only a small active subset.
A payments system might retain years of completed transactions but frequently search only unresolved payments.
Instead of:
CREATE INDEX idx_payments_status
ON payments (status);PostgreSQL can use:
CREATE INDEX idx_payments_pending
ON payments (merchant_id, created_at)
WHERE status = 'pending';The index stores entries only for rows matching the predicate.
That distinction becomes important when historical rows dominate a table while operational queries concentrate on a much smaller working set.
Partial indexes can reduce index size and avoid maintaining unnecessary entries. SQL Server provides the equivalent concept through filtered indexes. Microsoft notes that a well-designed filtered index can reduce storage and update cost while providing more targeted statistics for the relevant subset of rows.
Partial indexes fit patterns such as active subscriptions, failed jobs, unprocessed events, non-null values, open tickets, or records belonging to a specific operational state.
They become less attractive when the supposedly selective condition eventually matches most of the table.
A common failure occurs when an engineering team creates an index for WHERE status = 'active', only for the business model to evolve until 90 percent of rows become active. The small targeted index has effectively turned into another large index that the database must maintain.
Teams also need to verify that application predicates actually match the partial index condition. Small differences introduced by ORM-generated SQL, implicit type conversions, parameterization, or changing business logic can prevent the optimizer from choosing the index.
Partial indexes should therefore represent stable access patterns, not temporary assumptions.
When should a covering index be used to avoid table lookups?
Composite indexes optimize how the database finds rows. Covering indexes address another problem: what happens after the rows are found.
Suppose an API frequently executes:
SELECT customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = 841
AND status = 'pending';If the index contains only tenant_id and status, the engine may locate matching entries quickly but still visit the underlying table to retrieve the remaining columns.
PostgreSQL allows additional payload columns through INCLUDE:
CREATE INDEX idx_orders_lookup
ON orders (tenant_id, status)
INCLUDE (customer_id, total_amount, created_at);When conditions allow an index-only scan, PostgreSQL can answer the query using information stored in the index instead of repeatedly accessing heap pages.
SQL Server follows the same broad principle with included columns in nonclustered indexes. Microsoft recommends putting columns used for filtering, aggregation, and sorting in the key while considering output-only fields as included columns.
This can be valuable for high-volume read APIs, dashboards, reporting endpoints, and other latency-sensitive paths.
But covering every query is not a sensible objective.
Wider indexes consume more storage, fit fewer entries into each page, increase cache pressure, and require additional work when included values change. Microsoft specifically warns that large numbers of included columns can reduce cache efficiency and increase data-modification cost.
A covering index should therefore serve an important and repeatable access pattern rather than compensate for poorly designed queries.
When does adding an index actually make database performance worse?
An index should solve a measured workload problem.
Teams should be cautious when indexes are added because a monitoring dashboard reports one slow query or because an ORM recommends them automatically.
Before creating another production index, engineers should evaluate:
- Read frequency and business impact. A query running thousands of times per minute deserves different optimization attention from an internal report executed twice per day. The team should establish whether latency affects customer experience, transaction throughput, batch completion windows, or infrastructure cost before expanding the index footprint.
- Write amplification. Every relevant INSERT, UPDATE, and DELETE may require index maintenance. A read optimization can therefore reduce throughput on write-heavy systems such as event stores, payment ledgers, telemetry platforms, and high-volume transactional applications.
- Selectivity and data distribution. Indexing a low-cardinality value such as a boolean flag may provide little benefit when the query still returns a large percentage of the table. A partial index, different column order, partitioning strategy, or even a sequential scan may perform better.
- Production query plans. Teams should compare execution plans before and after the change using representative data volumes. Estimated improvements alone are insufficient when statistics, cache state, parameter values, and data skew influence optimizer decisions.
The goal is not the largest collection of indexes. The goal is the smallest useful index set that supports the workload.
When should database indexing become an architecture-level discussion?
Indexing becomes an architectural concern when teams repeatedly tune individual queries but the underlying performance problem keeps returning.
At that point, engineering leaders may need to examine schema design, partitioning, data lifecycle policies, caching, connection behavior, ORM patterns, workload isolation, replicas, or broader platform modernization.
External engineering partners can sometimes help provide that wider view. Firms such as GeekyAnts, Thoughtworks, and Accenture work across areas including product engineering, application modernization, data platforms, cloud infrastructure, and performance engineering rather than treating database tuning as an isolated DBA task. GeekyAnts, for example, positions performance and data integration within its enterprise modernization practice, while Thoughtworks focuses on data platform modernization and Accenture supports database architecture and large-scale modernization programs.
The useful engagement in this situation is not simply asking a consultant to create more indexes. It is reviewing workload characteristics, query plans, growth projections, schema decisions, application access patterns, and infrastructure together.
For a VP of Engineering or Head of Platform, that distinction matters.
A database can look healthy at today’s traffic while containing indexing decisions that become expensive at the next scale threshold. Composite, partial, and covering indexes provide powerful tools, but their value depends on where the application reads, writes, sorts, and filters data.
The next useful step is usually a focused performance review of the highest-cost production queries and the indexes supporting them. That conversation can reveal whether the organization needs another index, a different one, or a broader change in how the platform handles data.
