4 ms·

SQlite "merely" assumes that the problems that come with distributed systems are handled at the application layer. You'll have to solve those problems for yours

by zdkl 6y ago

SQlite "merely" assumes that the problems that come with distributed systems are handled at the application layer. You'll have to solve those problems for yourself, sure, but in practice I have rarely (I think never actually) had dataloss through a fault of sqlite. Also, did you know you can use in-memory instances (and share them across threads!) with the right incantation? And that you can backup your on-disk instance to an in-memory one, do your expensive transactions without hitting the disk then backup the modified instance right back to disk, even in-place if you want! Sqlite is amazing when you don't expect the DB to do replication or failover on its own.

krab 6y ago
There is always DRBD as well. You can make replication a lower-layer problem. Not that it's without drawbacks.
iveqy 6y ago
No I did not know that, I've looked for a long time for a way to convert a sqlite3 database to an in memory database and then back again. Do you mean that there's support in sqlite3 for this? Could you point me in the right direction?
- Multicomp 6y ago
  We both learned something new today. Looks like this is what you want in combination with using an in memory database. I've been doing a handrolled in memory cache layer to speed data access, but with this, I can just call the db directly and then periodically sync to disk, redis rdb style. Sqlite is a staggeringly good piece of technology! https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
- beagle3 6y ago
  In Python you just have to open the special file name “:memory:” to get a memory-based db. I don’t remember what the raw SQLite incantation is (or if it’s different). Also, pay attention to “ATTACH” - it’s the way to use multiple databases (file and/or memory) while still letting SQLite handle it all (e.g. join a memory db to a file db, insert result into 3rd file db - all without having to look at records in your own code)
  - folmar 6y ago
    It's the same in plain sqlite.
cm2187 6y ago
In other words it is just a regular object you serialize from time to time...

by zdkl 6y ago

krab 6y ago

There is always DRBD as well. You can make replication a lower-layer problem. Not that it's without drawbacks.

iveqy 6y ago

No I did not know that, I've looked for a long time for a way to convert a sqlite3 database to an in memory database and then back again. Do you mean that there's support in sqlite3 for this? Could you point me in the right direction?

Multicomp 6y ago
We both learned something new today. Looks like this is what you want in combination with using an in memory database. I've been doing a handrolled in memory cache layer to speed data access, but with this, I can just call the db directly and then periodically sync to disk, redis rdb style. Sqlite is a staggeringly good piece of technology! https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
beagle3 6y ago
In Python you just have to open the special file name “:memory:” to get a memory-based db. I don’t remember what the raw SQLite incantation is (or if it’s different). Also, pay attention to “ATTACH” - it’s the way to use multiple databases (file and/or memory) while still letting SQLite handle it all (e.g. join a memory db to a file db, insert result into 3rd file db - all without having to look at records in your own code)
- folmar 6y ago
  It's the same in plain sqlite.

cm2187 6y ago

In other words it is just a regular object you serialize from time to time...