If the application only runs on one server, then definitely SQLite. That means you do not need fail over, replication or other fancy features. Why not simple SQLite?
A complex system is more likely to have problems. Network, TLS, runtime, your application may have so many reasons to crash. But for SQLite, just check IO. If it does not have problem, then you check your application.
I agree. So many usecases require a bounded context, and even ephemeral data stores. Memory caches are all the rage but the cost of doing network calls to fetch data from a memory cache can eclipse the cost of simply fetching the data from a local disk.
A traditional DBMS is a quite complex application, sometimes using that may introduce more bugs than your application itself.
If you decide to put your application on a single server, that means you do not care about the single point failure, and all your workload can be handled by the single server. So you can just run only one instance of your application, then you will not have such kind of problem.
Even if you want to have multiple instances of your application running on the same machine, SQLite can also handle that.
> what if you have separate services that need to access the same DB?
That means one single server cannot handle the workload. If the bottleneck is in the database module, a cluster is required to process the data. Clearly the SQLite is not a good option in this case.
If it is not that case, you can separate the database module and provide a lightweight wrapper around the SQLite to create a database service. And use multiple instances for calculation, then call the single database service instance for persistance.
I think comparing to the single instance of MySQL/Postgres/SQL Server, the performance of SQLite is not too bad. So we should keep the architecture simple, if possible.
But once you're at 100% of a core, then there aren't many orders of magnitude between that and "uses every core". In fact, the impact from not using a scripting language is on the same order as the threading would be! So, if it's worth spending effort squeezing out those last 1-2 orders of magnitude from the CPU, then it's probably worth thinking about the language as well.
If you've already blown through 4-5 OOMs going to a full core, then chances are you'll need it.
Of course, if you experience scaling issues there (you probably won’t if it’s anything less than enterprise-level usage), you can always just add a second db file!