pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWraparound
9+ messages / 4 participants
[nested] [flat]
* Wraparound
@ 2024-02-20 04:32 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Rajesh Kumar @ 2024-02-20 04:32 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi all,
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Wraparound
@ 2024-02-20 07:23 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2024-02-20 07:23 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Wraparound
@ 2024-02-20 07:27 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-02-20 07:27 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Sorry, I could not understand. If possible, please elaborate. Otherwise, no
issues
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.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Wraparound
@ 2024-02-20 07:40 Michael Banck <mbanck@gmx.net>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Michael Banck @ 2024-02-20 07:40 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi,
On Tue, Feb 20, 2024 at 10:02:02AM +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.
This is explained pretty well in the manual in my opinion:
https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
Michael
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Wraparound
@ 2024-02-20 07:42 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Laurenz Albe @ 2024-02-20 07:42 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Wraparound
@ 2024-02-21 03:37 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 9+ messages in thread
From: Rajesh Kumar @ 2024-02-21 03:37 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks, I'll check it out.
On Tue, 20 Feb 2024, 13:12 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Wraparound
@ 2024-02-21 08:25 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 9+ messages in thread
From: Vijaykumar Jain @ 2024-02-21 08:25 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
this might help explain what laurenz tried to put in words.
although this is actual wraparound trigger when db will be blocked for
activity but helps understand the setup, hopefully.
https://tuple-freezing-demo.angusd.com/
On Tue, Feb 20, 2024, 1:12 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> 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
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Wraparound
@ 2024-02-22 04:46 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-02-22 04:46 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
So, is there a way to change to 64bit?
On Wed, 21 Feb 2024, 13:55 Vijaykumar Jain, <vijaykumarjain.github@gmail.com>
wrote:
> this might help explain what laurenz tried to put in words.
> although this is actual wraparound trigger when db will be blocked for
> activity but helps understand the setup, hopefully.
>
> https://tuple-freezing-demo.angusd.com/
>
> On Tue, Feb 20, 2024, 1:12 PM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>
>> 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
>>
>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Wraparound
@ 2024-02-22 06:52 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2024-02-22 06:52 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, 2024-02-22 at 10:16 +0530, Rajesh Kumar wrote:
> So, is there a way to change to 64bit?
https://www.postgresql.org/search/?m=1&q=64-bit+xid&l=1&d=-1&s=r
- it is appealing
- it would break "pg_upgrade"
- it would inflate each table row by 8 bytes
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-02-22 06:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-20 04:32 Wraparound Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-02-20 07:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-20 07:27 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-02-20 07:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-21 03:37 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-02-21 08:25 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2024-02-22 04:46 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-02-22 06:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-20 07:40 ` Michael Banck <mbanck@gmx.net>
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