5 ms·

Definitely a fun article. This was the key takeaway for me. SubtransControlLock indicates that the query is waiting for PostgreSQL to load subtransaction

Definitely a fun article. This was the key takeaway for me. SubtransControlLock indicates that the query is waiting for PostgreSQL to load subtransaction data from disk into shared memory. I felt the article fell down for two reasons: (1) It didn't really articulate the need for transactions in the first place (database integrity). Nor did it discuss the implications on integrity with this change. (2) It didn't articulate the possibilities of other architectures (pushing to a read cache other than PostgresSQL like Cassandra). I got the feeling they were really pushing PostgresSQL to its limits in their cluster with their load - and it was time to consider another design.

CaveTech 5y ago
1) It’s an article talking about optimizing transactions, I think it’s fair to assume prior knowledge about what a transaction is. The author is not responsible for rebuilding the world. 2) They have a very specific, narrow band problem which they solved two ways. What you’re suggesting is several orders of magnitude more work and would likely take years.
jpgvm 5y ago
I don't think there was sufficient room in the article to cover the value of sub-transactions, let alone transactions as a whole. In this case I think it's fair for it to be assumed knowledge given the contents of the article. They are definitely not pushing the limits of PostgreSQL as a whole, they just hit an edge case with a not heavily used feature. Sub-transactions aren't overly common in most PostgreSQL workloads I have encountered, especially not coupled with long-running transactions. Great write-up though! I do hope patches to make the sub-transaction cache configurable land, while it's not something I have ran into it would be a great lever to have if I do.
azundo 5y ago
Seems like they listened to your feedback, the article now opens with a discussion of (1).
- tomnipotent 5y ago
  But it doesn't. It says they had sub-transactions, not what caused them. Nothing in the article gives concrete details on specific code or logic that was contributing to the issue. The entire "Why would you use a SAVEPOINT?" should have been replaced with "Why we were using SAVEPOINT". Personally, I was hoping for the post to dig into a specific example and explain why transactions were used in the first place (justification) or why removing transactions or sub-transactions had no negative impact on data integrity.
naasking 5y ago
I don't know what gave you the impression they should consider another design. They hit a buggy corner case that had straightforward workarounds. Suggesting this implies a need to rearchitect their system seems like a drastic overreaction.

CaveTech 5y ago

1) It’s an article talking about optimizing transactions, I think it’s fair to assume prior knowledge about what a transaction is. The author is not responsible for rebuilding the world. 2) They have a very specific, narrow band problem which they solved two ways. What you’re suggesting is several orders of magnitude more work and would likely take years.

jpgvm 5y ago

I don't think there was sufficient room in the article to cover the value of sub-transactions, let alone transactions as a whole. In this case I think it's fair for it to be assumed knowledge given the contents of the article. They are definitely not pushing the limits of PostgreSQL as a whole, they just hit an edge case with a not heavily used feature. Sub-transactions aren't overly common in most PostgreSQL workloads I have encountered, especially not coupled with long-running transactions. Great write-up though! I do hope patches to make the sub-transaction cache configurable land, while it's not something I have ran into it would be a great lever to have if I do.

azundo 5y ago

Seems like they listened to your feedback, the article now opens with a discussion of (1).

tomnipotent 5y ago
But it doesn't. It says they had sub-transactions, not what caused them. Nothing in the article gives concrete details on specific code or logic that was contributing to the issue. The entire "Why would you use a SAVEPOINT?" should have been replaced with "Why we were using SAVEPOINT". Personally, I was hoping for the post to dig into a specific example and explain why transactions were used in the first place (justification) or why removing transactions or sub-transactions had no negative impact on data integrity.

naasking 5y ago

I don't know what gave you the impression they should consider another design. They hit a buggy corner case that had straightforward workarounds. Suggesting this implies a need to rearchitect their system seems like a drastic overreaction.