0035_how_to_use_subtransactions_in_postgres.md

Originally from: tweet, LinkedIn post.

---

How to use subtransactions in Postgres

I post a new PostgreSQL "howto" article every day. Join me in this journey – subscribe, provide feedback, share!

TL;DR

Don't use subtransactions, unless absolutely necessary.

What are subtransactions?

A subtransaction, also known as "nested transaction", is a transaction started by instruction within the scope of an already started transaction (src: Wikipedia). This feature allows users to partially roll back a transaction, which is helpful in many cases: fewer steps need to be repeated to retry the action if some error occurs.

The SQL standard defines two basic instructions describing this mechanism: SAVEPOINT and extension to the ROLLBACK statement – ROLLBACK TO SAVEPOINT. Postgres implements it, allowing slight deviations from the standard syntax – for example, allowing the omission of the word SAVEPOINT in the RELEASE and ROLLBACK statements.

You might be already using subtransactions, for example:

• In Django, using nested "atomic()" blocks.
• Implicitly: using the BEGIN / EXCEPTION WHEN ... / END blocks in PL/pgSQL functions.

How to use (if you really want)

Syntax:

• SAVEPOINT savepoint_name (SAVEPOINT)
• ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name (ROLLBACK TO)
• RELEASE [ SAVEPOINT ] savepoint_name (RELEASE SAVEPOINT)

An example:

Recommendations

The only actual recommendation I have for any project that aims to grow OLTP-like workload (web and mobile apps) is:

wherever possible, avoid subtransactions

...if you don't want this to happen one day:

...or this:

You can find the detailed analysis of four dangers of subtransactions in PostgreSQL Subtransactions Considered Harmful.

As of 2023 / PG16, these issues are not yet solved, although there is some work in progress to implement various optimizations:

• More scalable multixacts buffers and locking
• suboverflowed subtransactions concurrency performance optimize (unfortunately, patch reverted)

As a bottom line:

1. If you can, don't use subtransactions
2. Keep an eye on pgsql-hackers threads related to them and if you can, participate (help test and improve)
3. If absolutely necessary, then study Problem 3: unexpected use of Multixact IDs and:
   • use them in lower-TPS systems only
   • avoid deep nesting
   • be very careful with SELECT ... FOR UPDATE in transactions that include subtransactions
   • monitor the pg_stat_slru numbers ( PG13+, Monitoring stats) to quickly notice and troubleshoot SLRU overflow if it happens