From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Wraparound
Date: Tue, 20 Feb 2024 08:42:46 +0100
Message-ID: <428c4bb48dd7697fe8c68e197ff0d82c219f8dd0.camel@cybertec.at> (raw)
In-Reply-To: <CAJk5AtbKkv0AFfGkm-qS2ekG0bjCeEYe-pyHxorQcwAF-WKSvw@mail.gmail.com>
References: <CAJk5AtZmenJBWQmcru=fe_+Wzp90bpq5B68vHLrjTBkrEuRvhg@mail.gmail.com>
<11927075cfe167d8b64df7e767b595e27fabe3b0.camel@cybertec.at>
<CAJk5AtbKkv0AFfGkm-qS2ekG0bjCeEYe-pyHxorQcwAF-WKSvw@mail.gmail.com>
On Tue, 2024-02-20 at 12:57 +0530, Rajesh Kumar wrote:
> On Tue, 20 Feb 2024, 12:53 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Tue, 2024-02-20 at 10:02 +0530, Rajesh Kumar wrote:
> > > Is autovacuum wraparound issue is when transaction id reaches
> > > autovacuum_freeze_max_age or if transaction Reaches 2^32.
> > >
> > > My autovacuum_freeze_age setting is 200million.
> > >
> > > Db size is 150gb.
> >
> > 2^31 is the magical number at which very old transactions become
> > transactions from the future, which would cause data loss unless
> > all old rows have been frozen.
>
> Sorry, I could not understand. If possible, please elaborate. Otherwise, no issues
The (32-bit) transaction numbers are stored in "xmin" and "xmax" in each row.
You have to imagine these numbers as a closed ring: if you get past 2^32-1,
you wrap around to 0 and continue.
Now the 2^31 numbers before the current 32-bit transaction id are the past,
and the 2^31 numbers after it are the future. This includes wraparound, so
initially numbers close to 2^32 will also be past.
As the current transaction id crosses 2^31, very old transactions suddenly
become future transactions. All rows with a low "xmin" will suddenly appear
to have been created in the future and become invisible. Some rows with a
low "xmax" that have been deleted long ago can suddenly rise from the dead,
since the transaction that deleted them now appears to be in the future.
Perhaps you will find this article entertaining:
https://www.cybertec-postgresql.com/en/transaction-id-wraparound-a-walk-on-the-wild-side/
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, rajeshkumar.dba09@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Wraparound
In-Reply-To: <428c4bb48dd7697fe8c68e197ff0d82c219f8dd0.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox