4 ms·

I really appreciate what this project is going for. SQLite with practical replication gets to be pretty dangerous for other vendors in the space. It's the last

I really appreciate what this project is going for. SQLite with practical replication gets to be pretty dangerous for other vendors in the space. It's the last thing we really needed for our end game customers. Unfortunately, our org is being pushed away from SQLite because our domain is highly regulated. We have a really hard time getting something like this through technical review, especially for our up-market clients. The only thing I could defend is if this was somehow part of the first party solution and code base. I can sell SQLite to a bank if they are tolerant to single node failure semantics (I.e. vm snapshot restore). That part is no problem. Already did it half a dozen times myself. It gets tricky if they want more aggressive RPO. I cannot sell log-replicated SQLite if it relies on another, lesser-known 3rd party for that capability. I can get away with murder on my front end vendors but data vendors never seem to escape scrutiny. I want to hate SQL Server, but it's starting to grow on me. The knowledge that I won't have to argue with a hundred+ CTO/CIOs is starting to win out over the principled nerd things. Replicating SQLite feels like a cursed thing for many contexts - If someone isn't happy with periodic snapshots, then the nature of their business is probably pretty serious and they probably want deeper (perhaps legal) guarantees about how things would go.

xrd 3y ago
Really fascinated by your comments. So, running litestream on the node and pushing to S3 isn't a good story, eh? I would have assumed that everything in a single AWS tenant could be a viable path.
- bob1029 3y ago
  > So, running litestream on the node and pushing to S3 isn't a good story, eh? I think it's a fantastic story. But, once you bring people who are responsible for billions of dollars worth of assets and PII to the party, things get a bit tricky. In these settings, I cannot make the argument against the incumbent hosted RDBMS providers if we need a geo replica for business continuity purposes. Hypothetically I could push for it and maybe force it through, but then I'd be left with not very much political capital to force the next thing. I'd also have to be a little bit dishonest about certain aspects and would on the hook for something that not as many engineers could assist me with. Reliable persistence of data is but one small problem for us and I won't die on this hill anymore. Simply getting everything into the same tenant is table stakes. The hard parts are reducing the # of moving pieces (complexity) and 3rd party vendor count. Again, for many domains I think litestream can be an easy win. Gaming, AI/LLM state tracking, lesser-regulated SaaS, more flexible RTO, etc. No reason to pay (with money or time) for something more sophisticated than dumping WAL in S3 if you genuinely don't need it.
  - xrd 3y ago
    This sentence rings so true: > Reliable persistence of data is but one small problem for us and I won't die on this hill anymore. Simply getting everything into the same tenant is table stakes. The hard parts are reducing the # of moving pieces (complexity) and 3rd party vendor count. Yep. It is sad that the perception will be that MS SQL is more simple or Postgres is more simple (I love postgres, but just adding in a network means a lot of potential issues) than SQLite and WAL. It feels like the argument is really: familiar complexity vs, unfamiliar complexity. And, unfamiliar complexity will never win out no matter how much better the true complexity story is, especially in enterprise domains.
Scorpiion 3y ago
Hi bob1029, very interesting to hear about your experiences and point of view. Easy to forget the impact of how easy it's to sell an end product/service based on your selection of database (it does not always matter of course). I have thought about this as well, no one (?) will question if your app/service uses postgres but if you say sqlite I assume there will be some more questions. Would you mind sharing some numbers of what you consider an "aggressive RPO", and what RPO number sqlite+litestream can still handle? Of course, it will depend on the use case, but if you would do a rough estimate. I think it could be helpful for a lot of readers.
- bob1029 3y ago
  For our product, I'd consider an acceptable RPO for our most stringent customer to be no more than 5 seconds of committed data loss under a catastrophic scenario (i.e. primary log writer wiped off face of planet instantly). The reason I say 5 seconds and not 0 seconds is because performance is also a feature, and we don't want to shoot our other foot off if blocking/synchronous log replication happens to get stuck for a few minutes. A region-ending event is orders of magnitude more rare than a fiber cut, so an argument can be made for async replication. There are other bank systems that do utilize hard, synchronous replication semantics (hopefully for obvious reasons), but they can afford to sacrifice performance and availability during business hours (more than we can). We always defer to the records in these systems. We can tolerate a few bits of work getting out of sync and needing to be recovered in the back office. Mopping up a little bit of extra trash after the apocalypse is acceptable to all involved. We cannot tolerate all users being blocked for more than a few minutes, nor can we tolerate a de-sync of aggregate system state exceeding the same. Anything beyond this and we are at risk of having to wipe all work and disrupt end customer activity to keep ops and support teams from crashing.
  - Scorpiion 3y ago
    Thanks a lot for sharing bob1029, nice explanation and justifications, much appreciated!
  - DANmode 3y ago
    Someone who understands the careful balance to strike, when data availability is actually the most important factor. I'm in a very similar situation in my industry. Thanks for your words.

xrd 3y ago

Really fascinated by your comments. So, running litestream on the node and pushing to S3 isn't a good story, eh? I would have assumed that everything in a single AWS tenant could be a viable path.

bob1029 3y ago
> So, running litestream on the node and pushing to S3 isn't a good story, eh? I think it's a fantastic story. But, once you bring people who are responsible for billions of dollars worth of assets and PII to the party, things get a bit tricky. In these settings, I cannot make the argument against the incumbent hosted RDBMS providers if we need a geo replica for business continuity purposes. Hypothetically I could push for it and maybe force it through, but then I'd be left with not very much political capital to force the next thing. I'd also have to be a little bit dishonest about certain aspects and would on the hook for something that not as many engineers could assist me with. Reliable persistence of data is but one small problem for us and I won't die on this hill anymore. Simply getting everything into the same tenant is table stakes. The hard parts are reducing the # of moving pieces (complexity) and 3rd party vendor count. Again, for many domains I think litestream can be an easy win. Gaming, AI/LLM state tracking, lesser-regulated SaaS, more flexible RTO, etc. No reason to pay (with money or time) for something more sophisticated than dumping WAL in S3 if you genuinely don't need it.
- xrd 3y ago
  This sentence rings so true: > Reliable persistence of data is but one small problem for us and I won't die on this hill anymore. Simply getting everything into the same tenant is table stakes. The hard parts are reducing the # of moving pieces (complexity) and 3rd party vendor count. Yep. It is sad that the perception will be that MS SQL is more simple or Postgres is more simple (I love postgres, but just adding in a network means a lot of potential issues) than SQLite and WAL. It feels like the argument is really: familiar complexity vs, unfamiliar complexity. And, unfamiliar complexity will never win out no matter how much better the true complexity story is, especially in enterprise domains.

Scorpiion 3y ago

Hi bob1029, very interesting to hear about your experiences and point of view. Easy to forget the impact of how easy it's to sell an end product/service based on your selection of database (it does not always matter of course). I have thought about this as well, no one (?) will question if your app/service uses postgres but if you say sqlite I assume there will be some more questions. Would you mind sharing some numbers of what you consider an "aggressive RPO", and what RPO number sqlite+litestream can still handle? Of course, it will depend on the use case, but if you would do a rough estimate. I think it could be helpful for a lot of readers.

bob1029 3y ago
For our product, I'd consider an acceptable RPO for our most stringent customer to be no more than 5 seconds of committed data loss under a catastrophic scenario (i.e. primary log writer wiped off face of planet instantly). The reason I say 5 seconds and not 0 seconds is because performance is also a feature, and we don't want to shoot our other foot off if blocking/synchronous log replication happens to get stuck for a few minutes. A region-ending event is orders of magnitude more rare than a fiber cut, so an argument can be made for async replication. There are other bank systems that do utilize hard, synchronous replication semantics (hopefully for obvious reasons), but they can afford to sacrifice performance and availability during business hours (more than we can). We always defer to the records in these systems. We can tolerate a few bits of work getting out of sync and needing to be recovered in the back office. Mopping up a little bit of extra trash after the apocalypse is acceptable to all involved. We cannot tolerate all users being blocked for more than a few minutes, nor can we tolerate a de-sync of aggregate system state exceeding the same. Anything beyond this and we are at risk of having to wipe all work and disrupt end customer activity to keep ops and support teams from crashing.
- Scorpiion 3y ago
  Thanks a lot for sharing bob1029, nice explanation and justifications, much appreciated!
- DANmode 3y ago
  Someone who understands the careful balance to strike, when data availability is actually the most important factor. I'm in a very similar situation in my industry. Thanks for your words.