People might be fans of SQL, people might like PostgreSQL on Kubernetes or people might even love database ORMs. But have you ever heard of a single person who loves slow queries? In 20+ years of professional database engineering, I have not seen a single fan club paying respect to slow queries, bad SQL and a horrible user experience. So let's terminate long running queries and simply remove them from our system.
Table of Contents
A lot has been written about how to identify and fix slow queries, and I simple want to refer to some of those posts to help people along:
However, in this blog we want to focus on how to actually kill a slow query once it has been found in the system.
Basically, there are two ways to get rid of a slow query:
To ensure we have a slow query which we can eliminate, we first need to start one:
|
1 |
test=# SELECT pg_sleep(1000000); |
This query will simply sleep and not terminate in the foreseeable future. So how can we find and kill it?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
test=# x Expanded display is on. test=# SELECT * FROM pg_stat_activity WHERE datname IS NOT NULL AND state = 'active' ORDER BY query_start LIMIT 1; -[ RECORD 1 ]----+------------------------------ datid | 16384 datname | test pid | 1207 leader_pid | usesysid | 10 usename | hs application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2023-08-10 15:24:13.980079+02 xact_start | 2023-08-10 15:24:20.443691+02 query_start | 2023-08-10 15:24:20.443691+02 state_change | 2023-08-10 15:24:20.443697+02 wait_event_type | Timeout wait_event | PgSleep state | active backend_xid | backend_xmin | 899 query_id | query | SELECT pg_sleep(1000000); backend_type | client backend |
PostgreSQL successfully returned the query which ran for the longest time on my system. What is important here: When querying pg_stat_activity, you need to make sure that only look for rows that actually have a valid database as we would otherwise end up with system processes.
So: How can you eliminate this query? The important information we want to retrieve from pg_stat_activity is the process ID (pid) of the database backend executing what we want to terminate. In my case this PID = 1207.
pg_cancel_backend: Terminate the query and keep the connection alivepg_terminate_backend: Terminate the database connection including the queryLet's try the first function:
|
1 2 3 4 5 |
test=# SELECT pg_cancel_backend(1207); pg_cancel_backend ------------------- t (1 row) |
Executing this in a second connection will ensure that PostgreSQL kills the long running query, which will cause the following output in the first connection:
|
1 2 |
test=# SELECT pg_sleep(1000000); ERROR: canceling statement due to user request |
Sometimes queries will not stop immediately. This is fine, and patience is certainly advised.
However, sometimes we want to kill the entire backend. We do that by using the second function:
|
1 2 3 4 5 |
test=# SELECT pg_terminate_backend(1207); pg_terminate_backend ---------------------- t (1 row) |
With both methods, our problematic query will stop consuming valuable resources. The first method is less invasive, since it does not terminate the database session.
While it is easy to fix a single case, it's also important to keep an eye on the overall picture and make sure that bad SQL gets cleaned up automatically.
statement_timeout to automatically remove slow queriesOne way to do that is to make PostgreSQL remove bad queries for you. Use the statement_timeout variable. Here's how it works:
|
1 2 3 4 |
test=# SET statement_timeout TO 1000; SET test=# SELECT pg_sleep(1000000); ERROR: canceling statement due to statement timeout |
PostgreSQL terminated the query after 1 second.
What is important here: We can also set a reasonably good value in postgresql.conf to ensure that the setting is in effect in all database sessions of the PostgreSQL instance.
Long running queries have all kinds of implications which have to be kept in mind. It is not only about resources and performance, but also about making sure that table bloat is kept under control at all times. Check out the out post about Autovacuum, UPDATE and a lot more to learn more about this vital topic.
Sometimes a statement keeps running even though you tried to interrupt it with the methods shown in this article. Then you might want to read our article on how to cancel a statement that refuses to die.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
I have a question regarding the "pg_terminate_backend" function. You mentioned that it terminates the entire database connection, including the query. Are there any situations where it's more advantageous to use this method over "pg_cancel_backend," which only terminates the query but keeps the connection alive? I'd appreciate some insights on when to choose one over the other.
pg_cancel_backend()cancels the running query (makes it fail with an error), but the database session is preserved. It is less invasive thanpg_terminate_backend(). On the other hand,pg_cancel_backend()is useless if there is no statement executing in the database session.These functions do different things, and you have to choose which is the appropriate one for your use case.