#6422 [PATCH] Postgres adapter optimization for add_column method - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#6422 ✓committed

[PATCH] Postgres adapter optimization for add_column method

Reported by 2kan | February 12th, 2011 @ 06:50 PM | in 3.1

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.

In the assigned patch I've fixed it in the manner it was done for the mysql.

postgres_adapter_performance_optimization_against-rails-edge.diff 2.3 KB

Comments and changes to this ticket

You flagged this item as spam.
2kan February 12th, 2011 @ 06:50 PM
- Tag set to “patch, postgresql”
You flagged this item as spam.
Vijay Dev February 12th, 2011 @ 07:15 PM
- Title changed from “[PACTH] Postgres adapter optimization for add_column method” to “[PATCH] Postgres adapter optimization for add_column method”
Santiago Pastorino February 12th, 2011 @ 07:17 PM
- Milestone set to “3.1”
- Assigned user changed from “Santiago Pastorino” to “Aaron Patterson”
- Importance changed from “” to “Low”
Aaron Patterson February 15th, 2011 @ 06:55 PM
- State changed from “new” to “committed”
seems good. Merged and pushed.