CYBERTEC PostgreSQL Logo

idle_in_transaction_session_timeout: Terminating idle transactions in PostgreSQL

04.2018 / Category: / Tags: |

When running PostgreSQL on a production system, it might happen that you are facing table bloat. As you might know PostgreSQL has to copy a row on UPDATE to ensure that concurrent transactions can still see the data. At some point VACUUM can clean out dead rows but if transactions are too long, this cleanup might happen quite late and therefore table bloat (= your table keeps growing dramatically) is the logical consequence. Having a table, which has grown out of proportion, will have all kinds of bad side effects including but not limited to bad performance. idle_in_transaction_session_timeout has been added to PostgreSQL 9.6 to prevent bad things from happening in case long idle transactions are around.

MVCC: Why PostgreSQL has to copy rows on UPDATE

Why does PostgreSQL have to copy rows on UPDATE after all? Here is an example:

Session 1Session 2
CREATE TABLE a (aid int); 
INSERT INTO a VALUES (5); 
  
BEGIN; 
SELECT sum(aid) FROM a; 
… running ...UPDATE a SET aid = 9;
… running ...SELECT * FROM a;
… will return 5 ...… will return 9 ...

As you can see two results will be returned at the same time at the end of our example. Logically PostgreSQL has to keep both versions of a row. Just imagine if you want to UPDATE 100 million rows – your table will have to keep an additional 100 million rows.

Let us take a look at a second example:

Session 1Session 2
BEGIN; 
DELETE FROM a; 
… running ...SELECT * FROM a;
… running …… we will see rows ...
COMMIT; 
 VACUUM a;
 … now we can clean out rows ...

DELETE is not allowed to actually remove those rows. Remember, we can still issue a ROLLBACK so we cannot destroy data yet. The same applies to COMMIT. Concurrent transactions might still see the data. VACUUM can only really reclaim those deleted rows if no other transactions can still see them. And this is exactly where our problem starts: What if a transaction starts but is not closed for a long long time …

In PostgreSQL long transactions can cause table bloat

A long transaction is actually not a problem – the problem starts if a long transaction and many small changes have to exist. Remember: The long transaction can cause VACUUM to not clean out your dead rows.

Are long transactions evil in general? No: If a long transaction does useful work, it should be allowed to proceed unharmed. But what is a transaction is kept open because of bad coding or for some other reason?

Here is an example:

Session 1Session 2
BEGIN; 
SELECT … 
… doing nothing ...UPDATE “huge change”
… doing nothing ...DELETE “huge change”
… doing nothing ...INSERT “huge change”
… doing nothing ...UPDATE “huge change”

In this case we will end up in trouble at some doing. VACUUM might actually run but it is never allowed to clean out dead rows because a single transaction might still be allowed to see old data. Thus dead rows will keep accumulating as long as “Session 1” exists. PostgreSQL cannot clean dead tuples – even if you keep running VACUUM.

idle_in_transaction_session_timeout: Killing idle transactions in PostgreSQL

If a transaction is working, it is there for a reason – but if it just hangs around, why not just kill it? This is exactly what idle_in_transaction_session_timeout will do for you. Here is how it works:

In this example the timeout is set to 3 seconds (3000 milliseconds). Then we will sleep for 5 seconds, which is no problem at all. However, before the next SELECT there is a long pause – and this is when the session in question will be killed.

In other words: Transactions cannot stay open accidentally anymore as PostgreSQL will clean things out for you.

Note that you don’t have to set things in postgresql.conf globally. The beauty is that you can actually set this variable for a certain database or simply for a specific user. There is no need for making the change globally and suffering from potential side effects.

Settings things for a single user is actually pretty simple. Here is how it works:

Is there table bloat in my PostgreSQL database?

If you want to figure out if there is table bloat in your database or not: Consider checking out the pgstattuple extension, which has been covered in one of our older posts: /en/detecting-table-bloat/

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

8 responses to “idle_in_transaction_session_timeout: Terminating idle transactions in PostgreSQL”

  1. I believe that example of a long transaction is true only for Repeatable Read (or Serializable) isolation level. But by default BEGIN used Read Commited. So, after SELECT in the first session finished, VACUUM will remove dead rows in a table after subsequent UPDATE, DELETE commands in the session 2.

  2. Want to know what are the side effects of setting idle_in_transaction_session_timeout. Also Will CPU utilization come down after enabling it?

    • This has nothing to do with CPU utilization ("idle" means idle).
      The side effect will be unexpected disconnections if transactions are idle for too long.

      • Thanks. Still wondering what will the advantages, if I enable it altering the system settings because I was thinking to do so, other than releasing idle connections to reuse the connection slots

  3. There are two important question.
    The first one is:
    Lets say we have some role
    CREATE ROLE some_role WITH NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE
    INHERIT NOREPLICATION CONNECTION LIMIT -1;

    And we grant users to that role
    CREATE ROLE some_user WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'xxxxxx';

    GRANT some_role TO some_user;

    Will it make the job to apply the change on the role:
    ALTER ROLE some_role
    SET idle_in_transaction_session_timeout TO 1000;

    The second question is if we have long running processes like pg_dump, index rebuild, so how it will be affected?

    BTW the answer for the first question can be the answer for the second as well...

    • Group inheritance only affects permissions. Parameters set on the role level are not inherited. Long running statements won't be affected, because they are not idle in transaction (unless you create an index in a transaction and then idle without committing the transaction).

        • I thought I had answered that: "long running statements won't be affected".
          To be honest, a long running parallel pg_dump could have one of the sessions idling, so it may be affected. Test it.
          Best practice is to disable all these timeouts for operations like these.

Leave a Reply

Your email address will not be published. Required fields are marked *

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