PostgreSQL 14中的lz4压缩无法正常工作？

Question

我安装了PostgreSQL 14，并希望压缩一些数据以节省磁盘空间。这些数据是音频文件（1kB - 5MB），转换成base64字符串格式。我创建了3个表：

CREATE TABLE t_uncompressed (
    file_name VARCHAR(50) NOT NULL PRIMARY KEY,
    file_size BIGINT,
    raw_data       TEXT
);


CREATE TABLE t_lz4 (
    file_name VARCHAR(50) NOT NULL PRIMARY KEY,
    file_size BIGINT,
    raw_data       TEXT COMPRESSION lz4
);


CREATE TABLE t_pglz (
    file_name VARCHAR(50) NOT NULL PRIMARY KEY,
    file_size BIGINT,
    raw_data       TEXT COMPRESSION pglz
);

然后我将我的数据插入到这些表中。 我检查了数据的压缩情况，发现有403行使用了lz4进行压缩，只有一行用pglz进行压缩。

SELECT count(*) total, 
count(*) FILTER ( WHERE pg_column_compression(raw_data) NOTNULL) compressed, 
'lz4' compr_type
    FROM t_lz4
UNION
SELECT count(*) total, 
count(*) FILTER ( WHERE pg_column_compression(raw_data) NOTNULL) compressed, 
'pglz' compr_type
    FROM t_pglz;

 total | compressed | compr_type 
-------+------------+------------
   738 |          1 | pglz
   738 |        403 | lz4
(2 rows)

对我来说看起来奇怪的是，三个表的大小都相同！好的，关于未压缩表和pglz表，我可以理解，但为什么_lz4表的大小也相同呢？

我通过以下方式获取表格大小：

SELECT schemaname || '.' || tablename full_tname
    , pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')) total_usage
    , pg_size_pretty(pg_relation_size('"' || schemaname || '"."' || tablename || '"')) data_size
    , pg_size_pretty((pg_total_relation_size('"' || schemaname || '"."' || tablename || '"') -
                      pg_relation_size('"' || schemaname || '"."' || tablename || '"') -
                      pg_indexes_size('"' || schemaname || '"."' || tablename || '"')))
    AS TOAST
    FROM pg_catalog.pg_tables
    WHERE tablename ~ 't_';

  full_tname         | total_usage | data_size | toast  
---------------------+-------------+-----------+--------
 t_lz4               | 338 MB      | 80 kB     | 338 MB
 t_pglz              | 338 MB      | 80 kB     | 338 MB
 t_uncompressed      | 338 MB      | 80 kB     | 338 MB
 (3 rows)

数据库中使用的默认压缩是pglz，也许这个信息很重要...

postgres=# SHOW default_toast_compression ;
 default_toast_compression 
---------------------------
 pglz
(1 row)

- Frankie Drake

---

当输入数据（通常为文本）具有重复时，压缩是有效的。压缩音频/视频大小的唯一方法是使用有损（音频/视频）压缩算法。最好将这些数据存储在普通文件系统中，并在DB记录中存储它们的路径。通常，当您压缩随机二进制数据（即音频/视频/二进制文件到文本压缩算法）时，文件大小会膨胀一点并浪费时间（压缩/解压缩），而不会为过程添加任何有意义的价值。 - जलजनक

Answer 1

你没有说明是什么类型的音频文件。大多数音频文件格式已经被压缩过了，无法再进行压缩。将它们进行base64编码确实可以使它们稍微压缩一些，以消除编码所引起的膨胀。没有Huffman编码的LZ在压缩此类膨胀时效果特别差。这就是你通过查看表格大小而看到的：压缩是徒劳的。

而且PostgreSQL使用的压缩实现会检测徒劳性，并放弃对那些似乎不太可压缩的东西进行压缩。这就是你在pg_column_compression()中看到的。

- jjanes