We've been using SQLite as our principal data store for 6 years. Our application services potentially hundreds of simultaneous users at once, each pushing 1-15 megabytes of business state to/from disk 1-2 times per second.
We have not had a single incident involving performance or data integrity issues throughout this time. The trick to this success is as follows:
- Use a single SqliteConnection instance per physical database file and share it responsibly within your application. I have seen some incorrect comments in this thread already regarding the best way to extract performance from SQLite using multiple connections. SQLite (by default for most distributions) is built with serialized mode enabled, so it would be very counterproductive to throw a Parallel.ForEach against one of these.
- Use WAL. Make sure you copy all 3 files if you are grabbing a snapshot of a running system, or moving databases around after an unclean shutdown.
- Batch operations if feasible. Leverage application-level primitives for this. Investigate techniques like LMAX Disruptor and other fancy ring-buffer-like abstractions if you are worried about millions of things per second on a single machine. You can insert many orders of magnitude faster if you have an array of contiguous items you want to put to disk.
- Just snapshot the whole VM if you need a backup. This is dead simple. We've never had a snapshot that wouldn't restore to a perfectly-functional application, and we test it all the time. This is a huge advantage of going all-in with SQLite. One app, one machine, one snapshot, etc...
This is only true if you have more than one writing connection to the database. But that said, it is still a good idea to not have the database housed on a network attached store.
This might sound obvious, but it's not always apparent which applications uses SQLite.
I had lots of weird issues with Plex until I found out that uses SQLite, and moved the config directory from a shared NFS directory to a shared iSCSI volume.
> Make sure you copy all 3 files if you are grabbing a snapshot of a running system
Or use the ".backup" command in the CLI to take a clean single file snapshot if that's what you need. Or, you can call the checkpoint C API and if it can move all the transactions from WAL to DB, it will remove the WAL file when it's finished.
Yes - We have allowed maintenance windows each day.
That said, we have prototypes of architectures in which we have multiple instances running in the same production environment simultaneously, each with an independent SQLite database and some light-weight replication logic in the application itself. Simple DNS RR or customer-managed LB would be responsible for routing client traffic. There aren't a whole lot of entities that we actually need to synchronously replicate between application servers, so this is far more accessible for us to iterate on than throwing our hands up and jumping to some fully-managed always-on clustered database service and throwing away all of the lessons we've learned with SQLite.
We have not had a single incident involving performance or data integrity issues throughout this time. The trick to this success is as follows:
- Use a single SqliteConnection instance per physical database file and share it responsibly within your application. I have seen some incorrect comments in this thread already regarding the best way to extract performance from SQLite using multiple connections. SQLite (by default for most distributions) is built with serialized mode enabled, so it would be very counterproductive to throw a Parallel.ForEach against one of these.
- Use WAL. Make sure you copy all 3 files if you are grabbing a snapshot of a running system, or moving databases around after an unclean shutdown.
- Batch operations if feasible. Leverage application-level primitives for this. Investigate techniques like LMAX Disruptor and other fancy ring-buffer-like abstractions if you are worried about millions of things per second on a single machine. You can insert many orders of magnitude faster if you have an array of contiguous items you want to put to disk.
- Just snapshot the whole VM if you need a backup. This is dead simple. We've never had a snapshot that wouldn't restore to a perfectly-functional application, and we test it all the time. This is a huge advantage of going all-in with SQLite. One app, one machine, one snapshot, etc...