PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

Sorry, but your testing methodology needs some help. Were you testing OSes? Postgres? Storage configuration? I suggest investigating FIO first, use it to isolate the best performing disk configuration (Storage+Kernel+Filesystem+whatever), then do some pgbench tests with different postgres tuned parameters, to show the best way to tune postgres.

A few thoughts:

* You weren't testing OSes which the subject implied, you were testing Linux kernel variants and their stock OS configurations/kernel scheduler setups, and FreeBSD was tossed into the mix. Whether you are running Ubuntu, CentOS, Debian or whatever you should have the Linux kernel tuned to perform well, so adding the distribution as a variable is just a red herring. I'd be more interested in removing that variable and comparing different storage configurations (such as XFS, and LVM).

* Clients connecting over the network adds a huge variable at play (the network) -- ideally you would want to remove this.

* I may have missed it, but it wasn't clear if you had a warmup period to your benchmarks. Especially with a system like ZFS which has COW, you need to do a few benchmarks on the same blocks first, to break past the cache.

I've done plenty of benchmarks with LZ4 and GZIP compression. It really helps a lot, especially LZ4 should always be enabled as it is always faster. GZIP is fantastic for some datasets where you do sequential scans of many tables around 10MB - 100MB'ish sizes, but should be used with care.

The great thing about PostgreSQL is using tablespaces, so you can put tables and indexes on different ZFS filesystems, with different hardware(disk vs ssd), recordsizes and compression options.

He did adequately disclose that the OS was swapped out while pgsql and settings were held steady. Adding in more arbitrary benchmarks like FIO wont really clarify pgsql performance if that is the desired investigation. Instead, active bench marking where you identify the bottlenecks in each tested platform would be a better use of time, and you could hypothesize how to improve each platform if you were to pick it.

As a counter-example, I could easily cherry pick versions, tunables and patch sets to make the numbers go whichever way I want so these types of comparisons aren't that useful unless someone is dropping a big delta on the floor with out of the box settings vs another.

As a FreeBSD developer, I will actually tell you that Linux could be selected to graph massive wins by cherry picking hardware with very high core count and several NUMA domains. But even then, by selecting kernel features (which could be innocuously hidden in a version number/vendor patch set) you can cherry pick large swings. That said, FreeBSD+ZFS+PGSQL (https://www.slideshare.net/SeanChittenden/postgresql-zfs-bes...) is a joy to administer, and is unlikely to be the weak link in a production setup if you stick to a two socket system. There is a lot of work going on in HEAD that is relevant to this workload in the memory management subsystem, including NUMA support. And some TCP accept locking changes that'd be relevant for TCP connection turnover.

The benchmark is a bit arbitrary, there are just too many variables. Filesystems (ZFS/ext4), RAID (ZFS Raid vs Linux Software Raid), compression (ZFS lz4 vs raw), IO-Scheduler (CFS, deadline, ULE). Also it is not clear if the Postgres dataset includes the data and transaction logs (WAL).

Yeah I was thinking about cache as well.

It also seems unfair to compare Ext4fs on Linux with ZFS on FreeBSD. Ubuntu ships with ZFS included. There's also Btrfs.

Also, its known that Netflix uses FreeBSD internally, and I'm curious why.