ActiveRecord migrations: indexes
This course covers how ActiveRecord migrations add and remove indexes on PostgreSQL tables that keep receiving writes. It starts with the ShareLock a plain add_index takes and why its duration grows with the table. It then covers algorithm: :concurrently with disable_ddl_transaction!, along with the trade-offs PostgreSQL documents. Partial indexes with where: and expression indexes follow, each tied to this app's models. The last lessons cover remove_index, index_exists? and if_not_exists:, then combine the options into one migration that is safe to rerun.
What you'll learn
- ✓How the ShareLock of a plain add_index blocks writes during the build
- ✓Building indexes with algorithm: :concurrently and disable_ddl_transaction!
- ✓Partial indexes with where: over a slice of a table
- ✓Expression indexes, and when normalizing on write is the simpler choice
- ✓Removing indexes with remove_index, by column or by name
- ✓Rerunnable migrations with index_exists?, if_not_exists: and if_exists:
The lock a plain add_index takes
"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…
Read the full lesson free →Curriculum
7 lessonsThe lock a plain add_index takes
A plain add_index holds a ShareLock that blocks writes for the whole build, longer on bigger tables.
algorithm: :concurrently and disable_ddl_transaction!
algorithm: :concurrently lets writes continue during the build but requires disable_ddl_transaction! and has documented trade-offs.
Partial indexes: an active-enrollment case
The where: option builds a partial index over matching rows, shown with this app's enrollment status enum.
Expression indexes: indexing a function result
add_index accepts a SQL expression such as lower(email), and this app avoids needing it by downcasing emails on save.
remove_index: cleaning up an index that outlived its query
remove_index drops indexes that no longer serve a query, with concurrent removal and if_exists: for safe cleanup.
index_exists? and if_not_exists:: a migration safe to rerun
index_exists? and if_not_exists: make index migrations safe to rerun after a failed concurrent build.
Putting it together: a migration safe for a live table
One migration combines a guard, a partial condition and a concurrent build for a table taking writes.