3 ms·
Last time I worked with PostgreSQL, it would lock entire table while adding a new column. Imagine users table, then you have to schedule this kind of altering o
by rooam-dev 8y ago
Last time I worked with PostgreSQL, it would lock entire table while adding a new column. Imagine users table, then you have to schedule this kind of altering outside business hours... which impacts devs (they have to stay late and/or come early) and business potentially, it has to wait till next day to have a feature delivered.
All these are trade-offs and everyone decides what is more important for them.
- anarazel 8y ago> it would lock entire table while adding a new column It does so for the entire << 1ms it takes to add the necessary metadata.
- rooam-dev 8y agoIf that's the case, then one less problem to deal with, however I remember it was a matter of several minutes.
- anarazel 8y agoThat's only the case if you add a DEFAULT value for the new column. Up until the soon-to-be-released v11 that required updating existing rows. Without a default it's essentially just inserting a single row in a row into an internal catalog table (pg_attribute). With a default in v11, we basically just store the default for a new column out of line, and reference it when the row is read. In either case a lock has to be acquired on the table, which means if there's a longrunning transaction, you can end up blocking for a while...
- rooam-dev 8y agoAh, yes, i think that was the case. Thanks for extra details!