Postgres vs. File Systems: A Performance Comparison (2022) | Hacker News Reader

Postgres vs. File Systems: A Performance Comparison (2022)

enterprisedb.com

31 pointseatonphil9 comments

> I’ve used my two “usual” machines with different hardware configurations, a small one with SATA SSD and a bigger one with NVMe SSD.

ZFS has some known limitations running on NVMe/flash. See Allan Jude's EuroBSDcon 2022 presentation "Scaling ZFS for NVMe":

* https://registration.eurobsdcon.org/conferences/2022/program...

* https://www.youtube.com/watch?v=v8sl8gj9UnA

When ZFS was created spinning rust was still the main thing, and SSDs were just gaining popularity, so ZFS created "hybrid storage" pools to balance space, speed, and cost:

* https://cacm.acm.org/magazines/2008/7/5377-flash-storage-mem...

* https://web.archive.org/web/20080613124922/http://blogs.sun....

* https://web.archive.org/web/20080615042818/http://blogs.sun....

* https://www.brendangregg.com/blog/2009-10-08/hybrid-storage-...

* https://en.wikipedia.org/wiki/Hybrid_array

Yeah, I certainly understand ZFS was designed for different types of storage, but we now have flash storage as a default ...

Which is why I do these benchmarks - to educate myself and learn what behavior to expect from different systems, and maybe even find stuff to optimize in Postgres. The goal definitely is not to "shame" some of the filesystems (or whatever I benchmark), I have huge respect to all of the engineers working on these things.

FWIW I really like the ideas behind ZFS, and many deployments chose to use it. But that's kinda the point - to decide what's the right trade off you need to have some basic understanding what kind of behavior to expect.

In our production environment, we use XFS to run various databases, including MySQL, PostgreSQL, MongoDB, etc. It delivers performance that is either superior to or on par with ext4.

I'm interested to see if there're some ZFS users.

Are you testing performance with pgbench or something more custom/advanced?

As a former storage architect, I should point out that defaults are compromises and it is far more complicated as any benchmarking is.

Btrfs extent trees, XFS stride, stripe sizes. Partition alignment, WAL block and segment sizes all need to be tuned. Obviously abstractions like LVM and VM volumes impact this.

Short stroking spinning WAL drives. Or only partial allocation of SSD space was often required when systems were close to capacity.

But ZFS data consistency has a cost. And extents on spinning discs has a cost also.

8k WAL blocks with 16m segment defaults with 4k native disk sectors would be a place to start with 7200/60 = 100 as an assumption.

I wonder how much their tuned ZFS config looked like this.

https://vadosware.io/post/everything-ive-seen-on-optimizing-...

For example making sure ZFS compression is on while Postgres compression is off. Also checksumming where ZFS takes care of it.

I'd also be curious to see results where the Write Ahead Log was located on separate drives.

There's a link to a github repo [https://github.com/tvondra/pg-fs-benchmark] with all the configuration details, including the ZFS details for each of the runs.

For example https://github.com/tvondra/pg-fs-benchmark/blob/master/i5/20..., but it's all set by (admittedly ugly) scripts that are also in the repo: https://github.com/tvondra/pg-fs-benchmark/blob/master/i5/ru...

But yeah, I certainly did the tuning recommended on the vadosware.io page (I think I actually used it as one of the sources back then, I guess I should have mentioned that in the blog post).

From the title I thought it's a pg as a database vs file based databases aka no dedicated network interface comparison.

But then of course that wouldn't make much sense.

Holiday Monday derp.

Well it's good to see that the default ext4 is a very good choice. I'm personally going for xfs on the server, because I'm using MongoDB as well.

Readers may find Pack interesting: https://news.ycombinator.com/item?id=39793805

Reply on news.ycombinator.com