There are massive performance gains to be had by using transactions. In other RDBMSs transactions are about atomicity/consistency. But in SQLite, transactions are about batching inserts for awesome speed gains. Use them!
Along the same lines, SQLite is unlike other RDBMS in that you typically do NOT want to create a new connection per logical unit of work, because that means opening an actual file on disk each time. The more you can re-use a SQLite connection instance the better.
SQLite is internally handling the locking for you under most providers[0]. Any sort of external attempts at the same will just make things go slower. The only thing that could really beat the internal SQLite mutex is to put a very high performance MPSC queue in front of your single SQLite connection and take resulting micro batches of logical requests into transactions as noted above, or some more consolidated form of representation.
Inserting batches of items in one db transaction is faster in Posgres as well, in my experience. I expect that to be the case for all rdbms. Each transaction has a cost of "flushing", and every non-transaction query is implicitly a transaction. By doing multiple operations in one explicit transaction, the overhead of a transaction is amortized over more operations.
A bunch of inserts in a single transaction is generally fast and unproblematic. Though if you have weird clustered indexes and a lot of concurrency you might run the risk of frequent deadlocks.
Another useful trick you can do is to take advantage of memoization combined with BEGIN TRANSACTION / COMMIT to push at once a massive set of data.
What I have done so far was to collect safe data, that is not sensitive info like passwords and such, and save them in an array (in my case I used PHP language), and at a specific number of elements you know it's enough to commit in your database, you do so only once via transaction; in my case was 25K rows and worked flawlessly without users realizing anything at all.
I have even done the same thing with 100K rows and didn't even break a sweat!
If you're staring a Rust project then make sure to check that your SQLite dependency supports txs. Without txs, inserts can take a ~15ms. The `rusqlite` crate supports txs, with `sqlite` I didn't find an ergonomic solution.
>The first thing I've come to appreciate about SQLite is its documentation
I've told this story before, but the first time I used sqlite, years ago, I needed to store a floating point value, so I read the documentation: https://www.sqlite.org/datatype3.html
>REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
I parsed this as "an 8-bit floating point number". Wow, I thought. That's weird as hell. Oh well, I guess it's sql lite, ha ha. I went on to implement storing numeric values as text strings, which worked exactly as well as you'd expect. Only months later did I find out that it's a normal regular IEEE double, 64 bits long.
I was confused as well. How you phrased it, it seemed that your funny story was about how cool the docs were and saved your ass or something like that :)
It's funny that under "maybe it was an example of nobody reading shit all" there is a comment where someone didn't read the nickname and thought he's answering to the anectdote's poster comment :D
That's a very salient point. And reading well all the time is super hard. I think that's one of the reasons why people don't write documentation. First of all, who's gonna read all that? Second of all, who's gonna read all of that correctly?
My dad knows very little about computers but he likes joking with me whenever i’m struggling with some technical problem: “8 bits is a byte! That’s all you need to know! How hard could it be?”
An EE only needs to know that you don't stick your finger in a socket. A mechanical engineer only needs to know that you can't push a rope. And a civil enginer only needs to know that sh*t rolls downhill.
Which I think is fine (though you could make a case for a proper datetime type). What I am a lot less of a fan is the dynamic typing, ie that if you have an int column, sqlite will happy store a string in there. I am not sure why they do that but to me, it lets some bugs go unnoticed.
There are a variety of ways that bugs like that could sneak through a codebase.
I was taught to think of a RDMS as the last line of defense against data integrity issues. SQLite, on the other hand, is more than happy to let you shoot yourself in the foot.
The comment about concurrency was strange. They have 24 x 5 + 2 x 2 * 60 = 360 writes per minute, or about 6 writes per second. They're not seeing concurrency issues because the amount of time that's spent doing writes is like 1%. Naturally retrying a couple milliseconds later should succeed almost all the time.
Why do you need to manually retry at all? Doesn't it end up in a queue and gets executed as soon as it can? I thought this stuff was handled by the database automatically.
Sqlite has a gotcha where if your transaction is upgraded to a write transaction half way through it is impossible to safely restart automatically in the dvent of a conflict. Which is why sqlite provides a "begin immediate" keyword to work around it if you know you are making a write transaction.
If it blocked you until it was finished, then you wouldn't be able to handle a timeout because you would be blocked waiting for it to finish, so you'd have no capacity to realise the task needed to timeout (unless you wrote multithreaded code to detect the timeout "out of band"). If it doesn't block you, then you won't know whether it's been committed or not and so now you have to write some kind of loop to continuously test whether the query you just executed has been committed, abandoned or is still in progress.
In principle, you could have some kind of configuration that says "try to commit this for so long, but if you can't commit it by then just abort". But you haven't really solved the manual retry problem, just made it somewhat less likely to occur.
As far as I know, given the costs of a request, most database servers tend to use the latter approach. But in SQLite requests are very much cheaper, and at least part of SQLite's work happens in your thread, so it goes for the former approach. But from an end programmer's perspective it's only a quantitative difference, not a qualitative one.
Also, "the database" is a highly ambiguous term, but in SQLite it can really only refer to a file on disk. There's no database server, it's just a library that you call to manipulate the file. So who even is the database server? Maybe it's the code you're writing. Maybe it's you who should be handling this stuff. And that's SQLite's decision, and fair enough too. SQLite isn't trying to be a database server, it never offered to be a database server, and you shouldn't think it's a database server. You should think of it as a library for executing SQL queries against a file that holds a relational database. If you think that's tantamount to being a database server, then consider what you normally mean by "server". Is a library for manipulating zip files tantamount to an FTP server?
True, on their own those numbers are not that impressive. To give some more context: That load is average, there are bursts. I use ATOMIC_REQUESTS=True which ties the transaction to the Django POST request. Given that it's quite easy to run into N+1 queries using the ORM, I somehow expected to see "database is locked" issue more often.
Fly.io has inspired me for one-human scaled production. There's a ton you can do at 100% uptime... When the user is looking. Which is not often! You've got a ton of time for maintenance and reshuffling your infrastructure around. You've got tons of downtime if you do it in five minute intervals in the evening, and plenty of concurrency if you've got just one user. Serving from a file at a time is great. You can snapshot and replicate every five seconds and never miss more than one or two actions if you have to fail over if you're doing so for a single user's data, and for a single user.
That's a tradeoff you make if you're a user, and it's pretty worthwhile. There's something to play with
Can you link more concrete details about this? It kinda sounds like you're referring to the design of sharding databases by user, which i almost forgot about (relevant to me now, so i appreciate it). Though i'm super curious in how synchronizing data between users would work.. ie notifications, single producer multi consumer data, etc.
There's two classic pieces of wisdom that aren't so much wisdom anymore but pieces of the landscape:
The first is, don't write your own datastore, use a database. This is pretty much taken for granted today, but in the year 2000, there weren't a plethora of web frameworks. At the time, the unix filesystem didn't seem like such a bad interface to store things. Storing each user as their own file made sense. Simple enough. One (just one of several) way it runs into problems is when:
The other landscape concept - Run multiple copies of your app. This one is kinda controversial to this day, but it's core to the "Twelve Factor App", which was a somewhat prescient memo for it's day, if looking a little long in the tooth to my eye[1]. It's why people keep using Kubernetes. And it's correct advice - If you need to be running a ton of concurrency, you are better off scaling by process and keeping heavy-lifting logic out of your database. If you keep the database and application on separate hosts, you can scale to one big database server for writes, and many application servers and read-only database shards. This works really well for most crud and render-heavy applications you see on the web today; Social networks in particular absolutely thrive on this model.
This brings up another application architecture anomaly that's mostly disappeared - The "App in Database". There's a mildly successful model whereby almost all application logic lives in the database itself as stored procedures. You can do an entire app update as an atomic DDL commit! The database's permissions handle everything, and users just connect as users. In the old MVC model, the Database tables are M, the stored procedures C, and you write a V on top. I've encountered a few instances of this paradigm, most notably an accounting software that worked pretty great.
Fly suggests throwing all of this out the window. Most applications don't need a lot of write-scaling. You don't even need read concurrency. Run a single shard, make that shard fast, performant, and replicable - When it's shut down, you can make many copies of it, and resume from any of them. When a new one starts up, it becomes the primary, all of the others are marked as stale, and you resume making copies. If you're using Litestream like Fly does, this makes for a drop-dead simple app environment. Alternatives, like I'm using at home, are ZFS replication - SQLite is snapshot safe, so I just snapshot frequently, replicate those snapshots frequently, and reverse direction when necessary.
It's great that people are realising that SQLite is a solid tool to use everywhere. But it amusing that this article reads like: I tried a 25 year old database developed by an elite team in the fastest and best supported systems level programming language and it was good!
Yeah, I feel like there's this ingrained assumption people have that because SQLite can run in-memory that it's "not a real database". I've never really understood it (since I _love_ using it in my personal projects), but I've also never really had the chance to use SQLite in production.
Fun article. Would be interesting to see any benchmarks folks have on a variety of devices and computer sizes. I'd expect most developer machines to be quite beefy compared to, say, my kids Chromebooks. Those are still far stronger than anything I grew up with, of course. In ways that make any intuition I have basically worthless on performance.
The author tested only 100k rows. That almost certainly fits in your kids chromebook's memory just as much as it would a beefy computer. It would probably fit in memory even on a raspberry pi or a computer from the 90s.
Its pretty sad given how most cloud providers price out IOPS.
I've had to explain numerous times to our engineers that their laptop is running around 10x the IOPS of our database (~40,000). Table scans are actually more performant on a dev laptop than a replica.
Now I'm imagining an alternate reality where a Docker Container is like a caddy for laptops lining up the thunderbolt ports for hotswapping. And the delivery manager is the courier driver.
I dont get why you would want this. Most of sqlite's unique benefits come from its unique design choice of being in process. If you did queries over the wire you'd just get worst of both worlds.
I implemented once the MySQL network protocol (in TypeScript) to talk to a local SQLite DB. We used that for our CI environment, since our apps only did very vanilla SQL operations. It worked like a charm. So I can totally see some uses for such a beast -- but probably all of them are pre-production.
Postgres has foreign data wrappers which can kinda achieve this(see also: https://github.com/pgspider/sqlite_fdw ), but ive been surprised by how well sqlite is already supported by many tools : sometimes it comes for free as its used in testing - hooking up stuff like litestream can work really wel l too
FDWs still requires an instance of Postgres to be running. With implementing the Postgres wire protocol, you could theoretically use any Postgres driver to talk to a SQLite database with no client changes and no instance of Postgres
DuckDb supports reading/writing to a SQLite database, while using the improved DuckDB syntax. Sounds too good to be true, but I keep meaning to explore if there are any obvious gotchas.
But isn't this the point of being able to use both duckdb databases and sqlite databases from the duckdb runtime? You can mix and match read/write use-cases within the same duckdb db runtime?
Perform OLTP actions against sqlite db from within duckdb.
Perform OLAP actions against duckdb db from within duckdb.
Sometimes your OLTP content drives the OLAP query so read from both sqlite db and duckdb db at the same time.
AFAIK SQLite has weird locks, half year ago they weren't fair and writers could
get timeout easily. For workaround we implemented external lock and it have been worked quite smoothly since.
SQLite's locking code looks like:
while lock_is_still_held_by_somebody():
sleep(3) # 3 seconds
It's SQLite's locking implementation. In real code it includes some backoff but on long timeouts it reaches 3s. Jitter doesn't help BTW it's still unfair. On high contention each worker only has a slim chance to get a lock.
Is there a nodejs SQLite driver wrapper than does that hides the one writer concurrency issue for you without having to think about it? I never used SQLite on a server because of it (many times on desktop and other clients), but I do have reasons to do so, especially with them being so easy to snapshot and move around.
One thing that really confused me with sqlite is how read transactions that are upgraded to write transactions half way through tend to fail very easiltly under high write load and cannot be automatically restarted by sqlite. It all makes sense in their concurrency model but it was surprising to me. The tl;dr is if you know you are going to write data in a transaction but the first operation is a read,use begin immddiate, so you dont have to worry about having to restart things manually.
I also use SQLite in production and it’s been pretty great so far. Serves about 50 requests per second from a single threaded Node app on a low tier DigitalOcean machine. To be fair, most of those are cache hits but it’s still be nice. It’s saved me money by not needing a separate db server and the operational simplicity is great. Biggest speed up I found was turning on memory mapping (which is different from an in-memory database that the article talks about).
Sqlite being nearly as fast as a hash table might say more about how slow hash tables are than how fast sqlite is.
Sticking your data in a self-balancing BST usually gives you log n lookups, but most of us haven't internalised that log n with a small coefficient can be better than constant time with a huge coefficient on expected data sets.
So can you just run Django with SQLite (the default) now, with no special setup? And no data loss? Years ago I tried that when I made a simple web app for a one-off data collection at work. I had maybe 10 users on it and started to encounter concurrency problems. This was about 5 years ago. What's changed since then?
Initially, I was worried that Django might screw up migrations since it's a bit awkward to make certain schema changes in SQLite. Also almost no one seemed to use SQLite with Django in production, so I was a bit worried about how battle tested the SQLite part of the ORM was.
I've not explored it in any more depth than I wrote about in that article and my issue comments.
My hunch is that if you bump it up to 20s while receiving a huge amount of concurrent traffic you may find yourself running out of available Python workers to serve requests.
Sounds like they were testing it with a small enough amount of data that the disk version could just work from cache and didn't actually have to go to the disk
Yes, just not at the same time. If you can you want to enable WAL (that requires some shared memory primitives, so same machine generally), because otherwise writes block reads
Afaik SQLite in WAL mode takes your writes but still serializes the commits, so no SQLite-specific synchronization primitives needed. You might run into SQLITE_BUSY, in which case you can play with the timeout https://www.sqlite.org/c3ref/busy_timeout.html
Another programmer who's not a tester is trying to come up with a benchmark and has no clue how to do that (and doesn't even know they need to know how to do that). Not surprisingly, it's a Python / JavaScript programmer...
That test of memory vs disk I/O performance is ridiculous. Nobody should pay attention to that.
There are massive performance gains to be had by using transactions. In other RDBMSs transactions are about atomicity/consistency. But in SQLite, transactions are about batching inserts for awesome speed gains. Use them!