Ask HN: Have you ever used SQLite as a client/server DB for a high volume site?
12 comments
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...
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...
At that point why not use a heavier database that one would expect to be faster?
check out the numbers on SQLite. It's incredibly fast and beats PostgreSQL on a number of benchmarks.
[deleted]
Very nice write up! I haven’t found a blog about SQLite at that scale anywhere.
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.
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.
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.
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 :)
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 :)
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.
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.
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 [2] https://nomadlist.com [3] https://remoteok.io
[1] https://twitter.com/levelsio [2] https://nomadlist.com [3] https://remoteok.io
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."
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."
Have you ever used SQLite in the second scenario? What was your experience? Why did you stick with SQLite over a client/server database like PostgreSQL? What it simplicity of usage and deployment? Or was SQLite simply fast enough for your needs?
[1] https://sqlite.org/whentouse.html