如何在PostgreSQL 15中通过SQL触发器构建实时审计追踪链？-SQL-PHP中文网

审计触发器必须用AFTER ROW，因其能同时获取OLD、NEW、TG_OP等完整上下文；审计表应采用JSONB+UUID设计并动态触发，且需配置定期清理策略。

如何在postgresql 15中通过sql触发器构建实时审计追踪链？

审计触发器必须用 AFTER ROW，不能用 BEFORE 或 STATEMENT 级

BEFORE 触发器看不到 NEW 的最终值（比如序列生成的主键还没赋值），STATEMENT 级触发器拿不到单行变更细节——而审计的核心就是“谁在何时改了哪一行的什么字段”。AFTER ROW 是唯一能同时拿到 OLD、NEW、TG_OP、TG_TABLE_NAME 和客户端上下文的组合。

常见错误是写成 FOR EACH STATEMENT，结果日志里只有一条记录，却对应了 100 行 UPDATE；或者用 BEFORE 导致 NEW.id 为 NULL，审计表里 record_id 字段全空。

必须声明为 AFTER INSERT OR UPDATE OR DELETE ON target_table FOR EACH ROW
避免在触发器内做复杂计算或远程调用，否则拖慢主事务
如果要记录 IP，用 inet_client_addr()；但注意：连接池（如 PgBouncer）下该函数返回的是池地址而非真实客户端

审计日志表设计要用 JSONB + UUID，别学老教程建一堆固定字段

传统方案为每张业务表建一张审计表（users_audit、orders_audit），字段一一对应。问题在于：业务表加个字段就得同步改审计表，迁移成本高；不同表结构差异大，查询归档难统一。

PostgreSQL 15 原生支持 JSONB，直接把整行数据快照存进去，再配一个 record_id UUID 作为逻辑主键——更新时保持不变，就能串起某条记录的完整生命周期。

建表语句示例：CREATE TABLE audit.record_version (id BIGSERIAL PRIMARY KEY, record_id UUID NOT NULL, table_name TEXT NOT NULL, op_type TEXT NOT NULL, old_data JSONB, new_data JSONB, changed_at TIMESTAMPTZ DEFAULT NOW(), client_ip INET)
old_data 和 new_data 在 INSERT/DELETE 时只填其一，UPDATE 时都填
不建议在 audit.record_version 上建太多索引，重点加：CREATE INDEX ON audit.record_version (table_name, record_id) 和 (changed_at)

触发器函数里别硬编码表名或字段，用 TG_* 变量动态取

很多人写触发器函数时直接拼 SQL：INSERT INTO users_audit (...) VALUES (NEW.id, NEW.name, ...)。这导致函数无法复用，一张新表就得拷一份函数，后期维护爆炸。

正确做法是用 TG_TABLE_NAME、TG_OP 和 ROW_TO_JSON(NEW) / ROW_TO_JSON(OLD) 动态构造日志内容。这样同一个函数可挂给任意表，只要保证 record_id 字段存在（或用 gen_random_uuid() 自动生成）。

关键代码片段：INSERT INTO audit.record_version (record_id, table_name, op_type, old_data, new_data, client_ip) VALUES (COALESCE(NEW.record_id, gen_random_uuid()), TG_TABLE_NAME, TG_OP, CASE WHEN TG_OP = 'UPDATE' THEN ROW_TO_JSON(OLD) END, CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN ROW_TO_JSON(NEW) END, inet_client_addr())
依赖 pgcrypto 扩展生成 UUID，需提前执行：CREATE EXTENSION IF NOT EXISTS pgcrypto
注意：若业务表无 record_id 字段，COALESCE(NEW.record_id, ...) 会报错，应改用 gen_random_uuid() 无条件生成

audit.record_version 表必须配定期清理策略，否则半年就撑爆磁盘

审计日志是典型的“写多查少”场景，且多数合规要求只保留 180 天或 365 天。但没人会手动删，线上库一旦没设自动归档，audit.record_version 很快变成最大表，VACUUM 都跟不上。

别用 DELETE FROM audit.record_version WHERE changed_at —— 这会锁表、产生巨量 WAL、阻塞其他写入。正确方式是分区表 + <code>TRUNCATE 或按月继承表 + DROP TABLE。

PostgreSQL 15 支持原生声明式范围分区，推荐按 changed_at 月度分区：CREATE TABLE audit.record_version_y2026m10 PARTITION OF audit.record_version FOR VALUES FROM ('2026-10-01') TO ('2026-11-01')
清理脚本用 TRUNCATE audit.record_version_y2025m03，毫秒级完成，无锁无 WAL
务必在 audit schema 上禁用 public 访问权限：REVOKE ALL ON SCHEMA audit FROM PUBLIC，防止审计数据被误删或导出

实际落地最难的不是写触发器，而是让所有业务表都带上 record_id UUID DEFAULT gen_random_uuid() 字段——没有这个锚点，跨时间点追溯单行变更就失去依据。很多团队卡在这一步，最后退化成只记操作类型和时间，丢了最关键的“链”。