4 ms·

Been feeling a little miffed about this recently. Litestream is excellent but if you have multiple writers your db gets corrupted. Quite easy to do with rolling

by maxmcd 3y ago

Been feeling a little miffed about this recently. Litestream is excellent but if you have multiple writers your db gets corrupted. Quite easy to do with rolling deploys. LifeFS was announced and is intended to help this. Now seems like (https://fly.io/docs/litefs/getting-started-fly/ https://fly.io/docs/litefs/getting-started-fly/) it requires an HTTP proxy so that the application can guess about sqlite write/read usage by reading the HTTP request method. This seems... to introduce a different (maybe better?) set of gotchas to navigate. There are now SQLite cloud offerings but you pay the network overhead and avoiding that was so much of the appeal of using SQLite. Are people successfully using SQLite in a work or production setting with a replication and consistency strategy that they like? I've had trouble getting a setup to the point where I can recommend it for use at my jarb.

capableweb 3y ago
I've had success in a production capacity with using rqlite before. There are also a bunch of other alternatives that still seem to be actively maintained, although I've only used rqlite myself before: - https://github.com/canonical/dqlite https://github.com/canonical/dqlite - https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite - https://github.com/Expensify/Bedrock https://github.com/Expensify/Bedrock
morelisp 3y ago
> Litestream is excellent but if you have multiple writers your db gets corrupted. Isn't this not only well-documented, but (restricting to a single writer to avoid distributed systems issues while still making it easy to move that single writer around) sort of the whole point?
liveoneggs 3y ago
Do you use https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi... ?
bob1029 3y ago
> if you have multiple writers Our strategy is to not attempt replication at the level of SQLite. We use a single binary for our SaaS product which shares 1 SQLiteConnection instance for the lifetime of the whole ordeal. Remember - every single SQLite connection instance is a file system abstraction, not some in-memory/networking clever optimized thing that Postgres or SQL Server is managing on your behalf. Every time you open a new connection to SQLite you are doing some pretty heavy-duty OS calls, relative to just reusing a prior connection. SQLite itself is typically built with serialization on by default, which deals with multiple threads on one connection. In my experience, this is the most stable & performant arrangement (with WAL, et. al. also enabled). Our backup solution is to snapshot the entire VM (or block storage device) that SQLite is running on. Replication is not a concern because our restore strategy is to just bring back a snapshot if required. Our customers are ultimately responsible for this and typically handle it with a few clicks through AWS, Azure or a quick email to their private cloud provider. RPO and RTO is entirely in their court and all parties prefer it this way - them being highly-regulated banks and us being a small startup operating at the edge of the abyss. To this day, we have not once had to support recovery of a SQLite database from snapshot due to corruption or other weirdness. We've been at it for half a decade now.
benbjohnson 3y ago
Author here. The single-node restriction for Litestream was one of the main reasons we started LiteFS. There isn't a way to handle streaming backup from multiple nodes with Litestream & S3 as SQLite is a single-writer system and there aren't any coordination primitives available with S3. I agree that many of the SQLite cloud offerings introduce the same network overhead. With LiteFS, the goal is to have the data on the application node so you can avoid the network latency for most requests. Writes still need to go to the primary so that's unavoidable but read requests can be served directly from the replica. The LiteFS HTTP proxy was introduced as an easy way to have LiteFS manage consistency transparently so you can get read-your-writes consistency on replicas and strict serializability on the primary. That level of consistency works for a lot of applications but if you need stronger guarantees then there's usually trade-offs to be made.

by maxmcd 3y ago

capableweb 3y ago

I've had success in a production capacity with using rqlite before. There are also a bunch of other alternatives that still seem to be actively maintained, although I've only used rqlite myself before: - https://github.com/canonical/dqlite https://github.com/canonical/dqlite - https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite - https://github.com/Expensify/Bedrock https://github.com/Expensify/Bedrock

morelisp 3y ago

> Litestream is excellent but if you have multiple writers your db gets corrupted. Isn't this not only well-documented, but (restricting to a single writer to avoid distributed systems issues while still making it easy to move that single writer around) sort of the whole point?

liveoneggs 3y ago

Do you use https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi... ?

bob1029 3y ago

> if you have multiple writers Our strategy is to not attempt replication at the level of SQLite. We use a single binary for our SaaS product which shares 1 SQLiteConnection instance for the lifetime of the whole ordeal. Remember - every single SQLite connection instance is a file system abstraction, not some in-memory/networking clever optimized thing that Postgres or SQL Server is managing on your behalf. Every time you open a new connection to SQLite you are doing some pretty heavy-duty OS calls, relative to just reusing a prior connection. SQLite itself is typically built with serialization on by default, which deals with multiple threads on one connection. In my experience, this is the most stable & performant arrangement (with WAL, et. al. also enabled). Our backup solution is to snapshot the entire VM (or block storage device) that SQLite is running on. Replication is not a concern because our restore strategy is to just bring back a snapshot if required. Our customers are ultimately responsible for this and typically handle it with a few clicks through AWS, Azure or a quick email to their private cloud provider. RPO and RTO is entirely in their court and all parties prefer it this way - them being highly-regulated banks and us being a small startup operating at the edge of the abyss. To this day, we have not once had to support recovery of a SQLite database from snapshot due to corruption or other weirdness. We've been at it for half a decade now.

benbjohnson 3y ago

Author here. The single-node restriction for Litestream was one of the main reasons we started LiteFS. There isn't a way to handle streaming backup from multiple nodes with Litestream & S3 as SQLite is a single-writer system and there aren't any coordination primitives available with S3. I agree that many of the SQLite cloud offerings introduce the same network overhead. With LiteFS, the goal is to have the data on the application node so you can avoid the network latency for most requests. Writes still need to go to the primary so that's unavoidable but read requests can be served directly from the replica. The LiteFS HTTP proxy was introduced as an easy way to have LiteFS manage consistency transparently so you can get read-your-writes consistency on replicas and strict serializability on the primary. That level of consistency works for a lot of applications but if you need stronger guarantees then there's usually trade-offs to be made.