Following the Vacuum Series, which covered how Vacuum works, this time we discuss what to watch out for when monitoring.
Users who are new to PostgreSQL often know only that Vacuum is important, and assume that simply enabling Autovacuum or running Manual Vacuum through a Job (Cron) is all the treatment they need.
However, the complex and varied mechanisms derived from the MVCC model have a major impact not only on [Auto]vacuum Operation but also on everyday Database operation. In this post, we look at what happens when a Session in the
idle in transaction state meets a Vacuum Operation.PG_STAT_ACTIVITY
PG_STAT_ACTIVITY is one of the most frequently used Views for monitoring PostgreSQL and is essential for Session Level monitoring. Because it includes PID, Query, Status, and Wait information, you can extract a wide range of information from it, either on its own or by joining it with other performance Views.
Among the many columns in PG_STAT_ACTIVITY, we focus on
state, backend_xid, and backend_xmin.state
Current overall state of this backend. Possible values are: • active: The backend is executing a query. • idle: The backend is waiting for a new client command. • idle in transaction: The backend is in a transaction, but is not currently executing a query. • idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error. • fastpath function call: The backend is executing a fast-path function. • disabled: This state is reported if track_activities is disabled in this backend.
The
state column shows the current state of a Session. In monitoring, the active value is used to list active Sessions, and the idle value is used to filter out idle Sessions. The idle in transaction state we cover here means a Transaction is in progress but no Query is running.backend_xid
Top-level transaction identifier of this backend, if any.
backend_xid shows the XID of the Transaction. Only when you explicitly start a Transaction, or run DML with Auto-Commit disabled, is the XID at that point set as the backend_xid value.As shown below, when a Transaction is declared with
BEGIN TRANSACTION, the Session's state changes to idle in transaction, and the XID at the time of the first DML is assigned to backend_xid.backend_xmin
The current backend's xmin horizon.
The official documentation uses the abstract phrase "xmin horizon" to describe
backend_xmin. To understand what it really means, we first look at the various characteristics of backend_xmin. The characteristics of backend_xmin are as follows.- While a Session is running a Query (Active), it always has a
backend_xminvalue, which follows these two rules.
- The lowest (oldest)
backend_xidamong all connected Sessions is used as thebackend_xminvalue.
- If no Session currently has a Transaction in progress, the Current XID is used as the
backend_xminvalue.
Putting these together,
backend_xmin is the oldest existing Transaction ID that a Session refers to when running a Query.📌 backend_xmin also matters in Replication environments. We will cover this in the Replication Series.
How backend_xmin relates to Vacuum
Why does PostgreSQL track the oldest existing Transaction ID through the
backend_xmin column described above?Suppose a Vacuum job is scheduled. Among the Dead Tuples to be processed, some may "possibly" be referenced by a Transaction already started (by another Session). To keep Transactions read-consistent, there must be a method and a reference point to exclude Dead Tuples that may "possibly" be referenced from Vacuum.
In other words, PostgreSQL defines a Cut-Off point that separates Vacuum targets and "postpones" Vacuum for Tuples after that point. It uses the
backend_xmin column as this XID Cut-Off point.Let's confirm this with a simple example. The scenario uses two Sessions and runs Steps 1. - 6. in order, as follows.
ㅤ | Session S1 | Session S2 |
Step 1. | Create table A | ㅤ |
Step 2. | ㅤ | Create table B, then insert data with Auto-commit disabled (Transaction Start) |
Step 3. | Insert data into table A, then run an Update | ㅤ |
Step 4. | Run Aggressive Vacuum on table A (vacuum freeze) and observe the result | ㅤ |
Step 5. | ㅤ | End Auto-commit (Transaction End) |
Step 6. | Repeat Step 4. and observe the result | ㅤ |
Step 1. ~ Step 3.
- In Step 2., disable Auto-Commit and insert data (XID=1914)
- In Step 3., the XID at Insert is 1915 and the XID at Update is 1916 (checked with the pageinspect Extension)
- After Step 3., querying PG_STAT_ACTIVITY from Session S1 shows that Session S2's
stateisidle in transactionand itsbackend_xidis set to 1914, as below
- The
backend_xminof Session S1, which runs the query, is 1914
📌 Using HEAP_PAGE_ITEMS requires installing the pageinspect Extension.
Step 4.
- Force Data Freezing with the
vacuum freeze verbosecommand, then check the Vacuum log
- The log "0 removable, 2 nonremovable" confirms that both the "pre-Update image" (Dead Tuple) and the latest image were excluded from Vacuum
- Checking the 10th bit of
t_infomaskconfirms that the 2 Tuples were both excluded from Data Freezing
💡 Session S1, which ran Vacuum, has a backend_xmin of 1914, so Vacuum has no effect on Tuples of table A inserted or changed after that point (excluded)
Step 5. ~ Step 6.
- After ending the Transaction in Step 5., run the vacuum freeze verbose command again in Session S1
- The log "1 removable, 1 nonremovable" confirms that the "pre-Update image" (Dead Tuple) is now removable
- The remaining "post-Update image" has the 10th bit of t_infomask changed to 1, confirming that Data Freezing is also complete
💡 Because Session S2's Transaction ended normally, Session S1's backend_xmin became equal to the current XID (Current XID = backend_xmin = 1917). As a result, unlike Step 4., Tuples inserted or changed before XID=1917 were vacuumed normally
Test results
The point of this test is not only the relationship between Vacuum and
backend_xmin.In fact, Table A created during the test has nothing to do with the Transaction started by Session S2 or with Table B. Yet the Vacuum job was directly affected.
Put more generally, this leads to a scary conclusion: the mere presence of a Session with an old
backend_xid in the Database affects Vacuum on every Table.Some may mistake this for a normal state, because Vacuum completes quickly and leaves no particular Error log. But if the state lasts long, Table Bloating from unprocessed Dead Tuples will get worse, and the Vacuum targets that kept getting postponed will eventually become a headache you have to deal with.
When "idle in transaction" occurs
Based on the test results, we hope you now agree that Sessions with a
backend_xid, that is, Sessions in the idle in transaction state, need close attention.This state can also occur during perfectly normal Transactions, so you need the judgment to tell it apart from an abnormal state.
In general,
idle in transaction most often comes from the following 3 patterns.- A Transaction was run ad hoc in an SQL Editing Tool and never ended
- An Application developed with Autocommit disabled - ending the Transaction is omitted
- Other cases where an abnormal Client termination leaves a Session in the
idle in transactionstate
Among these, if the environment has been running without problems, rather than one with new workloads or a newly opened system, watch out especially for case 1.
Problem factors need proper control, such as appropriate DML privileges and separating development/test/QA environments.
Monitoring & Prevention
The explanation may have been complex, but the method for monitoring this situation is very simple.
As in the Script below, you only need to list Sessions that changed to the
idle in transaction state and stayed in it for a long time. Of these, the Sessions with a backend_xid value are the ones to watch, and the Application, User Name, and IP information extracted together can help judge whether human error is involved.On the other hand, if monitoring and periodic management are difficult, you can prevent the problem in advance with a Timeout setting.
The
IDLE_IN_TRANSACTION_SESSION_TIMEOUT Parameter checks how long the idle in transaction state has lasted and forcibly terminates the Session when the condition is met.The default is 0ms, which disables the Timeout. If needed, set the value based on the longest Transaction time you expect.
Conclusion
In this post, we looked at the meaning of the
idle in transaction state, backend_xid, and backend_xmin, and how they relate to Vacuum.Like a butterfly effect, a Transaction run by one Session affects the whole Database. This is an extension of the inconvenience and irrationality we have complained about throughout the Vacuum Series.
But once you understand the principle and can extract and interpret the right information, monitoring and prevention are fairly easy. We hope readers of this post make full use of it.
Related articles
