Sample Header Ad - 728x90

Disable TOAST compression for all columns

6 votes
2 answers
3030 views
I am running PostgreSQL on [compressed ZFS file system](https://bun.uptrace.dev/postgres/tuning-zfs-aws-ebs.html#disabling-toast-compression) . One tip mentioned is to disable PostgreSQL's inline TOAST compression because ZFS can compress data better. This can be done by setting column storage to EXTERNAL. I can do this column by column with:
ALTER TABLE my_table ALTER COLUMN my_column SET STORAGE EXTERNAL;
However, this might be a bit cumbersome, as every schema needs to have migrated to this by hand. Are there easy ways to - Set default STORAGE to EXTERNAL instead of MAIN for all columns - Disable TOAST compression other way I found [default_toast_compression option](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION) but the documentation is unclear if I can disable it.
Asked by Mikko Ohtamaa (331 rep)
Jul 31, 2022, 06:19 AM
Last activity: May 10, 2024, 05:07 AM