default_toast_compression

A configuration parameter determining the default TOAST compression method
This entry relates to a PostgreSQL feature which is part of PostgreSQL 14, due to be released in late 2021.

default_toast_compression is a configuration parameter determining the default TOAST compression method for columns of newly-created tables.

default_toast_compression was added in PostgreSQL 14.

Usage

default_toast_compression can be one of pglz (the default) or lz4 (which requires PostgreSQL to have been compiled with the option --with-lz4).

To determine whether lz4 is available, execute the following query:

postgres=# SELECT true FROM pg_config WHERE name = 'LIBS' and setting like '%lz4%';
 bool 
------
 t
(1 row)

Default value

The default value for default_toast_compression is: pglz.

Change history

Examples

By default, TOAST-able tables will be created with the compression type pglz:

postgres=# CREATE TABLE foo (val1 TEXT);
CREATE TABLE

postgres=# \d+ foo
                                          Table "public.foo"
 Column | Type | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
 val1   | text |           |          |         | extended | pglz        |              | 
Access method: heap

Setting default_toast_compression to lz4 (if available):

postgres=# ALTER SYSTEM SET default_toast_compression='lz4';
ALTER SYSTEM

postgres=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# ALTER TABLE foo ADD val2 TEXT;
ALTER TABLE

postgres=# \d+ foo
                                          Table "public.foo"
 Column | Type | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
 val1   | text |           |          |         | extended | pglz        |              | 
 val2   | text |           |          |         | extended | lz4         |              | 
Access method: heap

Note that default_toast_compression can only be set to lz4 if PostgreSQL was compiled with --with-lz4, otherwise an error will be raised:

postgres=# ALTER SYSTEM SET default_toast_compression='lz4';
ERROR:  invalid value for parameter "default_toast_compression": "lz4"
HINT:  Available values: pglz

Categories

GUC configuration item, Storage