Re: Long running query causing XID limit breach

On Thu, 2024-05-23 at 13:41 +0530, sud wrote:
> > Yes, that is correct.  You cannot run such long-running queries with a
> > transaction rate like that.
>
> When you mean transaction ,does it mean one commit ? For example if it's
> inserting+committing ~1000 rows in one batch then all the 1000 rows will
> be marked as one XID rather than 1000 different XID. and so we should look
> for batch processing rather than row by row types processing.
> Is the understanding correct?

Yes, that would help.

> > One thing you could consider is running the long-running queries on a standby
> > server.  Replication will get delayed, and you have to keep all the WAL
> > around for the standby to catch up once the query is done, but it should work.
> > You'd set "max_streaming_standby_delay" to -1 on the standby.
>
> We have the "Select query" running on a reader instance , but still the writer
> instance was showing up "MaximumUsedTransactionIDs" reaching 1.5billion, so it
> means both the instance as part of same cluster so sharing same XIDs

If a long running query on the standby influences the primary, that means that
you have "hot_standby_feedback" set to "on". Set it to "off".

Yours,
Laurenz Albe