PostgreSQL的表压缩

PostgreSQL提供了在表或列级别进行数据压缩的功能，以帮助减少存储空间和提高I/O性能。具体来说，PostgreSQL 14及以后的版本引入了对表级和列级压缩的支持。

表压缩

The COMPRESSION clause sets the compression method for the column. Compression is supported only for variable-width data types, and is used only when the column’s storage mode is main or extended. (See ALTER TABLE for information on column storage modes.) Setting this property for a partitioned table has no direct effect, because such tables have no storage of their own, but the configured value will be inherited by newly-created partitions. The supported compression methods are pglz and lz4. (lz4 is available only if --with-lz4 was used when building PostgreSQL.) In addition, compression_method can be default to explicitly specify the default behavior, which is to consult the default_toast_compression setting at the time of data insertion to determine the method to use.

COMPRESSION子句设置列的压缩方法。压缩仅支持可变宽度数据类型，并且仅在列的存储模式为主或扩展时使用。（有关列存储模式的信息，请参阅ALTER TABLE。）为分区表设置此属性没有直接影响，因为这些表没有自己的存储，但配置的值将由新创建的分区继承。支持的压缩方法是pglz和lz4。（只有在构建PostgreSQL时使用了-with-lz4时，lz4才可用。）此外，compression_method可以默认为显式指定默认行为，即在插入数据时参考default_toast_pression设置以确定要使用的方法。

支持的压缩方法

PostgreSQL 支持以下几种压缩方法：

lz4：基于LZ4算法的快速压缩，适合大多数使用场景。
pglz：基于PostgreSQL原生压缩算法的压缩方法。

创建表时定义压缩存储

在创建表时，可以为特定的列指定压缩方法。例如：

CREATE TABLE yewu1.t2 (id serial PRIMARY KEY,data_1 text COMPRESSION pglz,data_2 text COMPRESSION pglz
);

修改现有表的压缩存储

可以使用 ALTER TABLE 语句来更改现有列的压缩方法：

ALTER TABLE test_tableALTER COLUMN data_1 SET COMPRESSION pglz;

表级压缩的使用场景

使用表和列级压缩可以带来多种好处，但也有一些需要注意的事项：

优点

减少存储空间：压缩会显著减少数据文件的大小，从而节省存储成本。
提高查询性能：特别是在I/O密集的查询中，压缩可以减少磁盘读取的数据量，从而提高查询性能。

注意事项

压缩开销：虽然压缩可以减少存储和提高读取速度，但同样也会带来CPU的开销。因此，适合在读多写少的场景使记指定不同列的压缩方法。

可以结合对特定列的性能需求，指定不同列的压缩方法。

ALTER TABLE test_tableALTER COLUMN data_1 SET COMPRESSION pglz;

检查表级压缩状态

要确认某个表或列是否启用了压缩，可以查询 pg_attribute 视图：

SELECT attname, attcompression
FROM pg_attribute
WHERE attrelid = 'yewu1.t2'::regclass;

这将显示表中各列的压缩方法：

white=# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid = 'yewu1.t2'::regclass;attname  | attcompression 
----------+----------------tableoid | cmax     | xmax     | cmin     | xmin     | ctid     | id       | data_1   | pdata_2   | p
(9 rows)

通过这些信息，你可以了解到各列的压缩状态以及所使用的压缩方法。