PostgreSQLLongRunningTransaction¶

What it means¶

A transaction on a database instance has run for more than 5 minutes (300 seconds). This can be a slow query, a large import, or an application that started a transaction and did not end it.

What happens if you do nothing¶

A long running transaction causes problems for the whole database:

• VACUUM cannot clean up rows that were changed after the transaction started. Tables and indexes grow, and the storage fills up faster.
• The transaction holds its locks until it ends. Other queries that need the same rows or tables have to wait. See PostgreSQLBackendsWaiting.

What to do¶

1. Find the oldest open transactions:

   SELECT pid, usename, application_name, state,
          now() - xact_start AS duration, query
   FROM pg_stat_activity
   WHERE xact_start IS NOT NULL
   ORDER BY xact_start
   LIMIT 10;
   

   The query column shows the last query of the transaction.

2. Decide if the transaction is expected. A large import, a migration or a pg_dump can run for a long time. If it is expected, let it finish, and mute the alert until then.

3. If the transaction is not expected, stop it. First try to cancel the running query:

   SELECT pg_cancel_backend(<pid>);
   

   If the transaction is in state idle in transaction, or does not stop, close the connection. This rolls back the transaction:

   SELECT pg_terminate_backend(<pid>);
   

4. Fix the application, so that it ends each transaction with COMMIT or ROLLBACK, and keeps transactions short.

Prevent long transactions¶

You can set time limits for a database user. PostgreSQL then stops queries and idle transactions that run too long:

ALTER ROLE <app_user> SET statement_timeout = '5min';
ALTER ROLE <app_user> SET idle_in_transaction_session_timeout = '10min';

The settings apply to new connections of this user. Choose values that fit your application. Do not set them for users that run long imports or migrations.