Postgresql and ZFS
Why I need ZFS instead of buildin TOAST?
For tghub I use a pretty cheap VPS with only 1tb storage. So I need compress my data. Despite the fact that Postgresql has TOAST feature (The Oversized-Attribute Storage Technique), it doesn't suit for me. By default, TOAST trigger for text data with 2 kB size. The message table of my project tghub has almost 3b rows and continues to grow, but they are usually small, so TOAST will not work for me. I decided to try store message data in the separate tablespace and move it to the ZFS filesystem.
High level schema
Current performance
I managed to archive good balance between drives.
- sda -- Default Tablespace
- sdb -- ZFS Tablespace
- sdc -- Index Tablespace
During normal workload, the index disk is the busiest, that is expected. The disk has 2 indexes, which are been changed by random inserts (~800 rows per seconds). It's better not to overload drive with default tablespace, because it has indexes for search. When many users decide to search something at the same time, this disk should have capacity.
Tuning Postgresql and ZFS
I did some extra configuration for zfs ans postgresql:
- turn off TOAST, not to compress data twice:
ALTER TABLE channel_message
ALTER COLUMN text SET STORAGE EXTERNAL;- Follow zfs recommendation for databases and set size of sector to 32K. Default size is 128K. It's too much and causes to unwanted reading. We can even set size of sector to 8K, but it decreased compression ratio. So it's important to find a balance between reading speed (smaller sector) and compression radio (bigger sector)
sudo zfs set recordsize=32K datapool- Turn off updated time, it really doesn't make sense:
sudo zfs set atime=off datapool- Set compress to zstd, it's slower than lz4 but has better compression ratio (could be x2 for text data):
sudo zfs set atime=off datapoolCurrently, my channel_message table with almost 3b rows takes 1061Gb uncompress vs 291Gb compressed in the ZFS drive. It's more than 3x ratio!