从0到1构建PostgreSQL时态表：temporal_tables完整示例教程

从0到1构建PostgreSQL时态表temporal_tables完整示例教程【免费下载链接】temporal_tablesPostgresql temporal_tables extension in PL/pgSQL, without the need for external c extension.项目地址: https://gitcode.com/gh_mirrors/tem/temporal_tablesPostgreSQL时态表temporal tables是一种特殊的数据库表结构能够记录数据随时间变化的完整历史。temporal_tables项目提供了一个纯PL/pgSQL实现的解决方案无需依赖外部C扩展特别适合AWS RDS、Google Cloud SQL和Azure Database for PostgreSQL等云环境使用。本文将通过实际示例带您快速掌握时态表的创建与应用。为什么选择temporal_tablestemporal_tables扩展具有以下核心优势纯SQL实现采用PL/pgSQL编写无需编译C扩展可直接在托管PostgreSQL环境中使用性能优化提供两种版本函数完整版versioning_function.sql包含完整验证versioning_function_nochecks.sql则通过移除检查实现2倍性能提升丰富功能支持忽略无变化更新、历史表包含当前版本、自动版本号递增等高级特性兼容性好与原生PostgreSQL语法兼容学习成本低快速开始环境准备首先克隆项目仓库到本地git clone https://gitcode.com/gh_mirrors/tem/temporal_tables cd temporal_tables创建测试数据库并安装时态表函数createdb temporal_test psql temporal_test versioning_function.sql如果需要使用自定义系统时间功能还需安装系统时间函数psql temporal_test system_time_function.sql实战教程创建你的第一个时态表1. 创建基础表结构以订阅信息表为例首先创建基础表结构CREATE TABLE subscriptions ( name text NOT NULL, state text NOT NULL );2. 添加系统时间列时态表需要一个特殊的时间区间列来记录数据有效性ALTER TABLE subscriptions ADD COLUMN sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, null);3. 创建历史表历史表将存储所有数据变更记录使用LIKE语法复制基础表结构CREATE TABLE subscriptions_history (LIKE subscriptions);为提高查询性能建议为历史表添加索引CREATE INDEX ON subscriptions_history (sys_period); -- 如果表有唯一标识列也建议添加索引 -- CREATE INDEX ON subscriptions_history (id);4. 创建版本控制触发器触发器是实现时态表功能的核心它会在数据变更时自动维护历史记录CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON subscriptions FOR EACH ROW EXECUTE PROCEDURE versioning( sys_period, subscriptions_history, true );基本操作体验时态表功能完成上述配置后我们可以通过简单操作体验时态表的强大功能-- 插入测试数据 INSERT INTO subscriptions (name, state) VALUES (test1, inserted); -- 更新数据 UPDATE subscriptions SET state updated WHERE name test1; UPDATE subscriptions SET state updated twice WHERE name test1; -- 删除数据 DELETE FROM subscriptions WHERE name test1;查询当前表会发现数据已被删除SELECT * FROM subscriptions; -- 返回0行但查询历史表可以看到完整的变更记录SELECT * FROM subscriptions_history;结果类似namestatesys_periodtest1inserted[2017-08-01 16:09:45.54298302,2017-08-01 16:09:54.98417902)test1updated[2017-08-01 16:09:54.98417902,2017-08-01 16:10:08.88057102)test1updated twice[2017-08-01 16:10:08.88057102,2017-08-01 16:10:17.3365902)高级功能定制你的时态表设置自定义系统时间temporal_tables提供了set_system_time函数允许您设置自定义时间戳这在数据迁移和历史数据补录时非常有用-- 设置自定义系统时间 SELECT set_system_time(1999-12-31 23:59:59::timestamptz); -- 插入数据将使用指定的时间戳 INSERT INTO subscriptions (name, state) VALUES (test2, inserted); -- 重置为默认系统时间 SELECT set_system_time(null);忽略无变化更新默认情况下即使更新前后数据没有变化时态表也会记录历史。通过添加第四个参数可以只记录实际发生变化的更新CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON subscriptions FOR EACH ROW EXECUTE PROCEDURE versioning( sys_period, subscriptions_history, true, true );历史表包含当前版本默认历史表只存储历史数据通过第五个参数可以让历史表同时包含当前版本数据简化查询操作CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON subscriptions FOR EACH ROW EXECUTE PROCEDURE versioning( sys_period, subscriptions_history, true, false, true );启用后可以直接从历史表查询所有版本包括当前版本SELECT * FROM subscriptions_history WHERE name test1;而不必像以前那样联合查询当前表和历史表-- 启用前需要这样查询 SELECT * FROM subscriptions WHERE name test1 UNION SELECT * FROM subscriptions_history WHERE name test1;自动版本号递增您可以为表添加版本号列实现每次更新自动递增版本号-- 添加版本号列 ALTER TABLE subscriptions ADD COLUMN version int NOT NULL DEFAULT 1; ALTER TABLE subscriptions_history ADD COLUMN version int NOT NULL; -- 创建带版本号功能的触发器 CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON subscriptions FOR EACH ROW EXECUTE PROCEDURE versioning( sys_period, subscriptions_history, false, false, false, false, true, -- 启用版本号递增 version -- 版本号列名 );迁移现有表到时态表如果您需要将现有表转换为时态表可以按照以下步骤操作添加系统时间列ALTER TABLE existing_table ADD COLUMN sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, null);创建历史表CREATE TABLE existing_table_history (LIKE existing_table);复制当前数据到历史表INSERT INTO existing_table_history SELECT *, tstzrange(LOWER(sys_period), NULL) FROM existing_table;创建触发器CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON existing_table FOR EACH ROW EXECUTE PROCEDURE versioning( sys_period, existing_table_history, true, false, true );性能优化建议选择合适的版本函数对性能要求高且能接受较少验证的场景可使用versioning_function_nochecks.sql合理设计索引至少为历史表的sys_period列创建索引定期清理历史数据对于不再需要的历史数据可考虑归档或删除批量操作优化大批量数据操作时可暂时禁用触发器完成后重新启用并手动处理历史记录测试与验证项目提供了完整的测试套件您可以通过以下命令运行测试# 启动测试数据库 npm run db:start # 运行测试 npm test # 或使用make make run_test性能测试可以通过以下命令执行make performance_test总结temporal_tables提供了一个简单而强大的解决方案让您无需复杂配置即可在PostgreSQL中实现时态数据管理。通过本文介绍的步骤您可以快速创建和使用时态表有效跟踪数据变更历史满足合规性和审计需求。无论是新应用开发还是现有系统改造temporal_tables都是管理时间序列数据的理想选择。项目的所有功能实现都可以在以下文件中找到详细代码核心版本控制函数versioning_function.sql系统时间函数system_time_function.sql测试用例test/sql/【免费下载链接】temporal_tablesPostgresql temporal_tables extension in PL/pgSQL, without the need for external c extension.项目地址: https://gitcode.com/gh_mirrors/tem/temporal_tables创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考