> “But nobody writes production applications with SQLite, right?"
We've been doing it for 5 years now. Basic tricks we employ are:
Use PRAGMA user_version for purposes of managing automatic migrations, a. la. Entity Framework. This means you can actually do one better than Microsoft's approach, because you don't need a special unicorn table to store migration info. A simple integer compared with your latest integer and executing SQL in the range is all it takes.
Use PRAGMA synchronous=NORMAL alongside PRAGMA journal_mode=WAL for maximum throughput while supporting most reasonable IT recovery concerns. If you are running your SQLite application on a VM somewhere and have RTO which is satisfied by periodic hot snapshots (which WAL is quite friendly to), this is a more than ideal way to manage recovery of all business data while also giving good throughput to writers. If you are more paranoid than we are, then you can do FULL synchronous for a moderate performance penalty. This would be more for situations where your RTO requires the exact state of the system be recoverable the moment it lost power. We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful.
For development & troubleshooting, being able to copy a .db file (even while its in use) is tremendously powerful. I can easily patch up a QA database I mangled with a bad migrator in 5 minutes by stopping the service, pulling the .db local, editing, and pushing it back up. We can also ask our customers to zip up their entire db folder so we can troubleshoot the entire system state.
Being able to use SQLite as our exclusive data store also meant that our software delivery process could be trivialized. We use zero external hosts, even localhost, for our application to be installed or started. We don't even require a runtime exist on the base operating system. Unzip our latest release build to a blank Win2019 server, sc.exe the binary path, net start the service, and it just works. Anyone can deploy our software because it's literally that simple. We didn't even bother to write a script because its a bigger pain in the ass to set powershell execution mode.
So, its not just about the core data storage, but also about the higher-order implications of choosing to use a database solution that can be wholly embedded within your application. Because of decisions like these, we don't have to screw around with things like Docker or Kubernetes.
We are able to get reads on the order of 10k/s+, and writes on the order of 5k/s+ using NVMe drives and practical serialized business object sizes (0.1~5 megabytes). I can easily saturate an NVMe drive using SQLite. In fact, it is substantially easier to max out storage devices with SQLite and carefully-tuned code than it is with something like SQL Server.
I should amend my original post, because I know a lot of developers fall into the trap of thinking that you should always do the open/close connection pattern with these databases. That is a huge trap with SQLite. If you want to add some extra zeroes to your benchmark figures, only use a single connection for accessing SQLite databases. Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId and in cases where transactional scopes are otherwise required. This alone can take you from 100 inserts/second to 10k without changing anything else.
> Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId
You mean for generating unique primary keys? Why would last insert row id be slow?
> and in cases where transactional scopes are required
LastInsertRowId is not slow, but if you are inserting on the same connection from multiple threads, you will require a mutex or you will be getting other threads' row ids.
Transactional scopes meaning scenarios like debiting one account and crediting another. This is something you can also manage with locking with application-level primitives.
Yes it is substantially worse to use multiple connections vs a single connection. This is fairly easy to test in a few lines of code.
We need to remember that opening a connection to SQLite is like opening a file on disk. Creating/destroying file handles requires far more resources and ceremony than taking out a mutex on a file that is never closed.
That doesn't sound right. SQLite's lock for writes is not the best, but it is still pthread mutex under the hood. Are you sure your compilation options for SQLite is right? One common pitfall is compiling without `-DHAVE_USLEEP`. In absence of that flag, SQLite will use sleep in case of conflict, and that will have time resolution of 1 second, causing 1s delay on every lock contention. That flag tells SQLite to use usleep instead, and it is substantially faster on busy timeout.
>We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful.
How does virtualization help with data loss? I would expect that a VM can't have guarantees better than the underlying physical hardware provides.
I yet wait to see how somebody serve 1M users on a web service using sqlite. Sounds like you can do all that since you create desktop app, you almost never need anything more then sqlite for that.
> We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful.
Can you say more about how (and which) modern virtualization technologies help? RTO is something I've never found a happy to, since piecing together any missing data is painful, but avoiding a clustered DB setup (or the cost of Aurora) is always welcome.
We've been doing it for 5 years now. Basic tricks we employ are:
Use PRAGMA user_version for purposes of managing automatic migrations, a. la. Entity Framework. This means you can actually do one better than Microsoft's approach, because you don't need a special unicorn table to store migration info. A simple integer compared with your latest integer and executing SQL in the range is all it takes.
Use PRAGMA synchronous=NORMAL alongside PRAGMA journal_mode=WAL for maximum throughput while supporting most reasonable IT recovery concerns. If you are running your SQLite application on a VM somewhere and have RTO which is satisfied by periodic hot snapshots (which WAL is quite friendly to), this is a more than ideal way to manage recovery of all business data while also giving good throughput to writers. If you are more paranoid than we are, then you can do FULL synchronous for a moderate performance penalty. This would be more for situations where your RTO requires the exact state of the system be recoverable the moment it lost power. We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful.
For development & troubleshooting, being able to copy a .db file (even while its in use) is tremendously powerful. I can easily patch up a QA database I mangled with a bad migrator in 5 minutes by stopping the service, pulling the .db local, editing, and pushing it back up. We can also ask our customers to zip up their entire db folder so we can troubleshoot the entire system state.
Being able to use SQLite as our exclusive data store also meant that our software delivery process could be trivialized. We use zero external hosts, even localhost, for our application to be installed or started. We don't even require a runtime exist on the base operating system. Unzip our latest release build to a blank Win2019 server, sc.exe the binary path, net start the service, and it just works. Anyone can deploy our software because it's literally that simple. We didn't even bother to write a script because its a bigger pain in the ass to set powershell execution mode.
So, its not just about the core data storage, but also about the higher-order implications of choosing to use a database solution that can be wholly embedded within your application. Because of decisions like these, we don't have to screw around with things like Docker or Kubernetes.