Subject: Re: Proposal: Adding compression of temporary files

From: Filip Janus <[email-masked]>
Date: Sun, 5 Jan 2025 00:43:05 +0100

I apologize for multiple messages, but I found a small bug in the previous
version.

    -Filip-

so 4. 1. 2025 v 23:40 odesílatel Filip Janus <[email-masked]> napsal:

> Even though i started with lz4, I added also pglz support and enhanced
> memory management based on provided review.
>
>
>
>     -Filip-
>
>
> čt 28. 11. 2024 v 12:32 odesílatel Filip Janus <[email-masked]> napsal:
>
>>
>> I've added a regression test for lz4 compression if the server is
>> compiled with the "--with-lz4" option.
>>
>>     -Filip-
>>
>>
>> ne 24. 11. 2024 v 15:53 odesílatel Filip Janus <[email-masked]>
>> napsal:
>>
>>>
>>>
>>>     -Filip-
>>>
>>>
>>> st 20. 11. 2024 v 1:35 odesílatel Tomas Vondra <[email-masked]> napsal:
>>>
>>>> Hi,
>>>>
>>>> On 11/18/24 22:58, Filip Janus wrote:
>>>> > ...
>>>> >     Hi all,
>>>> >     Postgresql supports data compression nowadays, but the
>>>> compression of
>>>> >     temporary files has not been implemented yet. The huge queries
>>>> can
>>>> >     produce a significant amount of temporary data that needs to
>>>> >     be stored on disk
>>>> >     and cause many expensive I/O operations.
>>>> >     I am attaching a proposal of the patch to enable temporary files
>>>> >     compression for
>>>> >     hashjoins for now. Initially, I've chosen the LZ4 compression
>>>> >     algorithm. It would
>>>> >     probably make better sense to start with pglz, but I realized it
>>>> late.
>>>> >
>>>>
>>>> Thanks for the idea & patch. I agree this might be quite useful for
>>>> workloads generating a lot of temporary files for stuff like sorts etc.
>>>> I think it will be interesting to think about the trade offs, i.e. how
>>>> to pick the compression level - at some point the compression ratio
>>>> stops improving while paying more and more CPU time. Not sure what the
>>>> right choice is, so using default seems fine.
>>>>
>>>> I agree it'd be better to start with pglz, and only then add lz4 etc.
>>>> Firstly, pglz is simply the built-in compression, supported everywhere.
>>>> And it's also simpler to implement, I think.
>>>>
>>>> >     # Future possible improvements
>>>> >     Reducing the number of memory allocations within the dumping and
>>>> >     loading of
>>>> >     the buffer. I have two ideas for solving this problem. I would
>>>> >     either add a buffer into
>>>> >     struct BufFile or provide the buffer as an argument from the
>>>> caller.
>>>> >     For the sequential
>>>> >     execution, I would prefer the second option.
>>>> >
>>>>
>>>> Yes, this would be good. Doing a palloc+pfree for each compression is
>>>> going to be expensive, especially because these buffers are going to be
>>>> large - likely larger than 8kB. Which means it's not cached in the
>>>> memory context, etc.
>>>>
>>>> Adding it to the BufFile is not going to fly, because that doubles the
>>>> amount of memory per file. And we already have major issues with hash
>>>> joins consuming massive amounts of memory. But at the same time the
>>>> buffer is only needed during compression, and there's only one at a
>>>> time. So I agree with passing a single buffer as an argument.
>>>>
>>>> >     # Future plan/open questions
>>>> >     In the future, I would like to add support for pglz and zstd.
>>>> >     Further, I plan to
>>>> >     extend the support of the temporary file compression also for
>>>> >     sorting, gist index creation, etc.
>>>> >
>>>> >     Experimenting with the stream mode of compression algorithms. The
>>>> >     compression
>>>> >     ratio of LZ4 in block mode seems to be satisfying, but the stream
>>>> >     mode could
>>>> >     produce a better ratio, but it would consume more memory due to
>>>> the
>>>> >     requirement to store
>>>> >     context for LZ4 stream compression.
>>>> >
>>>>
>>>> One thing I realized is that this only enables temp file compression for
>>>> a single place - hash join spill files. AFAIK this is because compressed
>>>> files don't support random access, and the other places might need that.
>>>>
>>>> Is that correct? The patch does not explain this anywhere. If that's
>>>> correct, the patch probably should mention this in a comment for the
>>>> 'compress' argument added to BufFileCreateTemp(), so that it's clear
>>>> when it's legal to set compress=true.
>>>>
>>>
>>> I will add the description there.
>>>
>>>
>>>> Which other places might compress temp files? Surely hash joins are not
>>>> the only place that could benefit from this, right?
>>>>
>>>
>>> Yes, you are definitely right. I have chosen the hash joins as a POC
>>> because
>>> there are no seeks besides seeks at the beginning of the buffer.
>>> I have focused on hashjoins, but there are definitely also other places
>>> where
>>> the compression could be used. I want to add support in other places
>>> in the feature.
>>>
>>>
>>>> Another thing is testing. If I run regression tests, it won't use
>>>> compression at all, because the GUC has "none" by default, right? But we
>>>> need some testing, so how would we do that? One option would be to add a
>>>> regression test that explicitly sets the GUC and does a hash join, but
>>>> that won't work with lz4 (because that may not be enabled).
>>>
>>>
>>> Right, it's "none" by default. My opinion is that we would like to test
>>> every supported compression method, so I will try to add environment
>>> variable as
>>> you recommended.
>>>
>>>
>>>>
>>>> Another option might be to add a PG_TEST_xxx environment variable that
>>>> determines compression to use. Something like PG_TEST_USE_UNIX_SOCKETS.
>>>> But perhaps there's a simpler way.
>>>>
>>>> >     # Benchmark
>>>> >     I prepared three different databases to check expectations. Each
>>>> >     dataset is described below. My testing demonstrates that my patch
>>>> >     improves the execution time of huge hash joins.
>>>> >     Also, my implementation should not
>>>> >     negatively affect performance within smaller queries.
>>>> >     The usage of memory needed for temporary files was reduced in
>>>> every
>>>> >      execution without a significant impact on execution time.
>>>> >
>>>> >     *## Dataset A:*
>>>> >     Tables*
>>>> >     *
>>>> >     table_a(bigint id,text data_text,integer data_number) - 10000000
>>>> rows
>>>> >     table_b(bigint id, integer ref_id, numeric data_value, bytea
>>>> >     data_blob) - 10000000 rows
>>>> >     Query:  SELECT *  FROM table_a a JOIN table_b b ON a.id <http://
>>>> >     a.id> = b.id <http://b.id>;
>>>> >
>>>> >     The tables contain highly compressible data.
>>>> >     The query demonstrated a reduction in the usage of the temporary
>>>> >     files ~20GB -> 3GB, based on this reduction also caused the
>>>> execution
>>>> >     time of the query to be reduced by about ~10s.
>>>> >
>>>> >
>>>> >     *## Dataset B:*
>>>> >     Tables:*
>>>> >     *
>>>> >     table_a(integer id, text data_blob) - 1110000 rows
>>>> >     table_b(integer id, text data_blob) - 10000000 rows
>>>> >     Query:  SELECT *  FROM table_a a JOIN table_b b ON a.id <http://
>>>> >     a.id> = b.id <http://b.id>;
>>>> >
>>>> >     The tables contain less compressible data. data_blob was generated
>>>> >     by a pseudo-random generator.
>>>> >     In this case, the data reduction was only ~50%. Also, the
>>>> execution
>>>> >     time was reduced
>>>> >     only slightly with the enabled compression.
>>>> >
>>>> >     The second scenario demonstrates no overhead in the case of
>>>> enabled
>>>> >     compression and extended work_mem to avoid temp file usage.
>>>> >
>>>> >     *## Dataset C:*
>>>> >     Tables
>>>> >     customers (integer,text,text,text,text)
>>>> >     order_items(integer,integer,integer,integer,numeric(10,2))
>>>> >     orders(integer,integer,timestamp,numeric(10,2))
>>>> >     products(integer,text,text,numeric(10,2),integer)
>>>> >
>>>> >     Query: SELECT p.product_id, p.name <http://p.name>, p.price,
>>>> >     SUM(oi.quantity) AS total_quantity, AVG(oi.price) AS
>>>> avg_item_price
>>>> >     FROM eshop.products p JOIN eshop.order_items oi ON p.product_id =
>>>> >     oi.product_id JOIN
>>>> >     eshop.orders o ON oi.order_id = o.order_id WHERE o.order_date >
>>>> >     '2020-01-01' AND p.price > 50
>>>> >     GROUP BY p.product_id, p.name <http://p.name>, p.price HAVING
>>>> >     SUM(oi.quantity) > 1000
>>>> >     ORDER BY total_quantity DESC LIMIT 100;
>>>> >
>>>> >     This scenario should demonstrate a more realistic usage of the
>>>> database.
>>>> >     Enabled compression slightly reduced the temporary memory usage,
>>>> but
>>>> >     the execution
>>>> >     time wasn't affected by compression.
>>>> >
>>>> >
>>>> >     +------------+-------------------------+-----------------------
>>>> >     +------------------------------+
>>>> >     |  Dataset   | Compression.       | temp_bytes         | Execution
>>>> >     Time (ms)   |
>>>> >     +------------+-------------------------+-----------------------
>>>> >     +----------------------------- +
>>>> >     | A             | Yes                        |  3.09 GiB
>>>>
>>>> >     | 22s586ms           | work_mem  = 4MB
>>>> >     |                | No                         |  21.89 GiB
>>>>
>>>> >     | 35s                       | work_mem  = 4MB
>>>> >     +------------+-------------------------+-----------------------
>>>> >     +----------------------------------------
>>>> >     | B             | Yes                        |  333 MB
>>>>
>>>> >     | 1815.545 ms       | work_mem = 4MB
>>>> >     |                 | No                        |  146  MB
>>>>
>>>> >       | 1500.460 ms        | work_mem = 4MB
>>>> >     |                 | Yes                       |  0 MB
>>>>
>>>> >         | 3262.305 ms        | work_mem = 80MB
>>>> >     |                 | No                        |  0 MB
>>>>
>>>> >        | 3174.725 ms         | work_mem = 80MB
>>>> >     +-------------+------------------------+------------------------
>>>> >     +-------------------------------------
>>>> >     | C             | Yes                       | 40
>>>> MB
>>>> >     | 1011.020 ms        | work_mem = 1MB
>>>> >     |                | No                        |  53
>>>> >     MB                 |  1034.142 ms        | work_mem = 1MB
>>>> >     +------------+------------------------+------------------------
>>>> >     +--------------------------------------
>>>> >
>>>> >
>>>>
>>>> Thanks. I'll try to do some benchmarks on my own.
>>>>
>>>> Are these results fro ma single run, or an average of multiple runs?
>>>
>>>
>>> It is average from multiple runs.
>>>
>>> Do
>>>> you maybe have a script to reproduce this, including the data
>>>> generation?
>>>
>>>
>>> I am attaching my SQL file for database preparation. I also did further
>>> testing
>>> with two other machines( see attachment huge_tables.rtf ).
>>>
>>>>
>>>> Also, can you share some information about the machine used for this? I
>>>> expect the impact to strongly depends on memory pressure - if the temp
>>>> file fits into page cache (and stays there), it may not benefit from the
>>>> compression, right?
>>>>
>>>
>>> If it fits into the page cache due to compression, I would consider it
>>> as a benefit from compression.
>>> I performed further testing on machines with different memory sizes.
>>> Both experiments showed that compression was beneficial for execution
>>> time.
>>> The execution time reduction was more significant in the case of the
>>> machine that had
>>> less memory available.
>>>
>>> Tests were performed on:
>>> MacBook PRO M3 36GB - MacOs
>>> Virtual machine ARM64 10GB/ 6CPU - Fedora 39
>>>
>>>
>>>>
>>>> regards
>>>>
>>>> --
>>>> Tomas Vondra
>>>>
>>>>

---

From: Filip Janus <[email-masked]>
Date: Tue, 22 Apr 2025 09:17:30 +0200

Since the patch was prepared months ago, it needs to be rebased.

    -Filip-

ne 13. 4. 2025 v 21:53 odesílatel Dmitry Dolgov <[email-masked]>
napsal:

> > On Fri, Mar 28, 2025 at 09:23:13AM GMT, Filip Janus wrote:
> > > +    else
> > > +        if (nbytesOriginal - file->pos != 0)
> > > +            /* curOffset must be corrected also if compression is
> > > +             * enabled, nbytes was changed by compression but we
> > > +             * have to use the original value of nbytes
> > > +             */
> > > +            file->curOffset-=bytestowrite;
> > >
> > > It's not something introduced by the compression patch - the first part
> > > is what we used to do before. But I find it a bit confusing - isn't it
> > > mixing the correction of "logical file position" adjustment we did
> > > before, and also the adjustment possibly needed due to compression?
> > >
> > > In fact, isn't it going to fail if the code gets multiple loops in
> > >
> > >     while (wpos < file->nbytes)
> > >     {
> > >        ...
> > >     }
> > >
> > > because bytestowrite will be the value from the last loop? I haven't
> > > tried, but I guess writing wide tuples (more than 8k) might fail.
> > >
> >
> > I will definitely test it with larger tuples than 8K.
> >
> > Maybe I don't understand it correctly,
> > the adjustment is performed in the case that file->nbytes and file->pos
> > differ.
> > So it must persist also if we are working with the compressed data, but
> the
> > problem is that data stored and compressed on disk has different sizes
> than
> > data incoming uncompressed ones, so what should be the correction value.
> > By debugging, I realized that the correction should correspond to the
> size
> > of
> > bytestowrite from the last iteration of the loop.
>
> I agree, this looks strange. If the idea is to set curOffset to its
> original value + pos, and the original value was advanced multiple times
> by bytestowrite, it seems incorrect to adjust it by bytestowrite, it
> seems incorrect to adjust it only once. From what I see current tests do
> not exercise a case where the while will get multiple loops, so it looks
> fine.
>
> At the same time maybe I'm missing something, but how exactly such test
> for 8k tuples and multiple loops in the while block should look like?
> E.g. when I force a hash join on a table with a single wide text column,
> the minimal tuple that is getting written to the temporary file still
> has rather small length, I assume due to toasting. Is there some other
> way to achieve that?
>
>

---

From: Filip Janus <[email-masked]>
Date: Fri, 25 Apr 2025 23:54:00 +0200

The latest rebase.

    -Filip-

---

From: lakshmi <[email-masked]>
Date: Tue, 13 Jan 2026 18:23:16 +0530

Hi all,
I tried to replicate the temporary file compression issue by applying the
two patches shared in the thread on current PostgreSQL master.
here is what i observed,
1) patch 1:0001-Add-transparent-compression-for-temporary-files.patch
when applying the first patch it ultimately fails to apply due to context
mismatches.

failures i see are in the following files:
src/backend/storage/file/buffile.c
src/backend/utils/misc/guc_tables.c
src/backend/utils/misc/postgresql.conf.sample

2) The second
patch 0002-Add-regression-tests-for-temporary-file-compression.patch
,applies successfully without any issues.

Does it mean that the implementation patch needs to be rebased or otherwise
adjusted for the current codebase, and if so, what would be the recommended
way to proceed?could you please suggest how I should apply the
implementation patch in this case?

regards
lakshmi

On Tue, Jan 13, 2026 at 5:01 PM Filip Janus <[email-masked]> wrote:

> Rebase after changes introduced in guc_tables.c
>
>     -Filip-
>
>
> út 19. 8. 2025 v 17:48 odesílatel Filip Janus <[email-masked]> napsal:
>
>> Fix overlooked compiler warnings
>>
>>     -Filip-
>>
>>
>> po 18. 8. 2025 v 18:51 odesílatel Filip Janus <[email-masked]> napsal:
>>
>>> I rebased the proposal and fixed the problem causing those problems.
>>>
>>>     -Filip-
>>>
>>>
>>> út 17. 6. 2025 v 16:49 odesílatel Andres Freund <[email-masked]>
>>> napsal:
>>>
>>>> Hi,
>>>>
>>>> On 2025-04-25 23:54:00 +0200, Filip Janus wrote:
>>>> > The latest rebase.
>>>>
>>>> This often seems to fail during tests:
>>>> https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F5382
>>>>
>>>> E.g.
>>>>
>>>> https://api.cirrus-ci.com/v1/artifact/task/4667337632120832/testrun/build-32/testrun/recovery/027_stream_regress/log/regress_log_027_stream_regress
>>>>
>>>> === dumping
>>>> /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/regression.diffs
>>>> ===
>>>> diff -U3
>>>> /tmp/cirrus-ci-build/src/test/regress/expected/join_hash_pglz.out
>>>> /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/join_hash_pglz.out
>>>> --- /tmp/cirrus-ci-build/src/test/regress/expected/join_hash_pglz.out
>>>>  2025-05-26 05:04:40.686524215 +0000
>>>> +++
>>>> /tmp/cirrus-ci-build/build-32/testrun/recovery/027_stream_regress/data/results/join_hash_pglz.out
>>>>  2025-05-26 05:15:00.534907680 +0000
>>>> @@ -594,11 +594,8 @@
>>>>  select count(*) from join_foo
>>>>    left join (select b1.id, b1.t from join_bar b1 join join_bar b2
>>>> using (id)) ss
>>>>    on join_foo.id < ss.id + 1 and join_foo.id > ss.id - 1;
>>>> - count
>>>> --------
>>>> -     3
>>>> -(1 row)
>>>> -
>>>> +ERROR:  could not read from temporary file: read only 8180 of 1572860
>>>> bytes
>>>> +CONTEXT:  parallel worker
>>>>  select final > 1 as multibatch
>>>>    from hash_join_batches(
>>>>  $$
>>>> @@ -606,11 +603,7 @@
>>>>      left join (select b1.id, b1.t from join_bar b1 join join_bar b2
>>>> using (id)) ss
>>>>      on join_foo.id < ss.id + 1 and join_foo.id > ss.id - 1;
>>>>  $$);
>>>> - multibatch
>>>> -------------
>>>> - t
>>>> -(1 row)
>>>> -
>>>> +ERROR:  current transaction is aborted, commands ignored until end of
>>>> transaction block
>>>>  rollback to settings;
>>>>  -- single-batch with rescan, parallel-oblivious
>>>>  savepoint settings;
>>>>
>>>>
>>>> Greetings,
>>>>
>>>> Andres
>>>>
>>>>
>>>>

---

From: Filip Janus <[email-masked]>
Date: Tue, 13 Jan 2026 14:34:31 +0100

Hi,
Yes, it needs to be rebased. I am working on it. I will post it here soon.

    -Filip-

út 13. 1. 2026 v 13:51 odesílatel lakshmi <[email-masked]> napsal:

> Does it mean that the implementation patch needs to be rebased or
> otherwise adjusted for the current codebase, and if so, what would be the
> recommended way to proceed?could you please suggest how I should apply the

---

From: Filip Janus <[email-masked]>
Date: Sun, 18 Jan 2026 16:50:24 +0100

Hi,
Thank you, Tomas, for the thorough and detailed review!
I'm posting an updated patch set incorporating the changes from your review.

Changes applied from review:
- Simplified BufFileCreateTemp interface
- Improved error handling in BufFileLoadBuffer/BufFileDumpBuffer
- Unified compression header format (CompressHeader struct)
- Added tuplestore integration (compression when EXEC_FLAG_BACKWARD is not
required)
- Various code cleanups and comment improvements
Additional change (not from review):
- Switched from static shared buffer to per-file allocation. The shared
buffer
   provided a negligible performance benefit while keeping memory allocated
for the backend's lifetime.
Future work:
- Support for additional compression methods (gzip, zstd)
- Random access and seek operations with compression

    -Filip-

út 13. 1. 2026 v 14:34 odesílatel Filip Janus <[email-masked]> napsal:

>>>>>> ---
>>>>>> +ERROR:  could not read from temporary file: read only 8180 of
>>>>>> 1572860 bytes
>>>>>> +ERROR:  current transaction is aborted, commands ignored until end
>>>>>> of transaction block
>>>>>>  rollback to settings;

---

From: Zsolt Parragi <[email-masked]>
Date: Mon, 19 Jan 2026 22:40:16 +0000

Hello!

I tried to review the code. It compiled, the test suite passed.

I noticed two typos:

buffile.c:77 - "Disaled"
buffile.c:133 - "mathods"

And a few other small findings:

buffile.h:35 and buffile.c:63 - same constants defined first as an
Enum and then as #defines - code builds properly without the defines.

buffile.c:121 - compress_tempfile is defined, set to false at :167,
but never used otherwise

guc_tables.c:470 - the comment says that pglz isn't supported yet, but
we have a value for it, and I see support for it in the code

buffile.c:659: (and at other places) if USE_LZ4 is undefined, the
codepath doesn't do anything. I think these ifdefs should follow how
other compression code works, such as wal compression where there's an
#else path with elog(ERROR, ...)
Similarly, maybe there should be an explicit TEMP_NONE_COMPRESSION
branch that does nothing, and the default branch should be an error?

buffile.c:265: If seek isn't supported/limited, shouldn't there be at
least an assertion about it in BufFileSeek? And tell isn't mentioned,
but it seems to me that tell also doesn't work properly.

---

From: lakshmi <[email-masked]>
Date: Tue, 20 Jan 2026 16:21:14 +0530

Hi Filip,

I tested both patches on current master using git am -3 .They apply
cleanly,build fine,and the temp_file _compression GUC works as expected.
Query results are unchanged.

For hash join spill test,temp files were created as expected,but the logged
size were same for no,lz4,and pglz,which seems consistent with fixed-size
fileset chunking.It might be helpful to briefly note this in the
documentation to avoid confusion.

Thanks for working on this .
best regards,
lakshmi

---

From: lakshmi <[email-masked]>
Date: Wed, 21 Jan 2026 12:02:58 +0530

HI all,
While testing the temp file compression patch,noticed that the new
temp_file_compression GUC isn't documented yet.I put together a small docs
patch to add a short description and clarify that the effect of compression
depends on the workload(for example ,hash join spills may not show visible
size reduction due to fixed_size chunks).

patch is attached.Happy to adjust the wording if needed.
thanks,
lakshmi

On Tue, Jan 20, 2026 at 4:21 PM lakshmi <[email-masked]> wrote:

> For hash join spill test,temp files were created as expected,but the
> logged size were same for no,lz4,and pglz,which seems consistent with
> fixed-size fileset chunking.It might be helpful to briefly note this in the
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0fad34da6eb..57a8af2a2fc 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1955,6 +1955,26 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+    <varlistentry id="guc-temp-file-compression">
+     <term><varname>temp_file_compression</varname> (<type>enum</type>)</term>
+     <listitem>
+     <para>
+      Enables transparent compression of temporary files used by query execution.
+      Supported values are <literal>no</literal>, <literal>lz4</literal>, and
+     <literal>pglz</literal>.
+     </para>
+
+     <para>
+     The effectiveness of temporary file compression depends on the workload.
+     For example, temporary files created by hash join spills use fixed-size
+     chunks, so on-disk file sizes may not visibly shrink even when compression
+     is enabled. Statistics such as <literal>temp_bytes</literal> report logical
+     bytes written before compression.
+     </para>
+     </listitem>
+     </varlistentry>
+
+
      <varlistentry id="guc-hash-mem-multiplier" xreflabel="hash_mem_multiplier">
       <term><varname>hash_mem_multiplier</varname> (<type>floating point</type>)
       <indexterm>

---

From: Filip Janus <[email-masked]>
Date: Fri, 23 Jan 2026 17:40:32 +0100

Hi all,
Thanks for the feedback and the provided patch.
I've addressed your findings and proposals. Lakshmi's documentation patch
was incorporated.

    -Filip-

---

From: Filip Janus <[email-masked]>
Date: Sun, 25 Jan 2026 12:57:05 +0100

Fixed spacing in the patch.

    -Filip-

---

From: lakshmi <[email-masked]>
Date: Tue, 3 Feb 2026 22:37:58 +0530

Hi all,

I’ve applied the latest two patches on current 19devel and wanted to share
some testing results.

The patches apply cleanly, build and install without issues, and the server
starts normally. I verified that the new temp_file_compression GUC works as
expected and accepts the documented values (no, lz4, pglz), while invalid
values are correctly rejected.

For testing, I forced temp file usage by running parallel hash joins with a
small work_mem. I ran the same query with temp_file_compression set to no,
lz4, and pglz. In all cases, temp files were created and used (hash join
spilling to disk), query results were identical, and I did not see any
crashes or read/write errors.

The temp read/write counters were very similar across all three modes. This
seems expected for hash join spills, since they use fixed-size fileset
chunks, so compression doesn’t necessarily reduce the number of temp blocks
written. Execution time was comparable across modes, with no regressions
observed.

I also ran make check, which passed.

Based on this testing, everything looks good to me, and the observed
behavior matches the documentation clarification about workload-dependent
effects of temp file compression.

Thanks for working on this  I’m happy to test further or try additional
workloads if that would be useful.

Best regards,
Lakshmi

On Sun, Jan 25, 2026 at 5:27 PM Filip Janus <[email-masked]> wrote:

>>>> On Tue, Jan 20, 2026 at 4:10 AM Zsolt Parragi <
>>>> [email-masked]> wrote:
>>>>
>>>>> Hello!
>>>>>

---

From: Filip Janus <[email-masked]>
Date: Mon, 25 May 2026 09:23:15 +0200

Hi Tomas,

Thanks for the feedback.

> What is COMPRESS_BLCKSZ? I don't see that in the patch anywhere.
> What am I missing?

It is a #define I introduced in the latest revision of the patch,
in src/backend/storage/file/buffile.c:

  #define COMPRESS_BLCKSZ   (4 * BLCKSZ)   /* 32KB */

The version you benchmarked (from January) used BLCKSZ (8 KB) directly
as the compression unit -- each 8 KB buffer was compressed and written
separately. After your benchmark, I experimented with larger blocks
and found that compressing 32 KB at a time works noticeably better:
the algorithm gets more context per call (better entropy coding),
per-block framing overhead is amortized, and we make 4x fewer
compress/decompress calls.

The motivation was to improve both speed and compression ratio: with
8 KB blocks, the algorithm sees too little data per call to exploit
redundancy effectively, especially for wider rows where repetitive
patterns span more than one page. 32 KB gives substantially better
ratios with fewer calls, without excessive memory overhead (one
extra 32 KB buffer per open compressed BufFile).

For testing I recompiled with three values -- BLCKSZ (8 KB),
4*BLCKSZ (32 KB), and 8*BLCKSZ (64 KB). It is a compile-time
constant, not a GUC.

> I'm still not quite sure what "compression block size" means here,
> and how did you change it.

Same answer -- sorry for not being clear. Your benchmark used the
original 8 KB block size from the January patch. My main results
used the updated patch with 32 KB blocks. The comparison in my first
email was not entirely apples-to-apples -- I should have noted that
more clearly.

That said, the block size accounts for only a modest part of the
difference (e.g. lz4 d=1000 w=8 on HDD: 58% with 8 KB vs 52% with
32 KB). The larger gains come from the storage and memory pressure
differences between our machines.

> I wonder how much this depends on the particular data set (e.g. if
> we generate data differently, how much would it affect the results).

Good question. The d parameter already covers a range of data
redundancy (d=1 is least compressible, d=1000 is most), so the tables
show best and worst cases for the same schema. Real-world workloads
with wider rows, more NULLs, or variable-length fields would likely
compress differently -- I'd expect better ratios in many cases, since
the benchmark data is relatively compact (bigint + md5 text).

> What bothers me a little bit is that systems generally are not under
> such pressure 24/7, but only for some part of a day. But people will
> mostly set the GUC in the config file.

That is a fair point. temp_file_compression can be set at the session
level (SET temp_file_compression = 'lz4'), so an application could
enable it only for known-heavy queries. On our I/O-constrained
machines the worst case for lz4 was ~94% (within noise). Your
results on fast NVMe showed higher overhead -- up to ~135% for lz4
with w=1, where CPU cost dominates and there's no I/O to save. So
for systems with plenty of RAM and fast storage, per-session or
per-query activation may indeed be more appropriate than a global
setting.

No rush on further work -- happy to run more tests in the meantime
if anything comes to mind.

regards

    -Filip-

út 12. 5. 2026 v 16:14 odesílatel Tomas Vondra <[email-masked]> napsal:

> On 5/11/26 09:09, Filip Janus wrote:
> >
> >
> > Hi Tomas,
> >
> > Thanks for the thorough benchmark and the script -- it was very helpful
> > as a starting point for my testing. I understand the results on
> > your machine were discouraging, and I appreciate the honest assessment.
> >
> > I ran a similar benchmark on different x86_64 hardware to see how the
> > results change under more I/O pressure. The short version: lz4 and
> > zstd show significant speedups once storage or page cache becomes a
> > bottleneck.
> >
>
> I'm glad you didn't just give up and decided to run some more tests.
>
> > Setup
> > -----
> >
> > I used your run-hashjoins.sh as a base, with the same parameters:
> > 100M rows, d in {1, 10, 100, 1000}, w in {1, 4, 8}, drop-caches
> > between runs. I also added zstd to the compression methods tested,
> > and tested with a larger compression block size (32 KB instead of
> > the default 8 KB BLCKSZ).
> >
> > Two x86_64 machines:
> >
> >   (A) HPE BL460c Gen10, 2x Xeon Gold 6148, 64 GB RAM,
> >       rotational HDD (5 disks), io_uring, Fedora 43
> >
> >   (B) Dell MX840c, Xeon Gold 6148, SATA SSD (~224 GB),
> >       RAM capped to 16 GB via systemd MemoryMax
> >
> > Both use 32 KB compression blocks (COMPRESS_BLCKSZ = 4*BLCKSZ).
> >
>
> What is COMPRESS_BLCKSZ? I don't see that in the patch anywhere. What am
> I missing?
>
> > Results
> > -------
> >
> > Below are the relative timings (% of uncompressed baseline), directly
> > comparable to your table. Values below 100% mean compression is faster.
> >
> > Your results (Xeon, 64 GB, SSD/NVMe, 8 KB blocks):
> >
> >                      pglz              lz4
> >   rows  rep    1    4    8       1    4    8
> >   -------------------------------------------------
> >    10     1  661  688  300     144  148   86
> >    10  1000  460  472  234     119  119   58
> >   100     1  471  303  204     132  135  102
> >   100  1000  378  262  164     107   91   81
> >
> > Our results, machine A -- x86 HDD, 64 GB, 32 KB blocks:
> >
> >                      pglz              lz4              zstd
> >   rows  rep    1    4    8       1    4    8       1    4    8
> >   ----------------------------------------------------------------
> >   100     1  200  119   69      91   82   67      80   50   35
> >   100    10  204  101   70      91   64   66      83   44   39
> >   100   100  220  104   72      94   75   69      85   50   34
> >   100  1000  170   92   54      79   58   52      74   42   28
> >
> > Our results, machine B -- x86 SATA SSD, 16 GB cap, 32 KB blocks:
> >
> >                      pglz              lz4              zstd
> >   rows  rep    1    4    8       1    4    8       1    4    8
> >   ----------------------------------------------------------------
> >   100     1  284  103   79      92   81   82      98   59   53
> >   100    10  262   99   77      92   80   85      96   57   50
> >   100   100  221   89   67      80   70   64      85   49   44
> >   100  1000  155   51   42      72   39   39      77   27   29
> >
> > Analysis
> > --------
> >
> > I think the key difference is page cache pressure. Your machine has
> > 64 GB RAM with 8 GB shared_buffers, leaving ~56 GB for the OS page
> > cache. Even with 8 connections x ~10 GB temp files = ~80 GB, a large
> > portion stays cached and synchronous I/O to storage is limited.
> >
> > On our machines, I/O is a real bottleneck:
> >   - Machine A: rotational HDD with 8 concurrent streams
> >   - Machine B: SATA SSD but only 16 GB RAM, so the page cache
> >     cannot absorb 8 x 12 GB of temp data
> >
> > Under these conditions, reducing the bytes written translates
> > directly into wall-clock savings.
> >
>
> Seems like that. It's not a huge surprise that this matters more on
> systems with memory pressure and slower storage. I should have tested
> that on my machines too.
>
> I was going to question how common such systems are nowadays, when
> people can just spin a VM with plenty of RAM and SSDs. But given the
> current RAM shortage / costs, and relatively slow network storage (even
> if temporary files can use ephemeral disks), maybe it's not all that
> uncommon ...
>
> > Both your results and ours confirm that pglz is simply too slow for
> > this use case. Your benchmark shows 164-688% overhead; ours shows
> > 155-284% with w=1. Even under heavy I/O contention (w=8 on HDD)
> > where pglz eventually wins, it never outperforms lz4 or zstd. I
> > would recommend against offering pglz for temp file compression
> > altogether -- it creates a trap for users who might try it expecting
> > reasonable performance.
> >
>
> Right.
>
> > lz4 looks safe: the worst case in our data is 94% (w=1, d=100 on
> > HDD) -- barely distinguishable from noise. Under I/O pressure it
> > delivers 39-52% of baseline time (2-2.5x speedup).
> >
> > zstd is the most compelling option: it achieves the best compression
> > ratios (down to 22% of original size on the SATA SSD) and the best
> > speedups (27-28% of baseline = 3.5x faster), with no regression
> > exceeding 98% on x86_64. I would recommend zstd as the primary
> > option to document, with lz4 as a lighter-weight alternative.
> >
>
> Agreed. lz4 seems safe, zstd is good too. I wonder how much this depends
> on the particular data set (e.g. if we generate data differently, how
> much would it affect the results).
>
> > Compression block size
> > ----------------------
> >
> > I also tested 8 KB, 32 KB, and 64 KB compression block sizes.
> > 32 KB appears to be the sweet spot. Example for lz4, d=1000, w=8
> > on HDD:
> >
> >    COMPRESS_BLCKSZ    time (% of no)    compressed bytes
> >    --------------------------------------------------------
> >     8 KB (BLCKSZ)         58%             7.47 GB
> >    32 KB (4*BLCKSZ)       52%             7.22 GB
> >    64 KB (8*BLCKSZ)       56%             7.14 GB
> >
> > The 8K-to-32K improvement comes from fewer compress/decompress calls
> > (4x fewer), less per-block header overhead, and better compression
> > ratios. Going to 64K shows diminishing returns and slightly worse
> > timings, possibly due to increased cache pressure.
> >
>
> I'm still not quite sure what "compression block size" means here, and
> how did you change it.
>
> > Conclusion
> > ----------
> >
> > I think the data shows that the benefit of temporary file compression
> > depends heavily on the I/O characteristics of the system. On machines
> > with fast storage and ample page cache, compression is neutral -- it
> > means negligible overhead, which is a good outcome on its own. On
> > systems with real I/O pressure -- slower storage, limited RAM, or
> > concurrent workloads competing for page cache -- compression delivers
> > substantial speedups.
> >
>
> True.
>
> > The feature does not need to be enabled by default. Compression is
> > controlled by the temp_file_compression GUC, which defaults to "none".
> > That means there is no risk of regression for existing users. But for
> > administrators who know their systems are I/O-constrained -- spinning
> > disks, limited memory, heavy concurrent spilling -- having the option
> > to enable lz4 or zstd can make a real difference. The data above shows
> > up to 3.5x speedup in those scenarios, with no
> > downside when the setting is left at its default.
> >
> Yes, having it as opt-in for systems where it matters helps.
>
> What bothers me a little bit is that systems generally are not under
> such pressure 24/7, but only for some part of a day. But people will
> mostly set the GUC in the config file. I don't have a better solution to
> this, though.
>
>
> FYI I won't be able to do much work on this until ~June.
>
>
> regards
>
> --
> Tomas Vondra
>
>