3 ms·
All alters require a full read/write lock, it’s just that most return instantly. This can be a problem if you have long running transactions, as the alter block
by idunno246 8y ago
All alters require a full read/write lock, it’s just that most return instantly. This can be a problem if you have long running transactions, as the alter blocks behind all open txns and all new queries block behind that. python for instance has a very strong opinion that you should be using transactions for everything, and is much more likely to have to deal with it than say ruby.
But you’re right, my comment is mostly pedantic, that Postgres implements alters better so these tools aren’t needed.
- timewarrior 8y agoThis is great to know. I usually am able to manage without transactions. So alerts should be pretty fast.
- paulryanrogers 8y agoThere are some techniques for mitigating those, such as adding new columns as nullable without a default.
- idunno246 8y agoRight, adding a column with a default means the alter takes time while holding that lock and nothing can be read/written so is generally unsafe for big tables, but it doesn’t help if the alter can’t acquire the lock in the first place