alt Hacker News> 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.
> 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.
> 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).