Re: ZSTD TOAST compression, and an extensible compression method encoding

Hi Manu,

The patches I attached as v8 came from the wrong branch, I was rushing
and I shouldn't have been. They were v7 plus the earlier 0004, so they
had neither your context reuse, nor the comments, nor your name. Sorry
for the noise. v9, attached, is what my last email described. Thanks for
the repro. I ran repro-dctx-reset.sh unchanged against v9 and against v9
with the ZSTD_DCtx_reset() call removed:

- v9: all checks true, B2 included, the three expected "compressed zstd
data is corrupt" errors and no others: "RESULT pass".
- Without the reset: B2 ("slice of another value after an early stop")
comes back false with no error, while the full reads A2, A3 and B3
stay true. The slices that should have succeeded after an error fail
instead, which is the false error I saw before.

So both cases reproduce here, and the reset fixes both.

I've added Dharin, Peter and Robert, because this series overlaps with
Dharin's "Add zstd compression for TOAST using extended header format"
thread [1]/messages/by-id/CAOj6k6dy2CRVA6Lsb5N59zE-7KNVKt=oYwWyg8ULK8zOOY8e7A@mail.gmail.com from December, which nobody here had referred to yet. That
thread is where several pieces of this one came from:

- Dharin proposed a decoded in-memory form of an external TOAST pointer
[2]: /messages/by-id/CAOj6k6dEVi0NvLjMLDhyrJS_n_NZO5D_OU89AO1u53u6NCDDwQ@mail.gmail.com
toast_external_info_get() and toast_external_data on master
(6e58d6356fc). 0001 extends it with compress_method, so v9-0001 now
carries Suggested-by: Dharin Shah (or maybe just Co-authored-by?)

- Michael's preference there was one new vartag_external per method,
keeping the TOAST pointer at its plain size [4]/messages/by-id/aUOgmypuun0rc2xO@paquier.xyz, and Dharin's second
version did that with an external-only VARTAG_ONDISK_ZSTD [5]/messages/by-id/CAOj6k6f2B3hNxDcnB5AgHX4kaTW8XTAfMAjRx4upDBOugxqF4w@mail.gmail.com. v7
went the other way: one pair of long-form tags plus a method byte.
The 0002 commit message now says why. An inline compressed datum has
no vartag, so a tag per method can't give inline values a third
method. And vartag_external is a uint8 shared with the in-memory
pointer kinds, so it shouldn't take one tag per method. The cost is
one byte, in the pointer or the inline header, for methods other than
pglz and lz4. Michael said upthread that this cost doesn't bother
him [6]/messages/by-id/arS_NVVZZ2XsfCYU@paquier.xyz; I'm raising it here so the earlier preference isn't
overlooked.

- Peter asked for numbers [7]/messages/by-id/4c70b0f6-5aba-464c-b145-464a620c1222@eisentraut.org. For compression ratio, Dharin measured
170MB of mixed data at 22MB with zstd against 36MB with pglz or lz4
[5]: /messages/by-id/CAOj6k6f2B3hNxDcnB5AgHX4kaTW8XTAfMAjRx4upDBOugxqF4w@mail.gmail.com
zstd against 259MB pglz and 297MB lz4, or 114MB with a dictionary
[8]: /messages/by-id/CAFAfj_GACKVftwuRjy3Ls-1Xc3ojUUbVh=Rm7KpRuYbaS=uLPg@mail.gmail.com
this thread. None of these is an end-to-end benchmark, and the
pgbench run you mentioned would be the first. If I have time I'll
give it a try.

v9 is rebased on current master (0f3b42ce806):

0001 Decode the compression method in toast_external_info_get()
0002 Allow more than four TOAST compression methods
0003 Add zstd as a TOAST compression method (with context reuse)
0004 Add zstd compression dictionaries with a column-local lifecycle

Training is an explicit call for now; doing it from ANALYZE's sample is
the obvious next step once the lifecycle is agreed on. Well, that and
doing some benchmarking.

best.

-greg

[1]: /messages/by-id/CAOj6k6dy2CRVA6Lsb5N59zE-7KNVKt=oYwWyg8ULK8zOOY8e7A@mail.gmail.com
[2]: /messages/by-id/CAOj6k6dEVi0NvLjMLDhyrJS_n_NZO5D_OU89AO1u53u6NCDDwQ@mail.gmail.com
[3]: /messages/by-id/aW8keI6WZxywRQIt@paquier.xyz
[4]: /messages/by-id/aUOgmypuun0rc2xO@paquier.xyz
[5]: /messages/by-id/CAOj6k6f2B3hNxDcnB5AgHX4kaTW8XTAfMAjRx4upDBOugxqF4w@mail.gmail.com
[6]: /messages/by-id/arS_NVVZZ2XsfCYU@paquier.xyz
[7]: /messages/by-id/4c70b0f6-5aba-464c-b145-464a620c1222@eisentraut.org
[8]: /messages/by-id/CAFAfj_GACKVftwuRjy3Ls-1Xc3ojUUbVh=Rm7KpRuYbaS=uLPg@mail.gmail.com

Hi Greg,

v9, attached, is what my last email described.

Thanks, and no worries about v8. I went through v9 on my side, on
0f3b42ce806 with zstd 1.5.7:

- All four patches apply cleanly with git am. There are no compiler
warnings at -O2 or under cassert+ASan, and make check passes in both
(241 tests, compression_zstd and compression_zstd_dict included), as
does contrib/amcheck.
- 0003 carries the context reuse as I sent it, only with a longer
comment above it, and with my name on it. Thanks for that.

I ran repro-dctx-reset.sh unchanged against v9 and against v9
with the ZSTD_DCtx_reset() call removed

I get the same here. v9 passes, B2 included, with exactly the three
expected "compressed zstd data is corrupt" errors, nothing else, and no
ASan reports. With only the reset removed, B2 comes back with wrong data
and no error while A2, A3 and B3 stay true, and the slices of good
values after an error fail instead (9 errors where 3 are expected).

I also re-ran the timings on v9 against v9 with the reuse undone (the
v7 decompression code): three independent runs, 38 paired rounds in
all, hot cache, each round checked against an idle CPU calibration and
none discarded. Median ratio over the 38 rounds, with the range covered
by the three runs' 95% CIs; below 1 means v9 is faster:

- 32 kB slice: docs 0.739 [0.694, 0.749], json 0.648 [0.598, 0.653],
random 0.741 [0.696, 0.752]
- 32 kB prefix: docs 0.747 [0.702, 0.754], json 0.701 [0.653, 0.709],
random 0.761 [0.714, 0.789]
- 32 kB full read: docs 0.887 [0.828, 0.890], random 0.872 [0.822,
0.880]; json 1.000 [0.988, 1.006]
- 1 MB random slice: 0.694 [0.686, 0.703]
- 4 kB slice: 0.943 to 0.978 across the three corpora
- load, i.e. compression, which the reuse does not touch: 0.988 to
0.998, every range containing 1

All the slices, the 32 kB prefixes and the docs and random 32 kB full
reads were faster in each of the 38 rounds.

One small thing in the 0003 commit message: "slices and prefixes of
4kB-32kB values take about 40% less time" is more than I see. Here it
is 24-35% less at 32 kB and 2-6% at 4 kB, so maybe "about 25-35% less
time for 32 kB values, a few percent at 4 kB", unless your hosts show
otherwise.

I also see that the XXX on the dictionary prefix path in 0004 now
mentions the reset it would need if it moved to the streaming loop.
My checks don't exercise dictionaries, so I have nothing to add there.

I can share the scripts and the per-run numbers if that is useful.

Regards,
Manu

Hi Manu,

Thanks for the careful review and the second round of timings. You are
right about the commit message, and I can now say so from my own
numbers.

I built v9 on a bare-metal box (c6id.metal, 128 vCPU, local NVMe, THP
off, performance governor) and ran the A/B you'd expect: v9 against the
same tree with the decompression reverted to the real v7 path, that is
a fresh ZSTD_createDCtx()/free per value, not the reset removed. The
reset-removed build is not a fair speed baseline, because it corrupts a
plain slice scan; I used it only to reproduce that corruption, which it
does.

Median of 30 paired rounds, alternating order, on 80k x 46kB docs plus
160k x 4kB plus 200 x 1MB, all external, warm:

query v9 ms v7 ms ratio v9 faster wins
32kB prefix(100B) 2441.7 3975.8 0.614 39% 30/30
32kB slice@20k 8876.7 9316.3 0.953 5% 30/30
32kB full read 9774.2 10222.8 0.956 4% 30/30
4kB prefix(100B) 501.4 517.7 0.969 3% 30/30
1MB slice@500k 647.8 647.0 1.001 0% 10/30

So the big number only shows up for a small prefix or slice of a large
value, where the allocation dwarfs the decode. For whole-value reads
and for 4kB values it's a few percent, which is your point. I'll change
the 0003 commit message to something like:

Reusing the context saves the per-value allocation. For whole-value
reads and small (4kB) values this is a few percent; it rises to about
40% for small slices and prefixes of large (32kB) values, where the
allocation dominates the decode.

Your repro passes here too: v9 gives exactly the three expected
"compressed zstd data is corrupt" errors and no ASan reports, and with
the reset removed B2 returns wrong data with no error. A bare
count(length(substr(v,1,100))) over 80k external zstd values is enough
to show it: v9 and true v7 both return 80000, the reset-removed build
raises the corrupt error.

Two more things from the same box, since they bear on the series.

Dictionaries. Compression ratio (raw/relation size), 150k rows per
shape:

shape pglz lz4 zstd zstd+dict
logs 1.57 1.57 2.75 4.32
text 16.7 19.7 26.9 63.8
random 0.92 0.92 1.37 (no dict: ZDICT declined to train)

The dictionary is where the structured-but-small values win: +57% over
plain zstd on logs, more on templated text. Per-value CLI numbers on
~1kB docs line up: lz4 2.0x -> 3.6x with a dictionary, zstd 2.9x ->
5.2x. The proportional gain is largest for small values and fades as
values grow, for both codecs, which fits the dictionary being a
cold-start aid.

Lifecycle under load. I ran a 65-minute torture test on a cassert
build: 48 pgbench clients reading slices, prefixes and whole values,
inserting and updating dict-compressed rows, while a background loop
retrained the dictionary, ran VACUUM FULL (which retires superseded
dictionaries), added and dropped a second zstd_dict column, vacuumed,
and ran pg_amcheck --heapallindexed. A control column holds md5(v) as
written, checked after every step. Across the whole run: zero bad
signatures, zero amcheck errors, zero failed transactions, no assertion
failure or crash, and the dictionary count cycled as designed
(retrain adds one, the next rewrite retires the old ones). An earlier
run I killed with -m immediate at 5.6M rows came back through crash
recovery with every row still matching its signature.

I'll fold the commit-message change into the next version along with
anything else that comes out of review, rather than post a v10 that
differs only in that one sentence. If you want to compare setups, send
your scripts and per-run numbers and I'll diff them against mine.

best.

-greg

Here is v10, with the change I promised in the last message and two new
patches.

0001 Decode the compression method in toast_external_info_get()
0002 Allow more than four TOAST compression methods
0003 Add zstd as a TOAST compression method (context reuse)
0004 Add zstd compression dictionaries with a column-local lifecycle
0005 Abstract the dictionary codec behind a routine struct
0006 Add lz4 compression dictionaries

0001-0004 are v9 with one change: the 0003 commit message now reads, per
my last message,

For whole-value reads and small (4kB) values this saves a few percent;
it rises to about 40% for small slices and prefixes of large (32kB)
values, where the allocation dominates the decode.

0005 and 0006 are new. Rebased on master (1910daf9831); cfbot is green.

0005, abstracting the codec. Reviewing 0004 it became clear the whole
dictionary machinery (the pg_toast_dictionary catalog, column ownership,
containment, retirement, training with ZDICT) is codec-independent. Only
three calls differ per codec: compress a value with a dictionary,
decompress a whole value, decompress a prefix. 0005 puts those behind a
DictCompressRoutine struct dispatched on the datum's method byte, so a
second dictionary-aware codec is three functions plus a case. No behavior
change for zstd; it only opens the seam.

0006, lz4 dictionaries. lz4 has no trainer of its own; its headers point
at zstd's ZDICT, which 0004 already uses, so a trained lz4 dictionary is
just bytes stored in pg_toast_dictionary exactly like a zstd one, and the
lifecycle is reused unchanged. 0006 adds one DictCompressRoutine for lz4
(LZ4_loadDict + LZ4_compress_fast_continue, LZ4_decompress_safe_usingDict)
and the lz4_dict method. Training still needs zstd's builder, so an
lz4_dict column wants a server built with both; without a dictionary it
behaves like plain lz4.

lz4 dictionaries earn their place. Per-value CLI numbers, each document
compressed on its own against a 64kB ZDICT dictionary:

~1kB structured docs: lz4 2.0x -> 3.6x (+80%), zstd 2.9x -> 5.2x
~2-6kB structured docs: lz4 2.8x -> 3.8x (+33%), zstd 5.4x -> 6.0x

At the small sizes where TOAST dictionaries help most, lz4 nearly doubles,
and lz4+dict (3.6x) beats plain zstd (2.9x) while keeping lz4's much lower
decompression cost. The proportional gain is largest for small values and
fades as values grow, which matches the zstd dictionary numbers from my
last message.

I kept 0005 and 0006 separate so the abstraction reads as a no-op refactor
and lz4 as self-contained. If the series is already too large, I'm happy
to hold 0006; it does not change 0001-0005.

One thing worth settling: automatic dictionary evolution. Training is
manual today (pg_train_toast_dictionary). As a column accumulates data,
when should a dictionary be rebuilt, on what sample, and how do we tell a
new dictionary is actually better, all without touching the INSERT/UPDATE
fast path? I have a design with the following shape:

- Opt-in per column (an attribute option, default off), evaluated during
ANALYZE, which already has a block sample. No new scheduler; it rides
the autovacuum ANALYZE cadence.
- Trigger on a multiplicative row-growth ratio since the dictionary was
trained, plus a cooldown, so the retrain interval grows with the table.
- Decide "better" by a held-out size test: train a candidate on 80% of
the sample, compress the other 20% with both the current and candidate
dictionary, adopt only on a margin win (a GUC, say 5%). This runs in
the maintenance process, never on the write path.
- Cache the current dictionary OID in the relcache so the fast path is a
field read, not a catalog probe per value. This also resolves the XXX
already in toast_dictionary_current().
- Bound pg_toast_dictionary with a per-column cap on retained
dictionaries; when hit, decline and warn rather than force a rewrite.
Retirement stays rewrite-bound as it is now; cheaper VACUUM-driven
retirement is a harder follow-up, because proving a dictionary
unreferenced needs a full attribute scan.
- All of it is codec-agnostic: a codec supplies only train(samples) and a
compressed-size estimate, which is the seam 0005 adds.

I can post the full design, or turn it into a patch once the policy is
agreed. It can equally wait until the manual lifecycle lands; I don't want
to widen the series anymore before 0001-0004 are settled, but someone was
bound to ask about this aspect of the solution.

best.

-greg

Hi Greg,

On Sat, Oct 24, 2026 at 6:33 PM Greg Burd <greg@burd.me> wrote:

If you want to compare setups, send your scripts and per-run numbers
and I'll diff them against mine.

Thanks for re-measuring on bare metal, and for the new 0003 sentence.
Scripts and numbers are attached; first, the 1MB slice.

It is the database encoding. bench.sh runs on a SQL_ASCII database on
purpose. With a multibyte encoding, text_substring() asks detoast for
bytes [0, (start + len - 1) * eml) (varlena.c:666-718), and a request
at least as long as the raw size becomes a full decompression
(detoast.c:529); lines as of master 42e96cf2fe0, and v10 changes
neither. I checked it in the server, with breakpoints at the entry of
zstd_decompress_datum() and zstd_decompress_datum_slice(), on 20
values of 1 MB:

- UTF8: substr(v, 500000, 100) enters zstd_decompress_datum() 20
times and the slice function never.
- SQL_ASCII: the same query enters zstd_decompress_datum_slice() 20
times, with slicelength 500099.
- substr(v, 1, 100) is a slice in both (slicelength 400 and 100).

By the same code, substr(v, 20000, 100) on UTF8 asks for 80,396
bytes, more than a 32 or 46 kB value, so it is a full decompression
too; I did not run that one.

So, if your database is UTF8, your 1MB slice@500k (1.001) is a full
read, like our 1 MB full read (1.000 [0.985, 1.006]); your 32kB
slice@20k (0.953) sits with your full read (0.956); and your 32kB
prefix (0.614) is the one that stays a slice.

The 172 us per value that the reuse saves on our 1 MB slice (from the
median times) track page faults. On SQL_ASCII the backend takes 416
page faults per value on that slice with a fresh context and none with
the reused one, the same in three repetitions, about 0.4 us per fault.
The 1 MB prefix (0 and 0 faults) and full read (631 and 631) show no
fault difference, and their timings barely move (0.980 and 1.000).
These counts come from the builds I still have: v7 plus the reuse,
whose three zstd decode functions are line for line those of v10-0003,
and v7, whose two match v10 with the control diff below.

The 32 kB rows are not the same test: if I read your setup right,
they run on the 80k x 46kB docs, and ours on 32,000-byte docs, json
and random values. Size and content both differ, so I would not
read 0.614 vs our 0.70-0.76 as a disagreement.

Our setup: Ryzen AI 9 HX 370 laptop, 8 CPUs pinned, performance
profile; -O2 without asserts, gcc 16.2.1, zstd 1.5.7, --with-lz4
--with-zstd; shared_buffers 2GB; hot cache; SQL_ASCII, locale C.

Attached:
- bench.sh.txt: the benchmark as used, with only local paths replaced.
- run_v9_cite.sh.txt: the rounds, both builds back to back, order
alternating. Our lab's idle-CPU check is stubbed out; it only
flagged rounds, all 38 were clean, so no number depends on it.
- cite_v9.py.txt, cite_v9_runs.py.txt: the summaries (rename to .py;
the second imports the first).
- per-run-v9.txt: per round and point, the median of 5 reps for each
build and the ratio, runs A, B and C.
- nocfbot-v9n-without-reuse.diff.txt: our v7 control on top of 0003.
It also applies on top of v10-0004.
- branch_faults.sh.txt and branch_faults-out.txt: the breakpoint
and page-fault counts above, and their output.

Regards,
Manu