
Ask HN: Have you ever used SQLite as a client/server DB for a high volume site? - vanilla-almond
The SQLite website states that it works well for low to medium traffic websites (e.g. 100k hits as a conservative estimate). However, the site cautions about using SQLite as a client&#x2F;server database when multiple clients are trying to write to the database. [1]<p>Have you ever used SQLite in the second scenario? What was your experience? Why did you stick with SQLite over a client&#x2F;server database like PostgreSQL? What it simplicity of usage and deployment? Or was SQLite simply fast enough for your needs?<p>[1] https:&#x2F;&#x2F;sqlite.org&#x2F;whentouse.html
======
falcor84
That "100k hits" figure you quoted surprised me, so after going over their
page, I just wanted to add here that that's "100K hits/day", which translates
to just 1 query per second, which is unreasonably conservative.

In a post [0] from last year, Expensify tell how (with some modifications)
they managed to get sqlite to 4M qps on a single powerful server running 192
cores. That's 20K qps/thread for their huge table, and they got over 200K
qps/thread for a table with a single row (demonstrating how little overhead
sqlite has).

[0] [https://blog.expensify.com/2018/01/08/scaling-sqlite-
to-4m-q...](https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-
a-single-server/)

~~~
pizzaparty2
At that point why not use a heavier database that one would expect to be
faster?

~~~
masukomi
check out the numbers on SQLite. It's incredibly fast and beats PostgreSQL on
a number of benchmarks.

------
marktangotango
I used it to MVP a site that didn’t gain traction so never achieved “high
volume”; single process with multiple readers and single writer. My tests
showed it beat Postgres by a large margin on mixed workload (reads and writes
50/50). The use case was “single db per account/user”. I’d use it again.

------
jonahbenton
This is not a question of performance, rather about architecture and how
concurrency and failure states are handled.

SQLite does not have a socket server and only supports a single concurrent
writer to a given datafile. So by definition it is not client/server- a term
which implies process and network segregation between the thing issuing SQL
commands and the thing applying them against the physical storage.

It is perfectly possible to do a variety of high volume server-like activities
using such a component- and that page describes some of them- but it is kind
of an- if you know what you're doing and what the implications are- kind of
thing.

It is a completely different architectural component than Postgres or MySql.
It is in fact much faster for what it does precisely because of the different
tradeoffs it makes. Those tradeoffs are usually not worth it in conventional
client/server scale scenarios.

------
dangerface
I developed a game with sqlite on the backend, during development sqlite took
everything I threw at it.

I planed to switch to mysql for production but when I benchmarked sqlite it
was able to do ~10,000 query's a second (iirc) way more than I needed so I
figured I would try it in production.

This was a mistake sqlite quickly died in production because of the amount of
concurrent queries. During development it was just one processes using the
database so locking wasn't a thing, but when more than one process was trying
to use the same database waiting on locks slowed the whole thing to a stand
still.

I think its possible to write a client server protocol for sqlite with proper
locks that performs as well as mysql, but it was easier to just use mysql :)

------
petercooper
Not really a "high volume" site but I used it as the main database for a
community site with about 200k pageviews per month for several years.
Unsurprisingly, it was fine given that's a pageview every 10 seconds or so.

Yet even at that level we eventually ran into some repeating file corruption
and locking issues that confused me enough to eventually migrate it to
Postgres and chuck it up on Heroku to stop bothering with it.

------
nreece
I know Pieter[1] uses SQLite for Nomad List[2] and Remote OK[3], both high-
traffic sites. Maybe you can tweet him to learn more.

[1] [https://twitter.com/levelsio](https://twitter.com/levelsio) [2]
[https://nomadlist.com](https://nomadlist.com) [3]
[https://remoteok.io](https://remoteok.io)

------
combatentropy
To be clear, by client-server they mean a connection from a client application
directly to the database. For example, in PostgreSQL you can use the command-
line tool psql or a graphical tool like pgAdmin. Typically these client
applications connect to the database over the network. It's impossible to
connect to SQLite over a network, unless you put its database file on a
network filesystem (which is risky, as pointed out in the page you link to).

With web apps, the clientele don't connect directly to SQLite. They connect to
the web server, like Apache, and through some PHP or Python or whatever, your
web server connects to SQLite. From the point of view of the database, your
web app is the only customer.

As for multiple writers (which, again, would be going through your web app),
the same page has this assurance: "For many situations, this is not a problem.
Writers queue up. Each application does its database work quickly and moves
on, and no lock lasts for more than a few dozen milliseconds."

