zlacker

>>andren+(OP)
This was a fun read. But now I have a couple of questions

1. Since MySQL keeps delta to save storage costs, wouldn't read and writes slower because now I have to build the full version from the delta

2. On secondary indexes, they highlight the reads will be slower and also say:

> Now this may make secondary index reads slower since the DBMS has to resolve a logical identifier, but these DBMS have other advantages in their MVCC implementation to reduce overhead.

What are the other advantages they have to make reads faster?

Compared to MySQL, I remember reading that Postgres MVCC lets you alter the table without locking. Now I found out that MySQL also does not require locks. So, how are they doing?

Are there any similar posts which explain MySQL MVCC architecture?

>>avinas+SH1
1. It's a backward delta. So it's only needed when a transaction touches a row that has been modified by a concurrently running transaction. (Details differing depending on isolation level etc.). Writes can be faster because only the modified attributes need to be written to the delta undo log, not the whole row. I guess reads can be faster too in some cases, e.g. less fragmentation and wasted space (better cache usage,) over time due to in-place updates, and less pointer chasing to find the correct version if the vacuuming isn't keeping up with pruning old versions.

As for MySQL and locks, the original MyISAM table format used locks, but InnoDB tables are MVCC like pgsql.

>>jabl+S42
> Writes can be faster because only the modified attributes need to be written to the delta undo log, not the whole row.

what is delta undo log?

>>avinas+hb5
It's an undo log containing the deltas from the latest version (instead of the entire row), so that a previous version can be reconstructed in case of a rollback or if a concurrently running transaction needs the previous version.