4 ms·

An important reminder is to use the right tool for the job. SQLite is indeed amazing at what it does, and fits with many scenarios where it isn't often consider

An important reminder is to use the right tool for the job. SQLite is indeed amazing at what it does, and fits with many scenarios where it isn't often considered, but it isn't a silver bullet. If you have a scenario where you must have multiple machines (for redundancy or resiliency), then SQLite may not be the "best" choice. However, if these machines were needed for performance reason, you may find that a single well-written SQLite-based solution can be performant enough to run on a single machine (while reaping all the benefits of its simpler approach).

nine_k 6y ago
Creating read replicas has become easy with SQLite, thanks to Litestream (recently on HN: https://news.ycombinator.com/item?id=26103776 https://news.ycombinator.com/item?id=26103776). What is really hard with SQLite is efficient concurrent updates. But you don't need them very often.
- pbowyer 6y ago
  What I've never got to grips with is the "use a single writer with SQLite" advice. That seems doable with a long-running application server, but if your application boots up on every request (like PHP, Python & Ruby applications generally do) how do you do this, when you have multiple simultaneous users? It feels like SQLite is missing a separate gatekeeper-binary to act as the single writer: the database server as it were.
  - liuliu 6y ago
    SQLite uses file lock to coordinate with many processes. Your performance will be similar to use single writer if we ignore the repeated DB open / close due to the app boot on every request. I am not sure about real-world DB open / close cost. Because it is considered good practice to always use some kinds of SQLite connection pool if you need many readers / writers. Didn't get a chance to try.
  - jononor 6y ago
    With Python at least an application server with long-running processes is the most common way to deploy, for example with gunicorn. Though typical config will have green threads and/or multiple worker processes, so I am not quite sure how one the single-writer is enforced.
    - nine_k 6y ago
      File locking and waiting on the lock? Assuming that open + update + close is very fast and not very frequent, the wait time will be near zero, can be done synchronously pretty well.
  - coliveira 6y ago
    A solution is to append requests as files to a directory, and have a single process that reads the files and performs the write on SQLite. This way you can avoid locks and use the OS to manage the coordination.
iforgotpassword 6y ago
I just got to feel the downsides of trying to add mysql support to an app that was developed for sqlite exclusively first with digikam. Digikam has experimental mysql support so you can host your photos and the db on your NAS and access it from multiple computers. I assume that digikam makes many simple queries for each individual photo when showing an overview page with thumbnails, tags and dates, as it's all stored in the db and notably slower than with sqlite. With GBit LAN it's acceptable, wifi makes you want to rip your hair out. It's not the bandwidth, but the latency. The sqlite case never made you consider to fetch multiple thumbnails in one query and now it's probably hard to redesign the whole thing to do that.

nine_k 6y ago

Creating read replicas has become easy with SQLite, thanks to Litestream (recently on HN: https://news.ycombinator.com/item?id=26103776 https://news.ycombinator.com/item?id=26103776). What is really hard with SQLite is efficient concurrent updates. But you don't need them very often.

pbowyer 6y ago
What I've never got to grips with is the "use a single writer with SQLite" advice. That seems doable with a long-running application server, but if your application boots up on every request (like PHP, Python & Ruby applications generally do) how do you do this, when you have multiple simultaneous users? It feels like SQLite is missing a separate gatekeeper-binary to act as the single writer: the database server as it were.
- liuliu 6y ago
  SQLite uses file lock to coordinate with many processes. Your performance will be similar to use single writer if we ignore the repeated DB open / close due to the app boot on every request. I am not sure about real-world DB open / close cost. Because it is considered good practice to always use some kinds of SQLite connection pool if you need many readers / writers. Didn't get a chance to try.
- jononor 6y ago
  With Python at least an application server with long-running processes is the most common way to deploy, for example with gunicorn. Though typical config will have green threads and/or multiple worker processes, so I am not quite sure how one the single-writer is enforced.
  - nine_k 6y ago
    File locking and waiting on the lock? Assuming that open + update + close is very fast and not very frequent, the wait time will be near zero, can be done synchronously pretty well.
- coliveira 6y ago
  A solution is to append requests as files to a directory, and have a single process that reads the files and performs the write on SQLite. This way you can avoid locks and use the OS to manage the coordination.

iforgotpassword 6y ago

I just got to feel the downsides of trying to add mysql support to an app that was developed for sqlite exclusively first with digikam. Digikam has experimental mysql support so you can host your photos and the db on your NAS and access it from multiple computers. I assume that digikam makes many simple queries for each individual photo when showing an overview page with thumbnails, tags and dates, as it's all stored in the db and notably slower than with sqlite. With GBit LAN it's acceptable, wifi makes you want to rip your hair out. It's not the bandwidth, but the latency. The sqlite case never made you consider to fetch multiple thumbnails in one query and now it's probably hard to redesign the whole thing to do that.