3 ms·

Well, since the SQLite database is one simple file, isn't SQLite a hassle when you have some concurrency/parallelism ? And what if you have separate services th

Well, since the SQLite database is one simple file, isn't SQLite a hassle when you have some concurrency/parallelism ? And what if you have separate services that need to access the same DB ?

adamtaylor_13 2y ago
Nope! That’s what the write-ahead log (WAL) is for: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html Of course, if you experience scaling issues there (you probably won’t if it’s anything less than enterprise-level usage), you can always just add a second db file!
- TheRoque 2y ago
  I see. Wasn't aware of this. I considered taking SQLite for my current stack, but dismissed it because I've read lot of bad stuff with concurrency. However, it says that it doesn't work over networks, so basically you can't do WAL with a containerized stack ?
  - hruk 2y ago
    You can have multiple containers on the same host use the same db via Docker volumes - we have a number of production services set up this way.
  - adamtaylor_13 2y ago
    As hruk pointed out, you can use docker volumes to solve for this. However, you can also ship multiple DBs depending on your use-case. If it's a shared DB, it's probably not a great idea for micro-services. But if you're building a majestic monolith, there's few reasons NOT to go with SQLite. Especially paired with litestream[0]. 0: https://litestream.io/ https://litestream.io/
ammo1662 2y ago
> isn't SQLite a hassle when you have some concurrency/parallelism If you decide to put your application on a single server, that means you do not care about the single point failure, and all your workload can be handled by the single server. So you can just run only one instance of your application, then you will not have such kind of problem. Even if you want to have multiple instances of your application running on the same machine, SQLite can also handle that. > what if you have separate services that need to access the same DB? That means one single server cannot handle the workload. If the bottleneck is in the database module, a cluster is required to process the data. Clearly the SQLite is not a good option in this case. If it is not that case, you can separate the database module and provide a lightweight wrapper around the SQLite to create a database service. And use multiple instances for calculation, then call the single database service instance for persistance. I think comparing to the single instance of MySQL/Postgres/SQL Server, the performance of SQLite is not too bad. So we should keep the architecture simple, if possible.
- TheRoque 2y ago
  Well, running multiple instances of the same app is not only about "points of failure", it's also about using all the cores of your machine. Typically, using PI (nodejs), Docker or Uvicorn (python), you can spin up multiple instances of your backend to handle more concurrently.
  - Filligree 2y ago
    SQLite connections can be used concurrently inside a single process. You can spin up more threads instead, and spare yourself the filesystem locking overhead.
    - TheRoque 2y ago
      Threads in Python in python are locked to a single core, and in NodeJS it's really annoying to pull off. That's why people use PM2, Uvicorn and Gunicorn.
      - Filligree 2y ago
        That would be a problem, then. I'm always surprised when people try to write high-performance code in Python or JS.
        TheRoque 2y ago
        Nobody talked about "high performance". I'm just talking about using all the cores on a machine. People write stuff in Python and JS for obvious reasons that I don't need to detail here.
        Filligree 2y ago
        Performance is best judged on a logarithmic scale. Most software barely needs any, and there are many orders of magnitude between "barely uses the CPU" and "uses 100% of a core". But once you're at 100% of a core, then there aren't many orders of magnitude between that and "uses every core". In fact, the impact from not using a scripting language is on the same order as the threading would be! So, if it's worth spending effort squeezing out those last 1-2 orders of magnitude from the CPU, then it's probably worth thinking about the language as well. If you've already blown through 4-5 OOMs going to a full core, then chances are you'll need it.

Well, since the SQLite database is one simple file, isn't SQLite a hassle when you have some concurrency/parallelism ? And what if you have separate services that need to access the same DB ?

adamtaylor_13 2y ago

Nope! That’s what the write-ahead log (WAL) is for: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html Of course, if you experience scaling issues there (you probably won’t if it’s anything less than enterprise-level usage), you can always just add a second db file!

TheRoque 2y ago
I see. Wasn't aware of this. I considered taking SQLite for my current stack, but dismissed it because I've read lot of bad stuff with concurrency. However, it says that it doesn't work over networks, so basically you can't do WAL with a containerized stack ?
- hruk 2y ago
  You can have multiple containers on the same host use the same db via Docker volumes - we have a number of production services set up this way.
- adamtaylor_13 2y ago
  As hruk pointed out, you can use docker volumes to solve for this. However, you can also ship multiple DBs depending on your use-case. If it's a shared DB, it's probably not a great idea for micro-services. But if you're building a majestic monolith, there's few reasons NOT to go with SQLite. Especially paired with litestream[0]. 0: https://litestream.io/ https://litestream.io/

ammo1662 2y ago

> isn't SQLite a hassle when you have some concurrency/parallelism If you decide to put your application on a single server, that means you do not care about the single point failure, and all your workload can be handled by the single server. So you can just run only one instance of your application, then you will not have such kind of problem. Even if you want to have multiple instances of your application running on the same machine, SQLite can also handle that. > what if you have separate services that need to access the same DB? That means one single server cannot handle the workload. If the bottleneck is in the database module, a cluster is required to process the data. Clearly the SQLite is not a good option in this case. If it is not that case, you can separate the database module and provide a lightweight wrapper around the SQLite to create a database service. And use multiple instances for calculation, then call the single database service instance for persistance. I think comparing to the single instance of MySQL/Postgres/SQL Server, the performance of SQLite is not too bad. So we should keep the architecture simple, if possible.

TheRoque 2y ago
Well, running multiple instances of the same app is not only about "points of failure", it's also about using all the cores of your machine. Typically, using PI (nodejs), Docker or Uvicorn (python), you can spin up multiple instances of your backend to handle more concurrently.
- Filligree 2y ago
  SQLite connections can be used concurrently inside a single process. You can spin up more threads instead, and spare yourself the filesystem locking overhead.
  - TheRoque 2y ago
    Threads in Python in python are locked to a single core, and in NodeJS it's really annoying to pull off. That's why people use PM2, Uvicorn and Gunicorn.
    - Filligree 2y ago
      That would be a problem, then. I'm always surprised when people try to write high-performance code in Python or JS.
      - TheRoque 2y ago
        Nobody talked about "high performance". I'm just talking about using all the cores on a machine. People write stuff in Python and JS for obvious reasons that I don't need to detail here.
        Filligree 2y ago
        Performance is best judged on a logarithmic scale. Most software barely needs any, and there are many orders of magnitude between "barely uses the CPU" and "uses 100% of a core". But once you're at 100% of a core, then there aren't many orders of magnitude between that and "uses every core". In fact, the impact from not using a scripting language is on the same order as the threading would be! So, if it's worth spending effort squeezing out those last 1-2 orders of magnitude from the CPU, then it's probably worth thinking about the language as well. If you've already blown through 4-5 OOMs going to a full core, then chances are you'll need it.