Thread: Will using subtransactions will come back to bite me?

Will using subtransactions will come back to bite me?

From

Kannan Goundan

Date:

28 March 2022, 08:49:54

I'm a backend web developer working on a pretty typical Postgres-backed web application. Each HTTP request is handled within a "SERIALIZABLE" transaction.

For some requests, we need to perform a sub-operation (which might fail) and record the success/failure in the DB. Subtransactions offer a simple way to do that -- the overall request is still wrapped in a transaction, and the sub-operation would be wrapped a subtransaction.

But a few things I've read online have made me wary of subtransactions:

1. https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful

2. https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/

3. https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/

But those articles seem to describe a use case that's more demanding than mine. They talk about deeply-nested subtransactions, whereas I will only have 1 (maybe 2) levels of nesting within the top-level transaction.

I'd appreciate any pointers on how to determine whether a particular use of subtransactions will run into the issues described in the linked articles.

(I'm currently using Postgres 13.5 on GCP. Upgrading would require some effort, but I'm open to it!)

Re: Will using subtransactions will come back to bite me?

From

Laurenz Albe

Date:

28 March 2022, 15:31:57

On Mon, 2022-03-28 at 01:49 -0700, Kannan Goundan wrote:
> I'm a backend web developer working on a pretty typical Postgres-backed web application.
> Each HTTP request is handled within a "SERIALIZABLE" transaction.
> 
> For some requests, we need to perform a sub-operation (which might fail) and record the success/failure
> in the DB. Subtransactions offer a simple way to do that -- the overall request is still wrapped in a
> transaction, and the sub-operation would be wrapped a subtransaction.
> 
> But a few things I've read online have made me wary of subtransactions:
> 1. https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
> 2. https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/
> 3. https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/
> 
> But those articles seem to describe a use case that's more demanding than  mine. They talk about
> deeply-nested subtransactions, whereas I will only have 1 (maybe 2) levels of nesting within the top-level
transaction.
> 
> I'd appreciate any pointers on how to determine whether a particular use of subtransactions will
> run into the issues described in the linked articles.
> 
> (I'm currently using Postgres 13.5 on GCP. Upgrading would require some effort, but I'm open to it!)

Typically, if you have only a few subtransactions per transaction, you are fine.

The second article also describes a different problem with replication and subtransactions.
You might want to check your use case if you hit that problem.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com