在PolarDB 的非官方的数据库课程中，其中有一节提到了PolarDB for PostgreSQL 可以进行数据压缩，压缩比率非常高100%的数据经过压缩后，只剩下40%。在文章的评论区，我记得有一个同志问，这个会不会影响PostgreSQL的运行效率。

是的，其实不光他担心，传统DBA也担心这个问题，在POSTGRESQL中我们可以通过很多的方法对数据进行压缩，但这些方法都有一个问题，对CPU的消耗。所以提出这个问题的同志的担心是有必要的，但这里需要说明PolarDB for PostgreSQL的压缩是硬件压缩，也就是磁盘本身有压缩的功能，基于存算分离的设计和特性，这里磁盘的压缩和计算CPU是无关的。

但我们必须要经过测试才能证明这点，这里我们准备了两个数据库都是PolarDB for PostgreSQL 14,其中一个我们开启压缩，一个不开启压缩，其他的配置和功能完全一致。

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#数据库

主机是个人账号购买，消费

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#postgresql_02

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#数据库_03

压测很简单还是通过撰写一个测试的脚本来完成。

-- 清理旧表
DROP TABLE IF EXISTS table3;
DROP TABLE IF EXISTS table2;
DROP TABLE IF EXISTS table1;

-- 创建表结构
CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name TEXT,
    value INT
);

CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    table1_id INT REFERENCES table1(id),
    description TEXT
);

CREATE TABLE table3 (
    id SERIAL PRIMARY KEY,
    table2_id INT REFERENCES table2(id),
    tag TEXT
);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
DO $$
DECLARE
    i INT := 1;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    RAISE NOTICE '开始插入 table1';
    start_time := clock_timestamp();
    WHILE i <= 1000000 LOOP
        INSERT INTO table1(name, value) VALUES ('name_' || i, i);
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table1 插入耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);

    i := 1;
    RAISE NOTICE '开始插入 table2';
    start_time := clock_timestamp();
    WHILE i <= 1000000 LOOP
        INSERT INTO table2(table1_id, description) VALUES ((random()*999999 + 1)::INT, 'desc_' || i);
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table2 插入耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);

    i := 1;
    RAISE NOTICE '开始插入 table3';
    start_time := clock_timestamp();
    WHILE i <= 1000000 LOOP
        INSERT INTO table3(table2_id, tag) VALUES ((random()*999999 + 1)::INT, 'tag_' || i);
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table3 插入耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);
END
$$;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
DO $$
DECLARE
    i INT := 1;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    RAISE NOTICE '开始更新 table1';
    start_time := clock_timestamp();
    WHILE i <= 100000 LOOP
        UPDATE table1 SET value = value + 1 WHERE id = (random()*999999 + 1)::INT;
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table1 更新耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);

    i := 1;
    RAISE NOTICE '开始更新 table2';
    start_time := clock_timestamp();
    WHILE i <= 100000 LOOP
        UPDATE table2 SET description = description || '_u' WHERE id = (random()*999999 + 1)::INT;
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table2 更新耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);

    i := 1;
    RAISE NOTICE '开始更新 table3';
    start_time := clock_timestamp();
    WHILE i <= 100000 LOOP
        UPDATE table3 SET tag = tag || '_v' WHERE id = (random()*999999 + 1)::INT;
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table3 更新耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);
END
$$;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
DO $$
DECLARE
    i INT := 1;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    dummy TEXT;
BEGIN
    RAISE NOTICE '开始单表查询 table1';
    start_time := clock_timestamp();
    WHILE i <= 10000 LOOP
        SELECT name INTO dummy FROM table1 WHERE id = (random()*999999 + 1)::INT;
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table1 单表查询耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);

    i := 1;
    RAISE NOTICE '开始单表查询 table2';
    start_time := clock_timestamp();
    WHILE i <= 10000 LOOP
        SELECT description INTO dummy FROM table2 WHERE id = (random()*999999 + 1)::INT;
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table2 单表查询耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);

    i := 1;
    RAISE NOTICE '开始单表查询 table3';
    start_time := clock_timestamp();
    WHILE i <= 10000 LOOP
        SELECT tag INTO dummy FROM table3 WHERE id = (random()*999999 + 1)::INT;
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE 'table3 单表查询耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);

    i := 1;
    RAISE NOTICE '开始多表联查';
    start_time := clock_timestamp();
    WHILE i <= 10000 LOOP
        SELECT t1.name, t2.description, t3.tag
        INTO dummy, dummy, dummy
        FROM table1 t1
        JOIN table2 t2 ON t1.id = t2.table1_id
        JOIN table3 t3 ON t2.id = t3.table2_id
        WHERE t1.id = (random()*999999 + 1)::INT
        LIMIT 1;
        i := i + 1;
    END LOOP;
    end_time := clock_timestamp();
    RAISE NOTICE '多表联查耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);
END
$$;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.

下面我将执行的结果进行截图，方便大家比对

1 打开压缩的

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#postgresql_04

PLS4打开压缩

2 关闭压缩的

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_MySQL_05

PLS4关闭压缩

表名

压缩 (s)

不压缩 (s)

差异 (s)

说明

table1

5.172172

5.529172

+0.357

微小浮动

table2

30.73330

30.63361

-0.100

稳定

table3

30.28279

31.89955

+1.617

有波动

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#postgresql_06

压测的数据库性能

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#sql_07

不压测的数据库性能

数据插入，在数据插入的结果中明显开启压缩的情况CPU消耗要更平稳，而不开启压缩的PolarDB for PostgreSQL CPU突发的消耗会更多。

第二个测试，用大量的UPDATE对两个数据库进行同样的压力测试。

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#sql_08

同样的代码

在测试中出现了一个与大部分人想法相反的情况，压缩的PolarDB for PostgreSQL 要比不压缩的IOPS低，也就是执行同样的指令尤其在数据UPDATE的操作中，压缩的PolarDB for PostgreSQL IOPS要更低。

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#数据库_09

不压缩的 polardb for PG

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#sql_10

压缩的polardb for postgresql

3 最后我们通过多表的查询来对此次的测试压缩和不压缩的性能差别进行一个总结，我们执行同样的查询压测脚本在两个服务器上，同样的数据，同样的查询我们看看相关得到的结果

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#postgresql_11

查询脚本

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#数据库_12

不压缩查询的速度

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#postgresql_13

压缩查询的速度

📊 PostgreSQL 查询耗时对比(单位：秒)

查询类型

表名

压缩后耗时

未压缩耗时

单表查询

table1

12.307233

20.791665

单表查询

table2

37.118749

44.701972

单表查询

table3

37.540800

50.294852

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_PostgreSQL_14

压缩的测试性能图

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_PostgreSQL_15

不压缩测试性能图

所以那个问PolarDB for PostgreSQL压缩后会性能变差吗，NO NO NO ，经过上面的测试给出的结果是，一定要压缩，压缩后系统又稳定，SQL运行又快，So good ! 

数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？_#数据库_16