The article addresses this. Basically, you can have any number of concurrent readers, but only a single writer. Writing and reading can happen concurrently just fine. So the question is -- how many users does a website need before having only a single concurrent writer becomes a bottleneck?

That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [EDIT the number maximum number of concurrent users] is actually "1". And for many, that will be in the thousands or hundreds of thousands.

FWIW the webapp I use to help organize my community's conference has almost 0 cpu utilization with 50 users. Using sqlite rather than a separate database greatly simplifies administration and deployment.

>That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where that number is actually "1".

I can imagine a static website where the content is read-only for users and is only editable by admins/developers/content managers through some CMS.

That'd be a near-infinite ratio. The parent is discussing a site where the ratio is near 1 (i.e. roughly as many reads as writes).

It's not even the percentage of reads vs writes; it's about the total time spent writing. Suppose that while a single user was actively using the website, the time spent writing was 70%. That's a totally mad write load; but even then, if you had two concurrent users, things would probably still be quite useable -- you'd have to go up to 4 or 5 users before things really started to slow down noticeably.

Suppose, on the other hand, that a single user generated around a 1% write utilization when they were actively using the website (which still seems pretty high to me). You could probably go up to 120 concurrent users quite easily. And given that not all of your users are going to be online at exactly the same time, you could probably handle 500 or 1000 total users.

When I was working on database services there were actually many applications with more writes than reads. A common example is applications where you want to keep the user's state saved frequently, but you only need to reload it when the application is restarted. A lot of games work this way. So there can be a write every few seconds or every minute for every active user, but you only need to do a read a few times at the beginning of a new session.

Of course applications like a blog will have far more reads than writes. It just really varies depending on the type of application.

Right; lots of modern document software, for instance, basically saves continuously; so a Google Docs-style application could have such a load. But if you have a write transaction every second, but each transaction only takes 1ms, that's still ~1000 concurrent users before you start to get noticeable lag.

Remember, the person I was replying to claimed SQLite was "not useable outside of the model... where you have a single user". Yes, if you need 100k concurrent users doing a 1ms transaction every second, SQLite isn't for you. But 1000 concurrent users is a lot more than 1.

Although on reflection, what they may have meant for "single user" is a single process (perhaps with multiple threads). That sounds reasonable to me: people just don't realize how much you can actually do with a single multi-threaded process running on a modern server.

1 ms per transaction is quite optimistic! In practice occasionally you will have some sort of latency spike caused by an external event or a single extra-slow transaction, and then your single-threaded queue will get backed up and requests will fail, if your database is dependent on a single thread. SQLite is great for the right problem but it just isn't the right tool for a web service with even a small amount of traffic.

That's not been my experience. I write applications with in Go using a SQLite backend and it's not uncommon to see writes transactions that are in the 100s of microseconds. I typically see read transactions with multiple queries around ~50µs. That's all running on very modest $5/month DigitalOcean droplets. The vast majority of web applications do not see thousands of requests per second so I think SQLite is a great fit for most web apps.

As if a transaction only takes 1ms... Imagine you want to update the lastLogin field for a logged user for example.

> you can have any number of concurrent readers, but only a single writer

Only if your readers and writers are cleanly segregated.

Most languages and web frameworks don't have SQLite drivers out of box (or have extremely bad ones). Unlike SQLite, most databases don't really care about distinction between read-only and writable connections. So there is a good chance, that you will always open writable connection by default, because this is what your framework/ORM does. Furthermore, seemingly read-only web middleware often ends up writing to database on each request for one reason or another. If you try to reuse/pool connections (which is also important under high load), you need to be wary of keeping open writable connections in cache — again, something that does not matter to all major databases other than SQLite.

I was involved in maintenance of a small web app (db size < 5 Mb), written in Django, that had to serve ~1000 dynamic requests per second (the contents of each response were dependent on IP address of caller). The app worked with PostgreSQL, albeit poorly, but immediately ground to halt under load with SQLite — which was our default database choice for historical reason.

We ended up briefly caching results of most database queries in memory, which removed most of load from database (we also did a lot of other optimizations, but this was the decisive one). Eventually the app was able to withstand up to 9000 requests per second, but none of that was an achievement of SQLite — we just evaded database, Django and Python altogether on majority of requests.

While we are on this topic, the most widespread OS in the world, Android, also has extremely low-quality SQLite drivers — despite shipping SQLite as default database for many years. Android has a broken-by-design Cursor implementation (the devs admitted it themselves [1]), that always tries to count query results, even if you don't call getCount(). And a broken connection cache, that does not support read-only connections [2] (that method used to have a "TODO", but eventually they forgot, why they wanted it, so they removed it).

1: https://medium.com/androiddevelopers/large-database-queries-...

2: https://android.googlesource.com/platform/frameworks/base/+/...

SQLite doesn’t require you to know which connections are read and which are write. Connections get promoted to a write lock when you do a write DDL or if you begin an IMMEDIATE transaction.

Also, for your small app were you using WAL mode? Multiple readers only works in WAL journaling mode.

SQLite can promote connections, but it does not demote them back to read-only. As I understand, when a database connection is tainted by a single write, it's lock on SQLite file in promoted to write lock, which prevents other processes from opening any kind of connection to it.

Our Django setup needs multiple processes to work around the Grand Interpreter Lock. Disabling connection reuse in Django config slightly changed the behavior we observed, but didn't solve the performance problem.

With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives.

Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to <10k inserts per second even on the fastest hardware.

The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you.

The whole point of an embedded database is that the application should have exclusive control over it, so you don't have to worry about the kinds of things that SQL Server needs to worry about.

SQLite is not a direct replacement for SQL Server, but with enough effort it can theoretically handle even more traffic in your traditional one-database-per-app setup, because it's not worrying about multiple users, replication, et. al.

"The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you."

It's not the right approach because it's hard to get right, you want to offload that to the DB.

It's actually not that hard to get right. You can put a simple lock statement around a SQLiteConnection instance to achieve the same effect with 100% reliability, but with dramatically lower latency than what hosted SQL offerings can provide.

Also, the only reason we ever want to lock a SQLiteConnection is to obtain a consistent LastInsertRowId. With the latest changes to SQLite, we don't even have to do this anymore as we can return the value as part of a single invocation.

You won't have to worry about the last insert row id anymore. `RETURNING` was added recently. Enjoy the speeeed.

Yes, this is exactly how mORMot (https://github.com/synopse/mORMot) implements the DB server with SQLite.

> I have been able to insert hundreds of thousands of rows per second when using NVMe drives.

Thats... amazing! What is your setup like?

As far as I'm aware that threading information is on a per-connection basis.

SQLite is perfectly capable of supporting multiple, parallel reads.

SQLite must serialize writes, which makes a highly parallel write-heavy workload not good for it. However, with WAL enabled writes do not block reads.

Basically highly-parallel read loads with low write counts (low-enough that serializing them doesn't lead to unacceptable slow down of writes) or with loads where latency is acceptable in writes (but not reads) is a perfect use case for SQLite. And it turns out that a lot of web services are heavily asymmetrically biased towards reads.

A nice fix for this might be to extract just the wire protocol from dqlite to make a network connected, but not Raft replicated, sqlite.

https://github.com/canonical/dqlite

There's also Expensify's wrapper of sqlite, bedrockdb.com. But at that point why not just use traditional RDMS?

An embedded DB with an optional network protocol, but without raft, replication, etc, seems "normal" to me. Raima used to be popular in the 90's and was pretty much that. It's still less to manage than MySql or Postgres.

I've used SQLite a bit, but not enough to say I know where you run into performance issues. I would anticipate though, if it's similar to fread/fwrite with some marginal overhead for dealing with SQL, after considering most queries aren't very complex, I think most people are going to have a hard time hitting those limits.

And that's assuming you're making queries every time an event occurs versus persisting data at particular points in time.

I disagree. I wrote a multiplayer web based game using sqlite in go: http://decwars.com/

Most websites/frameworks access their database through a singleton pattern/single-connection anyway.

Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)

That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.

One could implement a write queue and caching for the front end. That might sound like a lot of work to go through to avoid moving to a proper multi-user database. But given that writes are probably done through a handful of API endpoints, it's probably not really that much work to implement.

Well, let me throw out a crazy idea:

Nobody said you have to use a single database/file. Obviously, you are going to want to spend a couple minutes thinking about referential integrity. But how often do you delete records in your web app?

UPDATE is also a write, and references crossing files sounds like a nightmare.

If your deployment environment has serious constraints, I'm sure you could make it work. The product you deliver would be SQLite + custom DBI layer to hide SQLite's limitations.

It would be a lot more work, and not be as robust or scalable, compared to a more traditional selection. But I can imagine cases where it would be appropriate.

A big database id not an issue. Contention requires a large number of writers, not a large amount of cold data.

As an IRC client with an SQLite backend: several hundred times per second.

Per user.

While reads are more like once every day per user.

Yes.

;)

Any site with real volume will have a connection pool.

All of those connections might need to write, and this is where SQLite gets tricky to implement at scale.

I love SQLite! It's perfect for many use cases, but not all. Fortunately, Postgres is also excellent.

Can you provide some examples of frameworks with such pattern? I actually have never seen it in any of the Python or Go web applications that I had a chance to work with.