150行SQL实现Postgres审计
内容提要
PostgreSQL的审计系统能够跟踪表内容的变化，适用于历史分析。通过创建审计表和触发器，记录每次插入、更新和删除操作，使用JSONB格式存储数据，便于查询和管理。该审计解决方案旨在低维护、易用且快速查询，最终实现高效的审计记录和查询功能。
延伸解读
审计表设计的权衡
传统审计表会镜像被审计表的结构，但源表结构变更时需同步修改审计表，维护成本高。本文采用JSONB存储记录数据，使审计表结构固定，无需随源表变化，且能集中存储多张表的审计历史。代价是查询JSONB内部字段效率较低，需额外提取主键并建立索引来优化。
索引策略与查询性能
为支持时间范围查询，使用BRIN索引，利用ts列自然递增的特性，索引体积远小于B树。为按表过滤，使用table_oid列并建B树索引，比用schema和表名更高效。为快速查询单条记录历史，将表OID和主键值哈希为UUID，并对record_id和old_record_id建部分索引。
启用审计的注意事项
通过公共API enable_tracking和disable_tracking可轻松开关审计，底层自动创建或删除触发器。但被审计表必须定义主键，否则会抛出异常。触发器在每次行级插入、更新、删除时执行，将变更写入审计表，无需用户干预。
性能影响与适用场景
审计会降低插入、更新、删除的吞吐量。在写入频率低于每秒1000次时，开销通常可忽略。若写入频率更高，建议考虑使用pgAudit等SQL外工具记录变更。本文方案适合低维护、易用且查询快速的场景，并已打包为扩展提供额外功能。
Q&A
PostgreSQL的审计系统有什么主要功能？
PostgreSQL的审计系统能够跟踪表内容的变化，适用于历史分析，记录每次插入、更新和删除操作。
如何在PostgreSQL中实现审计？
通过创建审计表和触发器，记录数据变化，并使用JSONB格式存储数据。
使用JSONB格式存储数据有什么优势？
JSONB格式便于查询和管理，并允许在单个审计表中存储多个表的审计历史。
审计会对数据库性能产生什么影响？
审计会增加插入、更新和删除的吞吐量，但在低频率写入时影响较小。
如何提高审计查询的性能？
可以通过创建索引来提高查询性能，包括时间范围和表过滤的索引。
如何启用和禁用PostgreSQL表的审计功能？
可以使用公共API，通过调用select audit.enable_tracking('<schema>.<table>')启用审计，使用select audit.disable_tracking('<schema>.<table>')禁用审计。
