Hello,
I hope this email finds you all well!
I have a data warehouse with a fairly complex ETL process that has been running for years now across PG 9.6, 11.2 and now 13.4 for the past couple of months. I have been getting the error “An I/O error occurred while sending to the backend” quite often under load in 13.4 which I never used to get on 11.2. I have applied some tricks, particularly with the socketTimeout JDBC configuration.
So my first question is whether anyone has any idea why this is happening? My hardware and general PG configuration have not changed between 11.2 and 13.4 and I NEVER experienced this on 11.2 in about 2y of production.
Second, I have one stored procedure that takes a very long time to run (40mn more or less), so obviously, I’d need to set socketTimeout to something like 1h in order to call it and not timeout. That doesn’t seem reasonable?
I understand that there is not just Postgres 13.4, but also the JDBC driver. I ran production for a several days on V42.2.19 (which had run with PG11.2 fine) to try and got the error a couple of times, i.e., the same as with 42.2.24, so I am not sure this has to do with the JDBC Driver.
So I am not sure what to do now. I do not know if there are some related configuration options since 11.2 that could trigger this issue that I missed, or some other phenomenon going on. I have always had a few “long running” queries in the system (i.e., > 20mn) and never experienced this on 11.2, and experiencing this maybe once or twice a week on 13.4, seemingly randomly. So sometimes, the queries run fine, and others, they time out. Weird.
Thanks,
Laurent.