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
