Conversation
What I don't like with this is that not using subtransactions implies not using stored procedures, which can help to scale 🤔
1
2
You mean they can make it *harder* to scale, right? 😉
Quote Tweet
Replying to @FranckPachot @lukaseder and 4 others
Better to 0.
Prepared Statements don't play well with transaction mode connection pooling.
And they imply server state.
I encourage to use Postgres without requiring server-side (session) state.
1
1
1
1
2
So if you're using subtx, you need to avoid:
- having 65+ of them in a transaction not to hit PGPROC_MAX_CACHED_SUBXIDS, and
- long-running transactions not to hit NUM_SUBTRANS_BUFFERS (XID age 65k)
These walls are hard to see so the conclusion is: "STOP IT", unfortunately
1
2
If you have a lot of TPS, "long-running" can be translated to "a few seconds long", because 65k can fly really fast
1
2
Why 65k:
NUM_SUBTRANS_BUFFERS is 32, each page is 8 KiB, each transaction needs 4 bytes -- this gives 8192/4 = 2048 XIDs per page, overall 2048 * 32 = 65536 transactions -- this defines the age of the long-running transaction that leads to SLRU overflow
1
2
I'm thinking about short procedure calls in OLTP to avoid 10 or 100 client/server roundtrips. Not a whole batch program. Which is still possible with intermediate commits BTW. But I agree 65k XIDs is low. Are there reasons for such a small NUM_SUBTRANS_BUFFERS in current systems?
1
1
I think it's old and should be increased -- some people do it, was discussed a few times in -hackers.
Actually, there is an interesting patch from worth checking, should improve SLRUs a lot: postgresql.org/message-id/fla
1
3
correction: 2 patches. And one of them is to allow configuring the sizes of SLRU buffers.
2
3
The problem with SLRUs is that they're permanent storage for metadata which is impermanent. Why can't "old XID X committed?" call always get the answer "yes", implicitly? IMV we need a second variety of eager VACUUM, just for SLRUs - perhaps only run when a transaction aborts.