PostgreSQL Subtransactions Considered Harmful

Lame title, but a really good, in-depth examination of the lurking issues with subtransactions on postgres.

This is a deep and excellent post. Thanks for publishing it and all the research required.

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.

Apparently Django uses subtransactions by default when you use atomic=True which can result in scary behavior

I agree with this. I think we get baited into using subtransactions by how we structure our code. Each function feels like a transaction -- it gets its own local variables, and if it fails, it doesn't have any effect on the rest of the program. (Not strictly true, of course, I'm sure some failing functions modify global state, or their receiver.)

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.)

I've seen plenty over the years as a professional developer of 20+ years and they are opinion pieces for the most part. I'd rather see a title along the lines of "Why you should avoid subtransactions".

Drinking bleach is harmful.