Why did you choose to run PG on ZFS? DBs on CoW FS aren't usually ideal.

We were running very large storage volumes in Azure (+2TB) and wanted to leverage ZFS compression to save money. After running some performance testing, we landed on a good balance of PGSQL and ZFS options that worked well for us.

Is the old ZFS block size trick still necessary (since DBs internalise so much that OSs tend to provide)?

It is - depending on the read-vs-write workload. For our workload, we landed on a record size (blocksize) of 128K which gives us 3x-5x compression. Contrary to the 8KB/16KB suggestions on the internet, our testing indicated 128K was the best option. And, using compression allows us to run much smaller storage volume sizes in Azure (thus, saving money).

We did an exhaustive test of our use-cases, and the best ZFS tuning options with Postgres we found (again, for our workload):

  * Enable ZFS on-disk compression

  * Disable ZFS in-memory compression (enabling this option costs us 30% perf penalty)

  * Enable primary caching

  * Limit read-ahead caching

Edit: Forgot to add, here are the required PGSQL options when using ZFS:

  * full_page_writes = off

  * wal_compression = off

Once the above options were set, we were getting close to EXT4 read/write speeds with the benefit of compression.