Interesting. The compression options that postgres offers itself were not enough?
Re:Bug Hunting in Btrfs
by yjftsjthsd-h on Wednesday March 20, 2024 @11:08AM
(#39767697)
It went the other way; we were using ZFS to protect against data errors, then found that it did compression as well. But looking now, the only native postgres options I see are TOAST, which seems to only work for certain data types in the database, and WAL compression (that's only existed since pg 15), so unless I've missed something I would tend to say yes it's far superior to the native options.
I mostly only looked into the options offered by MariaDB a while ago, and they seemed quite neat. I had just assumed that postgres was at least on par.
Thanks for reporting!
postgres allows to compress only large text and blob column values.
Sun marketed the combination of PostgreSQL and ZFS quite heavily. But IIRC most of the success stories they presented involved what was decidedly non-OLTP read heavy workload with somewhat ridiculously large databases. One of the case studies even involved using PostgreSQL on ZFS as an archival layer for Oracle, with the observation that what is a large Oracle database is medium-sized one for PostgreSQL (and probably even more so if it is read mostly and on ZFS).
Sunhad some recommendations for tuning PostgreSQL performance on ZFS, but the recommendations seemed weird or even wrong for OLTP on top of CoW filesystem (I assume that these recommendations were meant for the above mentioned DWH/archival scenarios).
Edit: Also there was a time when it was normal for databases to be running on Solaris, in which case ZFS is expected; my recollection is that Sun even marketed how great of a combination that was.