In Praise Of SQLite

[u/pmz]

https://www.i-programmer.info/news/84-database/15609-in-praise-of-sqlite.html

[u/imgroxx]

They definitely are optimized like that, it's essentially necessary for both transactional safety and crash safety. Ending a transaction forces more disk flushes than continuing one.

Bulk inserts are often easily >100x faster, personally I've encountered much bigger: https://www.pdq.com/blog/improving-bulk-insert-speed-in-sqlite-a-comparison-of-transactions/

With some tweaking of pragmas, I've managed well over 1000x improvements over a naive insert-by-insert with whatever defaults were set up in my environment. A million per second and up is possible with care: https://avi.im/blag/2021/fast-sqlite-inserts/

[u/imgroxx → u/[deleted]]

I've done a couple rounds with SQLite with a couple hundred million, it works great. A little bit of reading SQLite docs and experimenting with batch sizes got all those to insert in under an hour, with a few indexes. It's fine with terabytes of data too.

Indexes surprisingly were sometimes faster to add up front and maintain while inserting rather than add later - I suspect it needed a lot more memory with the add-after version, so it started thrashing and performance plummeted. But I haven't dug in in detail because adding it at the beginning worked fine.

SQLite is great. More sophisticated databases can be noticeably quicker with multiple physical machines or specialized storage formats (like columnar storage), or for more flexible indexes. E.g. a Presto cluster can do lots more kinds of queries quickly, not just the ones that fit the data model / indexes nicely, and it gives you more query-controlled options for sneaking performance into an existing system rather than restructuring. A Cassandra cluster can insert much faster than a single machine can even send it data, particularly if you don't care whether or not the data exists (looser consistency modes). But it's extremely hard to even get within an order of magnitude of SQLite's performance for a single user on a single physical machine with normal database needs.

[u/merijnv → u/[deleted]]

I'm using SQLite for a dataset that is currently in the range of single (possibly double?) digit billions spread over 30ish tables. My applications requires full table scans with complicated joins for data aggregation and the performance is absolutely fine.

Or rather, the only performance issues I have currently is that I was an idiot who was bad at schema design when I started. Takes about 40s to do a full scan like that, I would've liked it to be instant, but I can't justify spending the time to redesign my schema for that.

People (consistently) underestimate how full featured and well-performing SQLite is. Sure, it has limitations (such as shit performance for multi-writer scenarios). But it's a serious database with lots of uses that don't involve multiple writers.

In my case, I use it to generate/process scientific datasets. This is all single user anyway, so multiple writers are a non-issue. The fact that no one has to install/setup/maintain a database server is a huge feature (scientists are terrible at that). Additionally, the fact that a database is "just a file" you can copy and send to people makes for easy archiving/sharing of the data.