Re:Transaction ID Wraparound in Postgres
by jandrewrogers on Thursday July 23, 2015 @02:07PM
(#9937453)
Not really. There are good technical reasons for why most MVCC database engines intentionally avoid using transaction ids with enough bits such that they will never plausibly wraparound. More on that in a moment.
Empirically, you need at least a 64-bit transaction id to practically mitigate the possibility of wraparound; even MVCC systems with 48-bit transaction ids have wrapped around in real systems. Consequently, most databases like PostgreSQL are designed under the assumption that wraparound will happen and provide mechanisms for smoothly managing that in the hands of someone that knows what they are doing. No matter how much automatic safety you build into the database, there are performance optimization knobs that enable an incompetent or unskilled DBA to effectively disable or render ineffective the transaction wraparound management mechanisms. This is clearly explained in database documentation.
So back to why transaction id wraparound exists as a thing:
In an MVCC database, each logical record you store usually has multiple transaction ids associated with it that are stored with the record. Because most relational database records are typically quite small, the transaction id overhead in the heap row headers can be a significant percentage of the total storage cost. If you store transactional information in your indexes as well (some do, some don't) the relative space consumption is even worse.
This has an enormous performance impact. A typical row header in an OLTP database is 20-30 bytes. In cases where implementors have shaved a couple bytes off the row header size with heroic efforts, it has generated a 5-10% performance boost across the board due to the more compact representation. The difference between a 32-bit transaction id and a 64-bit transaction id is at least 8 bytes! Yes, you could use 64-bit transaction ids but at the cost of a database that is much more bloated -- and therefore slower -- than the 32-bit version. Since there are good technical solutions to transaction id wraparound, it would be silly to make everyone suffer from poor performance to keep a few poor DBAs from shooting themselves in the foot.
That is not to say you should never use a 64-bit transaction id in a database engine design -- I have, in fact -- but that you should understand the technical tradeoffs and implications.
Re:Transaction ID Wraparound in Postgres
by caf on Thursday July 23, 2015 @08:59PM
(#9939485)
Is there a reason that you couldn't store the transaction IDs in the row headers in a more compact form - for example using the full 64 bits for txmin, but storing txmax as a 24 bit delta from txmin?
Empirically, you need at least a 64-bit transaction id to practically mitigate the possibility of wraparound; even MVCC systems with 48-bit transaction ids have wrapped around in real systems. Consequently, most databases like PostgreSQL are designed under the assumption that wraparound will happen and provide mechanisms for smoothly managing that in the hands of someone that knows what they are doing. No matter how much automatic safety you build into the database, there are performance optimization knobs that enable an incompetent or unskilled DBA to effectively disable or render ineffective the transaction wraparound management mechanisms. This is clearly explained in database documentation.
So back to why transaction id wraparound exists as a thing:
In an MVCC database, each logical record you store usually has multiple transaction ids associated with it that are stored with the record. Because most relational database records are typically quite small, the transaction id overhead in the heap row headers can be a significant percentage of the total storage cost. If you store transactional information in your indexes as well (some do, some don't) the relative space consumption is even worse.
This has an enormous performance impact. A typical row header in an OLTP database is 20-30 bytes. In cases where implementors have shaved a couple bytes off the row header size with heroic efforts, it has generated a 5-10% performance boost across the board due to the more compact representation. The difference between a 32-bit transaction id and a 64-bit transaction id is at least 8 bytes! Yes, you could use 64-bit transaction ids but at the cost of a database that is much more bloated -- and therefore slower -- than the 32-bit version. Since there are good technical solutions to transaction id wraparound, it would be silly to make everyone suffer from poor performance to keep a few poor DBAs from shooting themselves in the foot.
That is not to say you should never use a 64-bit transaction id in a database engine design -- I have, in fact -- but that you should understand the technical tradeoffs and implications.