The lock a plain add_index takes

Two courses already behind this one

"SQL: indexes & performance" showed why an index makes a query fast: a B-tree instead of a sequential scan, with EXPLAIN ANALYZE confirming the difference. Its lesson "add_index in a real Rails migration" showed add_index running inside this app's create_courses and AddPerformanceIndexesToCoreTables migrations. This course repeats neither. It asks a question that only matters once a table has real rows and the application keeps writing to it while the migration runs: what does the database lock, and for how long, to build that index?

Measured on this app's connection

A scratch table with 50,000 rows, this app's PostgreSQL connection, bin/rails runner, timed:

conn.add_index :scratch_idx_timing, :n
# plain add_index on 50,000 rows: 30.8ms

Tens of milliseconds; reruns landed between about 20 and 31 ms. On a table this size, on a development machine with nothing else touching it, a plain add_index isn't slow, and there's no point building a scary story on a number that doesn't support one. This app's own tables tell the same story: a few hundred courses, a few thousand lessons, small enough that every add_index it has run finished instantly. That's the trap. The cost was never about milliseconds.

What add_index locks

According to PostgreSQL's documentation, a plain CREATE INDEX takes a ShareLock on the table for the whole build. A ShareLock lets other transactions keep running SELECT, so reads aren't affected, but it blocks every INSERT, UPDATE and DELETE on the table until the index is built. On a 50,000-row scratch table that lasts a few dozen milliseconds, and nobody notices. On a production table with a hundred million rows that's taking writes, the same ShareLock can be held for minutes. Every write to that table, from every request and every background job, waits behind it the whole time.

That's the mechanism: the row count changes how long the lock is held, not whether it's taken. This app has never felt it, because it has never had a table large enough, under enough concurrent writes, for the duration to matter. The migration this course builds works the same on an empty table or a huge one. That's why it's worth learning here, on a table small enough to reason about, before you need it on one that isn't.

Key takeaways

• "SQL: indexes & performance" covers why indexes speed up queries and how add_index works inside a migration. This course starts past both, with what happens to a table's writes while the index is built.
• On this app's PostgreSQL connection, a plain add_index over 50,000 rows takes tens of milliseconds, which is invisible at this app's table sizes.
• According to PostgreSQL's documentation, a plain CREATE INDEX holds a ShareLock, blocking every INSERT/UPDATE/DELETE (not SELECT) on the table for the whole build.
• The risk grows with the table, not the syntax: the same add_index that's invisible on a small table can hold up every write for minutes on a busy production table.