Motivate PostgreSQL recordsize recommendations

[nh2]

The Tuning Guide currently says for postgres:

Set compression=lz4 and recordsize=32K (64K also work well, as does the 128K default)

I would first think that recordsize=8K should be optimal given that postgres pages are 8K. Especially because the section on SQLite just below it says

Set the recordsize to 64K. Set the SQLite page size to 65536 bytes

which matches DB and ZFS record sizes.

Probably the recommendation for larger records is because of compression? It would be great if that section could explicitly point it out.

---

Relatedly, I found that the man zfsprops info on DBs looked very wrong, perhaps good to cross-check that:

• recordsize man page is wrong/outdated zfs#18156

Thanks!

[nh2]

If one didn't want compression for postgres, would the recommendation switch to recordsize=8K?

[gmelikov]

Probably the recommendation for larger records is because of compression? It would be great if that section could explicitly point it out.

yep, you may find some context from git history of these lines: #369

We may want to add explanation about compression, indeed, feel free to propose a PR.

Main point here is that small blocks are not cheap on ZFS, so LZ4 compression is so fast that by default it's really useful (that's why compression=on is by default nowadays).

[nh2]

PR: #601