透明页压缩TPC

前提条件

PostgreSQL 14或以上版本均支持此功能，如提示不支持，请升级内核小版本，具体操作，请参见升级内核小版本。

什么是透明页压缩

透明页压缩 TPC（Transparent Page Compression）中，页压缩指对Buffer Pool中的页执行实时的I/O压缩和解压缩，透明是指使用过程中无感知，数据在写入磁盘时自动压缩，数据在从磁盘读取时自动解压缩。

透明页压缩的主要目标是减少存储空间的使用，并提高数据库的读性能。通过压缩数据，可以降低磁盘I/O，减少存储，提高缓存效率，并加快数据传输速度。

应用场景

CPU使用率在50%以下，IOPS或IO吞吐容易达到瓶颈的场景下，推荐使用透明页压缩功能。

透明页压缩的优势

• 存储成本平均可节约50%。

• IO使用平均可节约50%。

• TPS在读场景会有提高，部分IO打满的读场景最高可提升100%。

影响

• 该功能会使CPU使用量上涨，压缩的CPU上涨约260%，解压的CPU上涨约40%。

• 在写场景TPS会有下降。

• 在与透明数据加密 TDE同时开启时，会影响压缩效果。

• 对TOAST数据的压缩效果不佳。

操作步骤

1. 透明页压缩功能依赖表空间，需要创建压缩表空间。

   CREATE TABLESPACE rds_compress LOCATION '/data/postgresql/rds_compress' WITH(COMPRESSION='zstd');

   重要

   本步骤创建的表空间名称、表空间路径以及压缩算法请勿修改。

2. 创建压缩表或索引。

   • 在创建或修改表、索引时指定压缩表空间，使用透明页压缩功能。

     • 表压缩

       -- 创建表时，指定压缩表空间，即可使用透明页压缩功能
       CREATE TABLE <tablename> ... TABLESPACE rds_compress;
       
       -- 将现有表的表空间修改为压缩表空间
       ALTER TABLE <tablename> SET TABLESPACE rds_compress;

     • 索引压缩

       -- 创建索引时，指定压缩表空间，即可使用透明页压缩功能
       CREATE INDEX <indexname> ... TABLESPACE rds_compress;
       
       -- 将现有索引的表空间修改为压缩表空间
       ALTER INDEX <indexname> SET TABLESPACE rds_compress;

   • 修改默认表空间为压缩表空间，后续创建的表和索引，将默认使用透明页压缩功能。

     -- 设置默认表空间为压缩表空间
     SET default_tablespace TO 'rds_compress';
     
     -- 创建表、索引时无需指定表空间，默认使用透明页压缩功能。
     CREATE TABLE <tablename> ...;
     CREATE INDEX <indexname> ...;

相关查询

• 查询表、索引是否创建在压缩表空间下：

  1. 在psql命令行中输入\d+ <表名>查询指定表的详细信息。

     如果表和索引都创建在压缩表空间下，则表示可以使用透明页压缩功能。以下为sysbench表的结构查询结果。

                                                                Table "public.sbtest1"
      Column |      Type      | Collation | Nullable |               Default               | Storage  | Compression | Stats target | Description 
     --------+----------------+-----------+----------+-------------------------------------+----------+-------------+--------------+-------------
      id     | integer        |           | not null | nextval('sbtest1_id_seq'::regclass) | plain    |             |              | 
      k      | integer        |           | not null | 0                                   | plain    |             |              | 
      c      | character(120) |           | not null | ''::bpchar                          | extended |             |              | 
      pad    | character(60)  |           | not null | ''::bpchar                          | extended |             |              | 
     Indexes:
         "sbtest1_pkey" PRIMARY KEY, btree (id), tablespace "rds_compress"
         "k_1" btree (k), tablespace "rds_compress"
     Tablespace: "rds_compress"
     Access method: heap

• 验证数据是否压缩：

  您可以使用pg_database_size、pg_tablespace_size、pg_relation_size、pg_table_size、pg_index_size、pg_total_relation_size等函数可以查看数据的实时大小。

  说明

  您可以利用sysbench测试工具分别向压缩表和非压缩表中插入相同数据量的测试数据，观察压缩表的磁盘占用情况，压缩表的大小约为非压缩表大小的50%。

• 计算指定表的压缩比：

  SELECT pg_relation_size('<tablename>')::float / (relpages * 8192) from pg_class WHERE relname = '<tablename>';

  说明

  上述语句中查询pg_class系统目录，使用pg_relation_size('<tablename>') 函数获取指定表的实际磁盘占用空间大小（以字节为单位）并转换为浮点数，通过除以 (relpages * 8192)计算得出每个页面的平均大小（以字节为单位）。其中relpages是表的总页面数，8192是PostgreSQL默认的页面大小（通常为8KB），最终，查询的结果表示指定表的平均压缩比率。值越小，表示压缩比越高。

常见问题

Q：开启透明页压缩TPC后，数据库工具（pg_dump，pg_basebackup）还能正常使用吗？

A：pg_dump和pg_basebackup工具可以正常使用，其中如果要将pg_basebackup生成的备份恢复到另一个数据库时，目标数据库也需要开启透明页压缩。