Hacker News Reader

Comment by tracker1 | Hacker News Reader

Not to start a flamewar, or discount SQLite, I love SQLite... but if you really need this kind of feature, wouldn't something like Firebird DB be a better option at that point? I know the licensing and embedding is different, just curious what others think.

View on HN

SQLite is really great database for mobile applications, where concurrency can make a massive difference in how responsive the app is.

Just as one example, imagine a mobile app that does a bulk data download, and needs to persist it all to the local database in a single transaction for consistency purposes. This transaction can take a couple of seconds, or maybe a minute of you're working with millions of rows.

Now without concurrency, your database is locked for the entire transaction, and this may block any user actions.

With WAL, you can at least continue doing reads during this transaction.

With BEGIN CONCURRENT, the user may be able to continue most actions, as long as it doesn't touch the same set of data.

Now there are other ways of solving this problem: use smaller transactions and different mechanisms to ensure consistency. Or use separate databases if you can structure your data that way. But BEGIN CONCURRENT can be a nice solution without having to restructure the data completely.

Now these data volumes may be extreme, but even with "background" transaction taking in the order of a second can have an impact on how responsive the app feels to the user.

I understand everyone wants concurrency because that's the way everything else in programming works these days, but SQlite in memory or on an SSD (in WAL mode) writes so fast that it makes infinitely more sense to dedicate a single database "write" worker that deals with the dB itself while allowing multiple "read" workers to access the database concurrently. Other workers are spawned to process their data and shovel their writes into a queue to feed that single worker.

In my experience, the CPU utilization of process workers preparing data into SQL statements is generally the chokepoint in most/script programs instead of SQlite write speeds themselves. Of course, this is dependent on numerous factors such as the existence of indices, compexity of write operation, etc., but--as a rule--SQlite is much more efficient if I dedicate one process to handle all writes and optimize that process for speed (i.e. batch writes, etc.).

I agree... My thinking is that if you're growing beyond a single process accessing the database, either put a service on top of the SQLite db, or move to a db more suited to the needs/workflow.

I generally think in terms of SQLite -> Firebird (maybe) -> PostgreSQL -> CockroachDB (or others). Just depends.

SQlite is much more efficient if I dedicate one process to handle all writes and optimize that process for speed (i.e. batch writes, etc.).

That increases complexity on the application side though.

This is some minimal write concurrency for people who correctly see that they don't need much write concurrency, but perhaps have one tiny situation where they need more than zero.

SQLite is getting to the point where small websites it can work as the default . Wordpress basic has SQLite in the default install configuration

What people tend to overlook is:

- SQLite is typically used on same-host without any networking…

- Meaning that latency is much, much lower…

- Meaning that you will have much fewer overlapping transactions at any given point in time…

- Which results in less memory usage and…

- An SQLite instance will remain “low-latency” in wall-clock time even under pressure…

- And you have more leeway for ping-ponging (the N+1 problem) which can reduce query complexity, amount of indices, etc

In short, the performance you can squeeze out of a SQLite system is is unexpectedly high, as long as you can fit app+db on the same machine. Instead, I believe you’d choose a single-master networked db often for other (legitimate, but typcially not perf) reasons.

As a meager indie dev, this is a very appealing trade off. I’m not a db expert, and looking at the total complexity and churn in the field, I really don’t want to become one. SQLite has like three knobs. And as a bonus, I can run the same system on client machines.

Of course, SQLite is great for a lot of things, including small websites, or nearly anything mostly read without a ton of writes. It's not much at all over the top of modern disk io... nvme drives are crazy fast. My only thinking is in terms of additional concurrency, or for that matter, multiple shared access since FB can grow from embedded to dedicated server... I often think of it as a few steps between SQLite and Postgres for some scenarios.

Reply on news.ycombinator.com