Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How long will a 64 bit Transaction-ID last in PostgreSQL?

andreas.scherbaum.la

Post reply on HN
Print view View original

11–20 of 33 posts

Re: How long will a 64 bit Transaction-ID last in PostgreSQL?

#11

Post by anarazel »

There's no chance we go for 64bit transaction ids on the tuples themselves - the space increase would be far too big. The overhead of tuple headers is already a problem, and xmin/xmax are a significant portion of that.

There were patches however that kept an 'epoch' (the upper 32bit of a 64bit transaction id) on a page level. Plus some rewrite logic when transactions that are too far away from each other to be represented as an index from a base epoch are about to be present on one page. That'd allow to effectively have 64bit xids.

The in-development zheap storage engine basically does something roughly akin to that, removing the need to perform freezing when a table becomes older than ~2^31 - safety-window transactions.

The transaction id that the system internally has effectively already keeps track of of xids in a 64bit manner, albeit in a somewhat over-complicated manner by keeping track of an epoch separately (there's a patch likely to land in the next version to just go for 64bit there). That's why you can see e.g. txid_current() return 64bit transaction ids.

Re: How long will a 64 bit Transaction-ID last in PostgreSQL?

#12

Post by anarazel »

hamandcheese wrote: post #5
throwawaymath wrote: post #2

The author talks a bit about the architecture of PostgreSQL transactions, touching on lazy transaction ID consumption and vacuuming. Notably, writes require IDs but reads do not. So this is focused on write-optimized workloads. If you want to get the basic tl;dr which answers the headline: these IDs will last so long it’s almost not worth quantifying. This is an obvious calculation even if you assume ostentatatious p…

My admittedly naive understanding of transaction IDs and MVCC is that they can’t be random because transactions are ordered and (depending on your isolation level) that ordering controls what’s visible inside a given transaction. Generally speaking a transaction can’t see any rows with a transaction ID greater than its own transaction ID.

We can't use transaction ids for that however, because they're necessarily assigned by the time a transaction starts to write, whereas visibility is determined by the time transactions commit. Snapshots, the datastructure that determines which transaction's writes ought to be visible and which not, use transaction ids as cutoff values however, to make visibility determinations cheaper.

A second large reasons why we'd not want to go for randomness is that we need to store data for each transaction id, namely whether it committed or not. If we'd assign them randomly we'd need to keep around a lot more of that data, and accesses would be a lot more expensive because there'd basically not be any locality.

Re: How long will a 64 bit Transaction-ID last in PostgreSQL?

#16

Post by ants_a »

Thorrez wrote: post #13

What if we interpret Moore's law to say that transaction speed will double every 2 years?

15 years for 1B/tx/s, 35 years for 1M/tx/s.

Or more generally:

    t_years = doubling_time*(txid_bits - log( initial_tx_rate_per_year * doubling_time / log(2)) / log(2))

Re: How long will a 64 bit Transaction-ID last in PostgreSQL?

#17

Post by KMag »

hamandcheese wrote: post #5
throwawaymath wrote: post #2

The author talks a bit about the architecture of PostgreSQL transactions, touching on lazy transaction ID consumption and vacuuming. Notably, writes require IDs but reads do not. So this is focused on write-optimized workloads. If you want to get the basic tl;dr which answers the headline: these IDs will last so long it’s almost not worth quantifying. This is an obvious calculation even if you assume ostentatatious p…

My admittedly naive understanding of transaction IDs and MVCC is that they can’t be random because transactions are ordered and (depending on your isolation level) that ordering controls what’s visible inside a given transaction. Generally speaking a transaction can’t see any rows with a transaction ID greater than its own transaction ID.

The transaction ordering you mentioned is sufficient if transactions are always retired in the order they begin, but it's not a necessary condition for even the highest transaction isolation levels. However, I don't think anyone has discovered a trick to getting high concurrent performance out of a database that strictly retires transactions in chronological order.

At the highest transaction isolation levels, you need to be able to perform a topological sort of the transaction dependency graph. That does require that the graph is acyclic, but doesn't preclude the DB engine from pretending a transaction that started later actually started earlier (or even had its first write chronologically earlier). For full transaction isolation, the DB engine just needs to be able to pretend transactions happened in a linear order, but that order isn't dictated by the chronological order of the first operation of each transaction. (It's not even constrained by the chronological order of the first write operation of each transaction in DBs that only track write-write conflicts.)

The easiest way to keep track of this consistency is some kind of monotonically increasing counter "timestamp" (or something like a vector of them in an asynchronous / distributed system ... Lamport vector clocks or similar), but this timestamp doesn't need to be identical to a transaction ID, and doesn't have to be unique. It's possible that most databases make unique transaction IDs moonlight as timestamps, but it's not a fundamental constraint.

Re: How long will a 64 bit Transaction-ID last in PostgreSQL?

#18

Post by Aeolun »

anarazel wrote: post #11

There's no chance we go for 64bit transaction ids on the tuples themselves - the space increase would be far too big. The overhead of tuple headers is already a problem, and xmin/xmax are a significant portion of that. There were patches however that kept an 'epoch' (the upper 32bit of a 64bit transaction id) on a page level. Plus some rewrite logic when transactions that are too far away from each other to be repres…

Isn’t 64 bit, even duplicated, completely irrelevant to how much data is generally stored in a row?

Re: How long will a 64 bit Transaction-ID last in PostgreSQL?

#19

Post by Aeolun »

kostaw wrote: post #15

Just imagine running `VACUUM` on that table that wrote 1M rows/seconds for 300 years and now you need to vaccuum quick because the transaction ids will wrap around next year...

Unless I’m missing something, you’d still have some 299700 years for your VACUUM to complete.

Re: How long will a 64 bit Transaction-ID last in PostgreSQL?

#20

Post by devit »

anarazel wrote: post #11

There's no chance we go for 64bit transaction ids on the tuples themselves - the space increase would be far too big. The overhead of tuple headers is already a problem, and xmin/xmax are a significant portion of that. There were patches however that kept an 'epoch' (the upper 32bit of a 64bit transaction id) on a page level. Plus some rewrite logic when transactions that are too far away from each other to be repres…

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.

Post reply on HN