postgresql,

How to terminate idle transactions in PostgreSQL

Aug 04, 2021 · 1 min read · Post a comment

As part of the PostgreSQL series, in today’s tutorial, we are going to see on how to set idle_in_transaction_session_timeout parameter and handle idle transactions, so you won’t face any table bloats.

Prerequisites

  • PostgreSQL

Solution

The default value for idle_in_transaction_session_timeout is 0, which means disabled.

Step 1. First things first, get the idle_in_transaction_session_timeout parameter value.

SHOW idle_in_transaction_session_timeout;

Output:

 idle_in_transaction_session_timeout 
-------------------------------------
 0
(1 row)

Step 2. Uncomment and set idle_in_transaction_session_timeout parameter value globally in the /var/lib/postgresql/data/postgresql.conf file. Let’s set the value to 10 seconds, for instance:

idle_in_transaction_session_timeout = 10000        # in milliseconds, 0 is disabled

Or, if you are running PostgreSQL on a managed service, for example Azure, you can set the parameter using the following command:

ALTER system SET idle_in_transaction_session_timeout='10000';

Note(s): The changes will apply globally, and I don’t recommend it to be honest.

Update idle_in_transaction_session_timeout for a specific database

psql -U <username> -d <database> -W
SET idle_in_transaction_session_timeout TO '5000';

Update idle_in_transaction_session_timeout for a specific user

ALTER USER devcoops SET idle_in_transaction_session_timeout TO '5000';

Conclusion

My 2 cents is to investigate what causes the connections to be in an idle state instead of calling it a day with a single update in the postgresql.conf file. Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.