gzip

扩展总览

MIXED 第三方扩展： pg_gzip ： 使用SQL执行Gzip压缩与解压缩

基本信息

• 扩展编号： 4010
• 扩展名称： gzip
• 标准包名： pg_gzip
• 扩展类目： UTIL
• 开源协议： MIT
• 官方网站： https://github.com/pramsey/pgsql-gzip
• 编程语言： C
• 其他标签： 无
• 备注信息： 无

元数据

• 默认版本： 1.0.1
• PG大版本： 17,16,15,14,13
• 动态加载： 无需动态加载
• 需要DDL： 需要执行 CREATE EXTENSION DDL
• 可重定位： 无法安装至任意模式下
• 信任程度： 未受信任，创建扩展需要超级用户权限
• 所需模式： 无
• 所需扩展： 无

软件包

• RPM仓库：PGDG
• RPM包名：pgsql_gzip_$v*
• RPM版本：1.0.0
• RPM依赖：无
• DEB仓库：PIGSTY
• DEB包名：postgresql-$v-gzip
• DEB版本：1.0.1
• DEB依赖：无

最新版本

扩展安装

使用 pig 命令行工具安装 pg_gzip 扩展：

pig ext install pg_gzip; # 扩展名称
pig ext install gzip; # 标准包名

使用 Pigsty剧本 安装 pg_gzip 扩展：

./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_gzip"]}' # -l <集群名>

从 YUM仓库 手工安装 pg_gzip RPM 包：

dnf install pgsql_gzip_17*;
dnf install pgsql_gzip_16*;
dnf install pgsql_gzip_15*;
dnf install pgsql_gzip_14*;
dnf install pgsql_gzip_13*;

从 APT仓库 手工安装 pg_gzip DEB 包：

apt install postgresql-17-gzip;
apt install postgresql-16-gzip;
apt install postgresql-15-gzip;
apt install postgresql-14-gzip;
apt install postgresql-13-gzip;

使用以下 SQL 命令在已经安装此扩展插件的 PG 集群上 启用 gzip 扩展：

CREATE EXTENSION gzip;

使用方法

Sometimes you just need to compress your bytea object before you return it to the client.

Sometimes you receive a compressed bytea from the client, and you have to uncompress it before you can work with it.

This extension is for that.

This extension is not for storage compression. PostgreSQL already does tuple compression on the fly if your tuple gets large enough, manually pre-compressing your data using this function won’t make things smaller.

• gzip(uncompressed BYTEA, [compression_level INTEGER]) returns BYTEA
• gzip(uncompressed TEXT, [compression_level INTEGER]) returns BYTEA
• gunzip(compressed BYTEA) returns BYTEA

Examples

> SELECT gzip('this is my this is my this is my this is my text');

                                   gzip
--------------------------------------------------------------------------
 \x1f8b08000000000000132bc9c82c5600a2dc4a851282ccd48a12002e7a22ff30000000

Wait, what, the compressed output is longer?!? No, it only looks that way, because in hex every byte is represented with two hex digits. The original string looks like this in hex:

> SELECT 'this is my this is my this is my this is my text'::bytea;

                                               bytea
----------------------------------------------------------------------------------------------------
 \x74686973206973206d792074686973206973206d792074686973206973206d792074686973206973206d792074657874

For really long, repetitive things, compression naturally works like a charm:

> SELECT gzip(repeat('this is my ', 100));

                                               bytea
----------------------------------------------------------------------------------------------------
 \x1f8b08000000000000132bc9c82c5600a2dc4a859251e628739439ca24970900d1341c5c4c040000

To convert a bytea back into an equivalent text you must use the encode() function with the escape encoding.

> SELECT encode('test text'::bytea, 'escape');
   encode
-----------
 test text

> SELECT encode(gunzip(gzip('this text has been compressed and then decompressed')), 'escape')

                      encode
-----------------------------------------------------
 this text has been compressed and then decompressed