←back to thread

What if we treated Postgres like SQLite?

(www.maragu.dev)
75 points markusw | 5 comments | 22 Sep 25 13:29 UTC | HN request time: 0s | source
Show context
eirikbakke ◴[22 Sep 25 16:11 UTC] No.45335495[source]▶
The PostgreSQL data directory format is not very stable or portable. You can't just ZIP it up and move it to a different machine, unless the new machine has the same architecture and "sufficiently" similar PostgreSQL binaries.

In theory the data directory works with any PostgreSQL binaries from the same major version of PostgreSQL, but I have seen cases where this fails e.g. because the binaries were from the same major version but compiled with different build options.

replies(5): >>45335952 #>>45336357 #>>45336495 #>>45336741 #>>45337960 #
1. tracker1 ◴[22 Sep 25 17:06 UTC] No.45336357[source]▶
Yeah, I was going to mention, just upgrading between PG versions can be a bit of a pain. Dump/Restore really seems like a less than stellar option of you have a LOT of data. I mean you can stream through gzip/bzip to save space but still.

I often wish that Firebird had a license that people found friendlier to use as it always felt like a perfect technical option for many use cases from embedded to a standalone server. PG has clearly eclipsed it at this point though.

replies(1): >>45336762 #
2. markusw ◴[22 Sep 25 17:31 UTC] No.45336762[source]▶
On the other hand, and especially if migrating from SQLite, there's typically not _that_ much data. Even hundreds of GBs would probably be okay? Drives are so fast these days. Would be interesting to benchmark.
replies(1): >>45336959 #
3. tracker1 ◴[22 Sep 25 17:48 UTC] No.45336959[source]▶
Maybe that's fair... just feels like a pain when I want to update an app, I have to jump through a few hoops as opposed to just updating the PostgreSQL version in a docker-compose or k8s config.

Would be nice if PG could at least automagically forward update when it starts and a database is from a prior version.

replies(2): >>45337256 #>>45343871 #
4. markusw ◴[22 Sep 25 18:07 UTC] No.45337256{3}[source]▶
Yeah, totally agreed. I'll have to look into that. I've really enjoyed the stability of SQLite, down to the file format.
5. WayToDoor ◴[23 Sep 25 07:28 UTC] No.45343871{3}[source]▶
https://github.com/pgautoupgrade/docker-pgautoupgrade