3 ms·

Why write the transaction ids in the tuples at all? In most production cases, transactions in flight are going to affect a small amount of rows overall, so you

by devit 8y ago

Why write the transaction ids in the tuples at all? In most production cases, transactions in flight are going to affect a small amount of rows overall, so you can just keep the data in memory, and store it to disk in a separate table if it gets large.

anarazel 8y ago
You need to access that data from different connections, so it needs to be correctly locked etc. Looking purely at the tuple you need to know where to look for the tuple visibility information. Accessing data stored in some datastructure off to the side will also have drastically worse cache locality then just storing it alongside with the data. E.g. for a sequential scan these checks need to be done for every tuple, so they really need to be cheap.
- londons_explore 8y ago
  Except in most usecases, the tuple is old enough that it was committed long ago and visible to everyone. It's only a tiny fraction of tuples which are recently committed and visibility rules come into play. That can be a special-cased slow-path
  - anarazel 8y ago
    In a system like postgres' current heap you cannot know whether it was committed long ago, without actually looking in that side table (or modifying the page the one time you do, to set a hint bit). You pretty fundamentally need something like the transactionid to do so. Also, in OLTP workload you often have a set of pretty hotly modified data, where you then actually very commonly access recently modified tuples and thus need to do visibility checks. There's obviously systems with different visibility architectures (either by reducing the types of concurrency allowed, using page level information about recency of modification + something undo based), but given this post is about postgres, I fail to see what you're arguing about here.
SomeHacker44 8y ago
The transaction ID per tuple is a core piece of data used in MVCC, the transaction management protocol underlying PostgreSQL in its current form.

by devit 8y ago

anarazel 8y ago

You need to access that data from different connections, so it needs to be correctly locked etc. Looking purely at the tuple you need to know where to look for the tuple visibility information. Accessing data stored in some datastructure off to the side will also have drastically worse cache locality then just storing it alongside with the data. E.g. for a sequential scan these checks need to be done for every tuple, so they really need to be cheap.

londons_explore 8y ago
Except in most usecases, the tuple is old enough that it was committed long ago and visible to everyone. It's only a tiny fraction of tuples which are recently committed and visibility rules come into play. That can be a special-cased slow-path
- anarazel 8y ago
  In a system like postgres' current heap you cannot know whether it was committed long ago, without actually looking in that side table (or modifying the page the one time you do, to set a hint bit). You pretty fundamentally need something like the transactionid to do so. Also, in OLTP workload you often have a set of pretty hotly modified data, where you then actually very commonly access recently modified tuples and thus need to do visibility checks. There's obviously systems with different visibility architectures (either by reducing the types of concurrency allowed, using page level information about recency of modification + something undo based), but given this post is about postgres, I fail to see what you're arguing about here.

SomeHacker44 8y ago

The transaction ID per tuple is a core piece of data used in MVCC, the transaction management protocol underlying PostgreSQL in its current form.