如何为PostgreSQL日志表创建支持任意数据类型的列？

针对你需要存储任意数据类型变更值的需求，PostgreSQL提供了多种可行方案，以下是详细对比和推荐：

1. PostgreSQL 16+：variant 类型（最优新方案） #

PostgreSQL 16引入的variant是专门为存储任意SQL数据类型设计的原生类型，会完整保留值的类型元数据。

• 优点：
  • 原生支持所有PostgreSQL数据类型（包括自定义类型、数组、复合类型），无需手动序列化/反序列化
  • 性能优于jsonb，避免JSON格式转换的开销
  • 可直接对存储值进行原生类型操作，比如new_val::numeric + 1
• 缺点：仅在PostgreSQL 16及以上版本可用
• 表结构示例：

CREATE TABLE logging.zaak_history (
  event_id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tstamp                    timestamp       DEFAULT NOW(),
  schemaname                text,
  tabname                   text,
  columnname                text,
  operation                 text,
  who                       text            DEFAULT current_user,
  new_val                   variant,
  old_val                   variant
);

2. jsonb（通用兼容方案） #

这是你考虑的方案，也是当前跨版本兼容的主流选择：

• 优点：
  • 兼容PostgreSQL 9.4+所有版本
  • 支持存储任意复杂结构的数据
  • 可创建GIN/GIST索引，实现高效的类型过滤查询
  • 内置丰富的JSON函数，方便处理存储值
• 缺点：
  • 读写时需要序列化/反序列化JSON，性能略低于variant
  • 转换回原生类型需要显式操作
• 表结构示例：

CREATE TABLE logging.zaak_history (
  event_id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tstamp                    timestamp       DEFAULT NOW(),
  schemaname                text,
  tabname                   text,
  columnname                text,
  operation                 text,
  who                       text            DEFAULT current_user,
  new_val                   jsonb,
  old_val                   jsonb
);
-- 插入时直接转换为jsonb
INSERT INTO logging.zaak_history (schemaname, tabname, columnname, operation, new_val, old_val)
VALUES ('public', 'users', 'age', 'UPDATE', to_jsonb(30), to_jsonb(29));

3. 多类型列存储（极致性能方案） #

如果你的变更日志仅涉及有限几种数据类型，可以为每种类型单独设置字段：

• 优点：
  • 完全使用原生数据类型，性能最优，支持原生索引
  • 避免类型转换开销，查询无需额外处理
• 缺点：
  • 表结构复杂，新增数据类型时需修改表结构
  • 触发器逻辑需要判断字段类型并填充对应列，维护成本高
• 表结构示例：

CREATE TABLE logging.zaak_history (
  event_id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tstamp                    timestamp       DEFAULT NOW(),
  schemaname                text,
  tabname                   text,
  columnname                text,
  operation                 text,
  who                       text            DEFAULT current_user,
  new_text                  text,
  new_bool                  boolean,
  new_numeric               numeric,
  old_text                  text,
  old_bool                  boolean,
  old_numeric               numeric
);

4. text 类型（极简方案） #

将所有值转换为text存储，查询时再转换回原类型：

• 优点：表结构简单，兼容所有版本，存储开销小
• 缺点：
  • 丢失原生类型的约束和校验，可能存储非法值
  • 查询时需手动转换类型，易出错
  • 无法利用原生类型的索引优化查询
• 表结构示例：

CREATE TABLE logging.zaak_history (
  event_id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tstamp                    timestamp       DEFAULT NOW(),
  schemaname                text,
  tabname                   text,
  columnname                text,
  operation                 text,
  who                       text            DEFAULT current_user,
  new_val                   text,
  old_val                   text
);
-- 插入时转成text
INSERT INTO logging.zaak_history (schemaname, tabname, columnname, operation, new_val, old_val)
VALUES ('public', 'users', 'age', 'UPDATE', '30', '29');
-- 查询时转回原类型
SELECT new_val::numeric FROM logging.zaak_history WHERE columnname = 'age';

• 若使用PostgreSQL 16+，优先选择**variant类型**，兼顾灵活性与性能；
• 若需要兼容旧版本，**jsonb**是最优选择，平衡了灵活性和可维护性；
• 若变更涉及的类型极少且追求极致性能，可考虑多类型列存储；
• 不推荐text类型，除非场景极简单且对类型校验、性能要求极低。

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