Subject: Proposal: Adding compression of temporary files

From: Filip Janus <[email-masked]>
Date: Thu, 14 Nov 2024 23:13:16 +0100

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.

# 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.

# 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.

# 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 = 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 = 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, 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, 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
+------------+------------------------+------------------------+--------------------------------------

Regards,

    -Filip-

---

From: Filip Janus <[email-masked]>
Date: Mon, 18 Nov 2024 22:58:27 +0100

Let's fix the compiler warning caused by an uninitialized local variable.

    -Filip-

čt 14. 11. 2024 v 23:13 odesílatel Filip Janus <[email-masked]> napsal:

> hashjoins for now. Initially, I've chosen the LZ4 compression algorithm.
> It would
> the buffer. I have two ideas for solving this problem. I would either add
> a buffer into
> extend the support of the temporary file compression also for sorting,
> gist index creation, etc.
>
> eshop.orders o ON oi.order_id = o.order_id WHERE o.order_date >
> '2020-01-01' AND p.price > 50

---

From: Tomas Vondra <[email-masked]>
Date: Wed, 20 Nov 2024 01:35:29 +0100

Hi,

On 11/18/24 22:58, Filip Janus wrote:
> ...
>     produce a significant amount of temporary data that needs to
>     be stored on disk 
>     hashjoins for now. Initially, I've chosen the LZ4 compression
>     algorithm. It would

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.

>     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 

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.

>     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.
> 
>     ratio of LZ4 in block mode seems to be satisfying, but the stream
>     mode could 

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.

Which other places might compress temp files? Surely hash joins are not
the only place that could benefit from this, right?

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).

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.

>     Tables*
>     *
>     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>;
> 
>     Tables:*
>     *
>     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 
>     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
>     GROUP BY p.product_id, p.name <http://p.name>, p.price HAVING
>     SUM(oi.quantity) > 1000
>     Enabled compression slightly reduced the temporary memory usage, but
>     the execution
>     |  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              
>     |                | 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? Do
you maybe have a script to reproduce this, including the data generation?

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?

regards

-- 
Tomas Vondra