PosrgreSQL 学习计划——了解 Toast 的 LZ4 压缩算法

lz4 是什么？

PostgreSQL 14 版本支持 TOAST 使用 LZ4 压缩算法，相较于 PGLZ，LZ4 算法速度更快，且压缩率也不输 PGLZ，从节省数据库计算资源角度考虑，推荐使用 LZ4 算法。

准备环境

创建表

# 创建 t2 表，压缩使用 pglz
CREATE TABLE T2(
    ID INT,
    C1 TEXT COMPRESSION pglz
);

# 创建 t3 表，压缩使用 lz4
CREATE TABLE T3(
    ID INT,
    C1 TEXT COMPRESSION lz4
);

读写测试

开启耗时记录

tmp01=# \timing
Timing is on.

写入耗时

tmp01=# INSERT INTO T2 SELECT i, repeat(i::varchar||'kkk',1000) FROM generate_series(1,100000) a(i);
INSERT 0 100000
Time: 2003.361 ms (00:02.003)

tmp01=# INSERT INTO T3 SELECT i, repeat(i::varchar||'kkk',1000) FROM generate_series(1,100000) a(i);
INSERT 0 100000
Time: 563.887 ms

写入 100000 条数据，相较于 PGLZ，LZ4 写入耗时减少 71%

压缩状态

可以看到查询其中的一条记录，t2 表的 c1 字段数据使用 PGLZ 压缩，t3 表 c1 字段数据使用 LZ4 算法压缩。

tmp01=# SELECT ID, pg_column_compression ( c1 ) FROM t2 LIMIT 1;
 id | pg_column_compression
----+-----------------------
  1 | pglz
(1 row)

tmp01=# SELECT ID, pg_column_compression ( c1 ) FROM t3 LIMIT 1;
 id | pg_column_compression
----+-----------------------
  1 | lz4
(1 row)

存储大小

SELECT
    relname,
    relpages,
    relpages * 8 / 1024 size_MB,
    reltuples,
    reltoastrelid,
    pg_relation_filepath ( reltoastrelid ) 
FROM
    pg_class 
WHERE
    relname IN ( 't2', 't3' );

输出

 relname | relpages | size_mb | reltuples | reltoastrelid | pg_relation_filepath
---------+----------+---------+-----------+---------------+----------------------
 t2      |     1799 |      14 |    100000 |         40970 | base/16385/40970
 t3      |     1126 |       8 |    100000 |         40975 | base/16385/40975

从当前测试数据看，压缩率 LZ4 也更有优势。（不代表真实业务场景的数据）

更新数据

tmp01=# UPDATE t2 SET c1 =  REPEAT('abc',1000);
UPDATE 100000
Time: 1026.617 ms (00:01.027)

tmp01=# UPDATE t3 SET c1 =  REPEAT('abc',1000);
UPDATE 100000
Time: 456.406 ms

除去算法速度快，本例的测试数据压缩后的数据更小，也会使读写速度更快一些

随机字符串

如果我们使用完全随机的字符串进行测试，会发现 PostgreSQL 不会压缩数据

-- 创建 random_string 函数
CREATE 
    OR REPLACE FUNCTION random_string ( INT ) RETURNS TEXT AS $$ SELECT
    array_to_string(
        ARRAY ( SELECT chr( ascii( 'B' ) + round( random( ) * 25 ) :: INTEGER ) FROM generate_series ( 1, $1 ) ),
        '' 
    ) $$ LANGUAGE SQL;


-- 插入数据
INSERT INTO T4 SELECT i, random_string(1000) FROM generate_series(1,100000) a(i);

通过 SQL 语句查询其中一条记录（t4 表跟 t2 表结构一样）

tmp01=# SELECT ID, pg_column_compression ( c1 ) FROM t4 LIMIT 1;
 id | pg_column_compression
----+-----------------------
  1 |
(1 row)

发现其没有应用任何压缩算法（pg_column_compression 函数返回值为空字符串，代表数据未被压缩，按原始值进行的存储）。

这是因为数据压缩率不足时，PostgreSQL 会存储原始数据。

查询字段的存储类型和压缩策略

除了借助 pg_column_compression 函数，也可以直接查询到字段存储策略和压缩类型

SELECT
    attrelid,
    attname,
    attstorage,
    attcompression
FROM
    pg_attribute 
WHERE
    attrelid IN ( 't2' :: regclass, 't3' :: regclass ) 
    AND attname = 'c1';

输出

attrelid    attname attstorage  attcompression
40967   c1  x   p
40972   c1  x   l

策略对应关系，t2 和 t3 表在创建时为指定存储策略，所以使用的默认值 x，即 extended，表示 “可以被压缩，也可以存储到 Toast 表”，压缩算法 p 指代 PGLZ，l 代表 LZ4

p : plain
x : extended
e : external
m : main

注意 ⚠️：PostgreSQL 对变长数据才提供这些选项，像 int 等基础类型，是不支持修改为行外存储的。

修改已存在列为 LZ4 压缩算法

ALTER TABLE t4 ALTER COLUMN c1 SET COMPRESSION lz4;

如果在修改算法之前插入过记录，之后修改算法，之前记录的压缩方式不会变化，可以使用 pg_column_compression 函数查看。

东东's Blog