Tackling query timeouts and cancellations in Postgres

When working with Postgres you will eventually need to cancel some queries to prevent queries from hogging too many resources when they run longer than you would want or expect.

Basic timeouts

If you want to limit how long any query can run for you can use the statement_timeout setting. If a query runs for longer than the value of this setting the query will fail. You can update this setting by running:

SET statement_timeout = '60s'

This setting affects any query ran on the same connection.

If you want to have different timeout values for different queries you need to change the setting before running each query, you then also have to make sure to update the setting before running other queries.

For example:

-- Set timeout for the next query
SET statemenet_timeout = '5s'; 

-- Run the query
SELECT * FROM users; 

-- Set the timeout back to a value that works for most queries
SET statement_timeout = '60s'; 

Or you can run each query in a transaction and make use of SET LOCAL to change the setting only for the current transaction:

BEGIN;
SET LOCAL statement_timeout = '5s';

-- Each of these queries will have a timeout of 5s
SELECT * FROM users;
SELECT * FROM accounts;
COMMIT;

-- statement_timeout goes back to the value it was before the transaction

You can also change the setting multiple times in one transaction:

BEGIN;
SET LOCAL statement_timeout = '5s';
-- This query has 5s timeout
SELECT * FROM users; 

-- This query has 10s timeout
SET LOCAL statement_timeout = '10s';
SELECT * FROM accounts; 
COMMIT;

-- statement_timeout goes back to the value it was before the transaction

Since Postgres 17 there is a transaction_timeout setting available as well, you can use this to set a timeout for an entire transaction and hence use it to set a timeout for a group of queries like so:

BEGIN;
SET LOCAL transaction_timeout = '5s'
-- This entire transaction has a timeout of 5s

SELECT * FROM users;
SELECT * FROM accounts;
COMMIT;

-- transaction_timeout goes back to the balue it was before the transaction

statement_timeout and transaction_timeout are best used to set a global limit for a maximum duration of a query or a transaction to stop too long queries from hogging too many resources, holding locks for too long etc...

Custom query cancellations

One big disadvantage of the approach outlined above is that you need to know after how long you want to cancel a query before you run it. What we want to be able to do is run a query and while the query is running have the option to cancel it at any time or let it finish.

We can cancel any running query using the pg_cancel_backend function. It takes PID of a process as an argument so in order to cancel a particular query we need to know which PID the query is running on.

A manual way to do this is to look in the pg_stat_activity view and search for the connection that is running the query you want to cancel.

If we want to do this programmatically we need to get the PID of the connection using the pg_backend_pid function before running the query/queries we might want to cancel.

Full example in SQL:

-- On the connection running a query we want to cancel

-- Get the current PID, save it somewhere in our application code 
-- You need to do this before running the actual query
SELECT pg_backend_pid(); 

-- Run our query
SELECT * FROM users;


-- On a different connection
-- When we decide to cancel the first query
SELECT pg_cancel_backend($1) -- $1 is the PID of the first connection

A full example in Typescript, using the pg library:

import { Pool } from "pg"; 

// Create a connection pool
const pool = new Pool();
   
// Get a connection from the pool to run our queries   
const dataConnection = await pool.connect();   

// Get the PID of this connection   
const { pg_backend_pid: dataConnectionPID } = ( 
    await dataConnection.query("SELECT pg_backend_pid()")   
).rows[0]; 


// Create a connection for cancelling queries   
const controlConnection = await pool.connect(); 
  
// Run our query   
const data = await dataConnection.query("SELECT * FROM users"); 


// Elsewhere in our application when we want to cancel the running query   
controlConnection.query("SELECT pg_cancel_backend($1)", [dataConnectionPID]);

// dataConnection will receive error: canceling statement due to user request  

Thanks for reading, I hope you found this informative!