How can I limit database query time during web requests?

Question

We've got a pretty typical django app running on postgresql 9.0. We've recently discovered some db queries that have run for over 4 hours, due to inefficient searches in the admin interface. While we plan to fix these queries, as a safeguard we'd like to artificially constrain database query time to 15 seconds--but only in the context of a web request; batch jobs and celery tasks should not be bounded by this constraint.

How can we do that? Or is it a terrible idea?

Answer 1

The best way to do this would be to set up a role/user that is only used to run the web requests, then set the statement_timeout on that role.

ALTER ROLE role_name SET statement_timeout = 15000

All other roles will use the global setting of statement_timeout (which is disabled in a stock install).