6 ms·

Why use Postgres if all you need is sqlite? Postgres is way overkill for a simple app with few users and no advanced database functionality.

TemptedMuse 1y ago
I'd argue that anything larger than a desktop app should not use SQLite. If you need Litestream for replication and backup it is probably better to just use Postgres. There are a ton of one-click deployment offerings for proper databases, Fly.io actually offers managed Postgres.
- victorbjorklund 1y ago
  Why would you argue that? Do you have some benchmarks backing it up or is it more a personal preference?
  - crazygringo 1y ago
    It's literally what they're designed for. SQLite is designed for one local client at a time. Client-server relational databases are designed for many clients at a time.
    - simonw 1y ago
      That's not entirely true. SQLite is designed to support many processes reading the same file on disk at once. It only allows one process to write at a time, using locks - but since most writes finish in less than a ms in most cases having a process wait until another process finishes their write isn't actually a problem. If you have lots of concurrent writes SQLite isn't the right solution. For concurrent reads it's fine. SQLite also isn't a network database out-of-the-box. If you want to be able to access it over the network you need to solve that separately. (Don't try and use NFS. https://sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... )
      - skeeter2020 1y ago
        the reality is very few workloads have access patterns that SQLite can't support. I would much rather start with a strategy like 1. use sqlite for my beta / single client, 2. duplicate the entire environment for the next n clients, 3. solve the "my application is wildly successful" and SQLite is no longer appropriate problem at a future date. Spoiler: you're never going to get to step #3.
        crazygringo 1y ago
        > 2. duplicate the entire environment for the next n clients That becomes an instant problem if users ever write to your database. You can't duplicate the environment unless it's read-only. And even if the database is read-only for users, the fact that every time you update it you need to redeploy the database to every client, is pretty annoying. That's why it's usually better to start with Postgres or MySQL. A single source of truth for data makes everything vastly easier.
    - victorbjorklund 1y ago
      Not true. Can you back up your claim that the developers of Sqlite says they dont recommend it for webservers? (hint they recommend it). If you have a read-heavy app (99% of saas) that runs on one server and dont have millions of users then sqlite is a great option.
      - crazygringo 1y ago
        I didn't say that. I said one local client at a time. If you're running on one server then your webserver is the one local client. Usually you want to be able to run multiple webservers against a single database though, since that's the first thing you'll usually need to scale.
vmg12 1y ago
Let's say I'm building a small app that I'm hosting on some shared vps, if I think about the effort involved in setting up sqlite with litestream and just getting a $5 (or free) postgres provider I don't think sqlite makes my life easier. Now if I'm building a local app then absolutely sqlite makes the most sense but I don't see it otherwise.
- bccdee 1y ago
  Litestream is dead simple to setup. You make an S3 bucket (or any compatible storage bucket), paste the access keys and the path to your db file in /etc/litestream, and then run dpkg -i litestream.deb systemctl enable litestream systemctl start litestream The fact it's so simple is my favourite thing about it.
  - indigodaddy 1y ago
    Are there any use cases/documentation about how litestream can be used within a docker based deployment? (Eg where systemctl wouldn't be used)
    - bccdee 1y ago
      You'd probably want to put the sqlite db in a volume & run litestream in a separate container that restarts automatically on failure. Systemctl's only in there to restart it if it crashes; litestream itself is (iirc) a single cli binary.
    - airblade 1y ago
      This is documented on the Litestream website.
    - simonw 1y ago
      Here's their docs on running in a Docker container: https://litestream.io/guides/docker/ https://litestream.io/guides/docker/
- victorbjorklund 1y ago
yellow_lead 1y ago
Because Postgres is mature, works, and has a version number above v1.0?
- zwnow 1y ago
  Version numbers dont mean anything as the whole Elixir ecosystem shows:D
- teeray 1y ago
  If v1.0 is your North Star, you should re-evaluate a whole lot of software in your stack: https://0ver.org/#notable-zerover-projects https://0ver.org/#notable-zerover-projects
  - 8organicbits 1y ago
    I think you're focusing on the wrong parts of the comment. People care about things like long-term support. Postgres 13, from 2020, is still officially supported. Litestream 0.1.0 was the first release, also from 2020, but I can't tell if it is supported still. Worrying about the maturity, stability, and support of an application database is very reasonable in risk adverse projects.
    - victorbjorklund 1y ago
      Litestream is just a backup solution. Should probably be compared to a backup solution for postgres that does automated backups over the network etc. That isnt part of postgres. Besides the question wasnt litestream vs postgres backup apps. It was sqlite vs postgres.
      - threatofrain 1y ago
        The original response at least concerned litestream because the not-1.0 comment only applies to that.
- victorbjorklund 1y ago
  I'm guessing this is a joke?
danenania 1y ago
For a cloud service, I think it comes down to whether you’ll ever want more than one app server. If you’re building something as a hobby project and you know it will always fit on one server, sqlite is perfect. If it’s meant to be a startup and grow quickly, you don’t want to have to change your database to horizontally scale. Deploying without downtime is also much easier with multiple servers. So again, it depends whether you’re doing something serious enough that you can’t tolerate dropping any requests during deploys.
- victorbjorklund 1y ago
  99,99% of apps dont need more than one app server. You can serve a lot of traffic on the larges instances. For sure downtime is easier with kubernete etc but again overkill for 99,99% of apps.
  - danenania 1y ago
    Right, but if your goal is to have a lot of users (and minimal downtime), there's no point in putting a big avoidable obstacle in your path when the alternative is just as easy.
    - victorbjorklund 1y ago
      If your goal is to serve billions of users you should probably use cassandra etc. Why limit yourself to postgres if your goal is to have a billion users online at the same time?
      - danenania 1y ago
        Because cassandra isn't easy to set up and has all kinds of tradeoffs on consistency, transactions, et al compared to an SQL db. On the other side, why not just store everything in memory and flush to a local json file if you won't have any users? sqlite is overkill!
      - skeeter2020 1y ago

TemptedMuse 1y ago

I'd argue that anything larger than a desktop app should not use SQLite. If you need Litestream for replication and backup it is probably better to just use Postgres. There are a ton of one-click deployment offerings for proper databases, Fly.io actually offers managed Postgres.

victorbjorklund 1y ago
Why would you argue that? Do you have some benchmarks backing it up or is it more a personal preference?
- crazygringo 1y ago
  It's literally what they're designed for. SQLite is designed for one local client at a time. Client-server relational databases are designed for many clients at a time.
  - simonw 1y ago
    That's not entirely true. SQLite is designed to support many processes reading the same file on disk at once. It only allows one process to write at a time, using locks - but since most writes finish in less than a ms in most cases having a process wait until another process finishes their write isn't actually a problem. If you have lots of concurrent writes SQLite isn't the right solution. For concurrent reads it's fine. SQLite also isn't a network database out-of-the-box. If you want to be able to access it over the network you need to solve that separately. (Don't try and use NFS. https://sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... )
    - skeeter2020 1y ago
      the reality is very few workloads have access patterns that SQLite can't support. I would much rather start with a strategy like 1. use sqlite for my beta / single client, 2. duplicate the entire environment for the next n clients, 3. solve the "my application is wildly successful" and SQLite is no longer appropriate problem at a future date. Spoiler: you're never going to get to step #3.
      - crazygringo 1y ago
        > 2. duplicate the entire environment for the next n clients That becomes an instant problem if users ever write to your database. You can't duplicate the environment unless it's read-only. And even if the database is read-only for users, the fact that every time you update it you need to redeploy the database to every client, is pretty annoying. That's why it's usually better to start with Postgres or MySQL. A single source of truth for data makes everything vastly easier.
  - victorbjorklund 1y ago
    Not true. Can you back up your claim that the developers of Sqlite says they dont recommend it for webservers? (hint they recommend it). If you have a read-heavy app (99% of saas) that runs on one server and dont have millions of users then sqlite is a great option.
    - crazygringo 1y ago
      I didn't say that. I said one local client at a time. If you're running on one server then your webserver is the one local client. Usually you want to be able to run multiple webservers against a single database though, since that's the first thing you'll usually need to scale.

vmg12 1y ago

Let's say I'm building a small app that I'm hosting on some shared vps, if I think about the effort involved in setting up sqlite with litestream and just getting a $5 (or free) postgres provider I don't think sqlite makes my life easier. Now if I'm building a local app then absolutely sqlite makes the most sense but I don't see it otherwise.

bccdee 1y ago
Litestream is dead simple to setup. You make an S3 bucket (or any compatible storage bucket), paste the access keys and the path to your db file in /etc/litestream, and then run dpkg -i litestream.deb systemctl enable litestream systemctl start litestream The fact it's so simple is my favourite thing about it.
- indigodaddy 1y ago
  Are there any use cases/documentation about how litestream can be used within a docker based deployment? (Eg where systemctl wouldn't be used)
  - bccdee 1y ago
    You'd probably want to put the sqlite db in a volume & run litestream in a separate container that restarts automatically on failure. Systemctl's only in there to restart it if it crashes; litestream itself is (iirc) a single cli binary.
  - airblade 1y ago
    This is documented on the Litestream website.
  - simonw 1y ago
    Here's their docs on running in a Docker container: https://litestream.io/guides/docker/ https://litestream.io/guides/docker/
victorbjorklund 1y ago

yellow_lead 1y ago

Because Postgres is mature, works, and has a version number above v1.0?

zwnow 1y ago
Version numbers dont mean anything as the whole Elixir ecosystem shows:D
teeray 1y ago
If v1.0 is your North Star, you should re-evaluate a whole lot of software in your stack: https://0ver.org/#notable-zerover-projects https://0ver.org/#notable-zerover-projects
- 8organicbits 1y ago
  I think you're focusing on the wrong parts of the comment. People care about things like long-term support. Postgres 13, from 2020, is still officially supported. Litestream 0.1.0 was the first release, also from 2020, but I can't tell if it is supported still. Worrying about the maturity, stability, and support of an application database is very reasonable in risk adverse projects.
  - victorbjorklund 1y ago
    Litestream is just a backup solution. Should probably be compared to a backup solution for postgres that does automated backups over the network etc. That isnt part of postgres. Besides the question wasnt litestream vs postgres backup apps. It was sqlite vs postgres.
    - threatofrain 1y ago
      The original response at least concerned litestream because the not-1.0 comment only applies to that.
victorbjorklund 1y ago
I'm guessing this is a joke?

danenania 1y ago

For a cloud service, I think it comes down to whether you’ll ever want more than one app server. If you’re building something as a hobby project and you know it will always fit on one server, sqlite is perfect. If it’s meant to be a startup and grow quickly, you don’t want to have to change your database to horizontally scale. Deploying without downtime is also much easier with multiple servers. So again, it depends whether you’re doing something serious enough that you can’t tolerate dropping any requests during deploys.

victorbjorklund 1y ago
99,99% of apps dont need more than one app server. You can serve a lot of traffic on the larges instances. For sure downtime is easier with kubernete etc but again overkill for 99,99% of apps.
- danenania 1y ago
  Right, but if your goal is to have a lot of users (and minimal downtime), there's no point in putting a big avoidable obstacle in your path when the alternative is just as easy.
  - victorbjorklund 1y ago
    If your goal is to serve billions of users you should probably use cassandra etc. Why limit yourself to postgres if your goal is to have a billion users online at the same time?
    - danenania 1y ago
      Because cassandra isn't easy to set up and has all kinds of tradeoffs on consistency, transactions, et al compared to an SQL db. On the other side, why not just store everything in memory and flush to a local json file if you won't have any users? sqlite is overkill!
    - skeeter2020 1y ago