Do databases make a delete and an insert when having to update rows?

Question

So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something made across all databases and then I started a discussion telling that I thought that it had no sense but I didn't had enough resources to support my position. He seems to know a lot but I can't understand why would dbs do that.

I mean, I know that if you update a field and you need more space for that row, then it may delete the row physically and put it at the end with the new data. But if for example you reduce the space used, why would it delete and re insert it at the end?

Is this even true? What are the benefits?

Answer 1

Is this even true?

No, it is an implementation detail. A database may implement a suitable update in place if it so chooses.

What are the benefits?

Splitting an update into a delete followed by an insert generally makes the implementation simpler. Potential side benefits include the ability to avoid transient key violations in a unique index, by suitably sorting the split delete/insert operations.

A split update may be a little slower and generate more log than a true in-place update (which is not always possible anyway).

As Kin noted in a comment, if you need an example (for SQL Server) see:

• Are all updates split into delete-insert? by Gail Shaw; and
• Do changes to index keys really do in-place updates? by Paul Randal

It is also related to implementing MVCC. In Wikipedia page about MVCC, it is mentioned:

When an MVCC database needs to update an item of data, it will not overwrite the old data with new data, but instead mark the old data as obsolete and add the newer version elsewhere. Thus there are multiple versions stored, but only one is the latest. This allows readers to access the data that was there when they began reading, even if it was modified or deleted part way through by someone else.

See also page 60 of PostgreSQL Internals pdf by Bruce Momjian (Postgres uses MVCC): "UPDATE is effectively a DELETE and an INSERT."