2 ms·
How often would a runaway query be able to run for days without the connection being dropped? I would expect either the incoming client request or the backgroun
by piaste 5y ago
How often would a runaway query be able to run for days without the connection being dropped? I would expect either the incoming client request or the background job that started the query to have their own timeouts, and to dispose of their connections/transactions when killed. If a request or job does last for hours, it's probably easier to notice compared to a query, due to generally better observability tools.
The only time I've seen a running query lasting for days in prod was when a human database toucher had forgot their DBeaver tabs open after running a complex query with too few filters.
- uhoh-itsmaciek 5y agoDue to some Postgres limitations, a query can run even after the connection drops (the backend process only checks if there's still a client to send results to after query completion). I think I saw some work being done on this in 14 or the upcoming 15 (i.e., to re-check periodically during query processing), but a lot of Postgres versions are still affected. I worked on the Heroku Data team and we regularly saw queries running for days. I think starting with an aggressive timeout to fail fast where you can't afford a slow query anyway, and then make exceptions where it makes sense, is easier to work with than tracking down problem cases and putting in limits after they cause issues.