第 16 章 经天纬地：时序、空间与时空查询

“时间”与“空间”都很容易被压缩成错误的表结构：

created_at timestamp,
longitude  numeric,
latitude   numeric

这四个字段看起来够用，却没有回答最关键的问题：

created_at 是事情发生、服务器接收，还是规则生效的时间？
timestamp 表示绝对时刻，还是某地墙上时间？
经纬度遵守哪个坐标参考系，顺序与单位是什么？
边界上的点算区域内还是区域外？
距离是角度、米，还是某个投影坐标系的单位？
历史查询应使用今天的围栏，还是当时生效的围栏版本？

一旦业务需要处理夏令时、迟到、乱序、重复写入、围栏换版或距离筛选，这些 未回答的问题就会从“数据建模细节”变成错误结果。

本章建立一条统一原则：

先固定时间与空间语义，再选择分区、扩展和索引；先证明逻辑答案，再证明 物理路径；最后才讨论容量和性能。

本章完成后

你应当能够：

• 区分事件时间、接收时间、处理时间和业务有效时间；
• 选择 timestamptz 与 timestamp，解释 PostgreSQL 的存储、输入和显示 时区职责；
• 用一次夏令时跳变说明“墙上时间差”为什么不等于实际经过时间；
• 识别迟到、乱序和重复是三个不同问题，并分别设计水位线、重算与幂等合同；
• 用 tstzrange 和半开区间 [) 表达无歧义的有效期；
• 选择事件时间作为分区键，写出可裁剪的半开范围谓词；
• 从计划中区分“只访问一个分区”与“扫描所有分区后再过滤”；
• 解释原生分区、聚合与 TimescaleDB 解决的问题边界；
• 区分 PostGIS geometry 与 geography 的计算模型和单位；
• 说明 SRID 是坐标参考身份，ST_SetSRID 不会转换坐标；
• 在 ST_Covers、ST_Contains、ST_Intersects、ST_DWithin、 ST_Distance 与 <-> 之间按业务语义选择；
• 解释包围盒候选与精确几何判断的二阶段关系；
• 用 GiST/SP-GiST 计划证明路径存在，同时不把小表强制计划冒充性能基准；
• 把事件时间裁剪、围栏有效期与空间谓词组合成可审计的时空查询；
• 在 Pigsty 中区分扩展装包、preload、CREATE EXTENSION、版本核对与 L1 节点一致性；
• 把 PostGIS 纳入备份恢复、大版本升级、WAL、索引和副本成本；
• 交付一个有冻结输入、反例、计划、权限、校验和、ADR 与精确复位路径的 配送事件 PoC。

贯穿本章的配送事件

实验固定三种时间：

固定两种空间表示：

冻结 fixture 包含：

13 ingest attempts
12 distinct delivery events
 4 geofence versions across 3 zones
 3 delivery hubs
 3 daily UTC partitions

13 次尝试中，e003 被发送两次；数据库保留尝试事实，再选出唯一规范事件。 三张日分区分别得到 1 / 7 / 4 行。e008 发生在 2026-03-08 23:59:59Z，e009 正好发生在次日 00:00:00Z，用来证明 半开分区边界。

一个十分钟却跨过两小时刻度的例子

纽约在 2026-03-08 进入夏令时。fixture 中：

墙上时间从 01:55 跳到 03:05，看起来相隔 70 分钟；两个绝对时刻实际只相隔 600 秒。实验把两项都保存为证据：

dst_e002_local=2026-03-08 01:55:00
dst_e003_local=2026-03-08 03:05:00
dst_elapsed_seconds=600

PostgreSQL 的日期时间类型与时区转换规则以官方 Date/Time Types 和 Date/Time Functions 为准。应用程序不应自己维护一份简化时区规则。

围栏边界不是实现细节

e003 位于 central v1 的东边界，也位于 east v1 的西边界。固定结果是：

本章选择 ST_Covers，所以边界算命中，e003 会同时属于两个区域。这是业务 合同，不是 PostGIS 替业务做出的唯一正确选择。若配送系统要求唯一归属，还要 增加优先级、分区化面集或确定性消歧。

另一个版本例子：

central v1  [2026-03-07 00:00Z, 2026-03-08 12:00Z)
central v2  [2026-03-08 12:00Z, 2026-03-10 00:00Z)

e004 在扩张前位于旧围栏外；同一点的 e005 正好在 12:00 发生，按 [) 落入 v2 并位于新围栏内。同一 zone_id 的有效期由 btree_gist 排他约束 禁止重叠，重叠写入固定失败为 SQLSTATE 23P01。

逻辑正确与物理路径分开

时间查询的正确写法是直接约束分区键：

WHERE occurred_at >= TIMESTAMPTZ '2026-03-08 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-03-09 00:00:00+00'

固定计划只出现：

delivery_event_20260308

把列包进表达式：

WHERE (occurred_at AT TIME ZONE 'UTC')::date = DATE '2026-03-08'

逻辑答案仍是七行，但计划通过 Append 访问三张分区。PostgreSQL 官方分区 文档强调，裁剪依据是分区边界，而不是分区上的普通索引；写法必须让规划器或 执行器能够把谓词与分区键对应。参见 Table Partitioning。

空间计划分别证明：

ST_DWithin geography -> event_20260308_geog_gist_idx
ST_DWithin geometry  -> delivery_hub_location_spgist_idx
ST_Covers join       -> event_20260308_location_gist_idx
zone_id lookup       -> geofence_version_no_overlap

这些计划在 12 行 fixture 上关闭顺序扫描，仅证明路径可用。正常规划器选择 顺序扫描并不表示索引失效，更不能用强制计划声称生产更快。

实验资产

规范与决策：

• 实验合同
• 时空 ADR
• 冻结夹具清单
• v1.4 proposal
• Pigsty 4.4 声明片段

冻结输入：

• 写入尝试
• 围栏版本
• 配送中心
• 确定性装载

实现与证据：

• 环境保护
• 建模、扩展、分区与索引
• 时间事实
• 分区目录
• 边界语义
• 距离语义
• 索引目录
• 最终状态
• 完整验证
• 自动审校器
• 精确复位

三份 CSV 不只是示例附件。自动化从数据库重新导出并逐字节比较； fixture-manifest.json 还固定行数、SHA-256、时间合同、坐标合同和许可证 边界。

快速运行

本地开发数据库应先完成第 4 章的角色与物理模型：

export PGSERVICEFILE=/path/to/pg_service.conf
export PGSERVICE=pg36-admin

PG36_EVIDENCE_DIR="$PWD/evidence/ch16" \
  ./static/labs/ch16/task.sh all

all 会：

1. 验证数据库、可写状态、PostgreSQL 14–18、管理员、owner/app 角色和 ch04-v1 模型；
2. 核对本机恰好可供应 PostGIS 3.6.4 与 btree_gist 1.8；
3. 只接管带精确 owner、marker、版本与扩展依赖的两个 schema；
4. 在单事务中安装扩展、创建三张日分区、四类数据表和 13 个管理索引；
5. 导入 13 次尝试，验证重复 payload 一致，再生成 12 个规范事件；
6. 从数据库回读三份冻结 CSV 并逐字节比较；
7. 采集 DST、迟到、乱序、分区路由、时间桶、围栏版本、边界和距离证据；
8. 采集扩展、索引、权限、对象体积和五份执行计划；
9. 证明混合 SRID、重叠有效期、应用写入分别以 XX000、23P01、 42501 失败；
10. 运行 34 个关系对象、扩展依赖、数据事实、索引、权限与业务校验和的 完整断言；
11. 证明错误 token、错误 target、活跃 worker 时 reset 分别以 P3660/P3661/P3663 被拒绝；
12. 在单事务中不用 CASCADE 精确复位，确认第 14 章扩展保留，再完整 重建第二次。

正式 Homebrew PostgreSQL 18.4 双周期证据得到：

status=ok
fixture=frozen-byte-identical
time=event+ingest+validity+dst
space=geometry+geography+srid+boundary
plans=pruning+gist+spgist+joint
guards=P3660+P3661+P3663
extensions=btree_gist:1.8+postgis:3.6.4
pigsty_l1=not-run
release_candidate_checksum=13902984b3da92a66638d0d6e2f886d6d8ac5cb20ba89ec08b1527ae79d2b923

安全边界

task.sh all 会删除并重建带本章精确 marker 的 shop_ch16、 shop_ch16_ext 和其中两项扩展，只适合本书本地/开发 fixture。生产环境 必须使用经过评审的扩展供应、在线分区与索引发布、备份恢复验证和回退流程。

学习路径

16.1 时间语义先于时序扩展

• 16.1.1 事件时间、处理时间与有效时间
• 16.1.2 时区、迟到、乱序与重复事件
• 16.1.3 范围类型、窗口与时间对齐

先学会给“时间”命名和验收。未固定语义时，引入任何时序扩展只会更快地得到 不确定答案。

16.2 时序表与时间分区

• 16.2.1 从 ch04 的分区 ADR 选择时间键
• 16.2.2 写入模式、冷热生命周期与保留
• 16.2.3 原生分区、聚合与可选时序扩展
• 16.2.4 不在本章重复在线分区化和维护细节

把事件时间落实到原生分区，理解裁剪、路由、生命周期和引入时序扩展的决策 门槛。

16.3 空间类型与坐标参考

• 16.3.1 geometry、geography 与测量语义
• 16.3.2 SRID、投影、单位与坐标转换
• 16.3.3 点、线、面、边界与有效几何

先固定坐标身份、表示与单位，再允许业务写空间谓词。

16.4 空间谓词与索引

• 16.4.1 包含、相交、邻近与最近邻
• 16.4.2 包围盒过滤与精确计算
• 16.4.3 GiST/SP-GiST 计划与选择率验证

从“问题是什么”推导谓词，再从谓词和数据分布推导索引，不反过来。

16.5 时空联合查询是本章收束目标

• 16.5.1 某时段、某区域内的配送事件
• 16.5.2 轨迹、停留、地理围栏与迟到修正
• 16.5.3 时间裁剪、空间索引与二阶段过滤

把事件时间、围栏有效时间和空间命中合成同一条可解释查询。

16.6 时空扩展的交付与观察

• 16.6.1 安装 PostGIS 与可选时序扩展
• 16.6.2 核对版本、依赖、备份和升级边界
• 16.6.3 观察分区、索引、写入与聚合成本

把本地 SQL 映射到 Pigsty 的装包、配置、建库、节点一致性和运行证据。

16.7 实战：配送事件的时空 PoC

• 16.7.1 生成确定性事件与地理数据
• 16.7.2 验证 SRID 错误、裁剪失效和空间索引
• 16.7.3 输出 ADR、PoC 证据与生产代价清单
• 16.7.4 超预算时先删扩展专属细节，不删基础判断力

最后完整执行两周期 PoC，并明确哪些结论已证明、哪些仍需生产规模测试。

权威参考

• PostgreSQL 18： 日期时间类型、 日期时间函数、 范围类型、 声明式分区
• PostGIS： 数据管理与坐标参考、 空间查询、 ST_Covers、 ST_DWithin、 ST_Transform
• Pigsty 4.4： 扩展概览、 包别名、 创建扩展、 PostGIS、 TimescaleDB

上一章：见微知著：全文、模糊与向量检索 · 返回上卷导读 · 下一章：合纵连横：分析加速与分布式选型 · 查看全书目录 · 查看索引中心