6 ms·

I’m not familiar with MySQL specifically, but people worried about HA have standby replicas. Enterprise DBs make this almost trivial to do and it’s very possibl

I’m not familiar with MySQL specifically, but people worried about HA have standby replicas. Enterprise DBs make this almost trivial to do and it’s very possible with PG extensions. I’m sure someone has built a system like that with SQLite, but it’s much less ideal than other database systems.

snovv_crash 4y ago
What is the point of having a HA database if your frontend app isn't running to be able to query it? SQLite gives the guarantee that if your frontend app is running, then the DB is available as well. Try doing that with a non-embedded DB.
- anonymousDan 4y ago
  The point of disaggregating your replicated database is you can scale your app and db tiers independently. Typically you only want to have a handful of database replicas (e.g. to failover after your primary fails), especially if you want strongly consistent replication. But your stateless app tier may require many more servers.
tptacek 4y ago
Exactly what is "less ideal" about it? SQLite has distinct advantages over n-tier databases: it's embedded, so you're not round-tripping to the network for fetches, which makes simple query patterns that are untenable for things like Postgres reasonable for SQLite. It's also, of course, faster (about as fast as you can possibly get) for some access patterns.
- beoberha 4y ago
  We’re not talking about latency though. The argument is around the HA story. Totally agree that if your app and DB are on the same machine and if HA isn’t a hard requirement, SQLite is probably what you should go to before PG. However, if you can’t have any downtime, you’d have to build some bespoke solution to make sure you have standby replicas ready to go if there’s an issue on the primary instance. For a lot of enterprises, that’s a total nonstarter.
  - chillfox 4y ago
    "you’d have to build some bespoke solution" Or you could use one of the many off the shelf solutions that has sprung up over the last few years.
  - tptacek 4y ago
    We're going around in circles. You can replicate SQLite. What makes replicated SQLite "less ideal" than n-tier databases?
    - kthejoker2 4y ago
      Just playing devil's advocate (I don't have much of a dog in the fight): * SQLite's replication isn't built in, you have to use another library (LiteStream, LiteFS, etc) to achieve it. That in itself is an indication it's not inherently designed for such, and at a minimum will require a lot more testing to ensure your HA/DR failover scenarios work like you're envisioning. Perception matters. * Litestream, LiteFS today are in "beta mode", fairly DIY, mostly CLI and YAML configs, making it comparatively complex and error-prone to configure vs. most n-tier databases which offer OOTB replication via GUI, and it's usually just a checkbox with cloud PaaS databases. * "No one ever got fired for choosing IBM" there are tons of blogs and walkthroughs of setting up HA/DR for all the major RDBMSes, it's a safe choice if you run into any issues, whereas you might find yourself deep in an obscure dev forum debugging Docker dependencies or networking protocols. It's a risk. * Needless to say, commercial support also matters. * On LiteStream's own tips page ( https://litestream.io/tips/ https://litestream.io/tips/ ) there's a subheader called "Data Loss Window", if I were competing in any way with this solution, I think I'd bring that up pretty early. Broadly, I think it'd be fine for a lightly written, mostly read OLTP system that's relatively stable. Anything else and I might get itchy about relying on just the WAL for replication. SQLite fills a great ethos of "no muss, no fuss" but replication a lot times is 100% fuss.
      - tptacek 4y ago
        The comment I replied to upthread was also talking about n-tier replication features that weren't built in. LiteFS isn't the only way to replicate a SQLite database. Do you want Oracle servers? Because "Nobody got fired for choosing IBM" is how you get Oracle servers. If you read just one additional sentence in to the Litestream "Data Loss Window" section, you'll find: "This is how many replication tools work including PostgreSQL’s log-shipping replication". I don't know what you mean by "relying on just the WAL". The WAL is the database. What else would you replicate? How are you contrasting this against Postgres replication?

snovv_crash 4y ago

What is the point of having a HA database if your frontend app isn't running to be able to query it? SQLite gives the guarantee that if your frontend app is running, then the DB is available as well. Try doing that with a non-embedded DB.

anonymousDan 4y ago
The point of disaggregating your replicated database is you can scale your app and db tiers independently. Typically you only want to have a handful of database replicas (e.g. to failover after your primary fails), especially if you want strongly consistent replication. But your stateless app tier may require many more servers.

tptacek 4y ago

Exactly what is "less ideal" about it? SQLite has distinct advantages over n-tier databases: it's embedded, so you're not round-tripping to the network for fetches, which makes simple query patterns that are untenable for things like Postgres reasonable for SQLite. It's also, of course, faster (about as fast as you can possibly get) for some access patterns.

beoberha 4y ago
We’re not talking about latency though. The argument is around the HA story. Totally agree that if your app and DB are on the same machine and if HA isn’t a hard requirement, SQLite is probably what you should go to before PG. However, if you can’t have any downtime, you’d have to build some bespoke solution to make sure you have standby replicas ready to go if there’s an issue on the primary instance. For a lot of enterprises, that’s a total nonstarter.
- chillfox 4y ago
  "you’d have to build some bespoke solution" Or you could use one of the many off the shelf solutions that has sprung up over the last few years.
- tptacek 4y ago
  We're going around in circles. You can replicate SQLite. What makes replicated SQLite "less ideal" than n-tier databases?
  - kthejoker2 4y ago
    Just playing devil's advocate (I don't have much of a dog in the fight): * SQLite's replication isn't built in, you have to use another library (LiteStream, LiteFS, etc) to achieve it. That in itself is an indication it's not inherently designed for such, and at a minimum will require a lot more testing to ensure your HA/DR failover scenarios work like you're envisioning. Perception matters. * Litestream, LiteFS today are in "beta mode", fairly DIY, mostly CLI and YAML configs, making it comparatively complex and error-prone to configure vs. most n-tier databases which offer OOTB replication via GUI, and it's usually just a checkbox with cloud PaaS databases. * "No one ever got fired for choosing IBM" there are tons of blogs and walkthroughs of setting up HA/DR for all the major RDBMSes, it's a safe choice if you run into any issues, whereas you might find yourself deep in an obscure dev forum debugging Docker dependencies or networking protocols. It's a risk. * Needless to say, commercial support also matters. * On LiteStream's own tips page ( https://litestream.io/tips/ https://litestream.io/tips/ ) there's a subheader called "Data Loss Window", if I were competing in any way with this solution, I think I'd bring that up pretty early. Broadly, I think it'd be fine for a lightly written, mostly read OLTP system that's relatively stable. Anything else and I might get itchy about relying on just the WAL for replication. SQLite fills a great ethos of "no muss, no fuss" but replication a lot times is 100% fuss.
    - tptacek 4y ago
      The comment I replied to upthread was also talking about n-tier replication features that weren't built in. LiteFS isn't the only way to replicate a SQLite database. Do you want Oracle servers? Because "Nobody got fired for choosing IBM" is how you get Oracle servers. If you read just one additional sentence in to the Litestream "Data Loss Window" section, you'll find: "This is how many replication tools work including PostgreSQL’s log-shipping replication". I don't know what you mean by "relying on just the WAL". The WAL is the database. What else would you replicate? How are you contrasting this against Postgres replication?