ZFS PostgreSQL and zfs/ufs performace

French Fries


Reaction score: 13
Messages: 53

Dear friends,

I am migrating from Debian GNU/Linux to FreeBSD. I could be using KfreeBSD, but I prefer blending-edge and also become part of a community, so I will not stick to Debian, although I appreciate the spirit.

The server board has 32 CPUs, 32GB of RAM, 1 x SSD and 5 x SATA disc drives. Recent hardware (2013 by the time of writing 2017). The server has a small RAID card, but it is disabled for obvious reasons.

What would be the best strategy when using PostgreSQL:
  1. All SATA drives in a single zpool, SSD as write/read cache in zpool.
  2. All SATA drives in a zpool0. 1 SSD in zpool1 with some tweaking to avoid duplication of data. PostgreSQL main cluster on zpool1, a secondary cluster on spool0.
  3. 4 x SATA drives on zpool0. 1 SSD on zpool1, no mirroring. 1 SATA on zpool2, no mirroring. PostgreSQL main cluster on zpool1, a secondary cluster on spool2.
  4. 4 x SATA drivers on zpool0. 1 SSD slice0 and 1 SATA slice1 on UFS2. PostgreSQL main cluster on SSD slice0 , a secondary cluster on slice1.
PostgreSQL is not the only application installed, and this is a low-traffice server, used for home experiments and self-teaching. PostgreSQL is installed with at least a 1/4 of shared memory and fsync on (I am not bold enough to remove sync). A power backup is installed. Frequent backups are done in PostgreSQL.

From a scrict point of view of disc transfer, what would give the better performance for a PostgreSQL database (larger than RAM and shared memory) with frequent disc access.

I am worried that ZFS might duplicate values when PostgreSQL has its own point-in-time recovery system. And this might leed to a traditional buffer-bloat issue.

So I am looking for the best solution which would go directly to disc. I like to remove all cache from my applications, including web, optimizing in code. But there are cases, i.e. database "INSERTS" when a large buffer might be preferable to let the OS migrate data inside the cluster.

So what would be your general opinion as regards ZFS, UFS2 and PostgreSQL? A solution would be to create a starting zpool of 4 drives and benchmark other solutions from that point. Still, I'd like to hear any feedback from users about this disc issue.

Kind regards,
French Fries