I am glad to see other SQLite advocates out here.
>Many modern startups could get pretty far with SQLite + aggressive caching
Not only did we get "pretty far", we got to what is effectively a final destination for this generation of our product. We don't even need "aggressive caching" beyond what the filesystem provides to us by way of accessing the SQLite db file.
Today, we have a single SQLite database persisting stateful data for an application that is used concurrently by 100+ users. I have benchmarked 512KB state update writes in a multithreaded scenario at >500 per second. 64KB updates exceed 1000 per second. It took us a lot of time and understanding to get to this point. At the end of the day with SQLite, less is always more. The first attempts we did involved connection pools, queues and all kinds of other fancy over-engineering in an attempt to extract more performance. We finally ended up with just the following:
- Enable Write Ahead Logging
- Set Synchronous to Normal
- Use a single SQLiteConnection
Justification for a single connection: We are using .NET with application-level locking and an RWLS where appropriate. This is in contrast to relying on the database to push these transactional scopes to disk. For us, losing an in-flight transaction tracked in memory is not catastrophic so we can tolerate this 1-100ms danger zone for the huge upside we get in throughput. This is a great example of something you can only do with SQLite. No hosted database solution offers this degree of exclusivity over your data store from an application perspective.
One other application note to keep in mind is that SQLite (by default) operates in serialized mode, so the above approach is consistent regardless of the fact that we are hitting a single connection from multiple threads at once. The usages of RWLS are required in our case simply because we need to do multiple dependent sequential operations (select+modify+update) or insertion of new items (insert+last insert row id from connection). It is hypothetically possible to avoid locking altogether (aside from the implicit SQLite serialization) if you can structure your database such that you perform all operations in a single command - E.g.: Use application-generated keys upon insertion rather than waiting for the DB to generate one.
Also, backups and restores are super trivial as mentioned elsewhere in this discussion. I have considered writing a backup agent directly into our software that is transactionally-aware. E.g.: During a backup request, I can take out a write lock on the RWLS for the SQLiteConnection while copying the database to a backup destination. Alternatively, since the DB lives on the same machine as the application, you can just take a single VM snapshot throughout the day, but this won't be as consistent a backup (tolerable for our use case though).
The biggest advantage we see with using SQLite as our principal data store is that it has allowed us to completely avoid operational annoyances regarding how we actually ship our stack to various environments. .NET Self-Contained Deployments + SQLite = one bin folder that "just works". We were able to build custom deployment tooling because of the simplicity of this model, rather than relying on off-the-shelf tech that doesn't quite fit 100% into our use case.