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 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 …
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 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.
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:
test=# SET idle_in_transaction_session_timeout TO '3000';
SET
test=# BEGIN;
BEGIN
test=# SELECT pg_sleep(5);
pg_sleep
----------
(1 row)
-- going out for a coffee ...
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:
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/