SQLite 3.39.2 | Hacker News Reader

SQLite 3.39.2

sqlite.org

82 pointsmarcobambini32 comments

I once experimented with using SQLite as a file store for small images (10s of Kb). We had so many images on disk, that a recursive ls command wouldn't complete in a single day.

At one point we even hit inode limits.

I tested out storing the images in sqlite files across a few directories. I also testing using tar and zip storage with the same schema. Turns out the blog post they wrote was right, and SQLite was indeed faster than our normal IO.

https://www.sqlite.org/fasterthanfs.html

> I once experimented with using SQLite as a file store for small images

Mapbox productionized this with their MBTiles [0] format, to store millions of small vector or raster map tiles in an SQLite database. Much easier to work with than millions of images on disk.

[0]: https://github.com/mapbox/mbtiles-spec

I love sqlite, but if you need just store, and even smaller library - look no further than `lmdb` - https://www.lmdb.tech/

LMDB is a really great read optimized key-value, transactional key-value store, but you have to be careful with when benchmarking memory mapped files. The problem is that a thread touching paged out memory just blocks without performing any system call. It looks like you're faster and it starts out very fast because as long as your data fits into main memory you can read it without any context switches, but as soon as you get under even slight memory pressure jitter goes through the roof and your userspace code is helpless because there is no load-if-mapped instruction to avoid blocking the thread if your accessed page has to be read in from the backing storage. Your database has no control over I/O parallelism and queue depth except very indirectly by delegating database accesses to a thread pool of a specific size and hoping for the best.

true dat, there was an excellent series from one University going over that in details - changed my opinion on the matter - https://db.cs.cmu.edu/mmap-cidr2022/

is it only me or does this site works only over plaintext http, while you gave an https link?

oh, interresting - try this - https://www.symas.com/lmdb - something happened to my chrome months ago, and now it asks me everytime to switch to secure - so it resolved it somehow on mine, but didn't know it didn't work for others

Same, curl spits out an error for me. "ST_CONNECT:tlsv1 alert protocol version"

how large is the database file? aren't backup a bit long to perform?

I genuinely don't remember what the ballpark number was, it was years ago when I did this. But I think the db was a tad larger than the raw files on disk because of the additional meta data I stored in the database for organization, but this is just a guess.

Backups of multiple millions of files will take forever as well, even just figuring out the delta based on inode data.

Maybe splitting it into 10 or 50 sqlite files would help...

Not if you use litestream

Anytime you think you need to invent a file format for your application, please save yourself the trouble and use sqlite. It'll handle consistency, searching, updating, OS-specific quirks, everything.

They even have a page talking about this, and this is the best way to think about SQLite.

https://www.sqlite.org/whentouse.html

> SQLite does not compete with client/server databases. SQLite competes with fopen()

Why do so many websites urge readers to avoid using sqlite as a production database? From what it looks like sqlite can handle a million daily users

We've been using SQLite in production (B2B setting) for almost a decade. Concurrent, multi-user systems too. Sometimes thousands of concurrent users will be hitting the same databases all at the same time. Literally not once have we had an incident related to SQLite/IO corruption or performance.

SQLite could handle a billion daily users with just one decent NVMe drive. You don't even have to do anything crazy to make this work. Just pick one of the top 10 techempower web frontends, tack on SQLite and set journal mode to WAL. Oh - try to use the same connection throughout as well. That one trips up a lot of developers used to the hosted SQL patterns.

Oh but what about high availability? DQLite, RQLite, et. al. are now ready to take you to the next level. You can also address this in your application logic instead of deferring to your RDBMS vendor (this is our path).

rqlite author here, happy to answer any questions.

This SQLite page actually tells you when it’s appropriate or inappropriate to use SQLite for a website.

https://www.sqlite.org/whentouse.html

SQLite supports high read concurrency but has to queue concurrent writers. If your writes are small and concurrency low, you may be able to get away with it but ymmv.

A couple of things come to mind. Scaling, specifically horizontally (though there are some solutions for that) and being write heavy like storing analytics. For many cases though it should work just fine for your average read heavy site.

Seems to me, it can work great if your use-cases match its strengths well. But if they ever don't, then it'll be a big mess with probably no solution but to fully switch over to a conventional RDBMS.

When I search for “sqlite sucks at” it gives some claims opposite to what commenters here say, mostly from 2000-2010. For which use cases is it not strong enough? In practice, not in theory or based on initial fopen() purpose.

Their own page here lists out the things it's not good for at the end: https://www.sqlite.org/whentouse.html

IMHO, going production also requires scalability, high availability, and tooling ecosystem (backup/restore/replication), etc, besides functionalities and performance.

I would add SQLite is missing access control, it is a big all or nothing. Usually it doesn't matter as much since the scope is so much smaller, but if you really want to back a major website with it some defense in depth would be nice.

Good point! Security and regulation are other key requirements in real world for many industries.

Agreed. In the real world actually operating the database is just as important as its intrinsic query functionality.

Yes, that's also why certified Oracle DBAs can earn $$$ for their expertise on this.

> 1. Add (long overdue) support for RIGHT and FULL OUTER JOIN.

Yay!

These features were already added in version 3.39.0.

For something used in so many places for so many reasons it's interesting to read everything they test for their releases... https://www.sqlite.org/testing.html

I was surprised to see this one given the size of the test suites. I mean, this is an incorrect result. Pretty significant bug.

> Fix an incorrect result from a query that uses a view that contains a compound SELECT in which only one arm contains a RIGHT JOIN and where the view is not the first FROM clause term of the query that contains the view

Reply on news.ycombinator.com