default_toast_compression

Definition PG 18 manual

This variable sets the default TOAST compression method for values of compressible columns. (This can be overridden for individual columns by setting the COMPRESSION column option in CREATE TABLE or ALTER TABLE.) The supported compression methods are pglz and (if PostgreSQL was compiled with --with-lz4) lz4. The default is pglz.

How it works

default_toast_compression sets the default compression method for compressible values. It chooses pglz or, when compiled in, lz4 for newly written compressible values; a column COMPRESSION clause overrides the session default.

default_toast_compression is a USER-context setting. An authorized role can change it for a session, while ALTER ROLE or ALTER DATABASE can establish a default for future sessions.

It supplies a default only when SQL omits an explicit choice, so schema migrations, object-level options, privileges, and later ALTER operations can override or outlive it.

Operational considerations

Changing default_toast_compression in one session and assuming role defaults, database defaults, or other pooled sessions changed with it.

Expecting a changed default to rewrite existing objects or override explicit DDL clauses.

Ignoring tablespace privileges, installed access methods or compression support, and restore portability.

Changing default_toast_compression globally without a rollback plan and a client or operational compatibility test.

Workload guidance

OLAP: LZ4 can favor faster compression and decompression when the build supports it, but measure actual compressibility and scan behavior before changing new-write policy.

OLTP: Benchmark pglz versus lz4 with representative wide values, update rates, CPU, WAL, and storage. Prefer explicit column COMPRESSION for important schemas over a surprising session default.

SMALL: Keep pglz unless CPU-versus-space measurements and package support justify lz4; changing the default does not recompress existing values.