PostgreSQL Subtransactions Considered Harmful

We then mindlessly copy that to our database code -- each mutation function takes a "database object", which could be a direct database connection, or it could be an in-progress transaction. It's generic so that you don't have to care. Functions that think their stuff needs to be a transaction just start one, and if it errors out, hey, it's rolled back.

(Whenever you have a "don't care" type, it means half the functions will be documented "// must be run in a transaction" and the other half will pessimistically create a transaction "just in case" it was invoked with a raw database connection instead of a transaction object.)

Thinking about it more critically, 100% of the times I've wanted to write this, I've wanted to abort the parent transaction as soon as the first child fails. I tend to retry transactions, and doing that twice doesn't make a lot of sense (parent transaction starts, calls a helper function, that starts a transaction, it has a conflict and has to be rolled back, helper function is re-run, that ends up committing, parent transaction fails because of a conflict... and the update gets rolled back anyway).

I basically structure my database APIs to take transaction objects, and make each public API member a transactional unit. Then, the very top level creates and commits the transaction, and can add whatever retry logic it deems necessary.

(Using the classic example, TransferFunds() would be public, and addMoney() and withdrawMoney() would be private. That way, the runner of a transaction can't do "doTx(addMoney); doTx(withdrawMoney)", it would be forced to do "doTx(TransferFunds)". And, all three would take a Transaction object instead of a TransactionOrDatabase object, so the type system enforces the transactional expectations of a money transfer operation.)

Maybe too much 30,000 feet perspective, but in general everything that is "state" in a session is a potential limit to performance and scalability.

In this blog post, it is the "state" associated with subtransactions. In another area, it may be Prepared Statements. Often regarded as better performing than non prepared (and they strictly are, generally), they impose hard limits into connection scalability. Connection poolers cannot hold this state and thus you cannot use transaction pooling, which boosts performance and resource usage much more than prepared vs non-prepared.

Anyway, that's another topic. But session state --is something I'm becoming more and more against. YMMV.

When doing multiple steps that need to track external state, subtransactions can greatly simplify the schema you need as well as make the database interactions more efficient. You need to safely record progress at each step, and you can't afford to lose previous steps because a transaction gets rolled back unexpectedly. You could start a transaction and commit between each step, but this gets pretty annoying if you need to SELECT FOR UPDATE or otherwise make sure what you are doing is concurrency safe, since this would require you to track all the resources you need as you go, which would greatly complicate the design of your non-sql code. Not to mention the added failure modes of not being able to reacquire those resources after step N for whatever reason.

Where things get stupid is ORMs and frameworks that start sub transactions all over the place when you don't need them and aren't even trying to use them. I'm looking at you, Django transaction.atomic(), you absolute idiot.

EDIT :

the very first reference in the above paper A Critique of ANSI isolation levels, which is from 1995 and I have only just started with but looks like important reading, is here https://www.microsoft.com/en-us/research/wp-content/uploads/...

It is quite disheartening that this essentially echoes this quote from the related blog post https://buttondown.email/nelhage/archive/22ab771c-25b4-4cd9-...:

> It is entirely possible to operate Postgres safely and with high performance and throughput at scale … but essentially the only way to do it is to have ready access to deep PostgreSQL experience and expertise on your team; people who Just Know where the landmines are because they’ve seen them before.

And https://blog.nelhage.com/post/some-opinionated-sql-takes/:

> My personal choice: MySQL

> The devil is in the details, but for me, as a default, and despite all these pet peeves, I would start with MySQL.

> As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. In addition, because MySQL is, in my experience, more widely deployed, it’s easier to find and hire engineers with experience deploying and operating it. Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.

Why should you need expert consultation in order to use core features, when other databases "just work" without all these caveats? Transaction ID exhaustion and VACUUM tuning comes to mind here.

I feel that we miss benchmarks and observability tools for the low level locks which are essentially single threaded, if you have lock contention on them the system will grind to a complete halt.

If you can recompile, raising the size of this buffer cache can help.

1. A lot of it is heavily dependent on implementation details. (Yeah we have to care about XID, but we shouldn’t have to care about it exponentially for nested scope workloads once that scope exits.)

2. Actual nested transactions could help alleviate that without too much burden on the MVCC implementation. (This would provide a mechanism for isolating and cleaning up scoped XIDs.)

If the subtransaction is later rolled back, this is starting to sound like the kind of speculative execution information leak that led to the Spectre attack. I wonder if you could base a similar attack on this?