期待 Postgres 19：时间表的到来

背景

在数据库领域，一个日益常见且棘手的问题是：“数据在上周二长什么样？”这可能涉及节假日促销前的产品价格，或者某次未经请求的组织架构调整前员工所属的部门。如果不引入整个审计触发器系统，我们如何确切地知道数据在特定日期变更前后的状态？

早在十多年前，SQL:2011 标准就通过“时序表”（Temporal Tables）正式提出了这一问题的解决方案。其他数据库引擎相对快速地采纳了其中的部分特性。然而，Postgres 一贯以稳健著称，但也因此显得缓慢。直到 Postgres 19 版本，原生时序表支持终于被引入——尽管等待时间漫长，但这一特性确实值得。

核心内容

传统方法的局限性

在探讨 Postgres 19 的新特性之前，我们需要回顾一下传统的处理方式，以便理解其改进之处。假设我们要追踪产品随时间变化的价格，一种直观的做法是创建如下表结构：

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE products (
    product_id INT NOT NULL,
    product_name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    CONSTRAINT no_time_travel CHECK (valid_from < valid_to)
);

这种结构包含产品ID、名称、价格以及价格有效的日期范围。然而，这种方法存在一个严重缺陷：它无法防止为同一产品插入重叠的日期范围。例如，产品 ID 42 可能在同一个周二同时拥有 $9.99 和 $14.99 两个价格，这将导致财务数据混乱。

为了解决这个问题，Postgres 社区通常依赖 btree_gist 扩展和排除约束（Exclusion Constraint）：

ALTER TABLE products
ADD CONSTRAINT no_overlapping_prices
EXCLUDE USING gist (
    product_id WITH =,
    daterange(valid_from, valid_to) WITH &&
);

当尝试插入冲突的行时，Postgres 会报错： ERROR: conflicting key value violates exclusion constraint "no_overlapping_prices"

虽然这种方法有效，但它存在几个显著问题：

1. 技术门槛高：虽然 BTREE 索引广为人知，但 GiST 是 Postgres 特有的，且 btree_gist 是可选扩展，需要用户具备特定经验才能理解和使用。
2. 语法不直观：排除约束的语法晦涩难懂，除非查阅文档，否则开发者很难将其视为标准做法。
3. 缺乏原生时序意识：表本身并不理解这是时序数据。它只是普通的列加上一个使用复杂索引类型的约束。每次更新涉及时间范围时，应用程序必须手动拆分和拼接行，以承担时序正确性的全部责任。

时序数据的历史演进

Postgres 对原生时序支持的需求由来已久。SQL:2011 标准引入了应用时间周期（APPLICATION TIME periods）、无重叠约束（WITHOUT OVERLAPS constraints）以及用于时序 DML 的 FOR PORTION OF 语法。

在 Postgres 生态系统中，Henrietta Dombrovskaya（Hetti）是最早的时序数据倡导者之一。她与 Chad Slaughter 共同开发了 pg_bitemporal 扩展。这是一个完全在 Postgres 内部使用 PL/pgSQL 管理双时序表（Bitemporal Tables）的框架。自 2015 年以来，她在多次会议上展示了如何同时追踪“有效时间”（Valid Time，即事实在世界中何时为真）和“事务时间”（Transaction Time，即数据库记录该事实的时间）。

• 有效时间：例如“该价格从一月生效至六月”。
• 事务时间：例如“该行于3月12日下午3:47插入，并于4月3日上午9:01被取代”。

结合两者形成的双时序表可以回答诸如“基于我们上周二所知的信息，当时我们认为价格是多少？”这类复杂问题。pg_bitemporal 扩展同样依赖于 EXCLUDE USING gist 机制，但加倍应用：一个用于有效范围，另一个用于断言范围。

尽管 pg_bitemporal 功能强大，但扩展有其局限性。它无法改变查询规划器对时序谓词的看法，无法在引擎层面集成约束系统，也无法提供原生的 DML 语法。因此，该功能必须进入核心代码。

Postgres 19 的革新：范围类型与原生支持

Postgres 19 accommodates 了双时序系统中的应用时间部分。虽然这不是完整的双时序支持，但这已经是正确方向上的巨大飞跃。

在 Postgres 19 中，我们不再使用单独的 valid_from 和 valid_to 列，而是使用单个范围类型列：

CREATE TABLE products (
    product_id INT NOT NULL,
    product_name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    valid_at DATERANGE NOT NULL,
    PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS)
);

关键变化解析：

• 无需扩展：不再需要 btree_gist 扩展。
• 无需显式排除约束：主键中的 WITHOUT OVERLAPS 子句告诉 Postgres，product_id 在任意给定时间点必须是唯一的，但只要 valid_at 范围不重叠，同一产品可以有多个行。
• 原生理解：Postgres 现在真正理解我们在处理时序数据。

新旧方法对比：

| 特性 | 旧方法 (Pre-19) | 新方法 (Postgres 19) | | :--- | :--- | :--- | | 列定义 | 两个单独的日期列 (valid_from, valid_to) | 单个范围列 (valid_at DATERANGE) | | 约束机制 | 手动构建范围并使用 EXCLUDE USING gist | 主键中内置时序意识 (WITHOUT OVERLAPS) | | 依赖项 | 需要 btree_gist 扩展 | 自动处理依赖，无需手动安装扩展 |

数据示例与范围表示法：

INSERT INTO products VALUES
(1, 'Widget', 9.99, '[2025-01-01, 2025-07-01)'),
(1, 'Widget', 12.99, '[2025-07-01, 2026-01-01)'),
(1, 'Widget', 11.99, '[2026-01-01, 2026-07-01)'),
(2, 'Gadget', 24.99, '[2025-01-01, 2025-04-01)'),
(2, 'Gadget', 22.99, '[2025-04-01, 2026-01-01)'),
(2, 'Gadget', 26.99, '[2026-01-01,)');

范围符号说明：

• [ 表示包含（Inclusive）。
• ) 表示不包含（Exclusive）。
• 例如，[2025-01-01, 2025-07-01) 包含1月1日，但不包含7月1日。
• [2026-01-01,) 表示开放-ended 范围，意味着当前价格没有定义结束日期。

关键要点

• 原生时序支持落地：Postgres 19 引入了原生的时序表支持，特别是应用时间（Application Time）部分，填补了 SQL:2011 标准长期以来的空白。
• 简化开发复杂度：通过引入 DATERANGE 类型和 WITHOUT OVERLAPS 主键约束，开发者不再需要依赖 btree_gist 扩展或编写复杂的排除约束逻辑。