5

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.

2 Answers 2

3

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 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 ...

Update existing table columns

Here is a script to update all existing table columns of a given schema to disable compression - only those that actually need it.

DO
$do$
DECLARE
   _ddl text;
BEGIN
   FOR _ddl IN
      SELECT concat(
             'ALTER TABLE '
           , c.oid::regclass  -- safe!
           , string_agg(format(' ALTER COLUMN %I SET STORAGE %s'
                             , a.attname
                             , CASE attstorage             -- only take away compression!
                                 WHEN 'm' THEN 'PLAIN'     -- main -> plain
                                 WHEN 'x' THEN 'EXTERNAL'  -- extended -> external
                                 ELSE null                 -- ELSE should not occur!
                               END
                              ), ',')
           , ';') AS cmd
      FROM   pg_catalog.pg_namespace s
      JOIN   pg_catalog.pg_class     c ON c.relnamespace = s.oid
      JOIN   pg_catalog.pg_attribute a ON a.attrelid = c.oid
      WHERE  s.nspname = 'public'                   -- only given schema
      AND    c.relname !~ '^pg_'                    -- exclude typical system tables (typically none in schema public)
      AND    c.relkind IN ('r', 'p', 'm')           -- only: tables, partitioned tables, materialized views
      AND    a.attnum > 0                           -- only user columns
      AND    NOT a.attisdropped                     -- exclude dropped columns
      AND    attstorage = ANY ('{m, x}'::"char"[])  -- only modify columns that can currently be compressed
      GROUP  BY c.oid                               -- PK
      ORDER  BY c.relname
   LOOP      
      RAISE NOTICE '%', _ddl;  -- to debug / execute manually
   -- EXECUTE _ddl;            -- PAYLOAD; uncomment to activate!
   -- DO NOT ignore any exceptions
      COMMIT;  -- OPTIONAL, to avoid lockig multiple tables at once; requires Postgres 11+
   END LOOP;   
END
$do$;

I added this after another answer suggested a script with many problems.

This works for Postgres 16 (and a couple earlier versions, at least down to Postgres 11). But since it messes with internals, there is no guarantee for future versions of Postgres.

The payload command is EXECUTE _ddl;, which is currently commented out, as this is a dangerously invasive operation.

This only updates columns that actually need an update - and only to disable compression. Note there are actually 4 possible states. The manual:

p (plain): Values must always be stored plain (non-varlena types always use this value).
e (external): Values can be stored in a secondary “TOAST” relation (if relation has one, see pg_class.reltoastrelid).
m (main): Values can be compressed and stored inline.
x (extended): Values can be compressed and/or moved to a secondary relation.

We only want to change xe and mp. Not everything to e (EXTERNAL).

Run a single ALTER command per table, not one for every column. Much cheaper.

Do not ignore all possible exceptions! A clean script should not raise any to begin with.

When concatenating dynamic SQL strings, beware of SQL injection. All identifiers are security hazards if not quoted properly. This script is safe. See:

We also do not need an expensive nested code block for every column.

I based this on Postgres system catalogs - the actual source of truth in a Postgres database - instead of the information schema view information_schema.columns. Either choice has its merits. But the latter is not a good fit for the purpose as it also contains columns of views, which do not store data and cannot be involved in this. See:

This script only targets relevant kinds of "tables": ordinary tables, partitioned tables and materialized views. The manual about pg_class.relkind:

r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index

COMMIT in a DO command requires Postgres 11 or later. See:

2
  • Thank you for an incredible comprehensive answer. I am setting up the new database from scratch before importing the data over, so I am happy to do dangerous operations and see how it plays out. Let me get back in a week or two to report what happened. Commented Aug 1, 2022 at 3:04
  • 1
    I also run a quick napkin analysis of my data and realised because toast affects mainly TEXT, JSONB, BYTEA records, which I have a lot, but the value length of the records stay mostly under 2kbytes. So the number of affected rows for this optimisation would be very low for my use case for now. Commented Aug 1, 2022 at 3:22
0

For existing columns:

DO $$DECLARE r record;
BEGIN
    FOR r IN
       SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name ||
              '" ALTER COLUMN "' || column_name || '" SET STORAGE EXTERNAL;' a
       FROM information_schema.columns
       WHERE table_schema = 'your_schema' AND table_name NOT LIKE 'pg_stat_statements%'
    LOOP
        BEGIN
            EXECUTE r.a;
        EXCEPTION WHEN OTHERS THEN
            -- ignore any `[0A000] ERROR: column data type integer can only have storage PLAIN`
            CONTINUE;
        END;
    END LOOP;
END$$;

After running this and VACUUM FULL all my tables, their size compressed by zstd on zfs decreased by 2GiB from 46GiB to 44GiB(uncompressed 89GiB).

Better one: Disable TOAST compression for all columns

2
  • Nice. A good recipe. Commented May 8 at 9:13
  • 2
    @Mikko Not a good recipe, actually. This script has multiple issues. Do not use it! I added a safe script with explanation to my answer. Commented May 10 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.