25 Apr '16, 5pm

How to create Postgres indexes concurrently in ActiveRecord migrations

By default, Postgres locks writes (but not reads) to a table while creating an index on it. That can result in unacceptable downtime during a production deploy. On a large table, indexing can take hours. However, Postgres has a CONCURRENTLY option for CREATE INDEX that creates the index without preventing concurrent INSERT s, UPDATE s, or DELETE s on the table. To make this option easier to use in migrations, ActiveRecord 4 introduced an algorithm: :concurrently option for add_index . Here’s an example: class AddIndexToAsksActive < ActiveRecord::Migration disable_ddl_transaction! def change add_index :asks, :active, algorithm: :concurrently end end The caveat is that concurrent indexes must be created outside a transaction . By default, ActiveRecord migrations are run inside a transaction. ActiveRecord 4’s new disable_ddl_transaction! method must therefore be used in combi...

Full article: https://robots.thoughtbot.com/how-to-create-postgres-inde...

Tweets

@bronafide

zalora.sg 28 Apr '16, 2pm

Are you on facebook? Sign in with facebook We will never post on your behalf or share any information without your permiss...

Postgres Index Types

robots.thoughtbot.com 21 Apr '16, 12am

As discussed in “A Grand Piano for Your Violin” , indexes are quite useful for speeding up database queries. When creating...

Postgres window functions and Common Table Expr...

robots.thoughtbot.com 28 Apr '16, 7pm

CREATE TABLE posts ( id integer PRIMARY KEY, body varchar, created_at timestamp DEFAULT current_timestamp ); CREATE TABLE ...

Integrating Trailblazer and ActiveRecord transa...

rubyflow.com 27 Apr '16, 3pm

Integrating Trailblazer and ActiveRecord transactions outside of a request lifecycle by Maciej Mensfeld — Today When you u...