MIME-Version: 1.0
References: <CAD=mzVXR3GjM0vcthMBwEdbOKqSKcv8oojSS9coczWRi9BRYTA@mail.gmail.com>
 <CAKD7CDk=mB3Z2m9hLK=bX1=KThUwOE+yudmOEaBb6Grqg8HXaQ@mail.gmail.com>
 <CAD=mzVXm2wQD707KrwsQUH=8xE3Zknr8hWuTV_qz2XcTzFzYXg@mail.gmail.com>
 <CAKD7CDn8NpNhrsyqPucjHOxcDEjCuspmhG6Q0ej_wp4wM4jnOQ@mail.gmail.com> <CAD=mzVU8CzBR8v-GcYDFgFjV5nOWMhmPnk8_peXXyjtX__PL1A@mail.gmail.com>
In-Reply-To: <CAD=mzVU8CzBR8v-GcYDFgFjV5nOWMhmPnk8_peXXyjtX__PL1A@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Thu, 23 May 2024 12:49:00 +0530
Message-ID: <CAEzWdqc9gwq0KwqkaZJ9hVH4bhP_RdKPVWsqzA=a8cOh_Njj=g@mail.gmail.com>
Subject: Re: Long running query causing XID limit breach
To: sud <suds1434@gmail.com>
Cc: Muhammad Salahuddin Manzoor <salahuddin.m@bitnine.net>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001b5e9b061919dead"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqc9gwq0KwqkaZJ9hVH4bhP_RdKPVWsqzA%3Da8cOh_Njj%3Dg%40mail.gmail.com>
Precedence: bulk

--0000000000001b5e9b061919dead
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, May 23, 2024 at 11:42=E2=80=AFAM sud <suds1434@gmail.com> wrote:

>
>> Calculation Rationale
>> Daily XID Usage: Approximately 4 billion rows per day implies high XID
>> consumption.
>> Buffer Time: At 1 billion XIDs, you would still have 1 billion XIDs
>> remaining, giving you roughly 12 hours to address the issue if your syst=
em
>> consumes 200 million XIDs per hour.
>>
>>
>>
>
> OP mentioned that initially the number of business transactions is around
500million but the rows inserted across many tables are ~4-5billion in
total per day. So doesn't it mean that the XID consumption will happen
based on the transactions rather on the number of rows basis. Say
for example ~4billion rows may be loaded using a batch size of ~1000 using
bulk load, so that will be ~4million txn so it should use ~4million XID but
not 4 billion XID usage. And thus making the transactions process in
batches rather than row by row minimizes the XID usage. Correct?

--0000000000001b5e9b061919dead
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Thu, May 23, 2024 at 11:42=E2=80=AFAM =
sud &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.com</a>&gt; wr=
ote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D=
"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><div dir=3D"ltr"><div><br>Calculation Rationale<br>Daily XID Usage:=
 Approximately 4 billion rows per day implies high XID consumption.<br>Buff=
er Time: At 1 billion XIDs, you would still have 1 billion XIDs remaining, =
giving you roughly 12 hours to address the issue if your system consumes 20=
0 million XIDs per hour.<br><br><div><div dir=3D"ltr" class=3D"gmail_signat=
ure"><div dir=3D"ltr"><br></div></div></div></div></div></blockquote><div><=
br></div><div><br></div></div></div></blockquote><div>OP mentioned that ini=
tially the number of business transactions is around 500million but the row=
s inserted across many tables are ~4-5billion in total per day. So doesn=
9;t it mean that the XID consumption will happen based on the transactions =
rather on the number of rows basis. Say for=C2=A0example=C2=A0~4billion row=
s may be loaded using a batch size of ~1000 using bulk load, so that will b=
e ~4million txn so it should use ~4million XID but not 4 billion XID usage.=
 And thus making the transactions process in batches rather than row by row=
 minimizes the XID usage. Correct?=C2=A0</div></div></div>

--0000000000001b5e9b061919dead--