Hacker News Reader

Comment by animalnewbie | Hacker News Reader

Anyone got postgres (or general db) tips for a CoW-fs? I'm using postgres on zfs.

Ensure that the dataset containing your postgres data is configured with record size equal to postgres page size or close enough (Lots of places use 8kB ZFS records for 4kB pages).

This will reduce write amplification due to excessive read-modify-write cycles.

Pg uses 8kb pages by default. You do really want your fs/db page size to match though except in very very specific scenarios.

Why doesn't pg query the fs for the default scenario I wonder

Because it's a compile time flag, not a init or config parameter.

I agree it would be nice if the page size was more adaptive to just not have FS page size alignment issues.

Ahh, I didn't know it used 8kB page - that makes it even better, because it removes any read-modify-write cycle if you run with record size of 8k.

So, with the major caveat that I am not an expert and your mileage will vary: After some playing around with it, I intentionally reverted our postgres datasets back to the default ZFS size (EDIT: 128K) because we weren't super performance sensitive and the smaller pages killed compression. Obviously compression ratio vs speed is going to depend very heavily on exactly what you're doing, but it seems to have been a good trade for us.

an interesting hack is to create two tablespaces, one with record size of 8kB, one with recordsize set to maximum, and then appropriately assign tables to them according to ones performance needs. Rarely-written (for example historical) data can be put into partitions living on the large record tablespace (for example 1M recordsize) and have indexes redone with 100% fillfactor.

Of course all of that should be informed by getting actual data about performance first ;)

Reply on news.ycombinator.com