5 ms·

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 har

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?
    - chillfox 4y ago
      Sometimes I honestly think that the problem is that the SQLite ecosystem makes it easy to understand how things work, so people get scared. Whereas other things comes off as magic and magic is comforting, you don't need to know how it works, you can just trust it.
      - MobiusHorizons 4y ago
        It is so hard for me to imagine ever trusting magic, but I think you are very right that people seem to prefer it. I think this discrepancy explains so much of the conflict I get into in my career.
      - layla5alive 4y ago
        100% On so many topics, this is the answer. People want a pleasant fiction of clean abstracted magic.
    - ngrilly 4y ago
      > LiteFS isn't the only way to replicate a SQLite database. What are other ways to replicate that you're aware of? Another approach I like is to use a replicated block storage like GCP Regional Persistent Disks. This is making failover trivial. But then I'm not sure the replica can be used as a read-only copy.
- throwaway892238 4y ago
  Abstractions. Sometimes you need them. Different operating systems, CPU architectures, applications, clients, libraries, versions, hell different companies running proprietary apps, licensing, regulations... A database with a dumb network socket on a random host is fairly well abstracted. As far as HA goes, that abstraction can then go toward allowing your apps and DB to be built for different reliability/durability/etc requirements, potentially saving money, gaining better performance or reliability, meeting weird compliance requirements, contractual requirements, vendor or software limitations, etc. It's also easier to troubleshoot and maintain the database separate from the application in some circumstances. If SQLite is bundled with your application, it might be harder to deal with a problem affecting one or the other. In traditional organizations, you can hire people to manage databases and people to manage applications, rather than one person who has to deal with both, or even weirder, two people that would be working on one db-in-your-app.
  - tptacek 4y ago
    Sometimes this is true, and sometimes it's what CORBA advocates say you to get you to adopt object brokers. Sometimes your app benefits from an explicit networked database tier, and sometimes it doesn't. I'm fine if you think most of the time it does. I'm not fine if you think it rarely does: that's not true. There are big advantages to not having a separate database tier, and the industry is sleeping on them. (To be clear: the company I work for sells a Postgres service, and we benefit from n-tier databases! I have strong opinions here because I think this is an interesting controversy.)

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?
  - chillfox 4y ago
    Sometimes I honestly think that the problem is that the SQLite ecosystem makes it easy to understand how things work, so people get scared. Whereas other things comes off as magic and magic is comforting, you don't need to know how it works, you can just trust it.
    - MobiusHorizons 4y ago
      It is so hard for me to imagine ever trusting magic, but I think you are very right that people seem to prefer it. I think this discrepancy explains so much of the conflict I get into in my career.
    - layla5alive 4y ago
      100% On so many topics, this is the answer. People want a pleasant fiction of clean abstracted magic.
  - ngrilly 4y ago
    > LiteFS isn't the only way to replicate a SQLite database. What are other ways to replicate that you're aware of? Another approach I like is to use a replicated block storage like GCP Regional Persistent Disks. This is making failover trivial. But then I'm not sure the replica can be used as a read-only copy.
throwaway892238 4y ago
Abstractions. Sometimes you need them. Different operating systems, CPU architectures, applications, clients, libraries, versions, hell different companies running proprietary apps, licensing, regulations... A database with a dumb network socket on a random host is fairly well abstracted. As far as HA goes, that abstraction can then go toward allowing your apps and DB to be built for different reliability/durability/etc requirements, potentially saving money, gaining better performance or reliability, meeting weird compliance requirements, contractual requirements, vendor or software limitations, etc. It's also easier to troubleshoot and maintain the database separate from the application in some circumstances. If SQLite is bundled with your application, it might be harder to deal with a problem affecting one or the other. In traditional organizations, you can hire people to manage databases and people to manage applications, rather than one person who has to deal with both, or even weirder, two people that would be working on one db-in-your-app.
- tptacek 4y ago
  Sometimes this is true, and sometimes it's what CORBA advocates say you to get you to adopt object brokers. Sometimes your app benefits from an explicit networked database tier, and sometimes it doesn't. I'm fine if you think most of the time it does. I'm not fine if you think it rarely does: that's not true. There are big advantages to not having a separate database tier, and the industry is sleeping on them. (To be clear: the company I work for sells a Postgres service, and we benefit from n-tier databases! I have strong opinions here because I think this is an interesting controversy.)