News for hackers. Stuff that matters.
Re:Zero-downtime Postgres migrations – the hard parts by Dylan16807 on Wednesday May 27, 2015 @06:14PM (#9614764)
Does that lock the table for 4 hours? I was going to talk about how it should be straightforward to add support for almost-non-blocking alters but it looks like all the pieces are already there:

* 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.

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.