Skip to main content

Missing Database Indexing

Missing Database Indexing​

Context: One line of missing SQL crashes your production environment every day when usage increases. This is the easiest performance issue to solve, and unfortunately one of the most expensive to ignore.

The $15,000/month Time Bomb

In one engagement, the company was running the largest AWS PostgreSQL instance (costing ~$15,000/month) because of a single LIKE %foo% query with no index. CPU usage was under 10%, but the query spiked CPU and regularly crashed the site more frequently as business grew. Worse, there was no bigger server to upgrade to, they were at the end of the line of upgrades with an increasing crash frequency.

A CREATE INDEX ... USING GIN statement removed the outages. They dropped down to a $1,000/month instance.

A database index is a data structure that can quickly point to the rows a query needs to access. It does not manipulate data, it is simply a way to point to the data which is faster than looking through every item one-by-one. Yes, it marginally increases time to insert, but for most database cases, data is read many times more than it is written, and the tradeoff is milliseconds of writing time to save seconds of lookup time.

How the project misbehaves​

The pattern: Performance degrades as data grows, crashes become frequent, but the root cause stays hidden.

Any of these are common signs you have DB index challenges:

  1. When loading a page with many entities of the same type it is slow to respond.
  2. A custom search feature, or a complex WHERE clause that involves at-least one large table.
  3. A sudden and mysteriously high CPU load on the database.
  4. It is slow, but if you reload the page a few times it gets faster and seems to go away.
  5. It is slow for some customers but fast for others, assuming customers are not more than 10x different in data size.
  6. Random crashes you can't really explain seem to be on the same day of the week or around the same time of day but never exact, and not always.
  7. Some customers have significantly more data than most.

All of these problems are allowed to deepen as the team shrugs off those little performance issues as quirks when they are sporadic with thought-terminating guesses like "must be system load," or similar hand-waving.

These signals strongly suggest an indexing issue, which can help to target where to start looking to confirm the issue.

Patterns that can easily create this problem:​

The trap: This issue hides in plain sight during development and only reveals itself in production and after data growth.

  • Generate a query by using an ORM and forget to add the index, or it is not obvious there is a need to add an index.
  • Manually write the SQL but not think about creating an index due to lack of awareness about indexing or simply forgetting
  • Local or test environments only query a small data set, so the problem is never raised
  • Local and test have a large dataset, but the query uses the same parameters frequently enough that results seem fast due to the DB storing recent results in memory.
  • Using wildcard queries on the left side of a parameter: WHERE something ILIKE '%query', which cannot make use of an index.

When the query isn't coming from your software​

Not every slow query originates from your application code. Common culprits:

  • PMs/Analytics/BI teams running ad-hoc queries against production
  • Data science pulling training datasets during business hours
  • Executives generating board reports (seriously)
The monthly mystery crash

I've seen companies spend weeks debugging "random" production crashes that occurred every first week of the month seemingly semi-randomly. The cause? An executive with production DB access was running SELECT * on 10M row tables without indexes to gather data for the monthly board meeting.

This sent the dev team onto a regular monthly wild-goose-chase to try and find and fix the outage.

Check pg_stat_activity (Postgres) or SHOW PROCESSLIST (MySQL) to see who's running what.

What the code problem looks like​

The core issue: Every query without an index forces a full table scan, checking every single row.

-- This query runs on every page load
SELECT * FROM orders WHERE customer_id = ? AND status = 'pending';

-- But there's no index to support it
-- Missing: CREATE INDEX idx_customer_status ON orders(customer_id, status);

Without an index, the database scans every single order record to find matches. With a tiny 1M orders database, that's 1M comparisons executed per query. Add an index and we bring that down to just tens of operations.

A missing database index problem is ultimately a query forces the database to filter records in a WHERE clause, but due to a lack of an index that supports the WHERE clause it is forced to cycle through every single record one-by-one and check.

How this looks in your ORM​

Most ORMs hide the SQL, making it easy to miss indexing opportunities:

Rails (ActiveRecord):

Order.where(customer_id: customer.id, status: 'pending')
# Generates: SELECT * FROM orders WHERE customer_id = ? AND status = ?
# Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);

Django:

Order.objects.filter(customer_id=customer.id, status='pending')
# Generates: SELECT * FROM orders WHERE customer_id = %s AND status = %s
# Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);

Laravel (Eloquent):

Order::where('customer_id', $customerId)
     ->where('status', 'pending')
     ->get();
# Generates: SELECT * FROM orders WHERE customer_id = ? AND status = ?
# Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);

Entity Framework (C#):

context.Orders
    .Where(o => o.CustomerId == customerId && o.Status == "pending")
    .ToList();
// Generates: SELECT * FROM orders WHERE customer_id = @p0 AND status = @p1
// Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);
See examples for more ORMs (SQLAlchemy, Hibernate, Sequelize, Prisma)

SQLAlchemy (Python):

session.query(Order).filter(
    Order.customer_id == customer_id,
    Order.status == 'pending'
).all()
# Generates: SELECT * FROM orders WHERE customer_id = ? AND status = ?
# Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);

Hibernate (Java):

session.createQuery("FROM Order WHERE customerId = :customerId AND status = :status")
    .setParameter("customerId", customerId)
    .setParameter("status", "pending")
    .list();
// Generates: SELECT * FROM orders WHERE customer_id = ? AND status = ?
// Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);

Sequelize (Node.js):

Order.findAll({
    where: {
        customer_id: customerId,
        status: 'pending'
    }
});
// Generates: SELECT * FROM orders WHERE customer_id = ? AND status = ?
// Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);

Prisma (Node.js/TypeScript):

prisma.order.findMany({
    where: {
        customerId: customerId,
        status: 'pending'
    }
});
// Generates: SELECT * FROM orders WHERE customer_id = ? AND status = ?
// Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);

GORM (Go):

db.Where("customer_id = ? AND status = ?", customerID, "pending").Find(&orders)
// Generates: SELECT * FROM orders WHERE customer_id = ? AND status = ?
// Needs: CREATE INDEX idx_customer_status ON orders(customer_id, status);

You do not always need to fully index every clause in the WHERE either, as an index like this below will return all statuses and the database will sequentially scan through only the customer's set of orders for pending status.

CREATE INDEX idx_customer ON orders(customer_id);

If your customers tend to have lots of orders, it might make sense to have the index on the customer_id, status. If customers have only a few orders, just customer_id is good enough. There are many advanced techniques to use here, but the primary issue that brings down the server is that there is no usable index what-so-ever, and that means every time the query runs it must filter through every single order in the entire database every time this query is used. If you have millions of orders you will have millions of lookups. The index on the other hand will do it in just a few operations.

The best way to find indexing issues is with a good APM, there are many paid-for commercial options that are worth it, there are some free tools which work very well, and many PaaS platforms provide performance reports as part of their offering. You should be able to use your APM to find slow queries. If not, you can also run SQL queries and investigate through your own code to look for issues.

Using an APM is faster and worth it, but you can do manual lookups in Postgres, MySQL or MSSQL if you don't have one.

Keep in mind, any good APM will show you in a few minutes what will take a lot of trial-and-error to locate with these queries, and more than these queries. Your time is not free, and spending time here delays new contributions.

Nevertheless, here are SQL approaches for those who do not run APMs for various reasons:

-- Basic index information
SELECT 
    indexname,
    indexdef
FROM pg_indexes 
WHERE tablename = 'your_table_name'
  AND schemaname = 'public';
-- Comprehensive index details
SELECT
    i.relname AS index_name,
    t.relname AS table_name,
    a.attname AS column_name,
    ix.indisunique AS is_unique,
    ix.indisprimary AS is_primary,
    am.amname AS index_type,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a,
    pg_am am
WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND i.relam = am.oid
    AND t.relname = 'your_table_name'
ORDER BY
    i.relname,
    a.attnum;
    index_name     |   table_name   | column_name | is_unique | is_primary | index_type | index_size
-------------------+----------------+-------------+-----------+------------+------------+------------
 users_pkey        | users          | id          | t         | t          | btree      | 16 kB
 idx_users_email   | users          | email       | t         | f          | btree      | 32 kB
 idx_users_name    | users          | first_name  | f         | f          | btree      | 24 kB
 idx_users_name    | users          | last_name   | f         | f          | btree      | 24 kB
-- All indexes with usage statistics
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY schemaname, tablename, indexname;

You can use these above queries to cross-reference your knowledge of the code and use them as a basis to investigate what tables receive the most sequential scans with your sense of what the code searches for against these tables.

Fixing it​

The Solution: add an index

This is the fastest, simplest, and safest option to restore normal service.

If you have an extremely large table, you should however plan around the time it may take to add the index for the first time. Scheduled downtime or feature-flags are the simplest options.

You may also wish to take the problematic code offline without bringing down the entire project. Add feature flags to temporarily disable the part of the app that runs this query so there is no longer a full service outage but only a partial service outage. This is not always possible but it is a useful way to restore the rest of the business value instead of holding the entire business hostage over one broken feature.

Zero-Downtime Index Creation​

In some situations you may be adding an index to an absolutely massive table for the first time. In such cases, you may wish to use enhance the creation SQL:

Postgres: CREATE INDEX CONCURRENTLY

  • Allows concurrent writes during index creation
  • Takes longer but doesn't lock the table

MySQL: ALGORITHM=INPLACE, LOCK=NONE

  • Available in MySQL 5.6+
  • Rebuilds index without blocking writes

Both options are production-safe and used routinely by high-traffic applications.

Why other solutions fall short​

When indexes are the answer, these alternatives burn time and money:

Marginally helpful but still wrong:

  • Cache layer (Redis) - Adds complexity, invalidation logic, security concerns, and doesn't fix the underlying query. You're caching around the problem.
  • Dedicated read replica - Reduces load on primary but doesn't speed up the query itself. Still scans millions of rows. Turns the outage into a harder to debug game of "russian roulette" for whatever workload is using the unlucky database backend.

Actively harmful:

  • Scale up the database - Buys you time (more RAM for query caching) but doesn't address O(n) lookup. (The $15k/month trap mentioned earlier.)
  • Microservices architecture - Adds network overhead and distributed system complexity to the still-existing query problem.
  • Switch database engines (for example, MySQL → Neo4J) - System-wide rewrite for what's solvable with one SQL statement. Unproven improvements. High risk in all categories.

All these options delay resolution, increase risk, and cost more than CREATE INDEX.

Not an index issue, but often looks like one: Select *.​

The confusion: SELECT * can bottleneck like a missing index, but the fix is different.

While select itself is not about indexing, select * can cause data bottlenecks that look like index issues when the table row itself contains a lot of data, such as file blobs or large JSON documents. * enforces all data to come through the network, and is mostly discarded in the app code later on. Some ORMs solve for this, but not all, and not all use-cases of those ORMs can manage it, and many raw-sql-coded solutions have developers write select * out of habit or convenience. For large data volumes where the queries are quite fast the data transmission overhead could be quite high, slowing everything back down. Instead, write queries specific to what data you need.

A final word about performance tradeoffs of indexing​

Common concern: "But don't indexes slow down writes?"

Everything in software is a tradeoff. The tradeoff here is heavily in favor of indexes. Adding milliseconds of write-overhead saves seconds (or minutes) of read time.

In most cases, the majority of data is read more times than written, so even if there was a serious write-time-tradeoff it would be worth it anyway except in specific write-heavy edge cases.

Here's real benchmark data from PostgreSQL showing actual performance across different scenarios. It took several days of compute to generate these reports.

Test Environment: These tests ran on a local development box. PostgreSQL 16.10 on Ubuntu 24.04, AMD Ryzen 9 7900X, 64GB RAM, 2TB WD Black SN850X NVMe SSD

Count Star

Full table scan to count all rows

-- Test table schema:
-- CREATE TABLE test_table_* (
--   id SERIAL PRIMARY KEY,
--   status VARCHAR(20),
--   category VARCHAR(50),
--   price DECIMAL(10,2),
--   description TEXT,
--   created_at TIMESTAMP DEFAULT NOW()
-- );

-- Query being tested:
SELECT COUNT(*) FROM test_table_1m_0idx;

Pk Lookup_random

Primary key lookup with random ID

-- Test table schema:
-- CREATE TABLE test_table_* (
--   id SERIAL PRIMARY KEY,
--   status VARCHAR(20),
--   category VARCHAR(50),
--   price DECIMAL(10,2),
--   description TEXT,
--   created_at TIMESTAMP DEFAULT NOW()
-- );

-- Query being tested:
SELECT * FROM test_table_1m_0idx 
WHERE id = <random_id>;

Status Filter

Filter by status column (needs index on status)

-- Test table schema:
-- CREATE TABLE test_table_* (
--   id SERIAL PRIMARY KEY,
--   status VARCHAR(20),
--   category VARCHAR(50),
--   price DECIMAL(10,2),
--   description TEXT,
--   created_at TIMESTAMP DEFAULT NOW()
-- );

-- Query being tested:
SELECT COUNT(*) FROM test_table_1m_0idx 
WHERE status = 'active';

Price Range

Range query on price column (needs index on price)

-- Test table schema:
-- CREATE TABLE test_table_* (
--   id SERIAL PRIMARY KEY,
--   status VARCHAR(20),
--   category VARCHAR(50),
--   price DECIMAL(10,2),
--   description TEXT,
--   created_at TIMESTAMP DEFAULT NOW()
-- );

-- Query being tested:
SELECT COUNT(*) FROM test_table_1m_0idx 
WHERE price BETWEEN 100.00 AND 200.00;

Insert Test

Insert new record (indexes add overhead)

-- Test table schema:
-- CREATE TABLE test_table_* (
--   id SERIAL PRIMARY KEY,
--   status VARCHAR(20),
--   category VARCHAR(50),
--   price DECIMAL(10,2),
--   description TEXT,
--   created_at TIMESTAMP DEFAULT NOW()
-- );

-- Query being tested:
INSERT INTO test_table_1m_1idx 
(status, category, price, description)
VALUES ('test', 'benchmark', 99.99, 'Performance test');
  • Even in write-heavy workloads, the math favors indexes. And in read-heavy systems? It's not even close.
  • Modern databases (InnoDB, Postgres) have sophisticated optimizations. The theoretical CS 101 tradeoffs don't match production reality at scale.

In this data, you can see there is a difference between theoretical performance and actual performance in the data. The insertion test is slowest without an index when using 1 billion, which I have not investigated (as the single datapoint took over 1 entire day of compute time), but I wouldn't be surprised if in Postgres had fewer optimizations around finding space in the heap, autovaccume, page-level locks, or something with the WAL. There's lots going on in a modern database that does not get taught in compsci 101 style training. Many users of MySQL for example do not know that with InnoDB there is no heap and everything sits on an index already. Regardless, you should notice that even with 4 indexes on a table at the scale of 1 billion records, the computer-science-time increase is so small that the real performance is impacted more by things like system load than data structures.

NoSQL Databases

This pattern applies to NoSQL databases too, just different terminology:

  • MongoDB: Missing indexes on frequently queried fields
  • DynamoDB: Scans instead of queries, missing GSIs/LSIs
  • Cassandra: Missing clustering columns in WHERE clauses

The principle is identical: structure your data access patterns or pay the performance cost.

The Hidden Cost: Engineering Time​

Beyond the obvious direct expenses like $15k/month in infrastructure costs, missing indexes create a more insidious cost: engineering attention.

Every crash investigation:

  • 2-4 hours of senior engineer time
  • 1-2 hours of team coordination
  • 30-60 minutes of executive explanation

That's 4-6 engineering hours per incident. At 2-3 incidents per month, you're losing 12-18 senior engineering hours to a problem solvable in 15 minutes.

On top of that, you harm your business reputation and trust with end-users and customers.

More than just cost, it's lost product/market differentiation.

Organizational Audit Checklist​

  • Review your 3 most expensive database instances
  • Check pg_stat_user_tables for sequential scans
  • Survey: "Who has production DB access?"
  • Look at cost trends: Is growth with users sub-linear, linear, or exponential?