Disable TOAST compression for all columns

All we need is an easy explanation of the problem, so here it is.

I am running PostgreSQL on compressed ZFS file system. 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 but the documentation is unclear if I can disable it.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

default_toast_compression only determines the compression method to be used when column values are toasted. It does not decide whether values are toasted at all.

Notably, the TOAST mechanism applies to values both stored inline and out-of-line. It’s "The Oversized-Attribute Storage Technique".

You want to disable compression completely, not just choose the preferred algorithm. Before going there: Is it really worth the possible complications? Let me cite the full quote from your reference page:

Disabling TOAST compression

To not compress data twice, you can disable PostgreSQL TOAST
compression by setting column storage to EXTERNAL. But it does not
make much difference
:

  • LZ4 is extremely fast.
  • Both LZ4 and ZSTD have special logic to skip incompressible (or already compressed) parts of data.

Bold emphasis mine. Consider using the (new in Postgres 14) LZ4 compression method and be done with it. See:

What you ask

You could set STORAGE per data type. Example for type varchar:

ALTER TYPE varchar SET (STORAGE = external);

This is then copied to every table column with that type, that is created after the change. The manual:

attstorage char

Normally a copy of pg_type.typstorage of this column’s type. For
TOAST-able data types, this can be altered after column creation to
control storage policy.

You have to change existing columns additionally.

Only base types can be altered this way (see pg_type.typtype in the manual). And it may introduce caveats. The manual on ALTER TYPE:

SET ( property = value [, ... ] )

This form is only applicable to base types. It allows adjustment of a subset of the base-type properties that can be set in CREATE TYPE. Specifically, these properties can be changed:

[…]
  • STORAGE can be set to plain, extended, external, or main (see Section 70.2 for more information about what these mean).
    However, changing from plain to another setting requires superuser
    privilege (because it requires that the type’s C functions all be
    TOAST-ready), and changing to plain from another setting is not
    allowed at all (since the type may already have TOASTed values present
    in the database). Note that changing this option doesn’t by itself
    change any stored data, it just sets the default TOAST strategy to be
    used for table columns created in the future. See ALTER TABLE
    to change the TOAST strategy for existing table columns.

That said, as a superuser, you could change all base types in the system with this query:

UPDATE pg_catalog.pg_type
SET    typstorage = 'e'
WHERE  typtype = 'b'
AND    typstorage = 'm';  -- "instead of MAIN"

I never tried that myself, and would not go there, and would strongly advise against it unless you know exactly what you are doing. Messing with system catalogs can break your database cluster.

I would just go with default_toast_compression = 'lz4'.
Maybe experiment with one or two selected data types with ALTER TYPE ... SET (STORAGE = external); first …

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply