An interactive transaction works like this in pseudo code.

beginTx

  // query to get some data (network hop)
  result = exec(query1)
  
  // application code that needs to run in the application
  safeResult = transformAndValidate(result)
  
  // query to write the data (network hop)
  exec(query2, safeResult)
  

How would you batch this in postgres and get any value? You can nest them all in a single transaction. But, because they are interactive transactions that doesn't reduce your number of network hops.

The only thing you can batch in postgres to avoid network hops is bulk inserts/updates.

But, the minute you have interactive transactions you cannot batch and gain anything when there is a network.

Your best bet is to not have an interactive transaction and port all of that application code to a stored procedure.

> How would you batch this in postgres and get any value? You can nest them all in a single transaction. But, because they are interactive transactions that doesn't reduce your number of network hops.

you can write it as stored procedure in your favorite language, or use domain socket where communication happens using through shared memory buffs without network involved.

In your post, I think big performance hit for postgres potentially comes from focus on update only statement, in SQlite updates likely happen in place, while postgress creates separate record on disk for each updated record, or maybe some other internal stuff going on.

Your benchmark is very simplistic, it is hard to tell what would be behavior of SQlite if you switch to inserts for example, or many writers which compete for the same record, or transaction would be longer. Industry built various benchmarks for this, tpc for example.

Also, if you want readers understand your posts better, you can consider using less exotic language in the future. Its hard to read what is and how is batched there.