Does SQLite have perf tools these days? I got bit badly by SQLite perf a decade ago (a bad default tanked performance and took a week of autotools, profiling, and mucking around in source to fix). Now I just use Postgres whenever I need anything resembling performance or scale. A decade is a long time, though -- do the tools exist now?
If not, some jobs will benefit from a better perf insurance policy than "I could probably get this into a profiler if I needed to."
Also if you want things like advanced datatypes or indexes, or features sqlite doesn't have (of which there are many, though in the last few years the support for "advanced" SQL has really taken a leap forward making it much more enjoyable).
Anyone looking at hand-rolled disks on files isn't needing anything sqlite won't give them. + getting people comfortable with sql & then migrating from sqlite to postgres isn't a bad tradeoff.
It becomes necessary at some (low) value of concurrent write usage basically. Or if you want to lean on logic built into one of the "proper" RDBMS (GIS, granular access managment, ...).
I often see the discussed, but I've not seen any benchmarks testing throughput of concurrent writes in SQLite compared to Postgres, so it's hard to know exactly how far WAL mode make it scale before it's simply the wrong tool for the job. I'm quite curious to know, so at some point I'll get around to doing my own comparison. If you are able to give some indication then that would be very handy. I also think it can be made to scale much further if you have a Multi-Tenant architecture where you essentially do a database per tenant, though that has it's drawbacks along other dimensions (namely schema changes). At some point it's going to come down to disk throughput.
SQLite will not scale writes anywhere near Postgres as there can still only be one writer at a time.
However much of the lore around writes remains from the “rwlock” mode where writers would block not just other writers but other readers as well.
That would kill your system at very low write throughputs, since it would stop the system entirely for however long it took for the write transaction to complete.
If the system is concurrent-writes-heavy it remains an issue (since they’ll be serialised)
What is considered a "very low write throughput"? Are we talking 10s of transactions per second? 100s of transactions per section? 1000s of transactions per second? 10,000s of transactions per second?
https://stackoverflow.com/questions/35804884/sqlite-concurre... has some pretty interesting numbers in it. Particularly the difference between using WAL mode vs not. The other aspect to consider is that the concurrent writes limitations apply per database file. Presumably having a database file per tenant improves aggregate concurrent write throughput across tenants? Though at the end of the day the raw disk itself will become a bottleneck, and at least the sqlite client library I'm using does state that it can interact with many different database files concurrently, but that it does have limitations to around ~150 database files. Again, depending on the architecture you can queue up writes so they aren't happening concurrently. I'd be very interested to know given all the possible tools/techniques you could throw at it just how far you could truly push it and would that be woefully underpowered for a typical OLTP system, or would it absolutely blow people's minds at how much it could actually handle?
When it comes to SQLite performance claims I tend to see two categories of people that make quite different claims. One tends to be people who used it without knowing its quirks, got terrible performance out of it as a result, and then just wrote it off after that. The other tends to be people who for some reason or other learned the proper incantations required to make it perform at it's maximum throughput and were generally satisfied with its performance. The latter group appear to be people who initially made most of the same mistakes as the former, but simply stuck it out long enough to figure it out like this guy: https://www.youtube.com/watch?v=j7WnQhwBwqA&ab_channel=Xamar...
I'm building an app using SQLite at the moment, but am not quite up to the point in the process where I've got the time to spent swapping it out for Postgres and then benchmarking the two, but I dare say I will, as I have a hard time trusting a lot of the claims people make about SQLite vs Postgres and I'm mainly doing it as a learning exercise.
