5 ms·

Sharing between processes? Absolutely. Machines? No. For example, LAMP stack applications could swap the M for SQlite (and I think it would have been better hi

Sharing between processes? Absolutely. Machines? No. For example, LAMP stack applications could swap the M for SQlite (and I think it would have been better historically if they did).

bob1029 2y ago
Sharing between processes isn't impossible but this where you get all of your performance caveats. I think it is a bit unfair to argue against SQLite on performance grounds but then not explore ways to utilize instances of it within a single process scope where it is most effective. Sharing a single SQLite connection across all threads within a process is where you get the best performance outcomes. Everything is serialized within the SQLite provider - assuming you are using a mainstream build. So, if your storage subsystem is fast (NVMe & friends), you will achieve very good outcomes. Any utilization model that requires a file open/close operation every time you want to touch the data is a complete non-starter by comparison. The "unit of work" paradigm that hosted providers recommend is catastrophic for performance with SQLite. One option for the "I absolutely must share this SQLite instance with 30+ services" scenario is to simply wrap it with a REST API or similar.
- liontwist 2y ago
  > but this where you get all of your performance caveats. You mean the one where it locks on write? It’s totally fine, if you wrote any cross process code yourself it’s probably going to do similar locking.
  - immibis 2y ago
    Yeah, but you're locking the whole file and if you try to open it while it's locked, sleep-polling for it to be unlocked. It's safe, but it's a minimum viable product - as they say, sqlite is a replacement for fopen, not for a fully featured database. Client/server systems have better locking.
    - liontwist 2y ago
      I mostly agree. If you need an RDMS get a real one that has all the features and granular locking. The only point I’m disagreeing about is the blanket statement “your SQLite website won’t work if it has concurrent writers”. It will.
- bawolff 2y ago
  I don't see why performance would be significantly different between multiple threads using same sqlite db vs multiple processes on same machine. Can you explain more what you mean?
  - bob1029 2y ago
    See section 5: https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html In the single process access model, you can connect exactly once and remain in a reserved lock state the entire time.
    - bawolff 2y ago
      Maybe i misunderstand, but it doesn't seem like the linked document supports what you are saying. The linked docment also doesn't describe how things work in WAL mode which is how most people would (or should) use sqlite in production.

Sharing between processes? Absolutely. Machines? No. For example, LAMP stack applications could swap the M for SQlite (and I think it would have been better historically if they did).

bob1029 2y ago

Sharing between processes isn't impossible but this where you get all of your performance caveats. I think it is a bit unfair to argue against SQLite on performance grounds but then not explore ways to utilize instances of it within a single process scope where it is most effective. Sharing a single SQLite connection across all threads within a process is where you get the best performance outcomes. Everything is serialized within the SQLite provider - assuming you are using a mainstream build. So, if your storage subsystem is fast (NVMe & friends), you will achieve very good outcomes. Any utilization model that requires a file open/close operation every time you want to touch the data is a complete non-starter by comparison. The "unit of work" paradigm that hosted providers recommend is catastrophic for performance with SQLite. One option for the "I absolutely must share this SQLite instance with 30+ services" scenario is to simply wrap it with a REST API or similar.

liontwist 2y ago
> but this where you get all of your performance caveats. You mean the one where it locks on write? It’s totally fine, if you wrote any cross process code yourself it’s probably going to do similar locking.
- immibis 2y ago
  Yeah, but you're locking the whole file and if you try to open it while it's locked, sleep-polling for it to be unlocked. It's safe, but it's a minimum viable product - as they say, sqlite is a replacement for fopen, not for a fully featured database. Client/server systems have better locking.
  - liontwist 2y ago
    I mostly agree. If you need an RDMS get a real one that has all the features and granular locking. The only point I’m disagreeing about is the blanket statement “your SQLite website won’t work if it has concurrent writers”. It will.
bawolff 2y ago
I don't see why performance would be significantly different between multiple threads using same sqlite db vs multiple processes on same machine. Can you explain more what you mean?
- bob1029 2y ago
  See section 5: https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html In the single process access model, you can connect exactly once and remain in a reserved lock state the entire time.
  - bawolff 2y ago
    Maybe i misunderstand, but it doesn't seem like the linked document supports what you are saying. The linked docment also doesn't describe how things work in WAL mode which is how most people would (or should) use sqlite in production.