3 ms·

A lot of projects will never need beyond the scale that a SQLite host can offer, and gain from the lower bills and simplicity of a single host. $8/month of GCP

A lot of projects will never need beyond the scale that a SQLite host can offer, and gain from the lower bills and simplicity of a single host. $8/month of GCP gives you 0.6GB memory and a single shared vCPU (no SLA), and you still need an application server. So with the same 'micro' specs for an application server, you are paying $16/month. With SQLite you can easily deploy to services like Hetzner Cloud and get 4vCPU + 8GM RAM for ~$15/month and use Litestream for backup. There are definitely trade offs with SQLite around low write performance (due to single writer limitation), and definitely not as feature filled as Postgres, but I think a lot of people underestimate the read performance and simplicity of a single host setup. And now with sidecar setups like Litestream, backup and disaster recovery of data is also a lot simpler and again, cheaper. With added attention, hopefully SQLite will continue to improve with extensions like Spatialite and others. Schema changes are still a point of friction as your data grows larger, however fairly stable heavy read scenarios, the simplicity of a SQLite setup is very nice to use. Don't get me wrong, Postgres is a great default, but for a growing number of use cases, SQLite can be an extremely portable, cheap and efficient setup.

endisneigh 4y ago
Why are you comparing GCP to Hetzner? You can easily deploy Postgres with auto backups to S3 using Hetzner as well.
- layoric 4y ago
  "easily deploy Postgres", how easy? What are the point in time restores like? One of the advantages of SQLite is its portability, so why wouldn't I compare it to Hetzner cloud since my experience of hosting is _exactly_ the same regardless of provider? The same can't be said for Postgres, unless you go self install route. And all I'm pointing out is the trade off, SQLite is simpler in some situations.
  - endisneigh 4y ago
    Docked compose up - that simple. I don’t see how using Litestream is any simpler than using Postgres. With containers you’d have the same experience. It’s also trivial to move your Postgres database to another machine.
    - layoric 4y ago
      Litestream is simpler for backups/disaster recovery. pg_dump -> S3 gives you a snapshot, but you then have to start worrying about time windows between backups and restore processes. Litestream does per second backups enabling point in time restores with 2 commands. Every time you want to upgrade Postgres you are going to have to manage that yourself, again, pg_dump -> S3 and back with possible issues around versions of pg_dump etc. I'm not trying to say these are insurmountable or even big issues, they are just trade offs. For the right use cases, SQLite's embedded nature can provide simplicity other offerings can't. Is Postgres able fill more use cases? Absolutely, it is an amazing piece of software.
      - tpetry 4y ago
        PgBackRest is working like Litestream to archive the WAL files, and for PG you can get those tools for more than a decade! Litestream is nothing novel. But you can‘t get it down to 1s, the minimum is 30s.
        layoric 4y ago
        I wasn't aware of PgBackRest, something will definitely keep in mind when offering alternatives to managed services, thank you! 30s is perfectly respectable, and something I will definitely start testing out as a sidecar container to Postgres. edit: It doesn't look like the use of PgBackRest composes as well as something like Litestream, eg it looks like PgBackRest has to be baked into the image running Postgres as opposed to a separate process monitoring changes to files like Litestream. Again, not huge issues, but definitely not as simple. Still very useful tech I'm glad you mentioned it, but a big draw card of SQLite is the simplicity of use.
latchkey 4y ago
The next one up on GCP is about $15 and supports a much higher workload. You're also not factoring in the developer costs to switch from SQLite to Postgres when you decide you really need some scale...
- layoric 4y ago
  Do you mean the `HA db-f1-micro` or are we looking at a different pricing chart? I have a hard time believing that would even get close to double of the single db-f1-micro, and limits on memory would still impact both. SQLite can perform extremely well for heavy reads/low change datasets, but it is scenario specific. It's bad at heavy write/chatty workloads due to its single writer limitation.

endisneigh 4y ago

Why are you comparing GCP to Hetzner? You can easily deploy Postgres with auto backups to S3 using Hetzner as well.

layoric 4y ago
"easily deploy Postgres", how easy? What are the point in time restores like? One of the advantages of SQLite is its portability, so why wouldn't I compare it to Hetzner cloud since my experience of hosting is _exactly_ the same regardless of provider? The same can't be said for Postgres, unless you go self install route. And all I'm pointing out is the trade off, SQLite is simpler in some situations.
- endisneigh 4y ago
  Docked compose up - that simple. I don’t see how using Litestream is any simpler than using Postgres. With containers you’d have the same experience. It’s also trivial to move your Postgres database to another machine.
  - layoric 4y ago
    Litestream is simpler for backups/disaster recovery. pg_dump -> S3 gives you a snapshot, but you then have to start worrying about time windows between backups and restore processes. Litestream does per second backups enabling point in time restores with 2 commands. Every time you want to upgrade Postgres you are going to have to manage that yourself, again, pg_dump -> S3 and back with possible issues around versions of pg_dump etc. I'm not trying to say these are insurmountable or even big issues, they are just trade offs. For the right use cases, SQLite's embedded nature can provide simplicity other offerings can't. Is Postgres able fill more use cases? Absolutely, it is an amazing piece of software.
    - tpetry 4y ago
      PgBackRest is working like Litestream to archive the WAL files, and for PG you can get those tools for more than a decade! Litestream is nothing novel. But you can‘t get it down to 1s, the minimum is 30s.
      - layoric 4y ago
        I wasn't aware of PgBackRest, something will definitely keep in mind when offering alternatives to managed services, thank you! 30s is perfectly respectable, and something I will definitely start testing out as a sidecar container to Postgres. edit: It doesn't look like the use of PgBackRest composes as well as something like Litestream, eg it looks like PgBackRest has to be baked into the image running Postgres as opposed to a separate process monitoring changes to files like Litestream. Again, not huge issues, but definitely not as simple. Still very useful tech I'm glad you mentioned it, but a big draw card of SQLite is the simplicity of use.

latchkey 4y ago

The next one up on GCP is about $15 and supports a much higher workload. You're also not factoring in the developer costs to switch from SQLite to Postgres when you decide you really need some scale...

layoric 4y ago
Do you mean the `HA db-f1-micro` or are we looking at a different pricing chart? I have a hard time believing that would even get close to double of the single db-f1-micro, and limits on memory would still impact both. SQLite can perform extremely well for heavy reads/low change datasets, but it is scenario specific. It's bad at heavy write/chatty workloads due to its single writer limitation.