Re: Long running query causing XID limit breach

On Sat, Jun 8, 2024 at 2:51 PM sud <suds1434(at)gmail(dot)com> wrote:

>
> Thank You so much Laurenz and Yudhi.
>
> Yes its RDS and as you mentioned there does exist a space limitation of
> ~64TB but as Laurenz mentioned the only time the second standby may crash
> would be probably because of the storage space saturation and thus we need
> to have appropriate monitoring in place to find this and get alerted
> beforehand. And also a monitoring to see how much WAL gets generated per
> hour/day to get an idea of the usage. I am not sure how to do it , but will
> check on this.
>

Not exactly related but just for our information, While going through the
"aurora postgres" database docs in regards to similar concepts which are
getting discussed here, I am finding some interesting stuff.

https://aws.amazon.com/blogs/database/manage-long-running-read-queries-on-amazon-aurora-postgresql-compatible-edition/

*Cancel the conflicting query on the reader node if the conflict lasts
longer than max_standby_streaming_delay (maximum 30 seconds). This is
different from Amazon RDS or self-managed PostgreSQL. With Amazon RDS or
self-managed PostgreSQL, the instance has its own physical copy of the
database, and you’re able to set the parameter max_standby_streaming_delay
as high as you want to prevent query cancellation.If the conflicting query
can’t cancel in time, or if multiple long-running queries are causing the
replication lag to go beyond 60 seconds, Aurora restarts the reader node to
ensure it’s not lagging far behind the primary node.*

So if i get it correct it means, even if hot_standby_feedback is set to OFF,
the constraints of max_standby_streaming_delay (30 seconds) and the
60-second replication lag limit applies. And thus Aurora may cancel
long-running queries or restart reader nodes to maintain synchronization
even if it just runs for >60seconds. So it's really odd but does that mean
, by no way you can guarantee a query to run >60 seconds on read replica in
aurora postgres?