[POSTGRESQL] How to reduce database round trip in transactions?

[hot yoke]

When starting a transaction in TypeORM or even other ORMs or query builders, I've noticed that each query is being sent separately to the database though I know this is using the same connection.

"BEGIN;", "... some SELECT, UPDATE, DELETE queries;", "COMMIT;" - these are all sent as individual queries which **adds to API latency **since each takes a round trip to the DB as it's on another remote server.

How can I send **transactions **with multiple statements as one whole query to the database, in a single round trip? Or is there some other type-safe query builder/ORM that supports this? For context, (1) I do not need to execute some custom JavaScript logic/code between each query. (2) Calling stored procedures is not an option with my specific use case (can't disclose some other details). (3) CTEs (WITH queries) are also not an option since I need the queries to run sequentially. PG documentation states that WITH queries are ran concurrently for those that include data modifying statements.

[stray cape]

Hi 👋

I'm not an expert on orm libraries but I'd say that none of them would support that because of (1) in your original message.

It would make the library jump through hoops to support a, I suppose, small use case.

Granted, that would indeed make your interactions with the database much faster, but it would also come with a possible world of headaches to maintain.

If you really want to do it, I suppose you could use a Proxy or something like that and accumulate the queries happening within the same tick of the event loop to then route them to the proper call sites.

But I'd say that's something up to you

[hot yoke]

@stray cape thanks for your response, appreciate it. May I just clarify why it won’t work because of (1)? I think I said there that I do not need to execute JS code in between the transactional queries. 😄 I just want all the transactional queries be sent in one round trip.

[stray cape]

The problem, in my opinion, is that from an orm maintainer's point of view it's very hard to know how your API is going to be used.

So you aim for known and battle tested use cases and try to optimize for those instead of opening doors for people to fall into.

I'd say this use case is something you should not support as an ORM maintainer because it would be super tricky to deal with and is very business related. And that's none of your problem

[jade light]

Prisma can actually do this, but you need to structure your code in such a way that it works (i.e. accumulate all the queries and them pass them to prisma.transaction().
However, this approah won't work in case the querues depend on the result of the previous one (which is like, the majority of cases), so you'll still end up sending one query after each one.

Do you actually experience degraded performance, or are you only trying to prematurely optimize something that will probably never be a problem in practise?

[hot yoke]

I've tried the Prisma playground and it seems similar to what I've experienced in TypeORM, MikroORM, and Drizzle ORM using PostgreSQL. Transactions always send incur one network round trip per query. Kindly see screenshot.

[hot yoke]

I experience degraded performance since my database is hosted on a different network from my server. There is really increased latency from the round trips.

I did ran some benchmarks in TypeORM transactions using MySQL (which allows raw multiple statements). There is a x2-2.5 reduction in my latency when using a raw query transaction (with multiple statements), compared to the library specific transaction methods.

But this time, I am planning to use PostgreSQL.

My use case is a transaction such as:

select
update
delete

^ they don't depend on each other. Just need to run them as a transaction in just one round trip