PostgreSQL Tutorial: Check uses of subtransactions

Summary: In this tutorial, you will learn how to check uses of subtransactions in PostgreSQL.

Table of Contents

Risks of too many subtransactions

If there are too many subtransactions in PostgreSQL, it may cause some risk or problems:

• PostgreSQL Subtransaction Problem: Transaction ID Growth

• PostgreSQL Subtransaction Problem: Subtransaction cache overflow

It is recommended to learn if subtransactions are used in your systems. If they are, it does not mean that they need to be eliminated immediately – it all depends on the risks of your particular case.

Query the pg_stat_slru view

pg_stat_slru is related to the inspection of SLRU (simple least-recently-used) caches. SLRU is a special buffer cache where non-user data is stored, such as the state of each transaction, as well as information about subtransactions. Asynchronous notifications also use SLRU.

From PostgreSQL 13 on, you can look at the monitoring view pg_stat_slru and check if blks_read in the row with name = 'Subtrans' keeps growing. That indicates that PostgreSQL has to read disk pages because it needs to access subtransactions that are no longer cached.

You can use the following query, to check uses of subtransactions in PostgreSQL:

SELECT blks_hit, blks_read, blks_written,
    flushes, truncates
FROM pg_stat_slru
WHERE name = 'Subtrans';

Call the pg_stat_get_backend_subxact function

pg_stat_get_backend_subxact() is a system function returning information about the subtransactions of the backend with the specified backend ID.

From PostgreSQL 16 on, you can call the function pg_stat_get_backend_subxact(integer) to return the number of subtransactions for a backend process and whether the subtransaction cache overflowed or not.

Here’s an usage example for pg_stat_get_backend_subxact(), querying the backend PID, subtransaction information, and the most recent query for any backends with a non-zero subtransaction count:

SELECT pg_stat_get_backend_pid(bid) AS pid,
       s.subxact_count,
       s.subxact_overflow,
       pg_stat_get_backend_activity(bid) AS query
FROM pg_stat_get_backend_idset() AS bid
    JOIN LATERAL pg_stat_get_backend_subxact(bid) AS s ON TRUE
WHERE s.subxact_count > 0;

Watch the wait event SubtransSLRU

Typically, during periods of heavy load on the pg_subtrans SLRU subsystem, the SubtransSLRU and SubtransBuffer wait events in PostgreSQL will arise. The SLRU (Simple Least Recently Used) cache is a mechanism backed by disk pages that stores various transaction-related information crucial for PostgreSQL’s operation.

Note: The corresponding wait event in PostgreSQL 12 and earlier versions is called SubtransControlLock. SubtransControlLock was renamed to SubtransSLRU in PostgreSQL 13.

When checking the visibility of a subtransaction in a row, PostgreSQL first retrieves its top transaction ID, necessitating access to the SLRU cache, a simple LRU cache backed by disk storage. As the number of overflowed snapshots increases, concurrent backends contend for access to the SLRU cache, leading to the SubtransSLRU lock and associated wait events. Moreover, in the presence of long-running transactions, PostgreSQL needs to fetch parent information for older subtransactions along with the latest subtransaction. This broadens the scope of the xid lookup, further straining the SLRU cache and resulting in frequent disk page loads and disk I/O operations, as indicated by the SubtransBuffer wait event.

You can use the following query, to watch the wait events in PostgreSQL:

SELECT wait_event_type, wait_event
FROM pg_stat_activity
WHERE pid != pg_backend_pid();

See more

PostgreSQL Monitoring