The Complete Guide to Database Indexing for Schema Changes

Published April 22, 2026 · SchemaLens

You add a foreign key. You run the migration. Everything looks green. Then your application slows to a crawl, queries time out, and your database CPU graph looks like a ski slope.

The culprit? You added the constraint but forgot the index. And now every INSERT into the child table triggers a full table scan on the parent.

Indexing during schema changes is not optional. It is the difference between a migration that deploys in milliseconds and one that locks tables for hours. This guide covers when to create indexes, how to do it safely, and the five index patterns every schema migration should include.

Why Indexing Matters During Schema Migrations

Most developers think of indexes as a performance optimization — something you add later when queries slow down. This is backwards. Indexes are a structural requirement for many schema changes. Without them, your migrations create production incidents.

Consider what happens when you add a foreign key without an index:

  1. Every INSERT or UPDATE on the child table must verify the parent key exists.
  2. Without an index on the parent column, the database scans the entire parent table.
  3. On a large table, this scan holds a lock for seconds or minutes.
  4. Concurrent writes queue up. Timeouts cascade. Your application falls over.

This is not theoretical. It is one of the most common causes of migration-related outages. The fix is trivial: add the index before or with the foreign key. But you have to know to do it.

The 5 Indexes Every Schema Migration Needs

1. Foreign Key Indexes

Every foreign key column needs its own index. Not the primary key on the parent table — that already has one. The foreign key column on the child table needs an index.

-- PostgreSQL / MySQL / SQLite
ALTER TABLE orders ADD COLUMN user_id INT NOT NULL;
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id);

-- CRITICAL: add the index on the child table
CREATE INDEX idx_orders_user_id ON orders(user_id);

Some databases (like PostgreSQL) do not auto-create this index. Others (like MySQL's InnoDB) require it for certain operations. Do not rely on defaults. Explicitly create the index.

2. Unique Constraint Indexes

When you add a UNIQUE constraint, the database creates an index automatically. But if you remove a unique constraint, the index may or may not be dropped depending on your dialect.

-- PostgreSQL: dropping the constraint drops the index
ALTER TABLE users DROP CONSTRAINT users_email_key;

-- MySQL: dropping the constraint may leave the index behind
ALTER TABLE users DROP INDEX users_email_key;

After removing a unique constraint, audit your indexes. Orphaned unique indexes waste write performance and storage.

3. Composite Indexes for Multi-Column Lookups

If your migration adds multiple columns that will be queried together, a composite index is often better than separate single-column indexes.

-- Good: covers queries like WHERE tenant_id = ? AND status = ?
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);

-- Less good for the same query pattern
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE INDEX idx_orders_status ON orders(status);

Order matters in composite indexes. Put the most selective column first. If you are unsure which is more selective, check your query logs.

4. Partial Indexes for Conditional Queries

PostgreSQL supports partial indexes — indexes that only cover rows matching a condition. These are smaller, faster to maintain, and highly effective for soft-delete patterns.

-- Only index active users; ignore deleted rows
CREATE INDEX idx_users_active_email
  ON users(email)
  WHERE deleted_at IS NULL;

If your migration adds a deleted_at column for soft deletes, add partial indexes on your existing unique constraints. Otherwise, you will not be able to reuse email addresses after deletion.

5. Covering Indexes for Read-Heavy Workloads

A covering index includes all columns needed for a query, allowing the database to satisfy the query entirely from the index without touching the table.

-- MySQL / PostgreSQL (INDEX INCLUDE)
CREATE INDEX idx_orders_user_created
  ON orders(user_id, created_at)
  INCLUDE (total_amount, status);

Use covering indexes when your migration adds columns to a table that is queried heavily and you know the exact access pattern.

How to Create Indexes Without Locking Tables

The biggest risk of index creation is table locking. On large tables, a standard CREATE INDEX can lock writes for minutes or hours. Here is how to avoid that in each major dialect.

PostgreSQL: CREATE INDEX CONCURRENTLY

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

This builds the index without locking the table. It takes longer and uses more CPU, but your application keeps running. Use it for every index on a production table with >100,000 rows.

MySQL: ALGORITHM=INPLACE, LOCK=NONE

ALTER TABLE orders ADD INDEX idx_user_id (user_id),
  ALGORITHM=INPLACE, LOCK=NONE;

MySQL 5.6+ supports online DDL for index creation. Always specify ALGORITHM=INPLACE, LOCK=NONE to ensure the operation fails rather than locks if online DDL is not possible.

SQL Server: ONLINE = ON

CREATE INDEX idx_orders_user_id ON orders(user_id)
  WITH (ONLINE = ON);

SQL Server Enterprise Edition supports online index builds. Standard Edition does not. Know your edition before running this in production.

SQLite: No Online Index Creation

SQLite locks the entire database during CREATE INDEX. For large SQLite databases, consider creating the index during a maintenance window or using a replica.

Indexing Checklist for Every Migration

Before you merge any schema change, verify:

If your migration tool does not enforce this checklist, enforce it in code review. A single missing index can cost more than every other migration in the sprint combined.

Index Migration Safety Tips

Beyond the checklist, follow these rules for safe index management:

How SchemaLens Helps

SchemaLens compares two schemas and flags missing indexes automatically. When you paste an old schema and a new schema, you see:

Use it during code review to catch index gaps before they reach production.

Try SchemaLens — free, browser-based, privacy-first.

Compare two SQL schemas instantly. Spot missing indexes before they break production.

Open Schema Diff →

Related Reading