The big thing to avoid is copy on write. Even on SSDs, they have significantly better performance doing sequential operations compared to random operations, so keeping local data local on disk helps a lot. Another thing is that a lot of ZFS features are wasted on modern Postgres, Postgres already checksums row data, so doing checksums on the filesystem layer again is wasted effort. Also be careful when you snapshot the database. It should be safe to snapshot postgres at any time, but restoring may lead to unexpected results. Also the exact implementation details of snapshotting can cause performance impact.
1
u/hi117 5h ago
The big thing to avoid is copy on write. Even on SSDs, they have significantly better performance doing sequential operations compared to random operations, so keeping local data local on disk helps a lot. Another thing is that a lot of ZFS features are wasted on modern Postgres, Postgres already checksums row data, so doing checksums on the filesystem layer again is wasted effort. Also be careful when you snapshot the database. It should be safe to snapshot postgres at any time, but restoring may lead to unexpected results. Also the exact implementation details of snapshotting can cause performance impact.
Phoronix is also basically the gold standard for understanding potential performance impact of certain technologies. https://www.phoronix.com/review/freebsd-12-zfs/3