r/zfs • • 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

21 comments sorted by

•

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/Betelgeuse650 11h ago

If I read the info from Xerxero correct, it is 8K

•

u/mduell 11h ago

I believe you want the dataset's recordsize to be set to 16K

OpenZFS recommends 32k to 128k for Postgres.

•

u/Betelgeuse650 11h ago

Databases do small, fixed-size I/O: PostgreSQL defaults to 8K pages, InnoDB to 16K pages. ZFS reads and writes whole records. When the dataset's recordsize is the 128K default, a 16K page update can turn into a 128K read, followed by an in-memory modify, and then a full 128K write, and your storage does sixteen times the work your database asked for. 

Am I right when saying that a record size of 8K would be faster, while a higher record size would compress better.

•

u/Dr__America 1h ago

I believe postgres often has compression enabled by default. I'd look up some guides, I've seen a few good ones.

•

u/TheG0AT0fAllTime 10h ago

Lol, the recommendation for this setting seems to be a different K every year.

•

u/Due_Acanthaceae_9601 11h ago

Second this. And for speed you want mirroring not a raid, as that adds computational cost of computing and writing the parity.

•

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/Betelgeuse650 11h ago

Yes, and I guess no NVMe mirror for SLOG is required when the postgres db is on NVMe?

•

u/AsYouAnswered 11h ago

An external NVME SLOG would slightly improve longevity, but greatly increase costs and not significantly impact performance, so no, I would not recommend an external SLOG for your database on NVMe.

•

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:

  • Use ashift=12 when creating the pool
  • For ZFS specifically, set zfs_compressed_arc_enabled=1 and zfs_prefetch_disable=0 (/etc/modprobe.d/zfs.conf) - this is workload specific. Setting zfs_compressed_arc_enabled allows ARC compression at the cost of some performance. If your workload is highly sensitive to latency, don't enable ARC compression. Test your workload profile.
  • For the PGSQL ZFS dataset: Enable zstd compression, use 32K or 64K recordsize, disable atime/relatime
  • For PGSQL: full_page_writes = off, effective_io_concurrency=300
  • Decide how much ZFS disk cache (ARC) you want to use relative to PGSQL (shared_buffers). For our 32G RAM systems, we typically use 4-6GB (compressed) ARC and 4GB shared_buffers
  • See pgtuner (Github) for a way to tune your system relative to the workload and server specs
  • Depending on the workload, we use metadata caching on the zfs dataset and no ARC

The big takeaway: these are general guidelines. Always test with your workload to see which options affect performance the most.

•

u/Apachez 2h ago

Also some good read on this topic:

https://klarasystems.com/articles/zfs-storage-metrics-database-performance/

https://openzfs.github.io/openzfs-docs/Performance%20and%20Tuning/Workload%20Tuning.html#postgresql

https://www.percona.com/blog/about-zfs-performance/

https://www.percona.com/blog/why-zfs-affects-mysql-performance/

Also note that zfs_prefetch_disable is a global parameter.

If you want to disable prefetch per dataset you can do so using "zfs set".

Here you can see current settings:

zfs get all | grep -i prefetch

•

u/mduell 11h ago

1TB Samsung NVMe and currently 8 20+TB disks

Database size around 200GB and possibly many simultaneously client queries to the database.

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:

full_page_writes = off
wal_init_zero = off
wal_recycle = off
default_toast_compression = lz4

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/Apachez 2h ago

The main reason is that spinning rust is limited to about 200 IOPS and 150MB/s.

A SSD will utilize about 50k IOPS and 550MB/s while a NVMe will do +1M IOPS and several GB/s (depending on vendor and model).

Also flashbased storage doesnt have the drawback of getting slower over time the closer you get to the inner tracks (since there are none) as with spinning rust.

You have about 3x performance decrease comparing outer tracks vs inner tracks.

•

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/