Thread
-
Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2024-11-14T22:13:16Z
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- -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2024-11-18T21:58:27Z
Let's fix the compiler warning caused by an uninitialized local variable. -Filip- čt 14. 11. 2024 v 23:13 odesílatel Filip Janus <fjanus@redhat.com> napsal: > 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- > -
Re: Proposal: Adding compression of temporary files
Tomas Vondra <tomas@vondra.me> — 2024-11-20T00:35:29Z
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. 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. > # 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? 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
-
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2024-11-24T14:53:38Z
-Filip- st 20. 11. 2024 v 1:35 odesílatel Tomas Vondra <tomas@vondra.me> 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 > >
-
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2024-11-28T11:32:15Z
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 <fjanus@redhat.com> napsal: > > > -Filip- > > > st 20. 11. 2024 v 1:35 odesílatel Tomas Vondra <tomas@vondra.me> 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 >> >> -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-01-04T22:40:46Z
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 <fjanus@redhat.com> 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 <fjanus@redhat.com> napsal: > >> >> >> -Filip- >> >> >> st 20. 11. 2024 v 1:35 odesílatel Tomas Vondra <tomas@vondra.me> 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 >>> >>> -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-01-04T23:43:05Z
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 <fjanus@redhat.com> 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 <fjanus@redhat.com> 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 <fjanus@redhat.com> >> napsal: >> >>> >>> >>> -Filip- >>> >>> >>> st 20. 11. 2024 v 1:35 odesílatel Tomas Vondra <tomas@vondra.me> 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 >>>> >>>> -
Re: Proposal: Adding compression of temporary files
Alexander Korotkov <aekorotkov@gmail.com> — 2025-03-15T10:40:30Z
On Sun, Jan 5, 2025 at 1:43 AM Filip Janus <fjanus@redhat.com> wrote: > > I apologize for multiple messages, but I found a small bug in the previous version. > > -Filip- Great, thank you for your work. I think the patches could use a pgindent run. I don't see a reason why the temp file compression method should be different from the wal compression methods, which we already have in-tree. Perhaps it would be nice to have a 0001 patch, which would abstract the compression methods we now have for wal into a separate file containing GUC option values and functions for compress/decompress. Then, 0002 would apply this to temporary file compression. ------ Regards, Alexander Korotkov Supabase
-
Re: Proposal: Adding compression of temporary files
Tomas Vondra <tomas@vondra.me> — 2025-03-17T22:13:06Z
On 3/15/25 11:40, Alexander Korotkov wrote: > On Sun, Jan 5, 2025 at 1:43 AM Filip Janus <fjanus@redhat.com> wrote: >> >> I apologize for multiple messages, but I found a small bug in the previous version. >> >> -Filip- > > Great, thank you for your work. > > I think the patches could use a pgindent run. > > I don't see a reason why the temp file compression method should be > different from the wal compression methods, which we already have > in-tree. Perhaps it would be nice to have a 0001 patch, which would > abstract the compression methods we now have for wal into a separate > file containing GUC option values and functions for > compress/decompress. Then, 0002 would apply this to temporary file > compression. > Not sure I understand the design you're proposing ... AFAIK the WAL compression is not compressing the file data directly, it's compressing backup blocks one by one, which then get written to WAL as one piece of a record. So it's dealing with individual blocks, not files, and we already have API to compress blocks (well, it's pretty much the APIs for each compression method). You're proposing abstracting that into a separate file - what would be in that file? How would you abstract this to make it also useful for file compression? I can imagine a function CompressBufffer(method, dst, src, ...) wrapping the various compression methods, unifying the error handling, etc. I can imagine that, but that API is also limiting - e.g. how would that work with stream compression, which seems irrelevant for WAL, but might be very useful for tempfile compression. IIRC this is mostly why we didn't try to do such generic API for pg_dump compression, there's a local pg_dump-specific abstraction. FWIW looking at the patch, I still don't quite understand why it needs to correct the offset like this: + if (!file->compress) + file->curOffset -= (file->nbytes - file->pos); + 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. regards -- Tomas Vondra -
Re: Proposal: Adding compression of temporary files
Alexander Korotkov <aekorotkov@gmail.com> — 2025-03-18T11:42:17Z
On Tue, Mar 18, 2025 at 12:13 AM Tomas Vondra <tomas@vondra.me> wrote: > On 3/15/25 11:40, Alexander Korotkov wrote: > > On Sun, Jan 5, 2025 at 1:43 AM Filip Janus <fjanus@redhat.com> wrote: > >> > >> I apologize for multiple messages, but I found a small bug in the previous version. > >> > >> -Filip- > > > > Great, thank you for your work. > > > > I think the patches could use a pgindent run. > > > > I don't see a reason why the temp file compression method should be > > different from the wal compression methods, which we already have > > in-tree. Perhaps it would be nice to have a 0001 patch, which would > > abstract the compression methods we now have for wal into a separate > > file containing GUC option values and functions for > > compress/decompress. Then, 0002 would apply this to temporary file > > compression. > > > > Not sure I understand the design you're proposing ... > > AFAIK the WAL compression is not compressing the file data directly, > it's compressing backup blocks one by one, which then get written to WAL > as one piece of a record. So it's dealing with individual blocks, not > files, and we already have API to compress blocks (well, it's pretty > much the APIs for each compression method). > > You're proposing abstracting that into a separate file - what would be > in that file? How would you abstract this to make it also useful for > file compression? > > I can imagine a function CompressBufffer(method, dst, src, ...) wrapping > the various compression methods, unifying the error handling, etc. I can > imagine that, but that API is also limiting - e.g. how would that work > with stream compression, which seems irrelevant for WAL, but might be > very useful for tempfile compression. Yes, I was thinking about some generic API that provides a safe way to compress some data chunk with given compression method. It seems that yet it should suit both WAL, toast and temp files in the current implementation. But, yes, if we would implement streaming compression in future that would require another API. ------ Regards, Alexander Korotkov Supabase
-
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-03-28T08:23:13Z
-Filip- po 17. 3. 2025 v 23:13 odesílatel Tomas Vondra <tomas@vondra.me> napsal: > On 3/15/25 11:40, Alexander Korotkov wrote: > > On Sun, Jan 5, 2025 at 1:43 AM Filip Janus <fjanus@redhat.com> wrote: > >> > >> I apologize for multiple messages, but I found a small bug in the > previous version. > >> > >> -Filip- > > > > Great, thank you for your work. > > > > I think the patches could use a pgindent run. > > > > I don't see a reason why the temp file compression method should be > > different from the wal compression methods, which we already have > > in-tree. Perhaps it would be nice to have a 0001 patch, which would > > abstract the compression methods we now have for wal into a separate > > file containing GUC option values and functions for > > compress/decompress. Then, 0002 would apply this to temporary file > > compression. > > > > Not sure I understand the design you're proposing ... > > AFAIK the WAL compression is not compressing the file data directly, > it's compressing backup blocks one by one, which then get written to WAL > as one piece of a record. So it's dealing with individual blocks, not > files, and we already have API to compress blocks (well, it's pretty > much the APIs for each compression method). > > You're proposing abstracting that into a separate file - what would be > in that file? How would you abstract this to make it also useful for > file compression? > > I can imagine a function CompressBufffer(method, dst, src, ...) wrapping > the various compression methods, unifying the error handling, etc. I can > imagine that, but that API is also limiting - e.g. how would that work > with stream compression, which seems irrelevant for WAL, but might be > very useful for tempfile compression. > > IIRC this is mostly why we didn't try to do such generic API for pg_dump > compression, there's a local pg_dump-specific abstraction. > > > FWIW looking at the patch, I still don't quite understand why it needs > to correct the offset like this: > > + if (!file->compress) > + file->curOffset -= (file->nbytes - file->pos); > This line of code is really confusing to me, and I wasn't able to fully understand why it must be done, but I experimented with it, and if I remember correctly, it's triggered (the result differs from 0) mainly in the last call of BufFileDumpBuffer function for a single data chunk. > + 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. > regards > > -- > Tomas Vondra > > -
Re: Proposal: Adding compression of temporary files
Dmitry Dolgov <9erthalion6@gmail.com> — 2025-04-13T19:53:43Z
> 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? -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-04-22T07:17:30Z
Since the patch was prepared months ago, it needs to be rebased. -Filip- ne 13. 4. 2025 v 21:53 odesílatel Dmitry Dolgov <9erthalion6@gmail.com> 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? > > -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-04-25T21:54:00Z
The latest rebase. -Filip- út 22. 4. 2025 v 9:17 odesílatel Filip Janus <fjanus@redhat.com> napsal: > Since the patch was prepared months ago, it needs to be rebased. > > -Filip- > > > ne 13. 4. 2025 v 21:53 odesílatel Dmitry Dolgov <9erthalion6@gmail.com> > 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? >> >> -
Re: Proposal: Adding compression of temporary files
Andres Freund <andres@anarazel.de> — 2025-06-17T14:49:00Z
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 -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-08-18T16:51:57Z
I rebased the proposal and fixed the problem causing those problems. -Filip- út 17. 6. 2025 v 16:49 odesílatel Andres Freund <andres@anarazel.de> 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 > > > -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-08-19T15:48:31Z
Fix overlooked compiler warnings -Filip- po 18. 8. 2025 v 18:51 odesílatel Filip Janus <fjanus@redhat.com> napsal: > I rebased the proposal and fixed the problem causing those problems. > > -Filip- > > > út 17. 6. 2025 v 16:49 odesílatel Andres Freund <andres@anarazel.de> > 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 >> >> >> -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-09-26T18:13:21Z
Rebase after changes introduced in guc_tables.c -Filip- út 19. 8. 2025 v 17:48 odesílatel Filip Janus <fjanus@redhat.com> napsal: > Fix overlooked compiler warnings > > -Filip- > > > po 18. 8. 2025 v 18:51 odesílatel Filip Janus <fjanus@redhat.com> napsal: > >> I rebased the proposal and fixed the problem causing those problems. >> >> -Filip- >> >> >> út 17. 6. 2025 v 16:49 odesílatel Andres Freund <andres@anarazel.de> >> 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 >>> >>> >>> -
Re: Proposal: Adding compression of temporary files
Tomas Vondra <tomas@vondra.me> — 2025-09-30T12:42:18Z
Hello Filip, Thanks for the updated patch, and for your patience with working on this patch with (unfortunately) little feedback. I took a look at the patch, and did some testing. In general, I think it's heading in the right direction, but there's still a couple issues and open questions. Attached is a collection of incremental patches with the proposed changes. I'll briefly explain the motivation for each patch, but it's easier to share the complete change as a patch. Feel free to disagree with the changes, some are a matter of opinion, and/or there might be a better way to do that. Ultimately it should be squashed to the main patch, or perhaps a couple larger patches. v20250930-0001-Add-transparent-compression-for-temporary-.patch - original patch, as posted on 2025/09/26 v20250930-0002-whitespace.patch - cleanup of whitespace issues - This is visible in git-show or when applying using git-am. v20250930-0003-pgindent.patch - pgindent run, to fix code style (formatting of if/else branches, indentation, that kind of stuff) - good to run pgindent every now and then, for consistency v20250930-0004-Add-regression-tests-for-temporary-file-co.patch - original patch, as posted on 2025/09/26 v20250930-0005-remove-unused-BufFile-compress_tempfile.patch - the compress_tempfile was unused, get rid of it v20250930-0006-simplify-BufFileCreateTemp-interface.patch - I think the proposed interface (a "compress" flag in BufFileCreateTemp and then a separate method BufFileCreateCompressTemp) is not great. - The "compress" flag is a bit pointless, because even if you set it to "true" you won't get compressed file. In fact, it's fragile - you'll get broken BufFile without the buffer. - The patch gets rid of the "compress" flag (so existing callers of BufFileCreateTemp remain unchanged). BufFileCreateCompressTemp sets the flag directly, which it can because it's in the same module. - An alternative would be to keep the flag, do all the compression setup in BufFileCreateTemp, and get rid of BufFileCreateCompressTemp. Not sure which is better. v20250930-0007-improve-BufFileCreateTemp-BufFileCreateCom.patch - Just improving comments, to document the new stuff (needs a check). - There are two new XXX comments, with questions. One asks if the allocation is an issue in practice - is the static buffer worth it? The other suggests we add an assert protecting against unsupported seeks. v20250930-0008-BufFileCreateCompressTemp-cleanup-and-comm.patch - A small BufFileCreateCompressTemp cleanup (better comments, better variable names, formatting, extra assert, ... mostly cosmetic stuff). - But this made me realize the 'static buffer' idea is likely flawed, at least the current code. It does pfree() on the current buffer, but how does it know if there are other files referencing it? Because it then stashes the buffer to file->buffer. I haven't tried to reproduce the issue, nor fixed this, but it seems like it might be a problem if two files happen to use a different compression method. v20250930-0009-minor-BufFileLoadBuffer-cleanup.patch - Small BufFileLoadBuffer cleanup, I don't think it's worth it to have such detailed error messages. So just use "could not read file" and then whatever libc appends as %m. v20250930-0010-BufFileLoadBuffer-simpler-FileRead-handlin.patch v20250930-0011-BufFileLoadBuffer-simpler-FileRead-handlin.patch - I was somewhat confused by the FileRead handling in BufFileLoadBuffer, so these two patches try to improve / clarify it. - I still don't understand the purpose of the "if (nread_orig <= 0)" branch removed by the second patch. v20250930-0012-BufFileLoadBuffer-comment-update.patch - minor comment tweak v20250930-0013-BufFileLoadBuffer-simplify-skipping-header.patch - I found it confusing how the code advanced the offset by first adding to header_advance, and only then adding to curOffset much later. This gets rid of that, and just advances curOffset right after each read. v20250930-0014-BufFileDumpBuffer-cleanup-simplification.patch - Improve the comments in BufFileDumpBuffer, and simplify the code. This is somewhat subjective, but I think the code is more readable. - It temporarily removes the handling of -1 for pglz compression. This was a mistake, and is fixed by a later patch. v20250930-0015-BufFileLoadBuffer-comment.patch - XXX for a comment I don't understand. v20250930-0016-BufFileLoadBuffer-missing-FileRead-error-h.patch - Points out a FileRead call missing error handling (there's another one with the same issue). v20250930-0017-simplify-the-compression-header.patch - I came to the conclusion that having one "length" field for lz4 and two (compressed + raw) for pglz makes the code unnecessarily complex, without gaining much. So this just adds a "header" struct with both lengths for all compression algorithms. I think it's cleaner/simpler. v20250930-0018-undo-unncessary-changes-to-Makefile.patch - Why did the 0001 patch add this? Maybe it's something we should add separately, not as part of this patch? v20250930-0019-enable-compression-for-tuplestore.patch - Enables compression for tuplestores that don't require random access. This covers e.g. tuplestores produces by SRF like generate_series, etc. - I still wonder what would it take to support random access. What if we remember offsets of each block? We could write that into an uncompressed file. That'd be 128kB per 1GB, which seems acceptable. Just a thought. v20250930-0020-remember-compression-method-for-each-file.patch - The code only tracked bool "compress" flag for each file, and then determined the algorithm during compression/decompression based on the GUC variable. But that's incorrect, because the GUC can change during the file life time. For example, there can be a cursor, anb you can do SET temp_file_compression between the FETCH calls (see the commit message for an example). - So this replaces the flag with storing the actual method. v20250930-0021-LZ4_compress_default-returns-0-on-error.patch - The LZ4_compress_default returns 0 in case of error. Probably a bug introduced by one of my earlier patches. v20250930-0022-try-LZ4_compress_fast.patch - Experimental patch, trying a faster LZ4 compression. So that's what I have at the moment. I'm also doing some testing, measuring the effect of compression both for trivial queries (based on generate_series) and more complex ones from TPC-H. I'll post the complete results when I have that, but the results I've seen so far show that: - pglz and lz4 end up with about the same compression ratio (in TPC-H it's often cutting the temp files in about half) - lz4 is on par with no compression (it's pretty much within noise), while pglz is much slower (sometimes ~2x slower) I wonder how would gzip/zstandard perform. My guess would be that gzip would be faster than pglz, but still slower than lz4. Zstandard is much closer to lz4. Would it be possible to have some experimental patches for gzip/zstd, so that we can try? It'd also validate that the code is prepared for adding more algorithms in the future. The other thing I was thinking about was the LZ4 stream compression. There's a comment suggesting it might compress better, and indeed - when working on pg_dump compression we saw a huge improvement. Again, would be great to support have an experimental patch for this, so that we can evaluate it. regards -- Tomas Vondra
-
Re: Proposal: Adding compression of temporary files
Tomas Vondra <tomas@vondra.me> — 2025-10-01T15:53:26Z
Hi, On 9/30/25 14:42, Tomas Vondra wrote: > > v20250930-0018-undo-unncessary-changes-to-Makefile.patch > > - Why did the 0001 patch add this? Maybe it's something we should add > separately, not as part of this patch? > I realized this bit is actually necessary, to make the EXTRA_TESTS work for the lz4 regression test. The attached patch series skips this bit. There's also experimental patches adding gzip (or rather libz) and zstd compression. This is very rough, I just wanted to see how would these perform compared to pglz/lz4. But I haven't done any proper evaluation so far, beyond running a couple simple queries. Will try to spend a bit more time on that soon. I still wonder about the impact of stream compression. I know it can improve the compression ratio, but I'm not sure if it also helps with the compression speed. I think for temporary files faster compression (and lower ratio) may be a better trade off. So maybe we should user lower compression levels ... Attached are two PDF files with results of the perf evaluation using TPC-H 10GB and 50GB data sets. One table shows timings for 22 queries with compression set to no/pglz/lz4, for a range of parameter combinations (work_mem, parallel workers). The other shows amount of temporary files (in MBs) generated by each query. The timing shows that pglz is pretty slow, about doubling duration for some of the queries. That's not surprising, we know pglz can be slow. lz4 is almost perfectly neutral, which is actually great - the goal is to reduce I/O pressure for temporary files, but with a single query running at a time, that's not a problem. So "no impact" is about the best we can do, it shows the lz4 overhead is negligible. For "size" PDF shows that the compression can save a fair amount of temp space. For many queries it saves 50-70% of temporary space. A good example is Q9 which (on the 50GB scale) used to take about 33GB, and with compression it's down to ~17GB (with both pglz and lz4). That's pretty good, I think. FWIW the "size" results may be a bit misleading, in that it measures tempfile size for the whole query. But some may use multiple temporary files, and some may not support compression (e.g. tuplesort don't). Which will make the actual compression ratio look lower. OTOH it's a more representative of impact on actual queries. regards -- Tomas Vondra
-
Re: Proposal: Adding compression of temporary files
Álvaro Herrera <alvherre@kurilemu.de> — 2025-10-20T09:36:08Z
Hello, This latest patchset is failing CI because of some compiler warnings on Windows, see below. Also, who is squashing all these fixup patches? I suppose it's up to Filip to submit a squashed version with the ones he approves of, but he hasn't replied since you (Tomas) posted them; I hope the series is not dead? Thanks [14:52:10.761] buffile.c: In function ‘BufFileLoadBuffer’: [14:52:10.761] buffile.c:725:95: error: passing argument 2 of ‘uncompress’ from incompatible pointer type [-Werror=incompatible-pointer-types] [14:52:10.761] 725 | ret = uncompress((uint8 *) file->buffer.data, &len, [14:52:10.761] | ^~~~ [14:52:10.761] | | [14:52:10.761] | size_t * {aka long long unsigned int *} [14:52:10.761] In file included from /usr/x86_64-w64-mingw32/include/zlib.h:34, [14:52:10.761] from buffile.c:64: [14:52:10.761] /usr/x86_64-w64-mingw32/include/zlib.h:1267:32: note: expected ‘uLongf *’ {aka ‘long unsigned int *’} but argument is of type ‘size_t *’ {aka ‘long long unsigned int *’} [14:52:10.761] 1267 | ZEXTERN int ZEXPORT uncompress OF((Bytef *dest, uLongf *destLen, [14:52:10.761] | ^~ [14:52:10.761] buffile.c: In function ‘BufFileDumpBuffer’: [14:52:10.761] buffile.c:854:101: error: passing argument 2 of ‘compress2’ from incompatible pointer type [-Werror=incompatible-pointer-types] [14:52:10.761] 854 | ret = compress2((uint8 *) (cData + sizeof(CompressHeader)), &len, [14:52:10.761] | ^~~~ [14:52:10.761] | | [14:52:10.761] | size_t * {aka long long unsigned int *} [14:52:10.761] /usr/x86_64-w64-mingw32/include/zlib.h:1244:31: note: expected ‘uLongf *’ {aka ‘long unsigned int *’} but argument is of type ‘size_t *’ {aka ‘long long unsigned int *’} [14:52:10.761] 1244 | ZEXTERN int ZEXPORT compress2 OF((Bytef *dest, uLongf *destLen, [14:52:10.761] | ^~ -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "XML!" Exclaimed C++. "What are you doing here? You're not a programming language." "Tell that to the people who use me," said XML. https://burningbird.net/the-parable-of-the-languages/ -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2025-10-20T11:53:16Z
Definitely, it’s not dead. I’m a bit busy at the moment, but I will address the compiler warnings along with the review. -Filip- po 20. 10. 2025 v 11:36 odesílatel Álvaro Herrera <alvherre@kurilemu.de> napsal: > Hello, > > This latest patchset is failing CI because of some compiler warnings on > Windows, see below. Also, who is squashing all these fixup patches? I > suppose it's up to Filip to submit a squashed version with the ones he > approves of, but he hasn't replied since you (Tomas) posted them; I hope > the series is not dead? > > Thanks > > [14:52:10.761] buffile.c: In function ‘BufFileLoadBuffer’: > [14:52:10.761] buffile.c:725:95: error: passing argument 2 of ‘uncompress’ > from incompatible pointer type [-Werror=incompatible-pointer-types] > [14:52:10.761] 725 | ret > = uncompress((uint8 *) file->buffer.data, &len, > [14:52:10.761] | > ^~~~ > [14:52:10.761] | > | > [14:52:10.761] | > size_t * {aka long long unsigned > int *} > [14:52:10.761] In file included from > /usr/x86_64-w64-mingw32/include/zlib.h:34, > [14:52:10.761] from buffile.c:64: > [14:52:10.761] /usr/x86_64-w64-mingw32/include/zlib.h:1267:32: note: > expected ‘uLongf *’ {aka ‘long unsigned int *’} but argument is of type > ‘size_t *’ {aka ‘long long unsigned int *’} > [14:52:10.761] 1267 | ZEXTERN int ZEXPORT uncompress OF((Bytef *dest, > uLongf *destLen, > [14:52:10.761] | ^~ > [14:52:10.761] buffile.c: In function ‘BufFileDumpBuffer’: > [14:52:10.761] buffile.c:854:101: error: passing argument 2 of ‘compress2’ > from incompatible pointer type [-Werror=incompatible-pointer-types] > [14:52:10.761] 854 | ret = > compress2((uint8 *) (cData + sizeof(CompressHeader)), &len, > [14:52:10.761] | > ^~~~ > [14:52:10.761] | > | > [14:52:10.761] | > size_t * {aka long long > unsigned int *} > [14:52:10.761] /usr/x86_64-w64-mingw32/include/zlib.h:1244:31: note: > expected ‘uLongf *’ {aka ‘long unsigned int *’} but argument is of type > ‘size_t *’ {aka ‘long long unsigned int *’} > [14:52:10.761] 1244 | ZEXTERN int ZEXPORT compress2 OF((Bytef *dest, > uLongf *destLen, > [14:52:10.761] | ^~ > > > -- > Álvaro Herrera Breisgau, Deutschland — > https://www.EnterpriseDB.com/ > "XML!" Exclaimed C++. "What are you doing here? You're not a programming > language." > "Tell that to the people who use me," said XML. > https://burningbird.net/the-parable-of-the-languages/ > > > -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2026-05-11T07:09:21Z
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. 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). 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. 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. 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. 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. 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. 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. I am attaching two PDFs visualizing my SSD/DHH results. Full CSV results and the benchmark script are attached. Happy to run additional tests if you have suggestions for other scenarios. regards -Filip- st 25. 3. 2026 v 21:24 odesílatel Tomas Vondra <tomas@vondra.me> napsal: > Hello Filip, > > Thanks for the updated patch. I finally had some more time to do a > review. I think the code looks pretty good, unfortunately the results of > my performance validation are not very positive :-( That's not your > fault, of course, but I'm not quite sure it can be fixed. > > The test I did is fairly simple - execute a hash join that spills data > (which is a case that can be compressed), and measure how long it takes. > And do it from multiple connections concurrently, to spill more data, > possibly more than available RAM. > > The attached script runs a hashjoin query, with these parameters: > > * rows: 1M, 10M and 100M rows > * duplicates: 1, 10, 100, 1000 (determines compressibility) > * workers: 1, 4, 8 (number of connections) > * compression: no, pglz, lz4 > > The system has 64GB RAM, shared_buffers was set to 8GB. That leaves > ~56GB for system and page cache. The data sizes need to spill about > 100MB per 1M rows, so 100M rows means ~10GB of temporary files. > > So what behavior would be "OK" in various cases? > > With 1M and 10M rows, the temporary files can be kept in memory, even > with 8 connections (we'll write ~8GB temp files in total). The kernel > may evict some of the data to disk, but that happens in the background, > and synchronous I/O is required. I believe the best outcome we can > expect is probably the same duration as without compression. > > With 100M rows this generates >10GB of temporary files per connections. > With 8 connections, that's >80GB, which exceeds the page cache capacity, > and so will have to do quite a bit of I/O. In this case we expect a > (hopefully) significant speedup, depending on how compressible the > temporary data are (the higher the "d" value, the better. > > With 50% compression. we'd need to write just 40GB, which could even fit > into page cache (and not need I/O at all). > > Here are the timings from the "xeon" machine, for 10M and 100M rows. The > attached PDFs have a more complete data from another machine (with two > types of storage). But the behavior is pretty much the same, so let's > focus on this example: > > | no | pglz | lz4 > rows rep | 1 4 8 | 1 4 8 | 1 4 8 > --------------------------------------------------------------- > 10 1 | 6 6 15 | 40 41 45 | 8 8 12 > 10 | 6 6 12 | 39 40 43 | 8 8 13 > 100 | 6 6 13 | 36 37 40 | 8 8 9 > 1000 | 6 6 13 | 27 28 30 | 7 7 7 > 100 1 | 76 136 233 | 361 413 477 | 101 184 239 > 10 | 87 143 226 | 368 398 470 | 110 157 248 > 100 | 87 128 233 | 367 402 477 | 96 169 247 > 1000 | 85 138 246 | 322 362 403 | 90 126 198 > > If we take the "no" compression as a baseline, then the relative timings > look like this: > > | pglz | lz4 > rows rep | 1 4 8 | 1 4 8 > ---------------------------------------------------------- > 10 1 | 661% 688% 300% | 144% 148% 86% > 10 | 647% 665% 347% | 143% 145% 106% > 100 | 599% 620% 306% | 135% 139% 74% > 1000 | 460% 472% 234% | 119% 119% 58% > 100 1 | 471% 303% 204% | 132% 135% 102% > 10 | 421% 277% 208% | 127% 110% 110% > 100 | 421% 313% 204% | 110% 132% 106% > 1000 | 378% 262% 164% | 107% 91% 81% > > That's not very encouraging, unfortunately. > > The pglz causes a massive regression, making it ~6x slower eve when > everything fits into memory, and there's no chance for the compression > to help. It works better for the large case, where it gets "only" 1.6x > slower than no compression. That doesn't seem like a great deal. > > With lz4 we do much better, it's only ~1.4x slower, and in a couple > cases it even beats no compression. It actually wins even with 10M rows > and 8 connections, which is interesting. But even this seems a bit > disappointing. > > The attached PDFs also show how much data was written to temporary files > (the second chart). It's pretty consistent between pglz/lz4. It's clear > how the "repetitions" parameter affects compressibility, although it's > interesting it gets worse for larger data sets. I assume it's a > consequence of how we write data to a hash table and then spill it, > which likely "mixes up" the data a bit. But I haven't looked into the > details, and I don't think it matters very much. > > Can you please review my benchmark script, and maybe try reproducing the > results? It's entirely possible I did some silly mistake. You'll need to > adjust a couple hard-coded paths in the script. If you don't have access > to suitable hardware, I may be able to provide something. > > It's also possible we do the compression wrong in some way, making it > much more expensive. For pglz that's unlikely, because the API is pretty > simple. And we know pglz is a bit slow. For Lz4 there are multiple ways > to do the compression, so maybe we're not using the right interface? Or > maybe we could tune the compression level somehow? Not sure. > > It's also possible the benchmark is too simplistic. For example, maybe > the results would be much more positive if the storage (and page cache) > was more utilized. For example, if there was a concurrent pgbench with > large scale, the compression might help a lot. > > But that's not an excuse to cause regressions for systems that have > enough RAM / lightly utilized storage (and I assume most systems will be > like that). I don't think a GUC is a good answer to this. If there was a > clear class of systems that universally (and significantly) benefit from > the compression, then maybe. But the gains seem fairly limited. > > I'd suggest reviewing my benchmark script and making sure I haven't made > some silly mistake, maybe try constructing your own test. And then maybe > check it there's a way to do the compression faster (at least for lz4 > there might be some hope). If not, we should probably cut our losses. > > I feel rather awful about this, mostly because I'm the one who suggested > working on this back in 2024. Finding out after ~14 months it may not > actually be a good idea feels pretty sad. I hope you at least learned a > little bit about the development process, and will try again with a > different patch ... > > > regards > > -- > Tomas Vondra > -
Re: Proposal: Adding compression of temporary files
Tomas Vondra <tomas@vondra.me> — 2026-05-12T14:13:57Z
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 -
Re: Proposal: Adding compression of temporary files
Filip Janus <fjanus@redhat.com> — 2026-05-25T07:23:15Z
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 <tomas@vondra.me> 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 > >