Postgres adapter optimization for add_column method

Looking at postgres adapter I found that add_column works not optimal. First it executes ALTER TABLE ADD COLUMN query and only then sets defaults and NOT NULL if they are needed. But in the real production environment it takes hours to run such migrations.

For example we run:

add_column :users, :failed_attempts, :integer, :null=>false, :default=>0 this migration executes:

UPDATE "users" SET "failed_attempts"=0 WHERE "failed_attempts" IS NULL

which took more then one hour to complete for the table with 2M+ records.

But:

ALTER TABLE users ADD COLUMN failed_attempts INTEGER NOT NULL DEFAULT 0;

took just 2 minutes to complete for the same table.

So such migration will lock the users table for an hour (or even more) and so stop the whole application for hours instead of just 2 minutes.

I've created a ticket and assigned a patch: https://rails.lighthouseapp.com/projects/8994/tickets/6422-patch-postgres-adapter-optimization-for-add_column-method

This is interesting. I think there aren’t so many of us using Postgres so quite a few bugs seem to have crept in over time.

Perhaps you can also implement some optimisations like Pratik has done for multi column changes on MySQL http://m.onkey.org/bulk-alter-table-with-rails-3-and-mysql

As a postgres user, +1 to the optimizations.