ReaderMX.
PostgreSQL码农集散地

PG 19 要引入时间旅行表(temporal table)了?

Pdigoal约 5 分钟

PG 19 要引入时间旅行表(temporal table)了?

数据库的 时态表 (Temporal Table) 是一种强大的功能，它允许数据库自动跟踪和记录表中数据随时间变化的所有历史版本。

简单来说，它就像是为你的数据表内置了一台“时光机”。

🚀 什么是时态表？

在普通的数据库表中：

  • 当你 UPDATE (更新) 一行数据时，旧的数据就被覆盖了，永远丢失了。
  • 当你 DELETE (删除) 一行数据时，这行数据就消失了。 你只知道数据现在是什么样子，却不知道它昨天或上周是什么样子。

而 时态表 解决了这个问题。它通过以下方式工作：

  1. 自动记录历史： 当你开启时态表功能时，数据库会自动创建一张“历史表” (History Table) 来“影子”你的主表（当前数据表）。
  2. 保留旧版本：
  • 当你 UPDATE 一行数据时，数据库会先将这行数据的旧版本（修改前的样子）复制到“历史表”中，然后再更新主表中的数据为新版本。
  • 当你 DELETE 一行数据时，数据库不会真的彻底删除它，而是将这行数据（删除前的最后一个版本）移动到“历史表”中。
  • 时间戳列： 数据库会自动在主表和历史表中添加两个特殊的列，通常叫做 SysStartTime (或 ValidFrom) 和 SysEndTime (或 ValidTo)。
    • SysStartTime 记录了这一行数据版本何时开始有效。
    • SysEndTime 记录了这一行数据版本何时失效（即被更新或删除）。对于当前仍然有效的数据，这个值通常是一个“遥远的未来”日期（如 9999-12-31）。

    💼 举例说明：员工部门变更

    假设我们有一个 Employees (员工) 表，用来跟踪员工所在的部门。

    场景：

    1. T1 (1月1日): Alice 入职，分配到 "Sales" (销售) 部门。
    2. T2 (6月15日): Alice 表现出色，被调到 "Marketing" (市场) 部门。
    3. T3 (12月1日): Alice 离职。

    1. T1: Alice 入职 (INSERT)

    当 Alice 在1月1日 09:00 入职时，我们插入一条数据：

    Employees (主表 - 当前数据)

    EmployeeID
    Name
    Department
    SysStartTime
    SysEndTime
    101
    Alice
    Sales
    2025-01-01 09:00
    9999-12-31 23:59

    EmployeesHistory (历史表)(此时历史表是空的)

    注意：SysEndTime 是 9999 年，表示这条记录是当前有效的。

    2. T2: Alice 换部门 (UPDATE)

    在6月15日 10:30，Alice 换到了 "Marketing" 部门。我们执行一个简单的 UPDATE 语句：UPDATE Employees SET Department = 'Marketing' WHERE EmployeeID = 101;

    时态表在幕后做了这些事：

    1. 归档旧版本： 把主表中 "Sales" 那条记录的 SysEndTime 修改为当前时间 (6月15日 10:30)，并将其移动到历史表。
    2. 插入新版本： 在主表中，将数据更新为 "Marketing"，并将其 SysStartTime 设置为当前时间 (6月15日 10:30)，SysEndTime 依然是 9999 年。

    现在的状态变为：

    Employees (主表 - 当前数据)

    EmployeeID
    Name
    Department
    SysStartTime
    SysEndTime
    101
    Alice
    Marketing
    2025-06-15 10:30
    9999-12-31 23:59

    EmployeesHistory (历史表)

    EmployeeID
    Name
    Department
    SysStartTime
    SysEndTime
    101
    Alice
    Sales
    2025-01-01 09:00
    2025-06-15 10:30

    注意看时间戳： 历史表中的记录显示，Alice 在 "Sales" 部门的有效期是从1月1日到6月15日。主表显示她现在在 "Marketing" 部门，从6月15日开始。

    3. T3: Alice 离职 (DELETE)

    在12月1日 17:00，Alice 离职了。我们执行：DELETE FROM Employees WHERE EmployeeID = 101;

    时态表在幕后做了这些事：

    1. 归档旧版本： 把主表中 "Marketing" 那条记录的 SysEndTime 修改为当前时间 (12月1日 17:00)，并将其移动到历史表。
    2. 清空主表： 主表中不再有 Alice 的记录（因为她当前不是在职员工）。

    现在的状态变为：

    Employees (主表 - 当前数据)(现在主表空了，没有 Alice 的记录)

    EmployeesHistory (历史表)

    EmployeeID
    Name
    Department
    SysStartTime
    SysEndTime
    101
    Alice
    Sales
    2025-01-01 09:00
    2025-06-15 10:30
    101
    Alice
    Marketing
    2025-06-15 10:30
    2025-12-01 17:00

    🔍 "时光机"查询 (Time-Travel Query)

    这才是时态表最酷的地方。现在，我们可以查询数据在过去任意时间点的状态。

    查询 1：Alice 现在在哪个部门？(这是一个标准查询，只查主表)SELECT * FROM Employees WHERE EmployeeID = 101;

    结果： (空) - 因为她已经离职了。

    查询 2：Alice 在 2025年3月1日 在哪个部门？(我们使用时态表特有的 AS OF 语法)SELECT * FROM Employees FOR SYSTEM_TIME AS OF '2025-03-01' WHERE EmployeeID = 101;

    结果：| 101 | Alice | Sales | ... | ... | (数据库会自动查找在 3月1日 时有效的那条历史记录)

    查询 3：Alice 在 2025年7月1日 在哪个部门？SELECT * FROM Employees FOR SYSTEM_TIME AS OF '2025-07-01' WHERE EmployeeID = 101;

    结果：| 101 | Alice | Marketing | ... | ... | (数据库找到了 6月15日 到 12月1日 之间有效的 "Marketing" 记录)

    💡 为什么要使用时态表？

    • 审计 (Auditing)： 轻松跟踪“谁在什么时间修改了什么数据”。
    • 历史报告 (Historical Reporting)： 分析数据随时间的变化趋势（例如，查询“去年此时的库存总量”）。
    • 数据恢复 (Data Recovery)： 如果有人错误地更新或删除了数据，你可以轻易地查到它被修改前的样子。
    • 合规性 (Compliance)： 很多行业（如金融、医疗）法规要求必须保留数据的历史变更记录。

    PostgreSQL 19 突然发了个这样的patch: "doc: Add section for temporal tables"

    Image
    Image

    看完内容白高兴一场, 是应用层逻辑删除来支持的, 还以为要引入时间旅行表(temporal table)了

    https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e4d8a2af07f56ec2537eb8f9a16599249db62c94

    doc: Add section for temporal tables  
    author  Peter Eisentraut <[email protected]>   
    Wed, 5 Nov 2025 15:38:04 +0000 (16:38 +0100)  
    committer Peter Eisentraut <[email protected]>   
    Wed, 5 Nov 2025 15:38:04 +0000 (16:38 +0100)  
    commit  e4d8a2af07f56ec2537eb8f9a16599249db62c94  
    tree  2045372146786da79d71663b7c2195098ec1789f  tree  
    parent  447aae13b0305780e87cac7b0dd669db6fab3d9d  commit | diff  
    doc: Add section for temporal tables  

    This section introduces temporal tables, with a focus on Application  
    Time (which we support) and only a brief mention of System Time (which
    we don't).  It covers temporal primary keys, unique constraints, and  
    temporal foreign keys.  We will document temporal update/delete and  
    periods as we add those features.  
    This commit also adds glossary entries for temporal table, application  
    time, and system time.  
    Author: Paul A. Jungwirth <[email protected]>  
    Discussion: https://www.postgresql.org/message-id/flat/[email protected]  
    doc/src/sgml/ddl.sgml   diff | blob | blame | history  
    doc/src/sgml/glossary.sgml    diff | blob | blame | history  
    doc/src/sgml/images/Makefile    diff | blob | blame | history  
    doc/src/sgml/images/temporal-entities.svg [new file with mode: 0644]  blob  
    doc/src/sgml/images/temporal-entities.txt [new file with mode: 0644]  blob  
    doc/src/sgml/images/temporal-references.svg [new file with mode: 0644]  blob  
    doc/src/sgml/images/temporal-references.txt [new file with mode: 0644]  blob  

    KEEP THE THREAD GOING

    更多来源文章