Hacker News Reader

Comment by curiousmindz | Hacker News Reader

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).

View on HN

Creating read replicas has become easy with SQLite, thanks to Litestream (recently on HN: 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.

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.

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.

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.

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.

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.

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.

Reply on news.ycombinator.com