3 ms·

I think people overstate this. Yes, the sqlite concurrency model is a bad choice if you have a high degree of concurrent writes. However for many applications

I think people overstate this. Yes, the sqlite concurrency model is a bad choice if you have a high degree of concurrent writes. However for many applications that simply isn't true. When it comes to websites i think people significantly overestimate the amount of concurrent writes.

kevingadd 2y ago
Depending on the amount of write throughput you need and whether you care about latency, "concurrent writes" aren't necessarily a problem either. You can just shove them into a queue and then have a single thread pull stuff out of the queue and push it into SQLite. That still scales, up to a point.
- dinosaurdynasty 2y ago
  FYI this is basically what Redis does (which does not support concurrent writes or reads afaik), and that still scales quite a lot.
- immibis 2y ago
  No, because your readers are still blocked by writers and still randomly fail if you forgot to set busy_timeout, which is something at least one person didn't they had to do until they read this comment.
  - emseetech 2y ago
    | readers are still blocked by writers We're all using WAL mode these days, right?
gwervc 2y ago
No, it's indeed a very real problem. I ran into with a very small service.
- liontwist 2y ago
  You want to configure it so it has a timeout. Take turns. That’s how locks work. The only difference between SQLite and Postgres write locking is the granularity.
  - miunau 2y ago
    It's not a trivial difference like you suggest.
    - liontwist 2y ago
      I agree It’s not a trivial difference in implementation and the concurrent write performance is probably a lot worse. But it’s talked about as if it’s a categorical limitation, the app will fail if there is concurrency. But it’s just a question of how much time will be spent locking. A website with a 16 process pool for handling requests will be fine.
  - immibis 2y ago
    sqlite also polls for lock availability
ComputerGuru 2y ago
This is a baseline benchmark I published of various p9x latencies for multiple readers/writers with a single SQLite database with different configurations set: https://github.com/mqudsi/sqlite-readers-writers https://github.com/mqudsi/sqlite-readers-writers Even if you don’t use message passing to use one thread to perform all updates/writes, it still performs very solidly for many use cases.

kevingadd 2y ago

Depending on the amount of write throughput you need and whether you care about latency, "concurrent writes" aren't necessarily a problem either. You can just shove them into a queue and then have a single thread pull stuff out of the queue and push it into SQLite. That still scales, up to a point.

dinosaurdynasty 2y ago
FYI this is basically what Redis does (which does not support concurrent writes or reads afaik), and that still scales quite a lot.
immibis 2y ago
No, because your readers are still blocked by writers and still randomly fail if you forgot to set busy_timeout, which is something at least one person didn't they had to do until they read this comment.
- emseetech 2y ago
  | readers are still blocked by writers We're all using WAL mode these days, right?

gwervc 2y ago

No, it's indeed a very real problem. I ran into with a very small service.

liontwist 2y ago
You want to configure it so it has a timeout. Take turns. That’s how locks work. The only difference between SQLite and Postgres write locking is the granularity.
- miunau 2y ago
  It's not a trivial difference like you suggest.
  - liontwist 2y ago
    I agree It’s not a trivial difference in implementation and the concurrent write performance is probably a lot worse. But it’s talked about as if it’s a categorical limitation, the app will fail if there is concurrency. But it’s just a question of how much time will be spent locking. A website with a 16 process pool for handling requests will be fine.
- immibis 2y ago
  sqlite also polls for lock availability

ComputerGuru 2y ago

This is a baseline benchmark I published of various p9x latencies for multiple readers/writers with a single SQLite database with different configurations set: https://github.com/mqudsi/sqlite-readers-writers https://github.com/mqudsi/sqlite-readers-writers Even if you don’t use message passing to use one thread to perform all updates/writes, it still performs very solidly for many use cases.