Hacker News Reader

Comment by h1fra | Hacker News Reader

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.

View on HN

> 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

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

Thanks :) 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

Fwiw many databases have update be delete+create because you've already invented those primitives so why do something else?

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

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

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

Direct block storage is good, but note that SSDs are essentially just doing CoW internally too.

It'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

In a multi version database you have little choice in the matter. You’re going to CoW anyway.

CoW?

copy on write

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

Reply on news.ycombinator.com