How can I set max_standby_archive_delay setting in RDS?

Hello larryq,

I understand that you are getting the following error "ERROR: canceling statement due to conflict with recovery" and in order to mitigate that you would like to set the paraameter “max_standby_streaming_delay” and “max_standby_streaming_delay”.

In order to change the value of the concerned parameters in RDS, you will require to change the same in the parameter group associated to the instance. If you are using default parameter group then you will require to create a custom parameter group and modify the values as per required for the parameters “max_standby_streaming_delay” and “max_standby_streaming_delay”.

To create a custom parameter group, you may consider to refer the document:
[+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Creating

Further please note, once you create the parameter group and attach it to the instance, you will require reboot in order for the parameter to be effective.

For more information, please find the reference link here:
[+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.DBInstance.Modifying.html

Moving ahead I would like few more suggestions which you might consider for the issue that you are facing:

    (1) Set the parameter "hot_standby_feedback" to 1 at replica.  
            -       This makes the primary aware about the dependent( contention) queries running at replica.   
            -       This also prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur. If this is applied, it will delay cleanup of dead rows on the primary, which may result in undesirable table bloat. However, the cleanup situation will be no worse than if the standby queries were running directly on the primary server, and you are still getting the benefit of off-loading execution onto the standby.  

However, if you want to set the "max_standby_archive_delay" and “max_standby_streaming_delay” to a higher value, you may consider, but then again if the query is implemented more than the delay, then it will error out. If you know the query time then you may consider to set it accordingly.

    (2) Update the "max_standby_archive_delay" and "max_standby_streaming_delay" to value ('T') accordingly at replica.  
            -       This would allow the queries at replica to run for longer duration (but still within the limit of 'T' beyond which it gets conflicted).  
            -       But setting 'T' to a way too high value may lead to replica lag.   
                      

You may also consider the following parameter:

    (3) Update the "vacuum_defer_cleanup_age" at the primary.    

            -       This prevents dead rows to be cleaned up as quickly as they normally would be, which in-turn allows more time for queries to execute before they are canceled on the standby, without having to set a high max_standby_streaming_delay. However it is difficult to guarantee any specific execution-time window with this approach, since vacuum_defer_cleanup_age is measured in transactions executed on the primary server.    
             

Please note, all the parameters mentioned above are dynamic and if the custom parameter is already associated to it, then the dynamic parameter doesn’t require a reboot whereas static parameter requires a reboot.

That being said, we strongly recommend you to test the same in the staging environment before moving the same to production.

Please let us know if you have any queries regarding the same.

Thank you and Have a great day.