4 ms·

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 s

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.

gigatexal 7y ago
Yeah... many readers but only one writer at a given time. We got around that by doing a SQLite db per user.
- ramraj07 7y ago
  Once conceived of a plan to store a lot of data per user and thought of the same idea. How do you store the dbs? Keeping it on say s3 means there's a multisecond (or longer) load time when a user logs in and you need to load the DB to the hard drive right? (I'm thinking a GB or more data per user).I considered an ec2 instance with a multi terabyte ebs attached, that can then effectively store a thousand users: data. Are there any other possibilities?
  - gigatexal 7y ago
    Our DBs are tiny for most users. We run s3 (minio rook ceph on k8s) locally so the network latency is a cluster latency. 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.
    - xyzzy_plugh 7y ago
      Put sqlite in redis.
      - gigatexal 7y ago
        Got a link or more details?
        gigatexal 7y ago
        Could be this? https://grisha.org/blog/2013/05/29/sqlite-db-stored-in-a-redis-hash/ https://grisha.org/blog/2013/05/29/sqlite-db-stored-in-a-red...
        xyzzy_plugh 7y ago
        I've never done it, but you can store binary blobs in redis, and a sqlite db is a binary blob.
- contingencies 7y ago
  This is called sharding[0] in the database world and can be done in many ways. It is particularly easy to achieve in sqlite because of the literally tiny overhead in terms of storing, shifting, duplicating and moving around sqlite databases relative to fully-fledged RDBS. [0] https://en.wikipedia.org/wiki/Sharding https://en.wikipedia.org/wiki/Sharding
hardwaresofton 7y ago
> - Lost decimal places on a simple database round-trip. 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 https://sqlite.org/lockingv3.html [1]: https://sqlite.org/wal.html https://sqlite.org/wal.html [2]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
biggestdecision 7y ago
Interesting that you had issues with floating point precision, what type were you using inside your application? SQLite's floating point type (REAL) is 64 bit.
e12e 7y ago
Interesting. How has your experience been with firebird?

gigatexal 7y ago

Yeah... many readers but only one writer at a given time. We got around that by doing a SQLite db per user.

ramraj07 7y ago
Once conceived of a plan to store a lot of data per user and thought of the same idea. How do you store the dbs? Keeping it on say s3 means there's a multisecond (or longer) load time when a user logs in and you need to load the DB to the hard drive right? (I'm thinking a GB or more data per user).I considered an ec2 instance with a multi terabyte ebs attached, that can then effectively store a thousand users: data. Are there any other possibilities?
- gigatexal 7y ago
  Our DBs are tiny for most users. We run s3 (minio rook ceph on k8s) locally so the network latency is a cluster latency. 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.
  - xyzzy_plugh 7y ago
    Put sqlite in redis.
    - gigatexal 7y ago
      Got a link or more details?
      - gigatexal 7y ago
        Could be this? https://grisha.org/blog/2013/05/29/sqlite-db-stored-in-a-redis-hash/ https://grisha.org/blog/2013/05/29/sqlite-db-stored-in-a-red...
        xyzzy_plugh 7y ago
        I've never done it, but you can store binary blobs in redis, and a sqlite db is a binary blob.
contingencies 7y ago
This is called sharding[0] in the database world and can be done in many ways. It is particularly easy to achieve in sqlite because of the literally tiny overhead in terms of storing, shifting, duplicating and moving around sqlite databases relative to fully-fledged RDBS. [0] https://en.wikipedia.org/wiki/Sharding https://en.wikipedia.org/wiki/Sharding

hardwaresofton 7y ago

> - Lost decimal places on a simple database round-trip. 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 https://sqlite.org/lockingv3.html [1]: https://sqlite.org/wal.html https://sqlite.org/wal.html [2]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

biggestdecision 7y ago

Interesting that you had issues with floating point precision, what type were you using inside your application? SQLite's floating point type (REAL) is 64 bit.

e12e 7y ago

Interesting. How has your experience been with firebird?