ZFS Tunings for PostgreSQL

Question

What are the recommended ZFS tunings for PostgreSQL 11 (with TimescaleDB 1.6 extension) running on striped mirrored vdevs of SATA3 SSDs?

I am considering:

• ashift=12
• atime=off
• relatime=on (or off?)

But am unsure about the following:

• xattr=sa
• logbias=throughput
• redundant_metadata=most

Data compression is pretty important, so I am thinking of keeping the default setting of recordsize=128K instead of changing it to 8K which will likely cause data compression to be much less effective.

Any advice greatly appreciated!

Answer 1

You should always use ashift=12 today unless you have an overwhelmingly good reason not to.

relatime=off might save you a vanishingly small amount. Don't expect it to be measurable.

xattr=sa is important if you use extended attributes (e.g. SELinux) and you have a lot of tables and file opening/closing operations.

logbias=throughput should reduce your disk I/O volume, and it will help with SSD storage where there is no huge disconnect between bandwidth and latency like there is on spinning media.

recordsize=8k will ensure you don't have any RMW overheads. You'll have to decide whether the extra compression ratio achievable with larger recordsize is a net victory in your use case.

You may find the slides from a lecture I gave on the subject useful.