Postgres Pro
Downloads
Home   >   mailing lists

Thread: Transaction Id Space, Freezing and Wraparound

Transaction Id Space, Freezing and Wraparound

From
Martín Fernández
Date:
Hello everyone, second time writing to this awesome mailing list.

I’m helping manage a postgresql 9.2.24 high volume transaction database and yesterday we were literally one hour away of having to deal with transaction id wraparound. We were really lucky about identifying the issue one hour before getting materialized, that gave us some time to reduce the transaction load so that we could run VACUUM FREEZE on our hottest table. By running VACUUM FREEZE on our hottest table we were able to move our oldest txid by almost 1 billion. 

After almost experiencing transaction id wraparound I decided to start investigating deeper around how postgresql works with transactions ids and wraparound in general. 

First thing that generated a lot of noise in my head was the following, if pg assigns contiguous numeric values for the txid, how does pg deal with fragmentation issues ? Then I later found that the txid space is actually circular and not linearly as I originally thought it was. This exposed me to the fact that the txid is actually exported as a 64bit value where the last 32bits are an epoch. My understanding is that the epoch is the component that allows the circularity of the data structure. I then started analyzing how pg decides if a given tuple is eligible for freezing.  I found out that pg will compare a cutoff_tx (I assume this is the last committed tx) with the xmin value of the given tuple, if xmin precedes the cutoff_tx the tuple is eligible (I’m ignoring HEAP_XMAX_IS_MULTI and HEAP_MOVED cases). Now, the xmin of a tuple is an 32 bit integer, so, how is the epoch part of an exported txid considered here ? What if we had a database really old where a txid with integer value 10 is greater than a txid of value 1000 ? 

I’m probably missing something here so bare with me if my previous explanation doesn’t make sense at all.

As usual, thanks before hand, any insight will be appreciated. 

PD: I based my research looking at 9.2.24 code base.

Best,
Martín

Re: Transaction Id Space, Freezing and Wraparound

From
Tom Lane
Date:
=?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= <fmartin91@gmail.com> writes:
> First thing that generated a lot of noise in my head was the following, if pg assigns contiguous numeric values for
thetxid, how does pg deal with fragmentation issues ? Then I later found that the txid space is actually circular and
notlinearly as I originally thought it was. This exposed me to the fact that the txid is actually exported as a 64bit
valuewhere the last 32bits are an epoch. My understanding is that the epoch is the component that allows the
circularityof the data structure. I then started analyzing how pg decides if a given tuple is eligible for freezing.  I
foundout that pg will compare a cutoff_tx (I assume this is the last committed tx) with the xmin value of the given
tuple,if xmin precedes the cutoff_tx the tuple is eligible (I’m ignoring HEAP_XMAX_IS_MULTI and HEAP_MOVED cases). Now,
thexmin of a tuple is an 32 bit integer, so, how is the epoch part of an exported txid considered here ? What if we had
adatabase really old where a txid with integer value 10 is greater than a txid of value 1000 ?  

Actually, XID epoch is an artifact that's bolted on for possible use by
replication or what have you.  So far as the core database is concerned,
XIDs are 32 bits in a circular space, and the way that we deal with your
question is we don't let the case arise.  Every old tuple must be marked
"frozen" before its XID gets to be 2 billion XIDs old; after that, we
don't particularly care just how old it is.  The whole "wraparound"
business just exists to make sure that happens in time.

If the stored XIDs were 64 bits wide, we'd not have to bother with all
of this mess ... but adding another 64 bits to tuple headers would be
a painful space cost, not to mention the storage compatibility issues.

            regards, tom lane

Re: Transaction Id Space, Freezing and Wraparound

From
Martín Fernández
Date:

Martín

On Tue, Nov 20th, 2018 at 6:0 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Martín Fernández <fmartin91@gmail.com> writes:
> First thing that generated a lot of noise in my head was the following, if pg assigns contiguous numeric values for the txid, how does pg deal with fragmentation issues ? Then I later found that the txid space is actually circular and not linearly as I originally thought it was. This exposed me to the fact that the txid is actually exported as a 64bit value where the last 32bits are an epoch. My understanding is that the epoch is the component that allows the circularity of the data structure. I then started analyzing how pg decides if a given tuple is eligible for freezing.  I found out that pg will compare a cutoff_tx (I assume this is the last committed tx) with the xmin value of the given tuple, if xmin precedes the cutoff_tx the tuple is eligible (I’m ignoring HEAP_XMAX_IS_MULTI and HEAP_MOVED cases). Now, the xmin of a tuple is an 32 bit integer, so, how is the epoch part of an exported txid considered here ? What if we had a database really old where a txid with integer value 10 is greater than a txid of value 1000 ? 

Actually, XID epoch is an artifact that's bolted on for possible use by
replication or what have you. So far as the core database is concerned,
XIDs are 32 bits in a circular space, and the way that we deal with your
question is we don't let the case arise. Every old tuple must be marked
"frozen" before its XID gets to be 2 billion XIDs old; after that, we
don't particularly care just how old it is. The whole "wraparound"
business just exists to make sure that happens in time.

If the stored XIDs were 64 bits wide, we'd not have to bother with all
of this mess ... but adding another 64 bits to tuple headers would be
a painful space cost, not to mention the storage compatibility issues.

regards, tom lane

Tom,

Thanks for the insight!!

I got confused with the comment under "Transaction IDs and Snapshots"(https://www.postgresql.org/docs/current/functions-info.html) "The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 billion transactions. However, these functions export a 64-bit format that is extended with an "epoch" counter so it will not wrap around during the life of an installation. "

Re: Transaction Id Space, Freezing and Wraparound

From
Jeremy Schneider
Date:
On 11/20/18 13:00, Tom Lane wrote:
> If the stored XIDs were 64 bits wide, we'd not have to bother with all
> of this mess ... but adding another 64 bits to tuple headers would be
> a painful space cost, not to mention the storage compatibility issues.

People keep saying that. But didn't someone come up with a way to do
this by storing the top 32 bits just once somewhere in the block, rather
than adding 64 bits to every tuple? I can't remember if there was an
email thread.

Maybe some other drawbacks to that approach, but lets at least point out
adding 64 bits to the tuple header isn't the only way to accomplish this.

And the other thread about memory management... if I'm going to start
religious wars, might as well just start them all at once right?  ;)

-J

-- 
http://about.me/jeremy_schneider