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

21–30 of 33 posts

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

#21

Post by anarazel »

Aeolun wrote: post #18
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?

Depends on your data. If you have a link table that just contains two 4 byte integers, then yes, that's pretty significant overhead. Even if you have a few hundred bytes per row, it's still not entirely negligible.

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

#22

Post by anarazel »

devit wrote: post #20
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.

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.

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

#23

Post by js8 »

Thorrez wrote: post #13

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

Let me guess - for this insight, you're asking one upvote for the first square of the chessboard, twice as many upvotes for the next square, and so on..

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

#24

Post by jtc331 »

Aeolun wrote: post #18
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?

Any overhead is generally considered irrelevant when you’re on a small database. For example, going from 50GB in a table to 75GB is 50% overhead, but easily handled in a time where you just pay for more GB on RDS.

That doesn’t hold true when working on multi-terabyte tables or databases when physical disk space in a chassis is actually a boundary you have to consider.

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

#25

Post by DannyB2 »

Thorrez wrote: post #13

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

If transaction speed doubles every 2 years, then just imagine for a sec, if you could also increase the word length by 1 bit every 2 years. So in 2 years you increase the transaction ID from 64 bits to 65 bits -- you've now just DOUBLED the number of transaction IDs available. In 2 years times 64 additional bits, or in 128 years, and I would say long before 128 years, it will begin to seem like a no-brainer to just move to 128 bit transaction IDs, since by that time a simple 'int' will be 256 bits and a short will be 128 bits. Machine instruction sizes will be long enough to encode machine instructions in ASCII making assemblers / disassemblers unnecessary.. The number of bits per pixel will be a steganogropher's delight. We could increase the bit rate of mp3s from 64 kbps to 96 kbps which should please all audiophiles everywhere. Dial up internet speeds should reach 128 kbps by then. Colonies on Mars, but still no male contraceptive.

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

#26

Post by amarshall »

This seems to be (with coincidental timing) the cause of Mandrill’s current outage [1]:

> Mandrill uses a sharded Postgres setup as one of our main datastores. On Sunday, February 3, at 10:30pm EST, 1 of our 5 physical Postgres instances saw a significant spike in writes. The spike in writes triggered a Transaction ID Wraparound issue. When this occurs, database activity is completely halted. The database sets itself in read-only mode until offline maintenance (known as vacuuming) can occur.

> The database is large—running the vacuum process takes a significant amount of time and resources, and there’s no clear way to track progress.

[1] https://news.ycombinator.com/item?id=19084525

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

#27

Post by SomeHacker44 »

devit wrote: post #20
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.

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

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

#28

Post by ldng »

amarshall wrote: post #26

This seems to be (with coincidental timing) the cause of Mandrill’s current outage [1]: > Mandrill uses a sharded Postgres setup as one of our main datastores. On Sunday, February 3, at 10:30pm EST, 1 of our 5 physical Postgres instances saw a significant spike in writes. The spike in writes triggered a Transaction ID Wraparound issue. When this occurs, database activity is completely halted. The database sets itself…

It is probably too late, for later reference, when this talk is up, check it out, it has some answers.

https://fosdem.org/2019/schedule/event/breaking_postgresql_o...

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

#29

Post by aaronbwebber »

I highly recommend using flexible-freeze if you run Postgres in production - does not take very much effort to set up and almost certainly will help you avoid issues with txnid wraparound:

https://github.com/pgexperts/flexible-freeze

It just runs `VACUUM FREEZE` when you schedule it (usually daily), starting with the tables closest to hitting a wraparound.

Post reply on HN