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.