toast_tuple_target

Description

The toast_tuple_target specifies the minimum tuple length required before we try to compress and/or move long column values into TOAST tables, and is also the target length we try to reduce the length below once toasting begins. This affects columns marked as External (for move), Main (for compression), or Extended (for both) and applies only to new tuples. There is no effect on existing rows. By default this parameter is set to allow at least 4 tuples per block, which with the default block size will be 2040 bytes. Valid values are between 128 bytes and the (block size - header), by default 8160 bytes. Changing this value may not be useful for very short or very long rows. Note that the default setting is often close to optimal, and it is possible that setting this parameter could have negative effects in some cases. This parameter cannot be set for TOAST tables.

Applies to

Tables and materialized views

Value type

integer

Usage

WITH (toast_tuple_target = value)

Scope

This relation-level setting applies to the table or materialized view. Any corresponding global parameter has a separate entry.

Common table storage rules

The WITH clause can specify storage parameters for tables, and for indexes associated with a UNIQUE , PRIMARY KEY , or EXCLUDE constraint. Storage parameters for indexes are documented in CREATE INDEX . The storage parameters currently available for tables are listed below. For many of these parameters, as shown, there is an additional parameter with the same name prefixed with toast. , which controls the behavior of the table's secondary TOAST table, if any (see Section 66.2 for more information about TOAST). If a table parameter value is set and the equivalent toast. parameter is not, the TOAST table will use the table's parameter value. Specifying these parameters for partitioned tables is not supported, but you may specify them for individual leaf partitions.

Materialized views

This clause specifies optional storage parameters for the new materialized view; see Storage Parameters in the CREATE TABLE documentation for more information. All parameters supported for CREATE TABLE are also supported for CREATE MATERIALIZED VIEW . See CREATE TABLE for more information.

Related entries

• CREATE TABLE

Documentation and source

• PostgreSQL 18 English manual
• PostgreSQL 18 English manual

Related entries

• autovacuum_analyze_scale_factorautovacuum_analyze_scale_factor
• autovacuum_analyze_thresholdautovacuum_analyze_threshold
• autovacuum_enabledautovacuum_enabled
• autovacuum_freeze_max_ageautovacuum_freeze_max_age
• autovacuum_freeze_min_ageautovacuum_freeze_min_age
• autovacuum_freeze_table_ageautovacuum_freeze_table_age
• autovacuum_multixact_freeze_max_ageautovacuum_multixact_freeze_max_age
• autovacuum_multixact_freeze_min_ageautovacuum_multixact_freeze_min_age
• autovacuum_multixact_freeze_table_ageautovacuum_multixact_freeze_table_age
• autovacuum_vacuum_cost_delayautovacuum_vacuum_cost_delay
• autovacuum_vacuum_cost_limitautovacuum_vacuum_cost_limit
• autovacuum_vacuum_insert_scale_factorautovacuum_vacuum_insert_scale_factor