Hey thanks for the clarification. That seems like a worthwhile tradeoff in your case.

For backups in particular, are ZFS snapshots alone not suitable to serve as a backup? Is there something else that the pg backup process does that is not covered by a "dumb" snapshot?

We use wal-g and extensively leverage its archive/point-in-time restore capabilities. I think it would be tricky to manage similar functionality with snapshots (and possibly more expensive if archival involved syncing to a remote pool).

That being said, wal-g has worked well enough for us that we haven't put a ton of time into investigating alternatives yet, so I can't say for sure whether snapshots would be a better option.

I'd personally recommend pgBackRest as a wal-g replacement. We (Covalent) started with wal-g ourselves, but pgBackRest does full backup and restore so much faster. Besides the parallelism (which is great), pgBackRest's backups are manifests, symbolically mapping to the individual objects in storage that may have come from previous backups. Which means that a differential or incremental backup doesn't need to be "replayed after" a full backup, but instead is just like a git commit, pointing to some newer and some older objects.

Also, auto-expiry of no-longer-needed WAL segments (that we use due to our reliance on async hot standbys) along with previous backups is pretty great.

And we haven't even started taking advantage of pgBackRest's ability to do incremental restore — i.e. to converge a dataset already on disk, that may have fallen out of sync, with as few updates as possible. We're thinking we could use this to allow data science use-cases that would involve writing to a replica, by promoting the replica, allowing the writes, and then converging it back to an up-to-date replica after the fact.

Have you looked at postgres row/column compression? Obviously, compressing the same data twice won't be too helpful, but maybe there are more wins to be had.

how/why did you choose Postgres over MariaDB? I am facing such a decision now.

After working for years with both, I'd say that PostgreSQL is much more friendlier to the developer and more pleasant to work with. In any area: documentation, features, error messages, available SQL features, available extensions, available docs, available books.

One tiny example: I prefer to work with databases using CLI interfaces (mysql and psql).

psql CLI is a tool which is pleasant to use, has no bugs in the interface and it even gets improvements from time to time.

mysql CLI is awful to use (e.g. doesn't display long lines properly, has difficulties with history editing, etc) and looks like there wasn't a single improvement since 1996 (I'm sure there were, I just never felt the effect of such improvements).

Actually, there was a significant regression. Many many years ago, Oracle decided to drop the support for the gpl-licensed readline altogether, likely because they can't ship it with MySQL Enterprise. To this day, Percona still carries a small patch to add that functionality back, which is great because I wouldn't touch any CLI without readline.

Dropping a link to `rlwrap` in case anyone is not familiar with it:

https://github.com/hanslub42/rlwrap

Note that I've never tried it myself with the mysql/mariadb CLI, but I have used it with other tools, and it's brilliant.

Yep Postgres seems to have more features too for sure. Also, a fun little limitation of the MySQL CLI is that it truncates hostnames to 100 characters - not usually a problem, but AWS database VPC hostnames easily hit that limit, and it just silently truncates rather than failing.

"It silently does something unexpected rather than failing" succinctly summarizes all the reasons you shouldn't be using MySQL.

I’ve been using a Postgres foreign data wrapper to interact with a MySQL database and it’s much nicer for interactive use.

Not MariaDB but after working with Postgres for six years I'm now using MySQL and... the error messages are useless, and comparing different types works in strange ways rather than failing like in Postgres.

To add, execution plans presented by MariaDB are also nearly useless.

User grants in Maria/MySQL drive me nuts. I hope it is better in Postgres

Postgres always