4 ms·
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). Wo
by antithesis-nl 2y ago
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 2y agoI 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).
- zimpenfish 2y ago> 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 https://github.com/phiresky/sqlite-zstd
- piterrro 2y agoI 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-zstandard-vfs-in-sqlite-benchmark https://logdy.dev/blog/post/part-3-log-file-compression-with... https://logdy.dev/blog/post/part-4-log-file-compression-with-column-zstandard-in-sqlite-benchmark 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
- pinoy420 2y agoYou should use mongodb. It’s web scale
- Traubenfuchs 2y ago> My biggest SQLite database is 1.7TB with What do you run this on? Just some aws vpc with a huge disk attached?
- antithesis-nl 2y agoA 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).
- immibis 2y agoI 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.
- cm2187 2y agoHow do you backup a file like that?
- antithesis-nl 2y agoUsing 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.
- hu3 2y ago> It doesn't block any reads or writes. That's neat! I bet it keeps growing a WAL file while the backup is ongoing right?
- rco8786 2y agoHard to imagine doing it any other way, which is probably fine up until you hit some larger files sizes.
- ncruces 2y agoThat copies the entire file each time (not just deltas). You may find sqlite_rsync better.
- leosanchez 2y agosqlite_rsync is new tool created by sqlite team. It might be useful.
- homebrewer 2y agoI use zfs snapshots, they work in diffs so they're very cheap to store, create, and replicate.
- blitzar 2y agoCtrl-c, Ctrl-v
- 2y ago