I have strange behavior from an Amazon RDS instance running PostgreSQL 9.6.11. This is new behavior from a system that has otherwise been behaving properly without any changes to code in the last few months. Additionally, we have a staging environment that the latest database and code changes were replicated to and it is not having these problems.
Nightly, thousands of jobs are run against the database server that consist mainly of very large SELECT queries (In the order of 8000 lines or so) that calculate statistics. Queries that typically take up to 1 minute, hang up, and do not complete, even after hours. However, if the server is rebooted to clear the queries, then I can run the same queries manually and they complete normally. The jobs will continue to process normally, and then eventually hang up again on stuck SELECT statements (usually 5 or 6, and it is not the same statements. They all vary slightly.).
I have done a lot of research and troubleshooting, but after determining that the query wasn't the problem, I'm at a loss.
The hung statements are all in
active state and have been processing for hours. The server is pegged at 99%+ load.
What things should I look for? What problems could cause this issue? Why would a combination of large SELECT statements hang up? What commands can I run to get more information?