4 ms·
Timely article for me. Just now starting to deal with migrating Postgres tables where adding a new non null column is taking 4+ hours. Love how the process has
by akurilin 11y ago
Timely article for me. Just now starting to deal with migrating Postgres tables where adding a new non null column is taking 4+ hours. Love how the process has to be completely different at this kind of scale.
- Dylan16807 11y agoDoes 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.
- Sinjo 11y agoYou'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@eldon.alvh.no-ip.org http://www.postgresql.org/message-id/20140517155857.GD7857@e...), but unfortunately it looks like it's gone stale.
- akurilin 11y agoIsn'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.