Shared Detoast

Overview

PostgreSQL uses the TOAST (The Oversized-Attribute Storage Technique) mechanism to store oversized fields, such as large JSONB values, long text, and large binary data. When a query needs to access the actual content of these fields, a detoast operation is performed to reassemble and decompress the chunked, possibly compressed data into complete values.

In native PostgreSQL, detoast is performed lazily: each expression that accesses the field runs its own detoast independently, and the results cannot be shared. For example:

SELECT big_jsonb->'a', big_jsonb->'b', big_jsonb->'c',
       big_jsonb->'d', big_jsonb->'e'
FROM t;

This query performs detoast on the big_jsonb column of the same row five times. For large fields, this causes significant CPU and memory allocation overhead.

PolarDB for PostgreSQLprovides the Shared Detoast feature: after the first detoast of a row is performed in the executor, the result is cached and reused by subsequent expression evaluations on the same row. This avoids repeated detoast operations and delivers significant performance gains at a small memory cost. This feature also takes effect for JIT execution.

Scope of application

This feature is supported on the following versions of PolarDB for PostgreSQL:

• PostgreSQL 14(kernel minor version 2.0.14.10.19.0 and later)

Applicable scenarios

The following scenarios can benefit from the Shared Detoast feature:

• A single large field is accessed by multiple expressions: for example, extracting multiple keys from the same JSONB column (in WHERE big->>'c3' = '1' AND big->>'c4' = '2', big only needs to be detoasted once), or running multiple functions on the same long text column.

• The select list outputs multiple fields from the same JSON column: for example, in SELECT big->'a', big->'b', big->'c' FROM t, the big column of the same row is detoasted only once and the results are reused by the multiple value-extraction expressions.

• A large field appears in both the filter condition and the select list: for example, WHERE big->>'type' = 'x' together with SELECT big->'payload'.

• A join condition and the select list both reference a large field: Nested Loop, Hash Join, and Merge Join nodes are supported.

The more times the field is accessed and the larger the field is, the greater the benefit.

Considerations and limits

The Shared Detoast feature has the following considerations and limits:

• Memory overhead: this feature trades memory for time. Caching detoast results increases the memory usage when processing a single row (approximately the decompressed size of the related fields), but the cache is released as soon as the row finishes processing and typically does not cause noticeable memory pressure.

• Disabled for some plan nodes: when a large field is downstream of nodes that need to materialize tuples, such as Sort, Hash (the inner table of Hash Join), Materialize, Memoize, WindowAgg, and incremental sort, the optimizer automatically disables the Shared Detoast feature for the related columns to avoid oversized materialized tuples, which would exhaust work_mem faster and generate extra temporary file I/O. This is automatic behavior and requires no manual intervention on your part.

• Applies only to columns accessed by expressions:

  • Only TOAST columns that are directly operated on in expressions (such as big->'key' and length(big_text)) are detoasted ahead of time.

  • Columns that are only output as-is (such as SELECT big FROM t) are not affected and keep the native lazy behavior.

• Minor overhead for workloads without large fields: the Shared Detoast feature traverses the plan tree one extra time during execution plan generation and maintains an independent memory context for tuple slots. For high-frequency short queries that do not involve TOAST large fields at all (such as sysbench-style OLTP workloads), measurements show a performance loss of less than 3%. If your workload is dominated by such queries and you need maximum performance, set the polar_enable_shared_detoast_datum parameter to OFF. After the feature is disabled, performance is on par with versions that do not include this feature.

• No impact on result correctness: the Shared Detoast feature only changes the timing and reuse of detoast operations. It does not change any query semantics or results.

Usage

The Shared Detoast feature is controlled by the polar_enable_shared_detoast_datum parameter. It is enabled by default on clusters that meet the version requirement and can be used without any configuration.

When the feature is enabled, the optimizer automatically identifies queries that can benefit during execution plan generation and applies the optimization, and automatically skips scenarios where it does not apply. No manual intervention is required. The polar_enable_shared_detoast_datum parameter takes effect when the optimizer generates an execution plan. Changes to the parameter take effect for newly executed queries, including prepared statements whose plans are regenerated.

The polar_enable_shared_detoast_datum parameter can be modified at the session level. You can temporarily disable or enable it within a session to compare and verify the optimization effect:

-- Disable for the current session
SET polar_enable_shared_detoast_datum = off;

-- Enable for the current session
SET polar_enable_shared_detoast_datum = on;

Example

• Create a test table with a large JSONB field and extract multiple keys from the same JSONB column. The big column of each row is detoasted only once:

  -- Create a test table with a large JSONB field
  CREATE TABLE t_big (id int, big jsonb);
  INSERT INTO t_big
  SELECT i,
         (SELECT jsonb_object_agg(k, repeat('x', 1000))
          FROM generate_series(1, 100) k)
  FROM generate_series(1, 10000) i;
  
  -- Extract multiple keys from the same JSONB column
  EXPLAIN ANALYZE
  SELECT big->'1', big->'2', big->'3', big->'5', big->'10'
  FROM t_big;

• You can compare the execution time of EXPLAIN ANALYZE with the parameter enabled and disabled to verify the optimization effect:

  SET polar_enable_shared_detoast_datum = off;
  EXPLAIN ANALYZE SELECT big->'1', big->'2', big->'3', big->'5', big->'10' FROM t_big;
  
  SET polar_enable_shared_detoast_datum = on;
  EXPLAIN ANALYZE SELECT big->'1', big->'2', big->'3', big->'5', big->'10' FROM t_big;