Hacker News Reader

Comment by latchkey | Hacker News Reader

It certainly has some good use cases and is not a toy, but there are some critical SQL features (like say: `last_at < NOW() - INTERVAL '10 minutes'`) that are not easily compatible with SQLite and are with postgres, that would require a lot of code rewrite in case you need to scale upwards. Testing against multiple databases is hard, so migrating to postgres wouldn't be trivial either. Hosting small postgres databases on things like GCP are ~$8 a month... why not just save yourself from future pain?

View on HN

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.

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...

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.

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

"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.

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.

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.

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.

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.

Where do you draw the line with compatibility? There's lots of small, DB specific differences like this between all the databases (mysql, sql server, postgres etc).

For this particular one, you can replace interval with something like `last_at < DATETIME(CURRENT_TIMESTAMP, '-10 day')`

> Hosting small postgres databases on things like GCP are ~$8 a month... why not just save yourself from future pain?

I think this is a fair point, if you think you'll run into sqlite's limitations in the future. litestream and friends make sqlite an option for some non-embedded use cases.. though they're hardly a panacea.

Moreover, many people are using ORMs which are going to hide the differences in a simple datetime query like that behind their query compilers.

A lot of devs seem to have an obsession with using the tech that only just works for the use case. Like you pointed out, Postgres works in everything from tiny apps to global scale SaaS. I really just can't see any reason you would want to use SQLite outside of local apps. Sure, many web apps can run on it, but they can all work on Postgres.

Postgres is a great default, no doubt, but it is still more complex to setup and maintain OR much more expensive to use as a managed offering than SQLite. Postgres extension upgrades can get pretty hairy, even on managed services. SQLite is a data file, so with projects like litestream, getting back to a working state is as simple as it gets, its a file.

SQLite definitely has its own problems, but using Postgres over SQLite and vice versa is still making a trade off. There is a massive spectrum between "tiny app" and "global scale SaaS", and with hardware improvements, SQLite can handle quite a lot. Would I pick SQLite as the default to use when aiming to build another "global scale SaaS"? No, I would more likely default to Postgres as well, but it definitely shines in a lot more scenarios than just in a local app.

> Like you pointed out, Postgres works in everything from tiny apps to global scale SaaS.

So does SQLite.

Weird comment. Don't use a DB that doesn't fit your use case. In this case if you plan to scale or want PGSQL features, don't use SQLite. But SQLite is great if you need to embed a db in an app or are just doing some local stuff.

Hosting postgres on your own computer is free. Why would you possibly pay someone $8 / month for a postgres instance to replace SQLite? Postgres is free, and super easy to set up.

We use an embedded postgres in our tests (it is just literally a vanilla postgres), the thing starts up fast and runs great. I'm sure there must be other embedded of examples out there.

I'm sure I could find reasons to use sqlite, but I'd have to find reasons NOT to use postgres if I ever thought I'd need its capabilities.

Right, and if a project is small enough to only need a small app server, it's probably small enough to just run the DB on the same server in the background.

You don’t even have to get that niche. SQLite can’t do ALTER COLUMN and triggers are very simple (e.g. you can’t assign to NEW in a BEFORE INSERT)

Reply on news.ycombinator.com