18 ms·

For an app running on one VPS? Sure, this might make a decent amount of sense. But... for an app running multiple instances, the article suggests a lot of extr

For an app running on one VPS? Sure, this might make a decent amount of sense. But... for an app running multiple instances, the article suggests a lot of extra complexity. Managing that complexity makes less sense to me than just firing up a managed database server (RDS, or whatever). Even if you're self hosting, I think running a MySQL/Postgres cluster is a lot less complex than the options this article calls out. edit: and more to the point - MySQL / Postgres replication is boring. It's done by a ton of deployments, and isn't going to surprise you, at least at smaller scale.

eternityforest 3y ago
What about fully independent instances, each serving N clients, with shared stuff done as a DHT? Is there a framework for that?
- anacrolix 3y ago
  Why use a DHT if you control all the nodes?
  - eternityforest 3y ago
    If you had something that needed very easy setup but also scalability, you could do it all as a monolith and not have anything else to set up besides just the one app and your certificates, and they'd just all automatically find each other. I'm not sure how else you could do that besides a DHT without a central database or manually assigning certain pieces of data to specific servers.
starcraft2wol 3y ago
I think the excitement about SQLite comes from LAMP stack fatigue Every Wordpress site could have been built on sqlite just fine, with better security too. But beyond that, you probably want a real RDMS with auth and alter table.
- zdragnar 3y ago
  Back in the day, WordPress always ran on Apache with mod_php, correct? Wouldn't that be unsafe to use sqlite, since every request ran in a separate thread?
  - graemep 3y ago
    Separate prcoesses because php was not thread safe back then. SQLite can handle multiple processes: https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5 I am not sure it could back then
    - zdragnar 3y ago
      Writes and changes to the database itself are locked to a single process at a time, per your link. If the load is very read heavy the write locks aren't an issue, but if you're tracking page views, comments or any other write-heavy feature, it'll be harder to get good performance.
      - simonw 3y ago
        Benchmark it with WAL mode turned on. You may be pleasantly surprised.
- simonw 3y ago
  SQLite has a pretty interesting auth story using the auth callback hook. Alter table is solvable too. I built tooling for that here: https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
MoSattler 3y ago
It's actually quite easy with things like LiteFS Cloud. https://fly.io/docs/litefs/ https://fly.io/docs/litefs/
- hobofan 3y ago
  LiteFS Cloud does nothing to help with the single-writer limitation of LiteFS.
rebolek 3y ago
Honestly, how big your application needs to be to outgrow SQLite? If you can serve X customers from one server with SQLite, good for you. If you need to serve more people, you should be in a position to afford migration to something more complicated and good for you too. I don’t see a problem here. Let’s start small with the right tools and don’t fall in the premature optimization trap. You don’t need DB cluster, Kubernetes and other fancy stuff when you start your project.
- 8n4vidtmkvmk 3y ago
  Migrations can kill your business. They are no small matter. Do you really want to stop everything just as your business is gaining momentum?
MagicMoonlight 3y ago
Why do you need multiple instances? You can get like 400 cores and 8TB of RAM on one server now. People like messing around with meme features but I don’t think there’s many companies in the world using more than that.
filleokus 3y ago
Yeah. I've gotten into discussions on HN before about this. sqlite is awesome when you run it in one instance on a VM or something, but solving migration between hosts or backup via the filesystem seems like a mess. Sure LiteFS helps, but is it better than a managed networked database? > One huge benefit to SQLite is the fact that it runs as an embedded part of your application But all production workloads I've been involved with the last ≈5 years have either been containerised apps offloading state to databases/caches/blob storage etc, or have strived towards that. What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Getting the benefits I like most about sqlite locally, and not having to deal with files in production.
- eduction 3y ago
  > What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Absolutely! And also, short of that but incrementally building toward it, some sort of simplification of initial db setup and configuration, including really solid, thoughtful defaults (many of them are but I am guessing they could be substantially improved). I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. Postgres will never match that but could get closer.
  - jimbokun 3y ago
    > I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. That is an incredibly important part of the story! Spending a lot of time on configuring and monitoring and operationalizing your database can kill a business iterating on its minimum viable project.
    - icedchai 3y ago
      In production, use a managed service (RDS, Cloud SQL, tons of others...) In dev, set up a Docker container. You won't spend a lot of time on either of these things.
binwiederhier 3y ago
Your comment implies that running a database cluster is easy, or easier than what OP describes. And while I do not endorse his suggestion of using SQLite in a multi node setup, I can tell you from personal experience that running a multi-node RDMS is hard as hell. In $oldjob I designed and managed a system that was eventually running many hundreds of MySQL 3-node groups. MySQL in particular has a lot of nobs that you need to understand to set up a cluster that doesn't lose data during a fail over (semi sync, sync bin log, trx commit flush, ....). And then of course: neither MySQL nor PostgreSQL have a built-in mechanism to handle fail overs at all. It's another software stack around that, or even something custom built. Again, not saying I agree with the author. I'm just saying you're portraying it easier than it actually is.
- tcgv 3y ago
  > I can tell you from personal experience that running a multi-node RDMS is hard as hell There exist varying levels of complexity. Here at my company, we have a very small cluster comprised of a main PostgreSQL instance and four replicas. It just works and requires very low maintenance effort. But I imagine that things can get really messy if you're dealing with dozens or hundreds of instances, for sure, especially if you need sharding.
  - binwiederhier 3y ago
    > we have a very small cluster comprised of a main PostgreSQL instance and four replicas As you said, it depends on what you want: - If you are okay with losing data in the event of a failover, then a simple setup is fine. - If you want lossless failovers (i.e. not a single transaction is lost), then that's hard. - If you want entirely automatic failovers, that's also very hard. - ... My experience stems from MySQL, and not PostgreSQL, so this may or may not apply to pg. I wrote about my experience in this blog post: https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-replication-and-automated-failover/ https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-r... --- TLDR: Not losing data with MySQL is really really hard.
    - fodkodrasz 3y ago

eternityforest 3y ago

What about fully independent instances, each serving N clients, with shared stuff done as a DHT? Is there a framework for that?

anacrolix 3y ago
Why use a DHT if you control all the nodes?
- eternityforest 3y ago
  If you had something that needed very easy setup but also scalability, you could do it all as a monolith and not have anything else to set up besides just the one app and your certificates, and they'd just all automatically find each other. I'm not sure how else you could do that besides a DHT without a central database or manually assigning certain pieces of data to specific servers.

starcraft2wol 3y ago

I think the excitement about SQLite comes from LAMP stack fatigue Every Wordpress site could have been built on sqlite just fine, with better security too. But beyond that, you probably want a real RDMS with auth and alter table.

zdragnar 3y ago
Back in the day, WordPress always ran on Apache with mod_php, correct? Wouldn't that be unsafe to use sqlite, since every request ran in a separate thread?
- graemep 3y ago
  Separate prcoesses because php was not thread safe back then. SQLite can handle multiple processes: https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5 I am not sure it could back then
  - zdragnar 3y ago
    Writes and changes to the database itself are locked to a single process at a time, per your link. If the load is very read heavy the write locks aren't an issue, but if you're tracking page views, comments or any other write-heavy feature, it'll be harder to get good performance.
    - simonw 3y ago
      Benchmark it with WAL mode turned on. You may be pleasantly surprised.
simonw 3y ago
SQLite has a pretty interesting auth story using the auth callback hook. Alter table is solvable too. I built tooling for that here: https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

MoSattler 3y ago

It's actually quite easy with things like LiteFS Cloud. https://fly.io/docs/litefs/ https://fly.io/docs/litefs/

hobofan 3y ago
LiteFS Cloud does nothing to help with the single-writer limitation of LiteFS.

rebolek 3y ago

Honestly, how big your application needs to be to outgrow SQLite? If you can serve X customers from one server with SQLite, good for you. If you need to serve more people, you should be in a position to afford migration to something more complicated and good for you too. I don’t see a problem here. Let’s start small with the right tools and don’t fall in the premature optimization trap. You don’t need DB cluster, Kubernetes and other fancy stuff when you start your project.

8n4vidtmkvmk 3y ago
Migrations can kill your business. They are no small matter. Do you really want to stop everything just as your business is gaining momentum?

MagicMoonlight 3y ago

Why do you need multiple instances? You can get like 400 cores and 8TB of RAM on one server now. People like messing around with meme features but I don’t think there’s many companies in the world using more than that.

filleokus 3y ago

Yeah. I've gotten into discussions on HN before about this. sqlite is awesome when you run it in one instance on a VM or something, but solving migration between hosts or backup via the filesystem seems like a mess. Sure LiteFS helps, but is it better than a managed networked database? > One huge benefit to SQLite is the fact that it runs as an embedded part of your application But all production workloads I've been involved with the last ≈5 years have either been containerised apps offloading state to databases/caches/blob storage etc, or have strived towards that. What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Getting the benefits I like most about sqlite locally, and not having to deal with files in production.

eduction 3y ago
> What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Absolutely! And also, short of that but incrementally building toward it, some sort of simplification of initial db setup and configuration, including really solid, thoughtful defaults (many of them are but I am guessing they could be substantially improved). I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. Postgres will never match that but could get closer.
- jimbokun 3y ago
  > I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. That is an incredibly important part of the story! Spending a lot of time on configuring and monitoring and operationalizing your database can kill a business iterating on its minimum viable project.
  - icedchai 3y ago
    In production, use a managed service (RDS, Cloud SQL, tons of others...) In dev, set up a Docker container. You won't spend a lot of time on either of these things.

binwiederhier 3y ago

Your comment implies that running a database cluster is easy, or easier than what OP describes. And while I do not endorse his suggestion of using SQLite in a multi node setup, I can tell you from personal experience that running a multi-node RDMS is hard as hell. In $oldjob I designed and managed a system that was eventually running many hundreds of MySQL 3-node groups. MySQL in particular has a lot of nobs that you need to understand to set up a cluster that doesn't lose data during a fail over (semi sync, sync bin log, trx commit flush, ....). And then of course: neither MySQL nor PostgreSQL have a built-in mechanism to handle fail overs at all. It's another software stack around that, or even something custom built. Again, not saying I agree with the author. I'm just saying you're portraying it easier than it actually is.

tcgv 3y ago
> I can tell you from personal experience that running a multi-node RDMS is hard as hell There exist varying levels of complexity. Here at my company, we have a very small cluster comprised of a main PostgreSQL instance and four replicas. It just works and requires very low maintenance effort. But I imagine that things can get really messy if you're dealing with dozens or hundreds of instances, for sure, especially if you need sharding.
- binwiederhier 3y ago
  > we have a very small cluster comprised of a main PostgreSQL instance and four replicas As you said, it depends on what you want: - If you are okay with losing data in the event of a failover, then a simple setup is fine. - If you want lossless failovers (i.e. not a single transaction is lost), then that's hard. - If you want entirely automatic failovers, that's also very hard. - ... My experience stems from MySQL, and not PostgreSQL, so this may or may not apply to pg. I wrote about my experience in this blog post: https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-replication-and-automated-failover/ https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-r... --- TLDR: Not losing data with MySQL is really really hard.
  - fodkodrasz 3y ago