Missing Database Indexes in a PR: What Reviewers Should Check

A missing database index almost never looks like anything in a diff. The pull request adds a filter option, a sort order, or a nightly job. The ORM call reads cleanly, the tests pass against a few hundred rows, and the release goes out. A week later the same query is scanning sixty million rows on every page load.

This is a review guide for the moment before merge. If a release is already slow and you are trying to work out why a query behaves differently in production, including plan changes, stale statistics, and skew, start with Why an API Is Slow in Production but Fast Locally. The two overlap on one point: a query plan run against production-sized data answers most index questions in a minute.

Why do missing indexes get through code review?

A missing index is a query that the database can only answer by reading far more rows than it returns, because no index matches the columns the query filters, sorts, or joins on. It gets through review for structural reasons, not careless ones:

• The diff shows an ORM call, not SQL. .filter(status="open") does not say “full table scan”. The query shape is only visible once you translate it.
• The indexes live somewhere else. They are defined across years of migrations, or added by hand in production. Nothing in the diff shows which exist.
• Tests run on tiny tables. A sequential scan over 2,000 rows takes about a millisecond. No test measures rows read, so none fails.
• The cost depends on production data. Table size, value distribution, and growth rate are not in the repository.

So the review question is not “did the author add an index?” It is “what does each new query ask the database to do, and how many rows will that touch in production?” Answering it takes three inputs: the SQL the code produces, the indexes that exist, and a rough size for the table.

Which changes in a PR create a new query shape?

A query shape is the combination of table, filter columns, sort, and joins, independent of the parameter values. Most index regressions come from a small set of innocent-looking changes that create a new shape against an existing large table.

Here is a realistic example. A pull request adds an “overdue” view for invoices and a nightly reminder job. Neither touches a migration.

# View: one account's overdue invoices, oldest first
def overdue(request):
    return (Invoice.objects
            .filter(account=request.account,
                    status="open",
                    due_at__lt=timezone.now())
            .order_by("due_at")[:50])

# Nightly job: every overdue invoice, all accounts
def send_reminders():
    overdue = Invoice.objects.filter(status="open",
                                     due_at__lt=timezone.now())
    for inv in overdue.iterator():     # no index starts with status or due_at
        notify(inv)

The only index that helps the view is Django’s automatic index on the account foreign key. For a small account that is fine. For the largest account, with hundreds of thousands of invoices, the database reads all of them, filters by status and date, and sorts the survivors. The job has nothing to use at all.

How does composite index column order decide what is covered?

A B-tree composite index stores entries sorted by its first column, then by the second within each value of the first, and so on. A query can seek directly to a contiguous range only when it constrains the leading columns. That is why the order of columns matters more than which columns are included.

The practical rules a reviewer can apply without a database:

• Equality columns first, then one range or sort column. (account_id, status, due_at) serves account_id = ? AND status = ? AND due_at < ? ORDER BY due_at.
• A prefix is usable on its own. The same index serves WHERE account_id = ?, which can make an existing single-column account_id index redundant.
• Skipping the leading column usually means a scan. Newer engines can sometimes do a skip scan (MySQL 8.0.13+, PostgreSQL 18+) when the leading column has very few distinct values. Do not rely on it for a tenant or account column.
• Only the first range condition narrows the seek. Columns after a range (<, BETWEEN, LIKE 'x%') can still filter inside the index, but they no longer bound the range.
• A different query may need a different index. The nightly job above needs its own index; a partial one keeps it small because paid invoices are excluded.

Which query patterns stop an existing index being used?

Sometimes the index exists and the query still scans. The query wraps the column in something the index was not built on, or asks a question a B-tree cannot answer by range. These are easy to spot in review once you know the list.

Does PostgreSQL index foreign keys automatically?

No. PostgreSQL creates an index for every primary key and unique constraint, but not for the referencing column of a foreign key. The PostgreSQL documentation on foreign keys notes that indexing the referencing columns is often a good idea but is not automatic. Two things get slow without it:

• Joins and lookups from parent to child, such as loading an invoice’s line items.
• Deletes and key updates on the parent. The database must check the child table for referencing rows, and with ON DELETE CASCADE it must find them to delete them. Without an index, each parent delete can scan the whole child table. A cleanup job that deletes 10,000 invoices can turn into 10,000 scans of the line items table.

Whether the column gets an index depends on the framework as much as the database:

A reviewer check that catches most of these: every new foreign key column in a migration either has an index in the same migration, or a comment explaining why the table will stay small.

What EXPLAIN evidence should a reviewer ask for?

Reading the query tells you whether an index should be used. EXPLAIN tells you whether it is. The useful version for review is EXPLAIN (ANALYZE, BUFFERS) on a database with production-like row counts, such as a staging copy, a sanitized snapshot, or a read replica. Plans on a 2,000-row development table prove nothing, because the planner is right to scan small tables.

Limit (actual time=4210.8..4210.9 rows=50 loops=1)
  ->  Sort (actual time=4210.8..4210.8 rows=50 loops=1)
        Sort Key: due_at
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Index Scan using invoices_account_id_idx on invoices
              (actual time=0.05..4174.2 rows=1916 loops=1)
              Index Cond: (account_id = 9)
              Filter: ((status = 'open') AND (due_at < now()))
              Rows Removed by Filter: 481344
              Buffers: shared hit=21870 read=164032
Execution Time: 4211.0 ms

Limit (actual time=0.04..0.19 rows=50 loops=1)
  ->  Index Scan using inv_acct_status_due_idx on invoices
        (actual time=0.04..0.18 rows=50 loops=1)
        Index Cond: ((account_id = 9) AND (status = 'open') AND (due_at < now()))
        Buffers: shared hit=54
Execution Time: 0.2 ms

Notice that the “before” plan does use an index. That is the trap: “it uses an index” is not the same as “it is indexed for this query”. The number that matters is rows read versus rows returned. Here the database read 483,260 rows to return 50.

After release, pg_stat_statements ranks queries by total time, and pg_stat_user_tables shows seq_scan and seq_tup_read per table. A jump in sequential rows read on a large table right after a deploy is the post-merge version of the same evidence.

How do you add the index without blocking writes?

Finding the missing index is half the review. The other half is making sure the migration that adds it does not cause its own outage. On a large table, index builds take minutes, and a plain CREATE INDEX in PostgreSQL blocks inserts, updates, and deletes on the table for the whole build. Reads continue. CREATE INDEX CONCURRENTLY avoids blocking writes, at the cost of a slower build and some rules of its own.

The catch is that most migration tools wrap each migration in a transaction, and CREATE INDEX CONCURRENTLY cannot run inside one. Each framework has an escape hatch, and the reviewer should see it in the diff:

from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models

class Migration(migrations.Migration):
    atomic = False   # required: CONCURRENTLY cannot run in a transaction
    dependencies = [("invoices", "0042_invoice_due_at")]
    operations = [
        AddIndexConcurrently("invoice", models.Index(
            fields=["account", "status", "due_at"], name="inv_acct_status_due_idx")),
        AddIndexConcurrently("invoice", models.Index(
            fields=["due_at"], name="inv_open_due_idx",
            condition=models.Q(status="open"))),   # partial index for the job
    ]

# Alembic (PostgreSQL)
def upgrade():
    with op.get_context().autocommit_block():
        op.create_index("inv_acct_status_due_idx", "invoices",
                        ["account_id", "status", "due_at"],
                        postgresql_concurrently=True)

# Rails
class AddOverdueIndexToInvoices < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!
  def change
    add_index :invoices, [:account_id, :status, :due_at], algorithm: :concurrently
  end
end

Three more migration checks belong in the same review:

• Order of deploys. The index should exist before the code that needs it takes traffic. Ship the migration first, or in the same release with migrations run before the new code starts.
• Invalid indexes after a failure. A failed concurrent build leaves an index marked invalid that still costs writes. The rollout plan should say how it is detected and dropped.
• MySQL. InnoDB builds most secondary indexes online; stating ALGORITHM=INPLACE, LOCK=NONE makes the statement fail instead of silently taking a blocking path. Check your version’s online DDL rules.

Can the new index make things worse?

Yes, on the write side. Every index is updated on insert and on any update to an indexed column, takes disk and cache, and in PostgreSQL an index on a frequently updated column can prevent heap-only tuple (HOT) updates. Reviewers should also catch duplicate indexes (a new (account_id, status) next to an existing (account_id, status, due_at)), and indexes added “just in case” on columns no query filters on. After release, pg_stat_user_indexes.idx_scan shows whether the index is actually used.

A review procedure for index risk

1. List the query shapes the PR adds or changes. Include ORM filters, sorts, relation loads, new lookups, and background jobs, and read the generated SQL where the ORM hides it.
2. Estimate the production size of each table. Row count and growth rate, plus the largest tenant or account if queries are scoped per tenant.
3. Match each shape against existing indexes. Equality columns first, then the range or sort column, against the leading columns of an index on that table. Check new foreign key columns separately.
4. Look for patterns that defeat an index. Functions, casts, leading wildcards, OR across columns, and mismatched sort directions.
5. Ask for EXPLAIN evidence. EXPLAIN (ANALYZE, BUFFERS) on production-like data, comparing rows read with rows returned.
6. Review the migration itself. Non-blocking build, deploy order, handling of an invalid index, and no duplicate of an existing index.

This belongs in the same review pass as the other data-size questions: is the new query bounded, does a loop issue a query per item, and what happens at the largest customer’s volume. How to Review a Pull Request for Production Reliability Risks covers the full checklist; the sibling post on N+1 queries that pass tests covers the per-item query problem. A query that is slow because of a missing index also holds its database connection longer, which is one of the ways pools run dry (see Connection Leak vs. Pool Exhaustion).

How Tomosu helps

The hardest part of this review is context the diff does not contain: which indexes already exist, which other code paths query the same table, and how that table is used across the service. Tomosu analyzes the repository as a whole and each pull request against it for production reliability risk, and for index risk that means:

• New query shapes against existing tables, including ORM filters, sorts, and background jobs, compared with the indexes defined in the repository’s migrations and models.
• Foreign key columns without an index, and parent deletes or cascades that would scan a child table.
• Migration safety: index builds that block writes, and concurrent builds inside a transactional migration.
• Evidence to request: the EXPLAIN output and table-size checks that would confirm or dismiss a finding before merge.

Tomosu reads indexes from the code, so an index created by hand in production and never added to a migration will not be visible to it; that is a gap worth closing anyway. Findings roll up into the Production Reliability Index alongside the pull request’s other risks.

Scan your repository with Tomosu →

Key takeaways

• A missing database index shows up in a PR as a new query shape: a new filter, sort, join, lookup, or background job on a large table.
• Tests pass because a scan over a small table is fast. Only production row counts expose it.
• Composite indexes serve queries that constrain their leading columns: equality columns first, then one range or sort column.
• PostgreSQL does not index foreign key columns automatically; Django and Rails usually do, SQLAlchemy and JPA do not.
• Ask for EXPLAIN (ANALYZE, BUFFERS) on production-like data and compare rows read with rows returned.
• Review the migration too: build concurrently, deploy it before the code that needs it, and avoid duplicate indexes.

Frequently asked questions

The index a query needs is decided by the query, not by the table. Review the shape each change adds, and the missing index is visible before it reaches production. Assess your repository →