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

Part 2: Hardware Acceleration and the Tools That Show It

2.1 The pattern behind the changes

The PG19 SIMD work shares a recipe:

1. Keep a portable scalar version.
2. Compile an optimized variant with a function target attribute (for example AVX2 or +crypto).
3. Pick the implementation at runtime via a function pointer or CPU check.

The user-visible interface and on-disk formats don’t change. You get speed for free, as long as the CPU has the instructions.

2.2 AVX2 page checksums (5e13b0f)

Since PG18, new clusters have data checksums enabled by default. The PG19 checksum docs describe the model as unchanged:

• Each data page carries a checksum, updated on write and verified on every read.
• Only data pages are protected, not internal structures or temporary files.
• It is cluster-wide, not per database or table.
• You inspect it with SHOW data_checksums.
• You can bypass failures temporarily with ignore_checksum_failure when recovering from corruption.
• Enabling or disabling on an existing cluster uses pg_checksums, as an offline operation, run on every node of a replicated cluster before restart.

The AVX2 commit (John Naylor, co-authored by Matthew Sterrett and Andrew Kim) adds a faster implementation:

• The checksum loop was already autovectorized by compilers. Annotating pg_checksum_block() with an AVX2 function target gives a further several-fold increase on x86.
• AVX2 uses 256-bit registers and vector multiplication, where the SSE2 baseline needs vector shifts and adds.
• The function pointer is set on first use. Overhead is irrelevant because inputs are multi-kilobyte pages.
• Only the server core uses the pointer. External programs that include checksum_impl.h still get a normal static function.
• It matters most with io_uring. With io_method = worker, I/O workers verify checksums in parallel, but with io_uring the checksum runs in the backend, so a faster kernel directly cuts latency.

Earlier on the mailing list, the proposer measured roughly 2x from enabling autovectorization and another 2x from AVX2. A change to the number of partial sums would have gone further but would have changed checksum results, so it was left out. Checksums on existing clusters therefore remain valid.

Online checksum enabling: some development-era sources list enabling or disabling checksums while the server is running as a PG19 feature, but the official PG19 checksum page I read describes only the offline pg_checksums method. Check your final release notes and docs before telling readers online enabling is available.

2.3 ARM CRC32C with the Crypto Extension (fbc57f2bc)

CRC32C protects WAL records and is exposed in SQL as crc32c(bytea). On x86, PostgreSQL 18 already used carry-less multiplication (PCLMUL) for long inputs. PG19 brings the equivalent to ARM (John Naylor, committed 4 April 2026).

According to the commit message:

• The ARMv8 cryptography extension has 64×64 to 128-bit carry-less multiply instructions (PMULL) that suit CRC computation.
• It was tested at around twice as fast as the scalar CRC instructions for longer inputs.
• It is used “where available”. The code is compiled with a +crypto target attribute and gated by a runtime check, so one binary works on CPUs with and without the extension.

Design points from the review thread:

• The main loop uses several 16-byte accumulators.
• Small constant-length inputs keep a direct call to avoid regressions on hot paths such as WAL insertion under the insert lock.
• Arm cores generally have low-latency carry-less multiply, so the indirection costs less than on x86.
• A separate SVE2 proposal exists but is not part of this commit.

Who benefits: Graviton, Ampere, Apple Silicon and other ARMv8 servers doing WAL-heavy work such as bulk loads, large transactions and crc32c() over big values. This matters more as ARM fleets grow.

2.4 SIMD hex_encode() / hex_decode() (ec8719c)

These functions underlie bytea text-format output (\x…), encode(x,'hex'), decode(x,'hex') and therefore anything that moves binary data through text, such as pg_dump, COPY of bytea columns and client drivers.

From the commit message (Nathan Bossart, co-authored by Chiranmoy Bhattacharya and Susmitha Devanga):

• New implementations use routines from port/simd.h, which testing showed are much faster for larger inputs.
• Small or invalid inputs fall back to the existing scalar code.
• Because it builds on port/simd.h, it applies to both x86-64 and AArch64.
• Decoding has to process two vectors at a time, because the output is half the length of the input.

The semantics documented in the binary-string chapter are unchanged:

• encode(..., 'hex') writes lowercase digits and always produces an even number of characters.
• decode(..., 'hex') accepts upper or lower case and raises an error for invalid hex or an odd number of characters.

SELECT encode('\xdeadbeef'::bytea, 'hex');   -- deadbeef
SELECT decode('DEADbeef', 'hex');            -- \xdeadbeef

Related additions in the same chapter that you may want to cover:

• encode/decode gained base64url (RFC 4648 §5, URL-safe, no padding) and base32hex (RFC 4648 §7).
  • base32hex preserves byte-wise sort order, but only under the C collation.
  • It can turn a UUID into a compact 26-character string via rtrim(encode(uuid_value::bytea,'base32hex'),'=').
• bytea ↔ uuid casts work directly.
• crc32(), crc32c() and bit_count() are documented as binary-string functions.

A bonus SIMD change in the same family: COPY FROM parsing of text and CSV input now uses SIMD (Nazir Bilal Yavuz, Shinya Kato).

2.5 Seeing the effects: contrib modules and tools

The remaining documentation links are small but connected. Several benefit from the same release, and together they make a good before/after toolkit for the changes above.

pgstattuple: measuring TOAST and bloat

PG19 improves pgstattuple performance by using streaming reads (Xuneng Zhou). Streaming reads use PostgreSQL’s read-stream and asynchronous I/O machinery, which means larger, better-prefetched reads for what is otherwise a full relation scan.

Functions worth showing:

• pgstattuple(regclass) does a full scan with a read lock and returns exact live, dead and free space figures.
• pgstattuple_approx(regclass) skips pages the visibility map marks all-visible and estimates the rest, giving exact dead-tuple stats with approximated live and free space.
• pgstatindex() reports avg_leaf_density and leaf_fragmentation for B-trees. pgstatginindex() reports the pending list, and pgstathashindex() covers hash indexes.
• By default only pg_stat_scan_tables members and superusers can run them.
• Results are not an instantaneous snapshot.

Use it to compare TOAST storage before and after a recompression:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT reltoastrelid::regclass AS toast_rel
FROM pg_class WHERE oid = 'docs'::regclass;

SELECT table_len, tuple_count, free_percent
FROM pgstattuple('pg_toast.pg_toast_12345');   -- use the name from above

oid2name: from file to relation

oid2name maps OIDs and filenodes in the data directory to names. In PG19, --extended also reports the relation’s file path (David Bidoc). The docs’ example shows columns for filenode, table name, OID, schema, tablespace and path, such as base/17228/155173. It stops you from hand-assembling paths from database and tablespace OIDs.

oid2name -d mydb -S -i -x -t 'pg_toast_%'

• -S includes system objects, which is where TOAST tables live (the pg_toast schema).
• -i includes indexes and sequences.
• -x adds tablespace, schema, OID, and path.
• -f maps a filenode you found with ls/du back to a name.

Remember the docs’ warning: the tool really deals in filenodes, not OIDs, and they diverge after rewrites like REPACK, VACUUM FULL or TRUNCATE. It also needs a running server with non-corrupt catalogs.

bloom: equality indexes, now with streaming reads

PG19 improves bloom index performance with streaming reads (also Xuneng Zhou). Bloom scans return a bitmap by walking the whole index, so a sequential, prefetch-friendly read pattern suits them.

Key facts from the docs:

• A bloom index stores a lossy signature per row, so results need a heap recheck.
• length is the signature size in bits (default 80, rounded up to a multiple of 16, max 4096). col1–col32 set the bits per column (default 2, max 4095).
• It supports only =, has operator classes only for int4 and text, doesn’t support UNIQUE, and can’t search for NULL.
• It shines on wide tables where queries combine arbitrary columns.

The documentation’s 10-million-row example makes the trade-off concrete:

Approach	Index size	Query time for i2 = … AND i5 = …
No index	–	about 357 ms (seq scan)
One 6-column B-tree	386 MB	still a seq scan, about 351 ms
One 6-column bloom	153 MB	about 22.6 ms (bitmap heap scan)
Six single-column B-trees	531 MB	about 0.05 ms

Bloom costs about a third of the space of the six B-trees. The B-trees are far faster, so Bloom is a trade of speed for compactness and flexibility.

CREATE EXTENSION bloom;
CREATE INDEX bloomidx ON tbloom USING bloom (i1,i2,i3,i4,i5,i6)
  WITH (length=80, col1=2, col2=2, col3=2, col4=2, col5=2, col6=2);

fuzzystrmatch: dmetaphone beyond ASCII

The PG19 change is small but useful: dmetaphone() now works with single-byte encodings beyond ASCII (Peter Eisentraut). Good context for the rest of the module:

• soundex, metaphone, dmetaphone and dmetaphone_alt still don’t work well with multibyte encodings such as UTF-8. For those, use daitch_mokotoff or levenshtein.
• daitch_mokotoff() returns a text[] of codes (up to six digits), because ambiguous spellings produce several plausible codes. Match with &&, and index with GIN:

CREATE INDEX ix_s_dm ON s USING gin (daitch_mokotoff(nm)) WITH (fastupdate = off);
SELECT * FROM s WHERE daitch_mokotoff(nm) && daitch_mokotoff('Swartzenegger');

• levenshtein is limited to 255 characters per argument. levenshtein_less_equal(a, b, max_d) is the accelerated version when only small distances matter.
• The module is “trusted”, so non-superusers with CREATE on the database can install it.

Putting It Together: a PG19 upgrade checklist for this topic

1. Confirm the build has lz4 (SHOW default_toast_compression). Decide whether to leave old data as pglz or rewrite hot tables.
2. Run pg_test_timing on each environment. Use timing_clock_source = system if the TSC is emulated or unreliable.
3. Don’t tune for AVX2 or ARM CRC32C. They activate at runtime. Compare checksum-heavy and WAL-heavy benchmarks on old and new binaries on the same hardware.
4. Benchmark bytea-heavy dumps and COPY workloads, since hex and COPY parsing improved on both x86-64 and AArch64.
5. Use pgstattuple, oid2name -x and pg_column_compression() to quantify what changed.