Re:Zero-downtime Postgres migrations – the hard parts
by Sinjo on Wednesday May 27, 2015 @08:16PM
(#9615307)
You're right on the approach. One thing to be careful of is that there's no option to add a not-null constraint with the `NOT VALID` flag. That means it'll hold the AccessExclusive lock while it checks every row in the table meets the constraint.
You can work around this by adding a `CHECK` constraint which does the same thing, but it's a little inconvenient. There was some work being done to add this feature to not-null constraints (http://www.postgresql.org/message-id/20140517155857.GD7857@e...), but unfortunately it looks like it's gone stale.
Re:Zero-downtime Postgres migrations – the hard parts
by akurilin on Thursday May 28, 2015 @01:09AM
(#9616167)
Isn't 2. basically a cron job you have to write yourself? Not hard, but also not exactly automated from what I can tell. You basically update a batch based on ids from a subquery, vacuum, analyze, rinse and repeat.
* It supports adding a null column immediately.
* It supports slowly filling in a possibly-default value on all records.
* It supports setting the column to non-null when every row has a value.
What pain points are you hitting, and is my analysis totally off-base?
Though a quick search shows that step three might decide to ignore indexes and be slow; that would be disappointing.