Hacker News Reader

Comment by btreecat | Hacker News Reader

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

View on HN

> 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

Reply on news.ycombinator.com