Last active
December 11, 2015 22:28
-
-
Save quarterdome/4669408 to your computer and use it in GitHub Desktop.
A postgres conundrum
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# | |
# 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