3 ms·
Vacuuming is such a complex task in optimize in Postgres. Depending on your access pattern you will need to also configure index with conditions and/or partitio
by h1fra 3y ago
Vacuuming is such a complex task in optimize in Postgres. Depending on your access pattern you will need to also configure index with conditions and/or partitioning (on top of sane vacuuming settings).
And when you realise that Update is the same as Delete+Create it adds up to the confusion.
- refset 3y ago> Update is the same as Delete+Create I gave a talk titled "UPDATE Considered Harmful" featuring exactly this idea a few months back. The etymology still makes no sense to me...they should have named it REPLACE instead of UPDATE: https://www.youtube.com/watch?v=JxMz-tyicgo https://www.youtube.com/watch?v=JxMz-tyicgo
- chuckhend 3y agoGood talk! It was surprising to me when I first learned that UPDATE does not literally overwrite what is on disk, its more like an abstracted API that gives me the impression that I updated the row on disk.
- refset 3y agoThanks :) The surprises don't stop though - it's abstract turtles all the way down...meaning plenty of fat to trim between UPDATE and what's happening in the MOSFETs. Might be of interest: https://arxiv.org/pdf/2307.11866.pdf https://arxiv.org/pdf/2307.11866.pdf
- hobs 3y agoFwiw many databases have update be delete+create because you've already invented those primitives so why do something else?
- rbanffy 3y agoThis is useful for transactional operations where you want to preserve the previous state until the transaction completes. If you don't need transactions, or you don't need point-in-time queries, then updating in place would save time.
- refset 3y agoExcept updating in place is almost never what is actually happens outside of RAM - there are usually various layers of Copy on Write happening anyway. So unless your database (or the high-churn subset) cleanly fits in RAM I would generally expect that the significance of any time saved is debatable. Denying yourself the possibility of adding transactions later down the line is a huge risk in most situations.
- rbanffy 3y agoIn any case, update in place will mean at least half as many CoW operations. Many databases have (or, at least, had) the ability to manage block storage directly without the need for a filesystem. If transactions can be supported only for some operations while others can be transactionless, you can speed up the operations that won't need them.
- refset 3y agoDirect block storage is good, but note that SSDs are essentially just doing CoW internally too.
- refset 3y agoIt's perhaps also worth a mention that embracing the internal characteristics of SSD throughout the software stack looks like the inevitable direction we're headed (short of another major breakthrough in storage tech) - e.g. see "A Survey on the Integration of NAND Flash Storage in the Design of File Systems and the Host Storage Software Stack" https://arxiv.org/pdf/2307.11866.pdf https://arxiv.org/pdf/2307.11866.pdf
- hinkley 3y agoIn a multi version database you have little choice in the matter. You’re going to CoW anyway.
- h1fra 3y agoYep not complaining about the implementation, just it's not the mental model you have when you begin using Databases. It took me many years to understand this bit.