Skip to content

Instantly share code, notes, and snippets.

@quarterdome
Last active December 11, 2015 22:28
Show Gist options
  • Save quarterdome/4669408 to your computer and use it in GitHub Desktop.
Save quarterdome/4669408 to your computer and use it in GitHub Desktop.
A postgres conundrum
#
# lets say we have a LARGE table
test=# select count(*) from blah;
count
----------
20000000
(1 row)
#
# and we want to add a column to it with a default value
test=# alter table blah add column active boolean not null default false;
ALTER TABLE
#
# this can easily take hours (took about 4.5 hours)
#
# but ...
#
# this code takes no time
test=# alter table blah add column active boolean;
ALTER TABLE
# and then this Ruby code takes 20 minutes (lets assume all ids are sequential)
20_000.times do |i|
min = i*1000
max = (i+1)*1000 - 1
Blah.where( "id between (#{min}, #{max}").update_all('active = false')
end
# and then this SQL code takes seconds
test=# alter table blah alter column active set default true;
ALTER TABLE
test=# alter table blah alter column active set default not null;
ALTER TABLE
#
# Yes, second examples looses transactionality ... but it is order of magnitude faster.
#
# Question: is there a Postgres way of doing it?
# Some sort of incremental commit?
# Some way of turning off transactionality for one update statement?
#
# Thanks for reading!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment