19 ms·
OK a one thing that stands out here and please correct me if I'm wrong: > ... multi-petabyte cluster of Postgres instances... blocksize relatively high at 64 k
by nwmcsween 5y ago
OK a one thing that stands out here and please correct me if I'm wrong:
> ... multi-petabyte cluster of Postgres instances... blocksize relatively high at 64 kb ...
The dataset should be the Postgresql "page size" which IIRC is 8KB, the reasoning for this is RMW cycles will read 64kb modify 8KB and write out the full 64KB amplifying writes 8 fold.
Also IIRC Postgresql will automatically use TOAST when needed?
- jhk727 5y agoGood callout - we use a higher blocksize than Postgres page size because it gives us a much higher compression ratio, at the cost of some read/write amplification. And yes - Postgres will automatically TOAST oversized tuples and compress the relevant data (if you configure it to do so). This is much lower impact for us than filesystem level compression, as it doesn't affect the main relation heap space (or any indexes).
- nwmcsween 5y agoThere has to be something better than a potential 8 fold write performance reduction wrt compression
- nwmcsween 5y agoWhat about: https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf https://people.freebsd.org/~seanc/postgresql/scale15x-2017-p... 16k record size 2x amplification and still (?) allows compression w/ lz4
- jhk727 5y agoWe tested this extensively a few years back. We saw a compression ratio of ~1.9 with 8k recordsize/lz4, ~2.7 with 16k/lz4, and now ~5.5 with 64k/zstd.