default_toast_compression
A configuration parameter determining the default TOAST compression method
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
- PostgreSQL 14
- added (commit bbe0a81d)
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
References
- PostgreSQL documentation: default_toast_compression
Useful links
- Configurable LZ4 TOAST compression - May 2021 blog article by Dilip Kumar / EDB