3 ms·

It wasn't a company you've heard of, but I can absolutely tell you that there are:) We really benefited from compressing our postgres databases; not only did it

It wasn't a company you've heard of, but I can absolutely tell you that there are:) We really benefited from compressing our postgres databases; not only did it save space, but in some cases it actually improved throughput because data could be read and uncompressed faster than the disks were physically capable of. 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.

eru 3y ago
Interesting. The compression options that postgres offers itself were not enough?
- yjftsjthsd-h 3y ago
  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.
  - eru 3y ago
    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!
- riku_iki 3y ago
  postgres allows to compress only large text and blob column values.
dfox 3y ago
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).

eru 3y ago

Interesting. The compression options that postgres offers itself were not enough?

yjftsjthsd-h 3y ago
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.
- eru 3y ago
  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!
riku_iki 3y ago
postgres allows to compress only large text and blob column values.

dfox 3y ago

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