When a database query is slow, one of the first solutions that often comes to mind is adding an index.
Indexes are extremely useful. They can dramatically improve query performance by allowing the database to find rows without scanning an entire table. But an index is not a universal solution to every performance problem.
In some situations, adding an index can introduce more costs than benefits. The right optimization depends on the workload, query frequency, data characteristics, and role of the database.
Let's look at some alternatives.
Indexes Have a Cost
Indexes are not free.
Every additional index requires storage, and the database must keep that index up to date whenever the underlying data changes. This means that INSERT, UPDATE, and DELETE operations can become more expensive as the number of indexes increases.
For example, imagine a table that receives thousands of writes every minute, but a particular query runs only once a week as part of an analytical report.
Adding an index specifically to optimize that weekly query might not be worthwhile. The index would be maintained continuously, even though the query that benefits from it is executed only occasionally.
This is an important principle:
The benefit of an index should be evaluated against its maintenance cost and the frequency of the workload that uses it.
An index can be an excellent solution for a frequently executed application query while being a poor choice for an occasional analytical query.
Separate Analytical Workloads from Application Workloads
Another option is to separate analytical workloads from the database that serves the application.
Application databases are usually optimized for transactional workloads: frequent reads and writes, short queries, and predictable response times.
Analytical queries can be very different. They may scan millions of rows, perform aggregations, join large tables, or process historical data.
Running these queries directly against the production database can consume CPU, memory, disk I/O, and other resources that the application needs.
One possible architecture is to provide analysts with a separate copy or replica of the data.
For example:
+------------------+
| Production DB |
| |
Application ----> | Transactions |
+--------+---------+
|
| Replication
v
+------------------+
| Analytics DB |
| |
| Reports / BI |
+------------------+
In this model, the production database remains focused on application traffic, while analytical queries can run against a separate system.
Depending on the requirements, this could involve a read replica, a reporting database, a data warehouse, or another dedicated analytical system.
The important idea is not necessarily the specific technology. It is workload isolation.
Background Processing Can Be a Better Approach
Sometimes the best solution is not to make the query faster at all.
Instead, ask whether the calculation really needs to happen when the user requests it.
Suppose an analytical report requires several expensive queries and calculations, but the underlying data changes relatively slowly.
Rather than calculating everything every time an analyst opens the report, the system could perform the calculations periodically in the background.
For example:
Production Data
|
v
Background Job
|
v
Precomputed Results
|
+----> Reporting Table
|
+----> CSV / Export
|
+----> Dashboard
A scheduled job could run every night, every hour, or according to whatever frequency makes sense for the business.
The results could then be stored in a reporting table or exported to a format such as CSV.
When an analyst needs the information, they are reading already-computed results instead of triggering an expensive calculation against the production data.
This approach is particularly useful when freshness requirements are limited.
If a report only needs to be updated once per day, there may be little value in spending significant resources making a real-time query extremely fast.
Large Text Fields Are a Different Problem
Indexes are also highly dependent on the type of data being searched.
Consider a table containing a large text column:
CREATE TABLE articles (
id BIGINT PRIMARY KEY,
title TEXT,
content TEXT
);
A common requirement might be to search for articles containing particular words or phrases.
A traditional B-tree index is generally not the right tool for arbitrary searches inside large blocks of text.
For example:
SELECT *
FROM articles
WHERE content LIKE '%database performance%';
The problem here is that the database is being asked to find text occurring anywhere inside the value.
This is fundamentally different from looking up an exact value or performing a range comparison.
For this type of workload, full-text search is usually a more appropriate solution.
Full-Text Search
A full-text search system processes text differently from a traditional index.
Instead of treating the entire text value as one large string, the system analyzes the content and builds a searchable representation based on words or other linguistic units.
PostgreSQL, for example, provides built-in full-text search capabilities.
A simplified example might look like:
SELECT *
FROM articles
WHERE to_tsvector('english', content)
@@ plainto_tsquery('english', 'database performance');
This allows PostgreSQL to use mechanisms designed specifically for searching text rather than relying on a conventional B-tree index.
The general lesson is important:
The type of search you need should influence the type of index or search technology you choose.
When a Specialized Search Engine Makes Sense
For some applications, text search becomes important enough that a dedicated search system is worth considering.
Technologies such as Elasticsearch are designed specifically for search workloads and provide capabilities such as full-text search, relevance scoring, filtering, aggregations, and distributed indexing.
A common architecture might look like this:
+------------------+
| Application |
+--------+---------+
|
+---------+---------+
| |
v v
+-----------+ +-------------+
| Database | | Elasticsearch|
| | | |
| Source of | | Search |
| Truth | | Index |
+-----------+ +-------------+
The database remains the authoritative source of the application's data, while the search engine maintains a representation optimized for search.
Of course, introducing another system also introduces additional operational complexity. Data synchronization, indexing pipelines, monitoring, backups, and failure handling all need to be considered.
So a specialized search engine should be introduced because the workload requires it—not simply because a query happens to be slow.
Start With the Workload, Not the Index
When a query is slow, the first question should not always be:
"Which index should I add?"
A better set of questions is:
How often does this query run?
Is it part of the application's critical path?
How much data does it process?
Is the workload transactional or analytical?
How expensive will an additional index be to maintain?
Does the query need real-time results?
Would precomputing the result be sufficient?
Is the data being searched structured data or large amounts of text?
Would a separate reporting or search system be more appropriate?
These questions help identify the actual nature of the problem.
The Broader Lesson
Indexes are one of the most important tools available for database optimization, but they are only one tool among many.
A slow query might be best addressed with an index. In another situation, the right solution could be a read replica, a reporting database, background processing, precomputed results, full-text search, or a dedicated search engine.
The key factors include:
Query frequency — A query executed thousands of times per hour has different optimization requirements from a weekly report.
Workload criticality — Queries on the application's critical path may require very different optimization strategies from occasional analytical queries.
Index maintenance costs — Additional indexes consume storage and can increase the cost of writes.
Workload isolation — Analytical queries may be better executed outside the production database.
Data characteristics — Searching structured values is different from searching large amounts of natural language text.
Freshness requirements — If results do not need to be real-time, background processing and precomputation may be more efficient.
Database performance optimization is rarely about finding a single universal trick.
Adding an index can be the right solution—but only when the workload justifies it. For occasional analytical queries, an index may provide little benefit compared with its ongoing maintenance cost. For heavy reporting workloads, separating analytics from production may be more appropriate. For expensive calculations, background processing and precomputed results can eliminate unnecessary work. And for large-scale text search, full-text indexing or a specialized search engine may be a better fit.
The broader principle is simple:
Optimize for the workload, not just for the query.
Understanding how the database is used, how frequently operations run, how fresh the results need to be, and what kind of data is being searched will usually lead to a better solution than automatically adding another index.
