3 ms·

You're not the only commenter saying this, so I gotta ask: whatever VM that SQLite DB is on, is your business cool with a business disruption, dataloss, or both

You're not the only commenter saying this, so I gotta ask: whatever VM that SQLite DB is on, is your business cool with a business disruption, dataloss, or both when that VM goes down, when that AZ goes down, or that disk fails? Or am I missing something? If you have some sort of fail-over-to-last-backup plan, is that not just a distributed database with more steps, and why not something like RDS, or CockroachDB? (From mine, it is an explicit requirement that we weather such events. I keep trying to keep global outages from our platforms off the requirements list…; so, IDK, perhaps your requirements allow different a different approach.)

alberth 5y ago
Mentioning RDS is an apples to oranges comparison. RDS is a fully managed service by a 3rd party. Nevertheless, checkout https://litestream.io https://litestream.io - given that SQLite is just a file, it’s incredibly easy to replicate. Failover is coming in the next release, which is already beta. Also checkout this Expensify blog post on how they use SQLite. It’s scales amazingly well. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... EDIT: I don’t understand, why the downvote?
- deathanatos 5y ago
  I mention RDS because it mostly solves the failover/continuity problem. While failovers on RDS are typically outages, they're also typically quite short lived, no more than a few minutes. That can work for many people/business's requirements. That it is managed means you (in theory) can rely on AWS to manage the underlying infrastructure. You just worry about the SQL. It is a "boring technology" choice, as the article that HN likes to post every now and then says. Litestream: What are the durability guarantees? (Are there any?) what would make me pick that over RDS or Cockroach? "given that SQLite is just a file, it’s incredibly easy to replicate" — sorry, that just doesn't click with me. What about a file is "inherently easy to replicate"? I would not know how to implement something that wouldn't be O(n) with the filesize. Ideally you'd just sync up changes, but how to get those? Normal DBs with streaming WALs (RDS/PG/MySQL) or Raft-based replication (Cockroach) have good answers there. Expensify: I skimmed the article, but AFAICT, the article talks about scaling QPS to a single SQLite file, but nothing about replication/durability.
  - LVB 5y ago
    "[Litestream] runs as a separate background process and continuously copies write-ahead log pages from disk to one or more replicas." - https://litestream.io/how-it-works https://litestream.io/how-it-works
  - alberth 5y ago
    Instead of just continuously firing off questions when you acknowledge you’re not even reading the articles linked too … nonetheless, bedrockdb has replication and failover. https://bedrockdb.com/ https://bedrockdb.com/
- DenseComet 5y ago
  Can you really still call what Expensify uses SQLite? It is very heavily based on SQLite, but they say that they've "wrapped it in a custom distributed transaction layer named Bedrock". Feels like they've gotten much closer to a traditional DB architecture, with the related management overhead that people try to avoid by using SQLite. http://www.bedrockdb.com/ http://www.bedrockdb.com/
libraryofbabel 5y ago
I’d wager that many of the people pushing SQLite for 95% of use cases have not been involved in running a database for a large always-on business. Just because your SQLite database can handle the traffic doesn’t mean you’ve solved robust database infrastructure. Now if they said postgres on RDS can be used for 95% of real world use cases, that I could get behind.
coryrc 5y ago
> is your business cool with a business disruption Every few years in Seattle, there's a big snowstorm, and everything just shuts down. We live. If a service goes down once in a blue moon, I could catch up my email, spend some time morale building with my team, or, god forbid, just go home because work isn't that important.
- deathanatos 5y ago
  I mean … perhaps. But it's not my call, and of the places I've worked … that's not been the business decision that gets made.
lelanthran 5y ago
> is your business cool with a business disruption, dataloss, or both when that VM goes down Most businesses seem to handle downtimes okay; some money[1] is lost, maybe. [1] And if the business feels that the expected (negative) value of the downtime is less than the cost of mitigating it, they simply (correctly) ignore it. For example, most businesses don't build a separate highway to their building in the event that a traffic jam causes fewer customers to show up. The expense is not worth the savings.
bob1029 5y ago
The customers we have today can tolerate some degree of outage. VM snapshots are usually the path. Our roadmap has replication being done at the application level (i.e. business logic is aware of other nodes and policy around replication). Those specific entities which absolutely must survive would be synchronously replicated to additional witnesses on the network. Note that this is about non-repudiation, not operational uptime. If we cared about always on operations, we would take a radically different path. Abstractly, I think the role of ensuring business data survives some incident is not something to be pushed down to the database layer. When you pull this concern into business logic, you can produce far more robust solutions.
raxxorrax 5y ago
Buy something like nimble and with the correct config your system is down less than 30 minutes if something really bad happened. And no, that is usually not a severe problem for most businesses. There is good middleware like activeMQ that can manage transactions in the meantime if your DB is on holidays. A but of planning makes you very resilient against threats.

alberth 5y ago

Mentioning RDS is an apples to oranges comparison. RDS is a fully managed service by a 3rd party. Nevertheless, checkout https://litestream.io https://litestream.io - given that SQLite is just a file, it’s incredibly easy to replicate. Failover is coming in the next release, which is already beta. Also checkout this Expensify blog post on how they use SQLite. It’s scales amazingly well. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... EDIT: I don’t understand, why the downvote?

deathanatos 5y ago
I mention RDS because it mostly solves the failover/continuity problem. While failovers on RDS are typically outages, they're also typically quite short lived, no more than a few minutes. That can work for many people/business's requirements. That it is managed means you (in theory) can rely on AWS to manage the underlying infrastructure. You just worry about the SQL. It is a "boring technology" choice, as the article that HN likes to post every now and then says. Litestream: What are the durability guarantees? (Are there any?) what would make me pick that over RDS or Cockroach? "given that SQLite is just a file, it’s incredibly easy to replicate" — sorry, that just doesn't click with me. What about a file is "inherently easy to replicate"? I would not know how to implement something that wouldn't be O(n) with the filesize. Ideally you'd just sync up changes, but how to get those? Normal DBs with streaming WALs (RDS/PG/MySQL) or Raft-based replication (Cockroach) have good answers there. Expensify: I skimmed the article, but AFAICT, the article talks about scaling QPS to a single SQLite file, but nothing about replication/durability.
- LVB 5y ago
  "[Litestream] runs as a separate background process and continuously copies write-ahead log pages from disk to one or more replicas." - https://litestream.io/how-it-works https://litestream.io/how-it-works
- alberth 5y ago
  Instead of just continuously firing off questions when you acknowledge you’re not even reading the articles linked too … nonetheless, bedrockdb has replication and failover. https://bedrockdb.com/ https://bedrockdb.com/
DenseComet 5y ago
Can you really still call what Expensify uses SQLite? It is very heavily based on SQLite, but they say that they've "wrapped it in a custom distributed transaction layer named Bedrock". Feels like they've gotten much closer to a traditional DB architecture, with the related management overhead that people try to avoid by using SQLite. http://www.bedrockdb.com/ http://www.bedrockdb.com/

libraryofbabel 5y ago

I’d wager that many of the people pushing SQLite for 95% of use cases have not been involved in running a database for a large always-on business. Just because your SQLite database can handle the traffic doesn’t mean you’ve solved robust database infrastructure. Now if they said postgres on RDS can be used for 95% of real world use cases, that I could get behind.

coryrc 5y ago

> is your business cool with a business disruption Every few years in Seattle, there's a big snowstorm, and everything just shuts down. We live. If a service goes down once in a blue moon, I could catch up my email, spend some time morale building with my team, or, god forbid, just go home because work isn't that important.

deathanatos 5y ago
I mean … perhaps. But it's not my call, and of the places I've worked … that's not been the business decision that gets made.

lelanthran 5y ago

> is your business cool with a business disruption, dataloss, or both when that VM goes down Most businesses seem to handle downtimes okay; some money[1] is lost, maybe. [1] And if the business feels that the expected (negative) value of the downtime is less than the cost of mitigating it, they simply (correctly) ignore it. For example, most businesses don't build a separate highway to their building in the event that a traffic jam causes fewer customers to show up. The expense is not worth the savings.

bob1029 5y ago

The customers we have today can tolerate some degree of outage. VM snapshots are usually the path. Our roadmap has replication being done at the application level (i.e. business logic is aware of other nodes and policy around replication). Those specific entities which absolutely must survive would be synchronously replicated to additional witnesses on the network. Note that this is about non-repudiation, not operational uptime. If we cared about always on operations, we would take a radically different path. Abstractly, I think the role of ensuring business data survives some incident is not something to be pushed down to the database layer. When you pull this concern into business logic, you can produce far more robust solutions.

raxxorrax 5y ago

Buy something like nimble and with the correct config your system is down less than 30 minutes if something really bad happened. And no, that is usually not a severe problem for most businesses. There is good middleware like activeMQ that can manage transactions in the meantime if your DB is on holidays. A but of planning makes you very resilient against threats.