Subtransactions, overflow and the performance cliff

Describing PostgreSQL's subtransaction behaviour and the potential performance detriments.

• Within PostgreSQL's transaction control language, subtransactions allow nested transactions capability - transactions within a parent transaction.

• If the number of non-aborted subtransactions (PGPROC_MAX_CACHED_SUBXIDS) within a back end process/ transaction exceeds (64), further subtransaction activity within the process is no longer contained in shared memory, and this ACID information needs to be written/read from disk (pg_subtrans), "we can skip looking at pg_subtrans unless we know the cache has been overflowed").

• A postgres back end transaction exceeding this PGPROC_MAX_CACHED_SUBXIDS limit is called "subtransaction overflow". At the point of overflow, depending on the circumstances and concurrency, there is potential for any query system-wide to have performance affected (not limited to subtransactions), until this transaction has finished (committed/aborted).

• OLTP systems with greater concurrency and more demanding queries can suffer performance issues exponentially in relation to this.

It is highly recommended to change application/ database functionality to avoid overflow, whenever possible, to avoid performance issues. Please see sections of this article for further information:

• "Application alternatives to subtransactions"
• "Database alternatives to subtransactions"

If using subtransactions, monitoring the usage of subtransactions and the status of the subtransaction cache is also recommended. This will prevent being surprised by overflow related performance decreases. Please see section:

• "Options for monitoring subtransactions and whether suboverflow has been hit".

Subtransactions can be created in various different ways:

a) PL/pgSQL functions containing a block with an exception clause - ("also, a block containing an EXCEPTION clause effectively forms a subtransaction")

b) savepoints - "savepoints are implemented using subtransactions".

c) pgJDBC/EDB-JDBC - ?autosave=always property

d) EPAS - when setting edb_stmt_level_tx=on ### EPAS - edb_stmt_level_tx=true, mimicking oracle ACID functionality through use of savepoints.

e.g

CREATE TABLE loop_test (test integer);
CREATE OR REPLACE FUNCTION public.loop_test() RETURNS VOID AS
$$
BEGIN
FOR num IN 1..500 LOOP
BEGIN
INSERT INTO loop_test VALUES (num);
EXCEPTION 
WHEN OTHERS THEN
END;
END LOOP;
END
$$
LANGUAGE plpgsql;
SELECT public.loop_test();

e.g

CREATE TABLE savepoint_test(id integer);
BEGIN;
SAVEPOINT one;
INSERT INTO savepoint_test VALUES(1);
SAVEPOINT two;
INSERT INTO savepoint_test VALUES(2);
commit;

• ROLLBACK TO SAVEPOINT discards the effect of all commands that were run after that savepoint.
• ROLLBACK TO SAVEPOINT can be used to prevent the number of subtransactions ever exceeding 64, preventing overflow, at the cost of losing any commands executed after the savepoint.
• If the number of subtransactions ever exceeds 64, the transaction stays in overflow state, and rolling back to earlier transactions does not remove overflow.
• Please see a demonstration here.

• RELEASE SAVEPOINT does not prevent subtransactions contributing to the PGPROC_MAX_CACHED_SUBXIDS overflow limit and cannot prevent overflow.
• Please see a demonstration here.

• If you are hitting overflow, and using over 64 subtransactions in a transaction, your application mechanisms may be able to operate in a different approach where subtransactions aren't needed.

• For example, as described in Github's related article about subtransactions, they made some code changes to perform certain functions out of subtransactions. This is detailed in their merge requests 1 & 2.

• If:
• You have transactions which include inserts which may fail due to a unique/primary key or exclusion constraint violation
• You do not want insertion errors to cause the whole function to fail and to lose the previous statements:
• ON CONFLICT DO NOTHING can be used instead of savepoints, removing the risk of overflow.

Native functionality for observing whether a system is overflowed is only available in Postgres 16+, using function pg_stat_get_backend_subxact

The following options are available to see whether sessions are overflowed for Postgres 15- in a preprod environment. If you want to set up any of the following methods, please contact EDB support:

Using bpftrace with a custom BPF trace script, allows us to track subtransaction count for all transactions.

Using systemtap with a custom systemtap script, allows us to track subtransaction count for all transactions.

Please see Comparing SystemTap and bpftrace.

• Compiling postgres from source with Dilip Kumar's patch allows us to use the function pg_stat_get_backend_subxact to see whether a transaction has hit subtransaction overflow. It does not state which other transactions' performance are affected.

• Running SELECT pg_export_snapshot(); at the end of a transaction creates temporary data in $PGDATA/pg_snapshot/ until the transaction is finished.

• An external program can pick the data up and copy it to another location before postgres removes it, the data can be examined to see if suboverflow was hit:

• This is not a desired monitoring solution as:

• 1. We have to export the snapshot, requiring code changes and additional performance use
• 2. Postgresql can delete the snapshot data before the program can copy it, and may require a SELECT pg_sleep(1); at the end of the transaction to receive all snapshot data, and delaying a query would not make sense in production.

• The number of SAVEPOINT and EXCEPTION statements can be monitored.
• A large number of statements and decreased performance could hint at the possibility of overflow, and that there should be further investigation.
• However, unless the logging can be grouped by each transaction, it will not directly correlate to overflow.

If the answer is yes, there may be possible solutions to reduce but not solve the performance deterioration, depending on the use case. Please reach out to EDB Support for more information on this.