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
