CYBERTEC Logo

Kill long running queries in PostgreSQL

10.2023 / Category: , / Tags:

How to terminate long running queries in PostgreSQL:

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.

Identifying slow queries and bad performance in PostgreSQL

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.

Killing slow queries

Basically, there are two ways to get rid of a slow query:

  • Terminate the query but keep the database connection alive
  • Kill the entire database connection

To ensure we have a slow query which we can eliminate, we first need to start one:

Artificially create a long running query

This query will simply sleep and not terminate in the foreseeable future. So how can we find and kill it?

First, let's find the long running query:

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.

PostgreSQL offers two functions to kill slow queries:

  • pg_cancel_backend: Terminate the query and keep the connection alive
  • pg_terminate_backend: Terminate the database connection including the query

Let's try the first function:

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:

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:

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.

Preventing bad SQL in the future

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.

Use statement_timeout to automatically remove slow queries

One way to do that is to make PostgreSQL remove bad queries for you. Use the statement_timeout variable. Here's how it works:

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.

Finally …

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.

0 0 votes
Article Rating
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
John Smith
8 months ago

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.

laurenz
laurenz
8 months ago
Reply to  John Smith

pg_cancel_backend() cancels the running query (makes it fail with an error), but the database session is preserved. It is less invasive than pg_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.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram