[antithesis-nl]

Yup, they win. My biggest SQLite database is 1.7TB with, as of just now 2314851188 records (all JSON documents with a few keyword indexes via json_extract).

Works like a charm, as in: the web app consuming the API linked to it returns paginated results for any relevant search term within a second or so, for a handful of concurrent users.

[k_bx]

I think FS-level compression would be a perfect match. Has anyone tried it successfully on large SQLite DBs? (I tried but btrfs failed to do so, and I didn't get to the bottom of why).

[piterrro]

I did a small benchmark for compression with VFS and column compression a while ago: https://logdy.dev/blog/post/part-3-log-file-compression-with... https://logdy.dev/blog/post/part-4-log-file-compression-with... It all depends on the use cases and read/write patterns. Imo if well designed could yield added value

[zimpenfish]

> I think FS-level compression would be a perfect match. Has anyone tried it successfully on large SQLite DBs?

I've had decent success with `sqlite-zstd`[0] which is row-level compression but only on small (~10GB) databases. No reason why it couldn't work for bigger DBs though.

[0] https://github.com/phiresky/sqlite-zstd

[Traubenfuchs]

> My biggest SQLite database is 1.7TB with

What do you run this on? Just some aws vpc with a huge disk attached?

[immibis]

I can see that you're a user of AWS. Check some prices on dedicated servers one day. They're an order of magnitude cheaper than similar AWS instances, and more powerful because all compute and storage resources are local and unshared.

They do have a higher price floor, though. There are no $5/month dedicated servers anywhere - the cheapest is more like $40. There are $5/month virtual servers outside of AWS which are cheaper and more powerful than $5/month AWS instances.

[antithesis-nl]

A Windows Server VM on a self-hosted Hyper-V box, which has a whole bunch of 8TB NVMe drives; this VM has a 4TB virtual volume on one of those (plus a much smaller OS volume on another).

[cm2187]

How do you backup a file like that?

[antithesis-nl]

Using the SQLite backup API, which pretty much corresponds to the .backup CLI command. It doesn't block any reads or writes, so the performance impact is minimal, even if you do it directly to slow-ish storage.

[homebrewer]

I use zfs snapshots, they work in diffs so they're very cheap to store, create, and replicate.

[leosanchez]

sqlite_rsync is new tool created by sqlite team. It might be useful.

[blitzar]

Ctrl-c, Ctrl-v

[pinoy420]

You should use mongodb. It’s web scale

[teddyh]

(References <https://www.youtube.com/watch?v=b2F-DItXtZs>)

[bborud]

Someone might take that as advice.