Postgresql ZFS optimize https://wiki.wut.ee/sysadmin/postgresql

postgresql_zfs.md

PostgreSQL optimize

ZFS Config

atime = off  # Recoding access time on file open is stupid
relatime = on  # writing access time on file write is fine because we are writing anyway.
compression = lz4  # Its faster to compress/decompress on the CPU than it is to wait for the data from HDD/SSD  
recordsize = 128K  # postgres native 8k will give give horrible compression ratio, default 128k is fine
primarycache = metadata  # maybe?

Each postgres database cluster gets its own dataset

Postgres config

https://pgtune.leopard.in.ua/#/

And also

full_page_writes = false

This disables double writes for data because ZFS is always consistent, ~2x insert perf boost

• https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-FULL-PAGE-WRITES
• https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-page-writes/

Optional

Set zfs commit time to 1 second

echo 1 > /sys/module/zfs/parameters/zfs_txg_timeout
echo 'options zfs zfs_txg_timeout=1' >> /etc/modprobe.d/zfs.conf

And disable postgres synchronous_commit

synchronous_commit = off

This should give ~20x perf boost but in case of system chrash you will lose up to 1 second of data Data will still always be consistent thanks to ZFS

• https://www.postgresql.org/docs/11/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
• https://github.com/zfsonlinux/zfs/wiki/ZFS-on-Linux-Module-Parameters#zfs_txg_timeout

Links

• https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf
• https://www.youtube.com/watch?v=T_1Zo4m4v_M
• https://www.2ndquadrant.com/en/blog/pg-phriday-postgres-zfs/

Other

• https://reorg.github.io/pg_repack/

Upstream: https://wiki.wut.ee/sysadmin/postgresql