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
Last activity: May 10, 2024, 05:07 AM