PG19 Hacktober: 31 Days of New Features: Under the Hood: lz4 TOAST Default, Better Timing, and Hardware Acceleration Part-1

PostgreSQL 19 RC 1 landed today, and the headline features (REPACK, plan advice, and so on) are getting most of the attention. This post covers the quieter work that makes every cluster a little cheaper to run.
Part 1 covers the new TOAST compression default and the reworked timing infrastructure.
Part 2 covers the SIMD and CPU-instruction work (AVX2, ARM CRC32C, hex encoding) and the contrib modules and tools you’ll use to inspect the results.

Part 1: Defaults and Measurement

1.1 TOAST refresher: what lz4 actually replaces

TOAST (The Oversized-Attribute Storage Technique) exists because PostgreSQL pages are a fixed size (typically 8 kB) and tuples can’t span pages. Large values are compressed, split into chunks, or both.

The mechanics matter for understanding where the compression algorithm sits:

• Trigger threshold. TOAST kicks in only when a row is wider than TOAST_TUPLE_THRESHOLD (normally 2 kB). It then compresses and/or moves values out of line until the row is under TOAST_TUPLE_TARGET (also normally 2 kB, adjustable per table with toast_tuple_target) or no further gain is possible.
• Out-of-line storage. Values go into a separate TOAST table, cut into chunks of at most TOAST_MAX_CHUNK_SIZE bytes (about 2000 bytes, so four chunk rows fit per page). Each chunk row has chunk_id, chunk_seq and chunk_data, indexed by a unique index on (chunk_id, chunk_seq).
• The pointer. The heap row keeps an 18-byte TOAST pointer holding the TOAST table OID, the value’s chunk_id, the logical (uncompressed) size, the physical size, and the compression method used.
• Storage strategies. PLAIN (no compression, no out-of-line), EXTENDED (the default for most types: compress first, then go out of line), EXTERNAL (out of line, no compression) and MAIN (compress, avoid out-of-line except as a last resort).
• Header bits. TOAST borrows two bits of the varlena length word, which caps a value at 1 GB. Another bit combination marks the datum as compressed, in which case the length field holds the compressed size.

The algorithm is chosen per column with the COMPRESSION option on CREATE TABLE / ALTER TABLE. For columns with no explicit setting, PostgreSQL consults default_toast_compression at insert time. That GUC is what changed.

1.2 What changed in PG19

The release notes say the default TOAST compression method moves from pglz to the more efficient lz4. This is done by changing the default of default_toast_compression (commit 34dfca29, by Euler Taveira). The commit title ends in “take two”, which suggests an earlier attempt didn’t stick.

Three details matter in practice:

1. It depends on the build. lz4 requires a server built with --with-lz4. Without it the default stays pglz.
2. Nothing is rewritten. Only newly stored values use lz4. Each compressed value carries its own algorithm marker, so old pglz values keep decompressing as pglz. A single column can hold a mix indefinitely.
3. Other settings are unaffected. wal_compression = on also changed in PG19. It now translates to the first available of zstd, lz4 or pglz (commit 931c9701), where it previously always meant pglz.

The trade is a slightly worse compression ratio for noticeably cheaper compression and decompression. Both costs are paid on hot paths: every insert or update of a wide value, and every read that detoasts one.

1.3 Verifying it on your cluster

-- Is lz4 the default, and was this build compiled with it?
SHOW default_toast_compression;

-- Create wide, compressible values
CREATE TABLE docs (id bigint GENERATED ALWAYS AS IDENTITY, body text);

INSERT INTO docs (body)
SELECT repeat(md5(g::text), 200)      -- ~6.4 kB, compressible
FROM generate_series(1, 10000) g;

-- Which algorithm did each stored value get?
SELECT pg_column_compression(body) AS method, count(*)
FROM docs GROUP BY 1;

-- Logical vs stored size
SELECT avg(octet_length(body))   AS logical_bytes,
       avg(pg_column_size(body)) AS stored_bytes
FROM docs;

To see the “mixed column” behavior, compare against pglz:

SET default_toast_compression = 'pglz';
INSERT INTO docs (body)
SELECT repeat(md5(g::text), 200) FROM generate_series(1, 10000) g;

SELECT pg_column_compression(body), count(*) FROM docs GROUP BY 1;
-- both lz4 and pglz appear

You can pin a column regardless of the default. This only affects values written afterwards:

ALTER TABLE docs ALTER COLUMN body SET COMPRESSION pglz;

1.4 Practical guidance

1.3 Verifying it on your cluster

-- Is lz4 the default, and was this build compiled with it?
SHOW default_toast_compression;

-- Create wide, compressible values
CREATE TABLE docs (id bigint GENERATED ALWAYS AS IDENTITY, body text);

INSERT INTO docs (body)
SELECT repeat(md5(g::text), 200)      -- ~6.4 kB, compressible
FROM generate_series(1, 10000) g;

-- Which algorithm did each stored value get?
SELECT pg_column_compression(body) AS method, count(*)
FROM docs GROUP BY 1;

-- Logical vs stored size
SELECT avg(octet_length(body))   AS logical_bytes,
       avg(pg_column_size(body)) AS stored_bytes
FROM docs;

To see the “mixed column” behavior, compare against pglz:

SET default_toast_compression = 'pglz';
INSERT INTO docs (body)
SELECT repeat(md5(g::text), 200) FROM generate_series(1, 10000) g;

SELECT pg_column_compression(body), count(*) FROM docs GROUP BY 1;
-- both lz4 and pglz appear

You can pin a column regardless of the default. This only affects values written afterwards:

ALTER TABLE docs ALTER COLUMN body SET COMPRESSION pglz;

1.4 Practical guidance

• Upgrade impact is gradual. After pg_upgrade nothing changes on disk. As rows are inserted or rewritten, the lz4 share grows.
• To convert existing data, rewrite it into a new table (CREATE TABLE AS / INSERT ... SELECT) or reload via dump and restore, and verify with pg_column_compression(). Don’t assume a table rewrite alone recompresses existing values. Test it on your version first.
• Substring-heavy columns. The TOAST docs note that EXTERNAL (no compression) makes substring operations on wide text and bytea faster because only the needed slices are fetched. That advice is independent of which algorithm is the default.
• Compare before/after using the contrib tools in Part 2 (pgstattuple on the TOAST relation, oid2name for file paths).

1.5 Better timing: timing_clock_source

Instrumentation such as EXPLAIN ANALYZE reads the clock twice per plan node per row. On a node that processes millions of rows, the clock-read overhead becomes a significant share of the measured runtime and distorts what you’re trying to measure.

PG19 adds the timing_clock_source setting (in the Resource Consumption docs, “Timing” section):

Value	Behavior
auto (default)	Uses the TSC clock source on supported x86-64 CPUs, otherwise the OS clock
system	Always uses the OS system clock
tsc	Uses a CPU instruction (RDTSC / RDTSCP on x86-64)

How the instructions are used:

• RDTSC is used for EXPLAIN ANALYZE, where the lowest overhead matters most.
• RDTSCP is used where higher precision is needed, such as track_io_timing. It avoids inaccuracies from CPU instruction re-ordering.

Caveats from the docs:

• Only superusers can change the setting.
• Changing it mid-query is not recommended and can make interval timings jump or go negative.
• TSC is unsupported on older x86-64 CPUs and other architectures.
• TSC is not advised on systems with an emulated TSC, where it is likely slower than the system clock. This matters for some virtualized environments.

SHOW timing_clock_source;                 -- auto

-- Compare instrumentation overhead
SET timing_clock_source = 'system';
EXPLAIN (ANALYZE, TIMING ON) SELECT count(*) FROM docs;

SET timing_clock_source = 'tsc';
EXPLAIN (ANALYZE, TIMING ON) SELECT count(*) FROM docs;

Don’t read a faster EXPLAIN ANALYZE as a faster query. The query runs at the same speed. What shrinks is the measurement error added on top of it.

1.6 pg_test_timing, rebuilt

The release notes credit Hannu Krosing and Tom Lane with improving pg_test_timing: it reports nanoseconds instead of microseconds, and adds a second table of exact timings.

pg_test_timing -d 5 -c 99.9

• -d / --duration sets the test duration in seconds (default 3).
• -c / --cutoff sets the percentage at which the exact-timings list stops (default 99.99). The largest observed value is always printed.
• Block 1 is a log2(ns) histogram with the bucket bound, percent of total, running percent and count. It counts zeros separately.
• Block 2 lists the exact timing differences observed, up to the cutoff.
• On TSC-capable platforms you get extra sections for RDTSCP and RDTSC. The tail reports the TSC frequency source (for example x86, cpuid 0x15), the frequency from calibration, and whether TSC will be used by default.

The docs’ example output (one machine, so illustrative only) shows average loop times of 44.67 ns for clock_gettime(CLOCK_MONOTONIC), 37.32 ns for RDTSCP and 27.12 ns for RDTSC. The tool is also how you decide whether track_io_timing will give useful numbers on your hardware, and whether auto will pick TSC. Run it on every VM class you deploy to. A bad result (slow or non-monotonic) is a reason to pin timing_clock_source = system.