PostgreSQL Concurrent Indexes in Rails: Avoiding Downtime in Production
When working with large production databases, adding an index may seem like a simple migration. However, on tables with millions of records, a regular index creation can lock writes and impact your application's availability.
In this article, we'll explore what concurrent indexes are, why they matter, and how to safely create them in Ruby on Rails.
The Problem with Normal Index Creation
Suppose we have a large users table with millions of records.
A typical Rails migration might look like:
add_index :users, :email
Behind the scenes, PostgreSQL executes:
CREATE INDEX index_users_on_email ON users(email);
While PostgreSQL builds the index, it acquires locks on the table.
On large tables this can lead to:
Slower API responses
Blocked INSERT operations
Blocked UPDATE operations
Blocked DELETE operations
Potential downtime during deployment
For example, imagine:
users table = 50 million rows
Creating an index on such a table can take several minutes, and during that time write operations may be affected.
The Solution: Concurrent Indexes
PostgreSQL provides a safer alternative:
CREATE INDEX CONCURRENTLY
This allows PostgreSQL to build the index while minimizing disruption to ongoing database operations.
In Rails, this can be achieved using:
class AddIndexToUsersEmail < ActiveRecord::Migration[7.1] disable_ddl_transaction!
def change
add_index :users, :email, algorithm: :concurrently
end end
How Concurrent Indexes Work
Normal Index Creation
Lock table
↓
Scan rows
↓
Build index
↓
Unlock table
Concurrent Index Creation
Scan table
↓
Build index in background
↓
Track ongoing changes
↓
Finalize index
The application continues to serve requests while the index is being created.
Why disable_ddl_transaction! Is Required
A common mistake is forgetting:
disable_ddl_transaction!
PostgreSQL does not allow:
CREATE INDEX CONCURRENTLY
inside a transaction.
Rails wraps migrations in a transaction by default, so we must disable it before creating concurrent indexes.
Without it, Rails raises an error during migration.
Complete Rails Example
class AddIndexToBookingsUserId < ActiveRecord::Migration[7.1] disable_ddl_transaction!
def change
add_index :bookings, :user_id, algorithm: :concurrently
end
end
When Should You Use Concurrent Indexes?
Concurrent indexes are recommended when:
The table contains millions of rows
The application is live
Downtime is unacceptable
The database receives frequent writes
Typical examples include:
Users table
Orders table
Bookings table
Transactions table
When Is a Regular Index Fine?
For smaller tables and development environments:
add_index :users, :email
is usually sufficient.
Since concurrent index creation performs additional work, it is generally slower than normal index creation.
Trade-offs
| Normal Index | Concurrent Index |
|---|---|
| Faster creation | Slower creation |
| Blocks writes | Minimal locking |
| Simpler | Requires special handling |
| Risky on large production tables | Safer for production |
Real Production Scenario
Imagine a booking platform with:
bookings table = 100 million rows
A slow query is identified:
Booking.where(hotel_id: params[:hotel_id])
The obvious fix is:
add_index :bookings, :hotel_id
Running a normal index creation during business hours could affect booking APIs.
Instead, deploying:
add_index :bookings, :hotel_id, algorithm: :concurrently
allows the application to remain available while the index is built.
Checking Existing Indexes
To view indexes in PostgreSQL:
SELECT * FROM pg_indexes WHERE tablename = 'users';
You can also use:
\d users
from the PostgreSQL console.
Interview Question:
Why do we use concurrent indexes in Rails?
A good answer is:
Normal index creation can lock writes on large tables and impact application availability. PostgreSQL provides CREATE INDEX CONCURRENTLY to build indexes with minimal locking. In Rails, this is achieved using algorithm: :concurrently along with disable_ddl_transaction!. Although concurrent indexes take longer to build, they are much safer for production systems with large datasets and high traffic.
Conclusion
Creating indexes is one of the easiest ways to improve query performance, but doing it incorrectly in production can introduce downtime.
Whenever you're adding indexes to large, high-traffic tables, consider using concurrent indexes. The migration may take longer to complete, but your users won't experience disruptions while it runs.
A few extra minutes during deployment are often worth avoiding minutes of downtime in production.