PostgreSQL16中pg_dump的LZ4和ZSTD压缩
pg_dump压缩lz4和zstd
LZ4和ZSTD压缩算法合入了PG16。LZ4补丁的作者是Georgios Kokolatos。由Tomas Vondra提交。由Michael Paquier、Rachel Heaton、Justin Pryzby、Shi Yu 和 Tomas Vondra 审阅。提交消息是：
Expand pg_dump's compression streaming and file APIs to support the lz4 algorithm. The newly added compress_lz4.{c,h} files cover all the functionality of the aforementioned APIs. Minor changes were necessary in various pg_backup_* files, where code for the 'lz4' file suffix has been added, as well as pg_dump's compression option parsing. Author: Georgios Kokolatos Reviewed-by: Michael Paquier, Rachel Heaton, Justin Pryzby, Shi Yu, Tomas Vondra Discussion: https://postgr.es/m/faUNEOpts9vunEaLnmxmG-DldLSg_ql137OC3JYDmgrOMHm1RvvWY2IdBkv_CRxm5spCCb_OmKNk2T03TMm0fBEWveFF9wA1WizPuAgB7Ss%3D%40protonmail.com
ZSTD补丁的作者是Justin Pryzby。由Tomas Vondra提交。由Tomas Vondra、Jacob Champion 和 Andreas Karlsson 审阅。提交消息是：
Allow pg_dump to use the zstd compression, in addition to gzip/lz4. Bulk of the new compression method is implemented in compress_zstd.{c,h},covering the pg_dump compression APIs. The rest of the patch adds test and makes various places aware of the new compression method. The zstd library (which this patch relies on) supports multithreaded compression since version 1.5. We however disallow that feature for now, as it might interfere with parallel backups on platforms that rely on threads (e.g. Windows). This can be improved / relaxed in the future. This also fixes a minor issue in InitDiscoverCompressFileHandle(), which was not updated to check if the file already has the .lz4 extension. Adding zstd compression was originally proposed in 2020 (see the second thread), but then was reworked to use the new compression API introduced in e9960732a9. I've considered both threads when compiling the list of reviewers. Author: Justin Pryzby Reviewed-by: Tomas Vondra, Jacob Champion, Andreas Karlsson Discussion: https://postgr.es/m/20230224191840.GD1653@telsasoft.com Discussion: https://postgr.es/m/20201221194924.GI30237@telsasoft.com
尝试下
~$ pg_dump --version pg_dump (PostgreSQL) 16devel ~$ pgbench --initialize --scale=100 dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 10000000 of 10000000 tuples (100%) done (elapsed 39.52 s, remaining 0.00 s) vacuuming... creating primary keys... done in 49.65 s (drop tables 0.00 s, create tables 0.08 s, client-side generate 39.96 s, vacuum 0.29 s, primary keys 9.32 s). ~$ psql --command="select pg_size_pretty(pg_database_size('postgres'))" pg_size_pretty ---------------- 1503 MB (1 row) ~$ time pg_dump --format=custom --compress=lz4:9 > dump.lz4 real 0m10.507s user 0m9.901s sys 0m0.436s ~$ time pg_dump --format=custom --compress=zstd:9 > dump.zstd real 0m8.794s user 0m8.393s sys 0m0.364s ~$ time pg_dump --format=custom --compress=gzip:9 > dump.gz real 0m14.245s user 0m13.064s sys 0m0.978s ~$ time pg_dump --format=custom --compress=lz4 > dump_default.lz4 real 0m6.809s user 0m1.666s sys 0m1.125s ~$ time pg_dump --format=custom --compress=zstd > dump_default.zstd real 0m7.534s user 0m2.428s sys 0m0.892s ~$ time pg_dump --format=custom --compress=gzip > dump_default.gz real 0m11.564s user 0m10.661s sys 0m0.525s ~$ time pg_dump --format=custom --compress=lz4:3 > dump_3.lz4 real 0m8.497s user 0m7.856s sys 0m0.507s ~$ time pg_dump --format=custom --compress=zstd:3 > dump_3.zstd real 0m5.129s user 0m2.228s sys 0m0.726s ~$ time pg_dump --format=custom --compress=gzip:3 > dump_3.gz real 0m4.468s user 0m3.654s sys 0m0.504s ~$ ls -l --block-size=M total 250M -rw-rw-r-- 1 postgres postgres 28M Apr 18 13:58 dump_3.gz -rw-rw-r-- 1 postgres postgres 48M Apr 18 13:57 dump_3.lz4 -rw-rw-r-- 1 postgres postgres 8M Apr 18 13:58 dump_3.zstd -rw-rw-r-- 1 postgres postgres 27M Apr 18 13:57 dump_default.gz -rw-rw-r-- 1 postgres postgres 50M Apr 18 13:56 dump_default.lz4 -rw-rw-r-- 1 postgres postgres 8M Apr 18 13:57 dump_default.zstd -rw-rw-r-- 1 postgres postgres 27M Apr 18 13:56 dump.gz -rw-rw-r-- 1 postgres postgres 48M Apr 18 13:55 dump.lz4 -rw-rw-r-- 1 postgres postgres 8M Apr 18 13:56 dump.zstd
根据命令的输出，得出以下关于三种压缩方法的结论：gzip:这是一种众所周知且广泛使用的压缩方法，可以在压缩率和压缩速度之间提供两行的平衡。lz4:这是一种非常快的压缩算法，以较低的压缩比为代价提供较高的压缩和解压速度。Lz4压缩转出的文件在48-50MB范围，明显大于gzip压缩转储。Zstd：这是一种比较新的压缩算法，压缩比高，压缩速度也不错。Zstd压缩转储的文件大小在8-8.5MB范围内，是三种压缩方法中最小的。令人吃惊的是zstd压缩时间最少，其次是lz4和gzip。该数据可能不是测量和比较的最佳数据。默认压缩级别，zstd生成最小的转储文件大小，其次是lz4和gzip。在最大压缩级别，zstd仍然生成最小的转储文件大小，其次是gzip和lz4。基于这些观察，如果首要任务是减少磁盘使用空间，zstd是推荐的压缩方法。但如果首要任务是减少压缩时间，则zstd和lz4都表现不错。如果担心与其他实用程序的兼容性，gzip仍然是一个可行的选择。
最后
PostgreSQL16中的pg_dump -Z/--compress将不仅仅支持整数。它可用于指定使用的压缩方法和级别。默认仍然是级别为 6 的gzip。但是块上的新方法lz4和zstd已经在这里了！
原文
https://www.cybertec-postgresql.com/en/lz4-zstd-pg_dump-compression-postgresql-16/
