I did set the recommended 8k block size for PostgreSQL dataset as described in the blogpost. Also note the logbias=throughput option.

You might also try setting primarycache=metadata, to avoid double caching the data. From what I have been told, this can help reduce memory pressure of the ARC competing with postgres' own caching.

Are you also setting a max limit for the ARC? You don't want postgres and the zfs ARC to compete for memory. I wonder if this impacts FreeBSD's poor performance in the read intensive tests.

I actually did try this and the results were worse. I did not investigate it further and went with primarycache=all

I stand corrected. Thank you.

Ah. OK, sorry.

The size of postgresql database was ~74GB for every test combination. The bare metal server had 256GB RAM for every test combination.

The only difference was the amount of RAM PostgreSQL could use - as specified in postgresql.conf.

So, 74GB database does not fit in PostgreSQL cache for 32GB instance and fits for 200GB instance.

I think it's pretty fair to test each OS with its filesystem of choice. I'm aware that you can use ZFS on Linux, but I'm not (yet) brave enough to recommend ZFS+Linux. And yes there's btrfs but would you trust it with your data ? :)

ZFS is bundled into the Antergos Linux installer.

I think pacman can get you their PostgreSQL package easily enough.

I have screenshots at the end of this (unpublished) article:

http://syro.org/systemd/zfs.html

Before the benchmark I did some test with FreeBSD+ZFS with LZ4 on/off. The LZ4 CPU overhead was pretty negligible and performance was slightly better than without compression, due to lower IO usage. That's the reason I've chosen LZ4 for the actual benchmark.

There is a 30 min warmup period before the actual benchmark. It is shown in the benchmarking script.

Hello, OP here. I'm certain that you can fine tune every OS for specific use case. I may indeed do that in a future blogpost. The question is what to compare ? Should I compare Linux kernel versions, PostgreSQL versions, filesystems (and features like compression, block size, ...) ? As you can see the permutations are endless and thats why I compared stock OSes with their default filesystems of choice.

I don't think that a Linux distribution is just a variable and the only thing that differs is the kernel version. Each distro made its own choices, for better or worse...

As for the clients connecting over the network - that was exactly my point. My idea was to benchmark in conditions similar to production deployment. I doubt that many production systems connect over unix socket.

And for the warmup period, as you can see in the benchmarking script there is a 30 min warmup period before I start to record the results.

With respect, I believe it should be the TPC-B benchmark, not TCP-B.

It is from the “Transaction Processing Performance Council”, correct? At least, that’s what they call themselves at tpc.org.

Otherwise, interesting results that I think need further examination.