4 ms·

> I cannot imagine any scenario wherein a single-node hosted DB (across the LAN) can outperform a properly-configured SQLite instance (in same process). SQLite

> I cannot imagine any scenario wherein a single-node hosted DB (across the LAN) can outperform a properly-configured SQLite instance (in same process). SQLite only supports one writer at a time, so any use case with more than a handful of concurrent writes will likely be better off with a remote DB which typically can handle thousands of active connections issuing transactions.

bob1029 4y ago
How fast a network are we talking about? SQLite can max out my NVMe drives, despite its internal serialization constraint. Thousands of connections at milliseconds is similar to a few connections at microseconds. Latency is everything.
- tptacek 4y ago
  Another thing people are sleeping on is that you can't have concurrent writes to a single SQLite database, but you can have lots of SQLite databases --- SQLite will even do joins across them.
  - geenat 4y ago
    Had no idea about joining across multiple databases! Hot tip! Yeah, you can drastically increase write throughput by just having extra SQLite databases.. Example pattern: * main.db * sessions.db * analytics.db * cache.db Will 4x your writes. Combined with a modern nvme drive, you begin reaching crazy fast write throughput which you'd require far more traditional database nodes to achieve, but on a single machine.
- gunnarmorling 4y ago
  > SQLite can max out my NVMe drives, despite its internal serialization constraint. Do you have a (write) benchmark which demonstrates that? Is this many competing small transactions (as in a typical web workload)? Or buffering multiple writes into fewer but larger transactions? I'm more positive on the latter, but of course it changes the semantics somewhat. SQLite itself says this [1]: "[SQLIte] will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds." Synchronizing on one lock hold for dozens of ms is a big issue for processing large number of concurrent writes, so I'm curious how you've solved this in your application. [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

bob1029 4y ago

How fast a network are we talking about? SQLite can max out my NVMe drives, despite its internal serialization constraint. Thousands of connections at milliseconds is similar to a few connections at microseconds. Latency is everything.

tptacek 4y ago
Another thing people are sleeping on is that you can't have concurrent writes to a single SQLite database, but you can have lots of SQLite databases --- SQLite will even do joins across them.
- geenat 4y ago
  Had no idea about joining across multiple databases! Hot tip! Yeah, you can drastically increase write throughput by just having extra SQLite databases.. Example pattern: * main.db * sessions.db * analytics.db * cache.db Will 4x your writes. Combined with a modern nvme drive, you begin reaching crazy fast write throughput which you'd require far more traditional database nodes to achieve, but on a single machine.
gunnarmorling 4y ago
> SQLite can max out my NVMe drives, despite its internal serialization constraint. Do you have a (write) benchmark which demonstrates that? Is this many competing small transactions (as in a typical web workload)? Or buffering multiple writes into fewer but larger transactions? I'm more positive on the latter, but of course it changes the semantics somewhat. SQLite itself says this [1]: "[SQLIte] will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds." Synchronizing on one lock hold for dozens of ms is a big issue for processing large number of concurrent writes, so I'm curious how you've solved this in your application. [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html