Is there a way to increase DB connection time for an AWS RDS during a large SQL query?

Question

When I first setup my Postgresql database I was able to run a large query that took approximately 5 minutes and 30 seconds to complete. I switched my AWS RDS instance to a lower tier to see what the performance difference would be, but the query was unsuccessful so I switched it back to the original one I had picked. However, the query is still not working even now that the instance is back on the original AWS tier. Looking at the instance in AWS CloudWatch monitoring, it appears as though the DB connection is only staying active for 5 minutes. Is there a way to extend this time?

I'm using DBeaver, AWS RDS, and Postgresql.

Answer 1

You can increase the "tcp_keepalives_ilde" in RDS Parameter Group, set it between 30 - 60 seconds will do. According to Postgres Documentation, Specifies the number of seconds of inactivity after which TCP should send a keepalive message to the client.

Setting statement_timeout in your scenario is NOT advisable according to here. By default the value is 0 and A value of zero (the default) disables the timeout.

In AWS RDS, "tcp_keepalives_idle" is a Dynamic Parameter, which means it will take effect immediately after apply without needing to reboot the instances. The actual wording from AWS Documentation is as follow: "When you change a dynamic parameter and save the DB parameter group, the change is applied immediately regardless of the Apply Immediately setting"

For the benefit of other, can refers to AWS Documentation on create new parameter group, then back to RDS to Modify the RDS Instance to use the newly created parameter group.