We have started a project with SQLite as the data store, but ended up replacing it with Firebird embedded due to two main reasons:
- Lost decimal places on a simple database round-trip.
- Issues with accessing the DB from multiple threads.
- Lost decimal places on a simple database round-trip.
- Issues with accessing the DB from multiple threads.
This seems like a misunderstanding of the types that SQLite was able to store natively and which one was right for your use-case. While SQLite certainly has less options out of the box than a larger database has for types, this seems like it could happen in another database as well with a wrongly chosen type (ex. integer vs bigint in postgres).
> - Issues with accessing the DB from multiple threads.
How SQLite acts under multiple thread access is well documented[0]. You can even get closer to bigger systems by using WAL mode[1]. The fact that SQLite isn't the best for concurrent writes is also discussed in the page on when to use SQLite[2]:
> Many concurrent writers? → choose client/server
> If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.
> SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will.
A bit of a reach but I'm also willing to bet that the architectural pattern that you were using that required multiple threads to write to the DB at the same time might benefit from passing that responsibility to a single thread which could possibly employ some batching, which is normally one of the low hanging fruits of DB write performance tuning.
[0]: https://sqlite.org/lockingv3.html
I figure you could just throw hardware at it like you mentioned. Move them to nvme backed S3 if needed.
And our use case is only ever load, do a read or write, and then save. So they DBs aren’t open for very long.
And with S3 compression you could save on download time but pay a decompress cost.
This approach has its downsides don’t get me wrong but it scales nicely but forget running aggregates across the databases at least not for a real-time result.