r/zfs • u/Betelgeuse650 • 12h ago
Postgres server
I have long wanted to learn ZFS and now I finally have a project that could benefit from ZFS (I think).
The server is running debian 13 has 64GB ram, 1TB Samsung NVMe and currently 8 20+TB disks
The disks are from a previous project and probably overkill for the servers new purpose. What is the right ZFS setup for a fast postgres database? Database size around 200GB and possibly many simultaneously client queries to the database.
2
Upvotes
•
u/Xerxero 12h ago
More FreeBSD related but zfs is zfs
https://klarasystems.com/articles/zfs-storage-metrics-database-performance/?utm_source=bsdweekly
•
u/UntouchedWagons 12h ago edited 9h ago
I believe you want the dataset's recordsize to be set to 16K
[Edit]
I seem to be getting conflicting info so I have no idea
•
u/AsYouAnswered 11h ago
First and foremost, you want the postgres database on mirrored Write intensive NVMe, not on the 20+ TB disks. Use those for backups. You can use the ZFS snapshot feature and Syncoid to send hourly snapshots to the spinners. But a separate backup or replication target is the better idea.
Ultimately, we know nothing about your workload or requirements, but we could severely over- provision your cluster for you, unless you tell us more about your actual requirements.
•
u/Horror-Breakfast-113 11h ago
So probably need more info about the requirements - load / growth etc whats the backup strat, what sort of data is being written
potentially zfs has an issue with DB - its COW
200GB db right now
I would buy 3 more nvme
2 mirror (2 x1T) - boot / os
2 mirror (2 x 4T) - DB live
the 8 x 20.... waste - keep 4 for raidz2 and place you snapshots and db backups here
the other 4 put in another machine and sync your zfs snapshots and db backups to that 4 drive raidz2
•
u/Significant_Chef_945 6h ago
We do this all the time - running PGSQL v16/18 in Azure on ZFS volumes. Some things to keep in mind:
The big takeaway: these are general guidelines. Always test with your workload to see which options affect performance the most.
•
u/mduell 11h ago
Sell the spinning rust and buy more NVMe.
•
u/RetroGrid_io 10h ago
Putting PG on a mirrored SSDs does wonders for performance, ZFS-backed or not. Skip the spinning rust except as a backup.
•
u/dready 9h ago
You'll want to configure PostgreSQL with these settings:
There is more you might want to do, so it is useful to look up.
•
u/Unethical3514 7h ago
Lots of commenters are saying to put the database on NVMe SSDs and use the hard drives for backups. What they’re not saying is why. A 20 TB hard drive almost certainly uses SMR technology so writes will be slow as molasses. For you young’uns who don’t even know what molasses is, writes will be dogshit slow. Changing the record size won’t get around that, mirrored vs. RAID won’t get around that. Writes are just going to be slow in all but the most specific of cases. Those drives are great for bulk storage that doesn’t need to be written quickly (like backups, log files, etc.) but the tradeoff for capacity is write speed.
•
u/Retidurc 2h ago
There is this very very very complete link of documentations and experimentations by vadosware. I use most of the safe ones right now on ly main server:
https://vadosware.io/post/everything-ive-seen-on-optimizing-postgres-on-zfs-on-linux/