PostgreSQL Tutorial: Check long running transactions

Summary: In this tutorial, you will learn how to check long running transactions in PostgreSQL.

Table of Contents

Dangers of long running transactions

A long running transaction on a database connection can cause major slowness on very active and frequently vacuumed tables, because vacuum can’t reclaim deleted rows that are newer than the long running transaction.

Any rows updated in a transaction may cause other transactions to wait. Those transactions may also be holding locks, and now they are stalled, which may cause them to block others. This pattern leads to blocking chains and in an OLTP workload, can degrade performance substantially.

The best practice is simple: Hold as few locks as you need for as short of a time as possible.

List long running transactions and slow queries

Poorly written queries or poorly structured data can cause very long execution times on your database. Typically discovered through slow response or extended increases in database CPU, the pg_stat_activity view can help to find out what query is causing issues. The pg_stat_activity view contains details of all currently running queries, including user, connection, and timing details.

Sometimes, applications may either become stuck / died during an open transaction and the database connection was leaked since the process itself did not terminate. That will left a long running transaction which was never committed / rolled back for a long period of time, up until it caused massive slowness when vacuums were proving to be worthless on very active tables.

You can use the following query, to list long running transactions and slow queries in PostgreSQL:

SELECT datname, pid, usename, application_name,
    client_addr, backend_start, xact_start, state_change,
    waiting, query
FROM pg_stat_activity
WHERE ( now() - xact_start ) > '30 minutes'
   OR ( now() - state_change ) > '10 minutes'
ORDER BY xact_start;

Alternatively, you can use the following query, to find the longest running transactions:

SELECT datname, pid, usename,
    state, backend_xmin, xact_start
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Terminate long running transactions

Where some queries look like they’re not going to finish, you can use the pid (process ID) from the pg_stat_activity or pg_locks views to terminate the running process.

• pg_cancel_backend(pid) will attempt to gracefully kill a running query process.
• pg_terminate_backend(pid) will immediately kill the running query process, but potentially have side affects across additional queries running on your database server. The full connection may be reset when running pg_terminate_backend, so other running queries can be affected. Use as a last resort.

To deal with long running transactions in a proactive way, you could:

• Set a large statement_timeout to automatically time out long queries, or
• Set idle_in_transaction_session_timeout to time out sessions which are idle within an open transaction, or
• Set log_min_duration_statement to at least log long running queries so that you can set an alert on them and kill them manually.

See more

PostgreSQL Monitoring