I've been doing this with several production PostgreSQL instances.
PostgreSQL on ZFS is great.
I have zstd compression enabled and I average 3.50x compression ratios.
(Probably some pretty awful CPU tradeoffs in there, but system load and query times seem fine. My database is 50 GB before zstd compression, so enabling it helps a ton.)
I also have ZFS snapshots taken hourly and shipped offsite. It's awesome that I don't need to pause PostgreSQL or anything to take the snapshot. It's atomic and instant.
With some quick and dirty `time` style tests zstd has a pretty low overhead. IIRC writing was around off = 100, lz4 = 110, zstd = 115 cpu utilization on my personal data set that resulted in 1x, 1.7x, 2.1x compression. Reading was negligible, single digit percentages, for both lz4 & zstd. For anything on a spindle that's a pretty good trade off of CPU time.
PostgreSQL on ZFS is great.
I have zstd compression enabled and I average 3.50x compression ratios.
(Probably some pretty awful CPU tradeoffs in there, but system load and query times seem fine. My database is 50 GB before zstd compression, so enabling it helps a ton.)
I also have ZFS snapshots taken hourly and shipped offsite. It's awesome that I don't need to pause PostgreSQL or anything to take the snapshot. It's atomic and instant.