← Back to context

Comment by andriy_koval

4 hours ago

> Sqlite smokes postgres on the same machine even with domain sockets [1]

for inserts only into singe table with no indexes.

Also, I didn't get why sqlite was allowed to do batching and pgsql was not.

6 comments

andriy_koval

Reply
andersmurphy  3 hours ago

> for inserts only into singe table with

Actually, there are no inserts in this example each transaction in 2 updates with a logical transaction that can be rolled back (savepoint). So in raw terms you are talking 200k updates per second and 600k reads per second (as there's a 75%/25% read/write mix in that example). Also worth keeping in mind updates are slower than inserts.

> no indexes.

The tables have an index on the primary key with a billion rows. More indexes would add write amplification which would affect both databases negatively (likely PG more).

> Also, I didn't get why sqlite was allowed to do batching and pgsql was not.

Interactive transactions [1] are very hard to batch over a network. To get the same effect you'd have to limit PG to a single connection (deafeating the point of MVCC).

- [1] An interactive transaction is a transaction where you intermingle database queries and application logic (running on the application).

  • andriy_koval  2 hours ago

    Thank you for clarification, I was wrong in my prev comment.

    > - [1] An interactive transaction is a transaction where you intermingle database queries and application logic (running on the application).

    could you give specific example why do you think SQlite can do batching and PG not?

    • hedora  1 hour ago

      Not the person you are responding to, but sqlite is single threaded (even in multi process, you get one write transaction at a time).

      So, if you have a network server that does BEGIN TRANSACTION (process 1000 requests) COMMIT (send 1000 acks to clients), with sqlite, your rollback rate from conflicts will be zero.

      For PG with multiple clients, it’ll tend to 100% rollbacks if the transactions can conflict at all.

      You could configure PG to only allow one network connection at a time, and get a similar effect, but then you’re paying for MVCC, and a bunch of other stuff that you don’t need.

      2 replies →

    • andersmurphy  37 minutes ago

      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)

      endTx

      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.