Everything I've seen on optimizing Postgres on ZFS

Hey mastax, thanks for the notes. I'm no expert but want to respond to the following:

1. AFAIK none of these parts of the stack (postgres, ZFS) are using asynchronous IO or Direct IO -- postgres doesn't support either, and ZFS supports async writes (that concept means something very specific in ZFS land), and ZFS doesn't support direct IO (though it comes up a lot).

2. See my response to 1

3. All my testing is actually on NVMe, and comparing the performance to mdraid/ext4 (I also tried LVM) it's actually pretty decent. People have been running databases on ZFS with great success for years and I'm quite possibly the least knowledgeable person who's written about it! You can be productive without O_DIRECT support (again Postgres doesn't use direct IO so). Absolutely worth the benefits that ZFS provides, in my mind.

What about postgres passthrough to disk. Is it better to do something like that? I normally see postgres on zfs, but I wonder.

A super deep and informative post as always Victor.

I'm still digesting it, also cross-referencing it with the ZFS section of your Hetzner drive failure write up of December 13th.

https://vadosware.io/post/handling-your-first-dead-hetzner-h...

An idea for a future post might be

"Our ZFS and Postgres setup for Nimbus WS: Hetzner, OVH and LeaseWeb"

Not a Postgres expert, but a MS SQL one: performance of the storage in well configured servers, after the warm up, is the least concern for perf tuning. In query tuning physical reads are a flag for bad configuration, logical reads are the first and foremost indicator and a 20% increase in storage performance brings less than 1% in total server performance. This is because if your query is well written and covered with indexes, reads are performed in memory and large writes are very rare.

Nonetheless, the article is interesting as a way to understand how Postgres and ZFS go together.

You're right! This article actually wasn't meant to be a benchmarking-style post, I didn't actually share my notes and numbers, because I originally started just experimenting and kind of fell down the rabbit hole. This originally started as me trying to pick a local filesystem after moving off of Ceph-for-everything -- it started as an mdraid vs LVM vs ZFS experiment.

That said, the reasons I haven't published all my notes is because:

- I only used one testing software pgbench (which is notorious for producing)

- Good evaluation needs a long term assessment of drive fragmentation which is hard to do (and literally takes time)

- Sometimes I changed multiple variables at a time, and not all the values I have in my notes are averaged across multiple runs, etc.

So in the end this post was just gathering what other much smarter people have said that I took inspiration from so it's all in one place.

Some intuition as well on why 16k can work better than 8k is in that scale15x talk -- for sequential table scans, it's almost like you're pre-faulting. A contact I've made through this research also recently suggested to me that I was only scratching the surface -- bigger record sizes should be better, as long as write amplification can be managed and fragmentation avoided (via a SLOG or more tuning).

Yea, I have run postgres/citus on zfs with petabytes of data. His analysis on record size is lacking. E.g there is a trade-off between compression vs record size. More compression means more effective iops

Appreciate ya! Getting more of the sage advice that better scientists/engineers have been generous enough to share is one of my past times.

If you liked this and you're into distributed systems you might like my posts on Paxos:

https://vadosware.io/post/paxosmon-gotta-concensus-them-all/

Thanks! I unfortunately wasn't really scientifically disciplined enough as I ran pgbench running through configurations, so I didn't include my notes or numbers but hopefully someday I can come back to this with a fresh wad of cash to spend on dedicated hardware fit for purpose and put some of this to rest!

One of the things about this kind of benchmarking/testing is that it really requires multiple days worth of evaluation (index bloat, disk fragmentation, etc) and is really hard to get right... I'm afraid I'd add more noise than signal.

Some great resources out there though, like Percona's post on MySQL:

https://www.percona.com/blog/mysql-zfs-performance-update/

Even in there though, the comments are full of reasonable objections, with some really sage advice in terms of fragmentation and really measuring whether ZFS can coalesce your writes. It's a hard topic!