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