PostgreSQL中TEXT[]数组列的高效存储与空间优化方案咨询

现有方案可行性评估 #

自定义查找表替换TEXT[]为INT[] #

这个方案是收益最高的可行方向，你担心的数组外键问题完全可以通过业务逻辑规避，不需要复杂的触发器或存储过程：

• 写入时先将数组内的字符串去重，批量插入查找表，使用ON CONFLICT (str_col) DO NOTHING避免重复写入，再批量查询得到字符串对应的整数ID，映射为INT[]存入原表即可
• 查找表设计为仅新增不修改、不删除，完全不会出现INT[]内ID找不到对应字符串的一致性问题，不需要额外的约束校验
• 空间压缩比可达5:1以上：单个数组100个元素的场景下，原TEXT[]单条占用约2500字节，替换为INT4数组后仅需400字节，加上查找表的存储开销，整体压缩比也能稳定在5倍左右
• 原有的数组唯一约束可以直接平移到INT[]字段，逻辑完全一致，不需要做额外调整
• 若不想修改业务查询代码，可以新增一个STORED生成列，自动将INT[]转换为TEXT[]返回，业务侧完全感知不到底层存储变更

ZSON扩展方案 #

这个方案理论上可以实现透明压缩，但性价比不高，不推荐优先选择：

• 需要将原TEXT[]字段转换为JSONB格式存储，原有数组查询逻辑需要适配JSONB语法，存在业务改造成本
• ZSON属于第三方非官方扩展，PostgreSQL大版本升级时需要等待扩展适配，长期维护成本较高
• 全局字典的自定义调优空间有限，压缩率不如自定义查找表方案可控

更适配PG14的长期方案 #

原生LZ4压缩（零改造成本） #

PostgreSQL 14已经内置支持LZ4压缩，不需要任何扩展或业务改造，直接调整字段压缩配置即可：

-- 给array_col字段设置LZ4压缩
ALTER TABLE your_table ALTER COLUMN array_col SET COMPRESSION lz4;
-- 全表重写让压缩生效，可根据业务情况选择低峰操作
VACUUM FULL VERBOSE your_table;

该方案对业务零侵入，压缩率可稳定在3:1左右，适合短期快速降低存储占用。

半透明映射方案（高压缩比+低改造成本） #

如果想要更高的压缩比，又不想大幅调整业务逻辑，可以采用查找表+生成列的组合方案：

-- 新建字符串映射表
CREATE TABLE string_map (
    str_id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    str_val TEXT UNIQUE NOT NULL
);
-- 原表新增INT数组字段，保留唯一约束
ALTER TABLE your_table ADD COLUMN array_col_int INT[] UNIQUE NOT NULL;
-- 新增STORED生成列，自动映射为原TEXT[]格式，业务查询直接使用该字段即可
ALTER TABLE your_table ADD COLUMN array_col TEXT[] GENERATED ALWAYS AS (
    ARRAY(SELECT s.str_val FROM unnest(array_col_int) WITH ORDINALITY AS u(id, pos)
          JOIN string_map s ON s.str_id = u.id ORDER BY u.pos)
) STORED;

该方案仅需要修改写入逻辑，查询逻辑完全不需要调整，长期扩展性好，压缩率和自定义查找表方案一致。

选型建议 #

• 零改造优先选原生LZ4压缩，操作成本最低，见效最快
• 追求最高压缩比优先选自定义查找表+生成列方案，长期维护成本低，一致性风险可控
• 不推荐ZSON扩展方案，综合性价比低于上述两类方案

内容的提问来源于stack exchange，提问作者johngreen283