When Adding an Index Freezes Your Application

Your team has been tracking a slow query for weeks. The users table has grown to millions of rows, and searching by email now takes seconds instead of milliseconds. Someone suggests adding an index. The fix seems simple: one CREATE INDEX command, problem solved.

You run the migration during working hours because the query is already hurting users. The index starts building. Then the alerts come in: application timeouts, failed transactions, support tickets piling up. The index you added to fix performance just caused a production incident.

This scenario plays out more often than most teams expect. Indexes and constraints look like simple schema changes, but they carry hidden costs that only show up under load.

What Happens When You Create an Index

When you run CREATE INDEX idx_users_email ON users(email), the database needs to read every row in the table, build a balanced tree structure, and write it to disk. During this process, the database typically locks the table to prevent data changes that would corrupt the index structure.

The type of lock depends on your database. In PostgreSQL, a regular CREATE INDEX acquires a SHARE LOCK. Reads can still happen, but any write operation - inserts, updates, deletes - must wait. In MySQL with InnoDB, the locking behavior varies by operation type and storage engine version.

For a table serving thousands of users, even a few seconds of write lock can create a backlog. Application connections queue up, timeouts cascade, and what was supposed to be a performance improvement becomes a downtime event.

The Concurrent Option Most Teams Forget

Most major databases offer a way to build indexes without blocking writes. PostgreSQL has CREATE INDEX CONCURRENTLY. MySQL supports ALGORITHM=INPLACE and LOCK=NONE on compatible versions.

-- PostgreSQL: builds index without blocking writes
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL: builds index without blocking writes (InnoDB)
ALTER TABLE users ADD INDEX idx_users_email (email), ALGORITHM=INPLACE, LOCK=NONE;

The concurrent approach takes longer. PostgreSQL's CREATE INDEX CONCURRENTLY must wait for ongoing transactions to finish, then perform extra passes to catch changes made during the build. But the trade-off is clear: your application keeps running while the index builds.

The mistake is treating concurrent index creation as optional or advanced. It should be the default for any production migration on a live table. Write it explicitly in your migration scripts. Do not rely on default behavior.

Constraints Also Lock

Foreign key and unique constraints bring their own locking surprises. When you add a foreign key, the database validates that all existing rows satisfy the relationship. This validation reads through the entire table and locks it during the process.

PostgreSQL offers a pragmatic escape: NOT VALID. This adds the constraint for new data only, skipping validation on existing rows. You can then run VALIDATE CONSTRAINT separately, during off-peak hours.

-- Add foreign key without validating existing data
ALTER TABLE orders ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Validate later, when traffic is low
ALTER TABLE orders VALIDATE CONSTRAINT fk_user_id;

Unique constraints have a similar story. Adding UNIQUE on a column requires checking for duplicates across the entire table. On a large table, this scan can lock writes for an uncomfortable duration.

Plan Index Migrations Separately

Many teams lump all schema changes into one migration file: add a column, create an index, add a foreign key. This works fine on small tables. On production tables with millions of rows, it creates a single point of failure.

Index and constraint changes deserve their own migration files, scheduled separately from table structure changes. Some teams go further and run index migrations manually, outside the automated pipeline, to maintain tight control over timing.

A practical approach:

  • Run table structure changes (add column, modify type) in one migration
  • Run index and constraint changes in separate migrations
  • Schedule index migrations during low-traffic windows
  • Use concurrent options explicitly in every migration script
  • Validate constraints separately from adding them

The Real Question

Once indexes and constraints are in place, you need to verify they do not break the running application. An index that changes query plans can cause unexpected performance shifts. A foreign key that blocks a delete cascade can stall background jobs.

This leads to a broader question: how do you keep migrations safe when old and new versions of your application access the same database? That is the topic for the next discussion, but the principle starts here: treat every schema change as a potential production event, not just a script to run.

Checklist for Index and Constraint Migrations

  • Use concurrent index creation (CREATE INDEX CONCURRENTLY or equivalent) on any table with active writes
  • Add foreign keys with NOT VALID when possible, validate separately
  • Run index migrations in separate files from table structure changes
  • Schedule large index builds during low-traffic windows
  • Monitor lock duration and query performance during and after migration
  • Test the migration on a copy of production data before running it live

The Takeaway

An index is not just a performance tool. It is a schema change that can lock your table, queue your writes, and timeout your users. Treat it with the same caution as any other production deployment. Use concurrent options, separate your migrations, and schedule for safety. The query speed you gain is worthless if the migration itself breaks the application.