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.
Table of Contents
Why does PostgreSQL have to copy rows on UPDATE after all? Here is an example:
| Session 1 | Session 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 1 | Session 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 …
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 1 | Session 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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
test=# SET idle_in_transaction_session_timeout TO '3000'; SET test=# BEGIN; BEGIN test=# SELECT pg_sleep(5); pg_sleep ---------- (1 row) <span style="color: #800000;">-- going out for a coffee ...</span> test=# SELECT pg_sleep(5); FATAL: terminating connection due to idle-in-transaction timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. |
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:
|
1 2 3 4 |
test=# CREATE USER joe; CREATE ROLE test=# ALTER USER joe SET idle_in_transaction_session_timeout TO 10000; ALTER ROLE |
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: https://www.cybertec-postgresql.com/en/detecting-table-bloat/
+43 (0) 2622 93022-0
office@cybertec.at
There are two important question.
The first one is:
Lets say we have some role
CREATE ROLE some_role WITH NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLEINHERIT NOREPLICATION CONNECTION LIMIT -1;
And we grant users to that role
CREATE ROLE some_user WITHLOGIN
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_roleSET 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).
And what about the second question?
I thought I had answered that: "long running statements won't be affected".
To be honest, a long running parallel
pg_dumpcould 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.
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
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.