7 ms·

Do you need (or at least benefit from) your database to run in-process? Because that is the only advantage I can see to SQLite over Postgres. Which makes it the

Do you need (or at least benefit from) your database to run in-process? Because that is the only advantage I can see to SQLite over Postgres. Which makes it the better candidate in many places, but not for anything like a server.

luto 4y ago
It makes the dev setup trivial, since there is no database server around.
- moron4hire 4y ago
  Setting up postgres on the same machine is also trivial. Modern, production-grade, web-scale machines are able to run more than one process, these days.
  - aiwv 4y ago
    Sure, but an embedded database is still simpler than client/server. For many tasks, postgres does not offer any meaningful benefit over sqlite so why add the complexity?
    - antifa 4y ago
      I've never worked on such a project that didn't benefit from postgres, but if I did, I would still use postgres because upgrading from sqlite to postgres, and learning a second SQL dialect, and converting a SQL between dialects sounds like a useless nightmare that costs me nothing to avoid.
      - CoolCold 4y ago
        I feel it's modern hype on lonely/indie/solo developer creating new thing which changes the world. Not caring on "complex" things till the project reaches 1 request per minute and finally have some data to be lost, is totally fine with this goal/scale.
freedomben 4y ago
For me it's maintenance. Sysadmin level of effort on a SQLite file is near 0
- jamal-kumar 4y ago
  Yeah it's nice not to have to think too hard about that when you're trying to focus on a product getting shipped. Plus, not everything is a public-facing website. It's amazing how little overhead it takes.
  - freedomben 4y ago
    > not everything is a public-facing website exactly. single-node availability is often "good enough." when using sqlite for a simple service, I've cranked things out from first-line-of-code to production in 30 minutes. When the app only gets a hit every minute or so and only from internal traffic, no need for the overhead of a highly available service.
- kristiandupont 4y ago
  That's fair, though I've never done any sort of sysadmin on a Postgres server either..
- everforward 4y ago
  I think this is true only if you're willing to give up resiliency. You'll have to shut the app down to back it up. That problem gets worse if you want some kind of a cold standby, since backups should be frequent. You could replicate it, but that requires running a separate daemon and starts to beg the "why not just have Postgres be the daemon?" question. Sysadmin tasks start to get very difficult when someone starts with SQLite and expects to get Postgres-like features out of it. I'd rather run Postgres than try to replicate or continually back up SQLite.
  - spiffytech 4y ago
    > You'll have to shut the app down to back it up. This is not the case: 1) SQLite recommends using the official backup API, rather than copying files on disk. The backup API can be used while the app is running. 2) Litestream is the hot new tool on the block. It streams incremental DB changes to a backup stored on S3, for up-to-date point-in-time recovery.

luto 4y ago

It makes the dev setup trivial, since there is no database server around.

moron4hire 4y ago
Setting up postgres on the same machine is also trivial. Modern, production-grade, web-scale machines are able to run more than one process, these days.
- aiwv 4y ago
  Sure, but an embedded database is still simpler than client/server. For many tasks, postgres does not offer any meaningful benefit over sqlite so why add the complexity?
  - antifa 4y ago
    I've never worked on such a project that didn't benefit from postgres, but if I did, I would still use postgres because upgrading from sqlite to postgres, and learning a second SQL dialect, and converting a SQL between dialects sounds like a useless nightmare that costs me nothing to avoid.
    - CoolCold 4y ago
      I feel it's modern hype on lonely/indie/solo developer creating new thing which changes the world. Not caring on "complex" things till the project reaches 1 request per minute and finally have some data to be lost, is totally fine with this goal/scale.

freedomben 4y ago

For me it's maintenance. Sysadmin level of effort on a SQLite file is near 0

jamal-kumar 4y ago
Yeah it's nice not to have to think too hard about that when you're trying to focus on a product getting shipped. Plus, not everything is a public-facing website. It's amazing how little overhead it takes.
- freedomben 4y ago
  > not everything is a public-facing website exactly. single-node availability is often "good enough." when using sqlite for a simple service, I've cranked things out from first-line-of-code to production in 30 minutes. When the app only gets a hit every minute or so and only from internal traffic, no need for the overhead of a highly available service.
kristiandupont 4y ago
That's fair, though I've never done any sort of sysadmin on a Postgres server either..
everforward 4y ago
I think this is true only if you're willing to give up resiliency. You'll have to shut the app down to back it up. That problem gets worse if you want some kind of a cold standby, since backups should be frequent. You could replicate it, but that requires running a separate daemon and starts to beg the "why not just have Postgres be the daemon?" question. Sysadmin tasks start to get very difficult when someone starts with SQLite and expects to get Postgres-like features out of it. I'd rather run Postgres than try to replicate or continually back up SQLite.
- spiffytech 4y ago
  > You'll have to shut the app down to back it up. This is not the case: 1) SQLite recommends using the official backup API, rather than copying files on disk. The backup API can be used while the app is running. 2) Litestream is the hot new tool on the block. It streams incremental DB changes to a backup stored on S3, for up-to-date point-in-time recovery.