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

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。

贯穿本章的配送事件

实验固定三种时间：

列	语义	用途
`occurred_at`	配送事件实际发生时刻	业务排序、分区、历史归属
`received_at`	该写入尝试被接收的时刻	迟到、乱序、重放审计
`valid_during`	围栏版本生效区间	历史时点连接

固定两种空间表示：

表示	本章职责
`geometry(..., 4326)`	拓扑谓词、边界判断、空间索引
`geography(..., 4326)`	以米为单位的距离判断

冻结 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 中：

事件	UTC	`America/New_York` 显示
`e002`	`06:55Z`	`01:55`
`e003`	`07:05Z`	`03:05`

墙上时间从 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`	`ST_Contains`	`ST_Touches`
central v1	true	false	true
east v1	true	false	true

本章选择 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 上关闭顺序扫描，仅证明路径可用。正常规划器选择顺序扫描并不表示索引失效，更不能用强制计划声称生产更快。

实验资产

规范与决策：

冻结输入：

实现与证据：

三份 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 会：

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

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

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.2 时序表与时间分区

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

16.3 空间类型与坐标参考

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

16.4 空间谓词与索引

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

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

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

16.6 时空扩展的交付与观察

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

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

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

权威参考

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

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

16.1 时间语义先于时序扩展

时序系统首先是时间语义系统，其次才是高吞吐写入、压缩或连续聚合系统。

如果一张表只有 created_at，读者无法判断：

它由设备、应用还是数据库生成；
它表示业务发生、消息到达、事务提交还是规则生效；
它能否用于重建业务顺序；
它是否适合成为分区键；
迟到事件应修改旧聚合，还是被丢弃；
修改历史维表后，旧事件是否要重新归属。

本节先建立一套可以写入 schema、查询和验收证据的时间词汇。

16.1.1 事件时间、处理时间与有效时间

一条事实至少可能有四只钟

时间	回答的问题	常见来源
事件时间 `occurred_at`	业务世界何时发生？	设备、业务服务、领域事件
接收时间 `received_at`	本系统何时看见这次尝试？	API/消息消费者入口
处理时间 `processed_at`	某处理阶段何时完成？	worker、ETL、聚合任务
有效时间 `valid_during`	某规则/版本何时适用？	业务配置、主数据版本

接收时间是处理时间的一种边界，但不要因此把所有处理阶段都压成一个字段。例如：

device occurred_at
  -> gateway received_at
  -> queue enqueued_at
  -> consumer processed_at
  -> database committed_at

每一项都可能有诊断价值，却不都应成为业务查询的默认时间。配送事件的“当天发生量”通常按 occurred_at；消息积压通常按 received_at - occurred_at 或 processed_at - received_at；围栏归属还要用 valid_during。

本章的最小模型

setup.sql 创建：

CREATE TABLE shop_ch16.ingest_attempt (
  attempt_id      text PRIMARY KEY,
  event_id        text NOT NULL,
  occurred_at     timestamptz NOT NULL,
  received_at     timestamptz NOT NULL,
  courier_id      text NOT NULL,
  event_type      text NOT NULL,
  longitude       numeric(9,5) NOT NULL,
  latitude        numeric(8,5) NOT NULL,
  source_sequence bigint NOT NULL,
  CHECK (received_at >= occurred_at)
);

CREATE TABLE shop_ch16.event_registry (
  event_id             text PRIMARY KEY,
  canonical_attempt_id text NOT NULL REFERENCES shop_ch16.ingest_attempt,
  first_received_at    timestamptz NOT NULL,
  last_received_at     timestamptz NOT NULL,
  attempt_count        integer NOT NULL,
  payload_fingerprint  text NOT NULL
);

原始尝试与规范事件分开，保留两种真相：

transport truth: 这条消息到过几次、每次何时到
business truth: 这个 event_id 只产生一个领域事实

若直接把 event_id 设为事件表唯一键并使用 ON CONFLICT DO NOTHING，数据库能做到幂等，却会丢失重试次数和 payload 冲突证据。本章先保存 ingest_attempt，再要求同一 event_id 的业务 payload 完全一致，选择最早到达的尝试作为 canonical。

`timestamptz` 表示绝对时刻

PostgreSQL 有两种常被混淆的 timestamp：

类型	语义	是否保留输入时区名称
`timestamp with time zone` / `timestamptz`	时间线上的绝对时刻	否
`timestamp without time zone`	没有时区解释的日期与墙上时间	不适用

timestamptz 输入会依据显式偏移、时区名称或会话 TimeZone 解释成绝对时刻；内部以统一形式保存，输出时再按当前 TimeZone 显示。原始的 America/New_York、Asia/Shanghai 名称不会随值保存。

因此，下面两个输入表示同一个时刻：

SELECT
  TIMESTAMPTZ '2026-03-08 07:05:00+00'
    =
  TIMESTAMPTZ '2026-03-08 03:05:00-04';

结果是 true。若业务还要知道用户选择的法定时区，应另存经过校验的 IANA 时区名：

event_timezone text

不要试图从 UTC offset 反推时区。-04:00 同时可能对应多个地区，也不能表达未来或过去的夏令时规则。

PostgreSQL 官方日期时间类型详细描述输入、存储与输出行为。特别要注意：一个没有偏移的字符串写入 timestamptz 时依赖会话 TimeZone，所以 API 合同应要求显式偏移。

`timestamp` 也有正当用途

不能把规则简化为“永远用 timestamptz”。以下值本来就不是一个已确定的绝对时刻：

商店每天 09:00 开门；
用户生日 1990-05-06；
“2027 年 5 月第一周一上午十点”这项待排程规则；
一张历史文档只记录了当地时间但未知地区。

它们应使用 date、time、timestamp 或“本地日期时间 + IANA 时区 + 解析状态”的复合模型。只有在规则被具体化到某个地区和日期后，才能解析出 timestamptz。

有效时间是区间，不是两个互不相关字段

本章围栏版本：

CREATE TABLE shop_ch16.geofence_version (
  zone_id      text NOT NULL,
  version      integer NOT NULL,
  valid_during tstzrange NOT NULL,
  zone_geom    geometry(Polygon, 4326) NOT NULL,
  PRIMARY KEY (zone_id, version)
);

与 valid_from、valid_to 两列相比，range 把“是否包含端点、是否为空、是否重叠”变成类型和操作符可见的合同：

valid_during @> event.occurred_at
valid_during && another_range
lower(valid_during)
upper(valid_during)

业务连接因而直接表达为：

JOIN shop_ch16.geofence_version AS zone
  ON zone.valid_during @> event.occurred_at

这回答的是“事件发生时哪个版本有效”，而不是“现在最新版本是什么”。

事务时间是另一条轴

本章没有实现完整双时态表。现实中还可能需要：

valid time: 业务上何时有效
system time: 数据库何时知道/记录这个版本

例如 3 月 10 日才补录“围栏从 3 月 8 日开始生效”，业务有效期与系统记录期不同。若需要审计“当时系统认为什么”，应增加系统版本、审计表或不可变事件日志，而不是覆盖旧行后只保留最终答案。

选择默认时间的判断表

问题	应优先使用
某日发生多少配送事件	`occurred_at`
消息积压/链路延迟	`received_at - occurred_at`
worker 吞吐与处理延迟	`processed_at - received_at`
历史事件属于哪个围栏	`valid_during @> occurred_at`
何时把修订写入数据库	审计/事务时间
数据保留按到达还是发生	由法规和回补合同明确，不能猜

一张表可以有多只钟，但每个查询只能在合同中明确自己使用哪一只。

16.1.2 时区、迟到、乱序与重复事件

时区是显示规则，也是输入解析规则

本章连接上下文固定：

SET TimeZone = 'UTC';
SET DateStyle = 'ISO, YMD';

这让导出和校验和稳定，不意味着用户只能看 UTC。展示时显式转换：

SELECT
  event_id,
  occurred_at,
  occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM shop_ch16.delivery_event
WHERE event_id IN ('e002', 'e003')
ORDER BY occurred_at;

AT TIME ZONE 的返回类型取决于输入：

timestamptz AT TIME ZONE zone -> timestamp
timestamp   AT TIME ZONE zone -> timestamptz

前者把绝对时刻投影为某地墙上时间；后者把无时区的墙上时间按指定地区解释为绝对时刻。方向相反，代码审查时必须看输入类型，不能只看函数名字。

夏令时反例

fixture 中：

e002 = 2026-03-08 06:55:00+00
e003 = 2026-03-08 07:05:00+00

纽约本地显示：

e002 = 2026-03-08 01:55:00
e003 = 2026-03-08 03:05:00

正确的实际时长：

SELECT e3.occurred_at - e2.occurred_at;
-- 00:10:00

错误模式是先把两边转成无时区本地时间再相减：

SELECT
  (e3.occurred_at AT TIME ZONE 'America/New_York')
  -
  (e2.occurred_at AT TIME ZONE 'America/New_York');
-- 01:10:00

第二个结果计算的是墙上刻度差，不是经过时长。在秋季回拨时还可能出现同一本地时刻两次。绝对持续时间应在 timestamptz 上运算；按本地日历排程则要先明确地区，再接受 DST 带来的 23/25 小时日。

temporal-analysis.sql 将两个本地显示与 600 秒实际差同时固化，防止只验证其中一面。

迟到不等于乱序

本章定义超过五分钟为“迟到”：

received_at - occurred_at > interval '5 minutes'

固定迟到事件：

e001 delay = 29100 seconds
e004 delay =   900 seconds

迟到比较事件时间与接收/处理时间。乱序比较多个事件在两条时间轴上的顺序。本章：

e004 occurred 11:55, received 12:10
e005 occurred 12:00, received 12:00:05

e004 先发生却后到达，因此 e004 -> e005 是乱序对。一个事件可以迟到但不造成当前批次乱序，也可以只晚几秒却翻转相邻事件顺序。

迟到策略不能藏在 SQL 里

流式或增量聚合常见策略：

策略	好处	代价
永远接受并重算	最接近最终事实	旧分区、缓存和下游长期可变
水位线内重算	成本可控	水位线外需要补偿路径
迟到旁路/人工处理	主链稳定	两套状态与操作流程
直接丢弃	简单	数据损失，必须有明确业务授权

水位线不是 now() - interval '5 minutes' 这么简单。还要定义：

以哪个来源、分区或租户推进；
空闲来源如何处理；
来源时钟漂移多大；
重放是否让水位线倒退；
聚合、缓存、物化视图和外部消费者如何更正；
超过水位线的数据被保留、补偿还是拒绝。

本章只标记迟到，不模拟完整流处理平台。它建立的是数据库中可重算的事实基础。

重复也有两种

传输重复：同一 event_id、同一 payload 被发送多次。本章 e003 有 a003/a004 两次尝试，注册表记录：

event_id=e003
canonical_attempt_id=a003
attempt_count=2

业务冲突：同一 event_id 带来不同 payload。不能将它静默视为普通重复。本章 loader 先计算 payload variant 数，只有恰好一个版本的 event_id 才进入注册表；生产应把冲突写入隔离表并报警。

一个可靠幂等键应来自领域身份，而不是接收时间或随机重试 ID：

good: order_id + event_type + domain sequence
risky: received_at
risky: database-generated serial for every retry

若来源只能提供“近似重复”，需要另设去重窗口、payload hash 与误合并风险，不能假装获得 exactly-once。

来源序列补足时间排序

两条事件可能具有相同 timestamp 精度，设备时钟也可能回拨。本章保留：

source_sequence bigint NOT NULL

同一 courier 内的业务顺序可用：

ORDER BY courier_id, source_sequence, event_id

它不替代时间：序列通常只能在单一来源内比较，也无法回答真实时长。稳健模型同时保存领域序列、事件时间、接收时间和唯一身份。

输入时钟也要被观测

本章约束 received_at >= occurred_at 是教学简化。生产设备的时钟可能快于服务器，直接拒绝会丢数据。更现实的处理是：

source_occurred_at
server_received_at
clock_skew_estimate
normalized_occurred_at (optional and versioned)
source clock quality/status

原始时间不可覆盖；任何校正都要带算法版本，才能在规则变化后重算。

16.1.3 范围类型、窗口与时间对齐

半开区间让相邻边界只有一个归属

本章统一采用：

[lower, upper)

左端包含，右端不包含。因此：

central v1 [00:00, 12:00)
central v2 [12:00, next_day)

正好 12:00 只属于 v2。日分区：

day8 [2026-03-08 00:00Z, 2026-03-09 00:00Z)
day9 [2026-03-09 00:00Z, 2026-03-10 00:00Z)

23:59:59 与次日 00:00:00 也不会重叠或漏掉。不要用 23:59:59.999999 人工制造闭区间上界：精度变化、类型转换和代码生成很容易产生缝隙。

PostgreSQL range 支持包含、重叠、相邻、交集等操作，并可由 GiST/SP-GiST 索引。参见官方 Range Types。

用约束保护有效期

同一围栏版本不能重叠：

EXCLUDE USING gist (
  zone_id      shop_ch16_ext.gist_text_ops WITH =,
  valid_during WITH &&
);

普通 B-tree 能找 zone_id，却不能单独表达“同 zone 的两个 range 不得重叠”。btree_gist 为标量等值提供 GiST operator class，使它能与 range 重叠操作符组合在一个排他约束中。

故意写入：

central 99 [2026-03-08 11:00Z, 13:00Z)

会与 v1/v2 冲突并返回 23P01。这比在应用中先 SELECT 再 INSERT 可靠，因为并发事务仍由数据库约束仲裁。

时间桶必须固定原点

本章用原生 date_bin 对齐 15 分钟：

SELECT
  date_bin(
    interval '15 minutes',
    occurred_at,
    timestamptz '2001-01-01 00:00:00+00'
  ) AS bucket_start,
  count(*)
FROM shop_ch16.delivery_event
GROUP BY bucket_start;

三个参数分别是：

stride   桶宽
source   待对齐时间
origin   网格原点

不固定 origin，就没有完整的桶合同。不同服务若使用不同原点，即使桶宽相同也无法合并。

date_trunc('hour', ...) 适合自然日历单位；date_bin 可表达 15 分钟这类任意固定长度，但不能把“一个月”当成固定秒数。月份、季度、当地营业日应使用明确日历与时区规则。

官方 Date/Time Functions 给出 date_trunc、date_bin 与 AT TIME ZONE 的类型和行为。

UTC 桶与本地日历桶不是同一产品

UTC 15 分钟监控桶：

date_bin('15 minutes', occurred_at, '2001-01-01Z')

“纽约当地营业日”则需要先定义本地日期边界，再转换成两个绝对时刻作为查询范围。不要简单写：

(occurred_at AT TIME ZONE 'America/New_York')::date = :day

这虽然逻辑可读，却可能包裹分区键而失去裁剪。更好的应用流程：

input local date + IANA zone
  -> resolve local midnight and next local midnight
  -> obtain two timestamptz bounds
  -> query occurred_at >= lower AND occurred_at < upper

DST 切换日的两个 UTC 边界可能相差 23 或 25 小时，这恰好是正确的当地日。

窗口函数不是时间窗口状态机

SQL 窗口函数：

lag(occurred_at) OVER (
  PARTITION BY courier_id
  ORDER BY occurred_at, event_id
)

能在当前查询快照中比较相邻事件，适合轨迹间隔、停留候选和乱序审计。但它不会自动：

等待迟到事件；
维护跨批水位线；
修正已发送给外部系统的结果；
把无限事件流变成有界状态。

数据库增量表、物化视图、TimescaleDB continuous aggregate 或外部流系统可以承接不同职责。选择前仍要先固定 event time、lateness 与 correction 合同。

可执行验收

运行：

psql "service=pg36-admin" \
  -f static/labs/ch16/temporal-analysis.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/time-buckets.sql

关键事实：

duplicate_event=e003:2
late_event_ids=e001,e004
out_of_order_pair=e004->e005
utc_day8_events=7
partition_boundary=e008=...20260308;e009=...20260309

时间桶共 11 个，事件总数仍为 12，迟到总数仍为 2；12:00 桶含 e005/e006 两行。聚合后的总量与原始事实不守恒时，应先定位过滤、边界或重复处理，而不是调整索引。

本节判断线

进入下一节前，至少能完整回答：

哪一列是 event time？
哪一列是 ingest/processing time？
业务规则如何表示 valid time？
输入没有 offset 时由谁解释？
绝对时长在哪种类型上计算？
迟到阈值与水位线是什么？
重复 payload 冲突如何处理？
所有相邻区间采用什么端点合同？
本地日如何转换成可裁剪的绝对范围？

回答不完整时，不应先争论日分区还是小时分区，也不应先安装时序扩展。

返回本章目录 · 下一节：时序表与时间分区 · 查看全书目录 · 查看索引中心

16.2 时序表与时间分区

分区不是“数据带时间戳以后自然要做的事”。它是一项物理设计决策：

查询能否按边界排除大部分数据？
写入能否稳定路由？
唯一性与外键合同是否仍成立？
分区数量、索引数量和维护动作是否可控？
迟到数据会落到仍可写的历史分区吗？
删除一个时间段是否真的比普通 DELETE 更有价值？

第 4 章先建立类型、约束与分区 ADR。本节只把那套判断落实到事件时间场景，不把“按天分区”包装成默认答案。

16.2.1 从 ch04 的分区 ADR 选择时间键

分区键首先决定数据归属

配送事件有两个候选：

occurred_at  事件实际发生时间
received_at  系统接收时间

按 received_at 分区的好处是写入几乎总落到最新分区，创建和冻结历史分区容易；坏处是“某业务日发生的事件”会散布到以后到达的分区。

按 occurred_at 分区使业务日查询与保留自然对应，也能只扫描目标事件时间范围；代价是迟到和重放会写旧分区，旧分区不能简单变成永久只读。

本章 ADR 选择：

partition_key: occurred_at
partition_timezone: UTC
partition_strategy: native RANGE
partition_bounds: "[)"

理由不是“事件表都该这么做”，而是本 PoC 的主要查询与历史围栏连接都以事件发生时间为准。若法规要求按接收时间保留原始消息，可让 raw ingest 与规范事件采用不同分区键。

父表合同

核心定义摘自 setup.sql：

CREATE TABLE shop_ch16.delivery_event (
  event_id       text NOT NULL,
  occurred_at    timestamptz NOT NULL,
  received_at    timestamptz NOT NULL,
  courier_id     text NOT NULL,
  event_type     text NOT NULL,
  source_sequence bigint NOT NULL,
  location       geometry(Point, 4326) NOT NULL,
  location_geog  geography(Point, 4326)
    GENERATED ALWAYS AS (
      location::geography
    ) STORED,
  PRIMARY KEY (occurred_at, event_id)
) PARTITION BY RANGE (occurred_at);

主键包含 occurred_at，不是为了业务身份。PostgreSQL 在分区父表上建立 UNIQUE/PRIMARY KEY 时，约束列必须包含所有分区键列；这样每个叶分区的局部唯一索引才能共同证明父表范围内不重复。

业务要求的全局 event_id 唯一性由未分区的：

event_registry(event_id PRIMARY KEY, ...)

承担。这个模式把两个合同分开：

event_registry: 全局领域身份
delivery_event: 分区内物理身份与数据载荷

若只在每个叶分区上建 UNIQUE(event_id)，同一个 ID 仍可出现在不同分区。应用重试改变 occurred_at 时尤其危险。

半开日分区

CREATE TABLE shop_ch16.delivery_event_20260308
PARTITION OF shop_ch16.delivery_event
FOR VALUES FROM ('2026-03-08 00:00:00+00')
             TO ('2026-03-09 00:00:00+00');

边界含义是：

lower <= occurred_at < upper

fixture 验证：

e008 2026-03-08 23:59:59Z -> delivery_event_20260308
e009 2026-03-09 00:00:00Z -> delivery_event_20260309

若没有可接收某值的分区，向父表写入会失败。生产应提前创建未来分区并监控覆盖范围，不应依赖事故发生后手工补表。

UTC 边界与当地业务日

本章日分区是 UTC 日，不等于纽约当地日。纽约 2026-03-08 当地日可能跨越两个 UTC 分区：

local 2026-03-08 00:00 America/New_York
  -> 2026-03-08 05:00Z

local 2026-03-09 00:00 America/New_York
  -> 2026-03-09 04:00Z

查询仍然写两个绝对边界：

WHERE occurred_at >= :lower_timestamptz
  AND occurred_at <  :upper_timestamptz

规划器可能保留两个相关 UTC 分区，其余分区被裁剪。这比为每个用户时区建立分区可控得多。

裁剪取决于谓词与分区边界

正例：

SELECT event_id
FROM shop_ch16.delivery_event
WHERE occurred_at >=
        TIMESTAMPTZ '2026-03-08 00:00:00+00'
  AND occurred_at <
        TIMESTAMPTZ '2026-03-09 00:00:00+00';

time-pruned-plan.sql 的固定计划只有：

Seq Scan on delivery_event_20260308

这已经是成功的分区裁剪。叶表只有七行，顺序扫描是合理选择；“没有使用 B-tree”不影响裁剪已经生效。

反例：

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

time-wrapped-plan.sql 显示：

Append
  -> delivery_event_20260307
  -> delivery_event_20260308
  -> delivery_event_20260309

逻辑答案一样，物理工作不同。不要用“给表达式建索引”代替分区裁剪；索引可减少每张叶表内的扫描，却不一定让规划器排除叶表。

官方声明式分区文档区分规划期与执行期裁剪，也明确指出裁剪由分区边界驱动而非普通索引。

从目录验证，而不是从表名猜

SELECT
  child.relname,
  pg_get_expr(child.relpartbound, child.oid)
FROM pg_inherits AS inheritance
JOIN pg_class AS child
  ON child.oid = inheritance.inhrelid
WHERE inheritance.inhparent =
      'shop_ch16.delivery_event'::regclass;

partition-catalog.sql 同时采集边界、行数、最早/最晚事件、owner 与 marker。正式验收不应只检查三张名字像日期的表。

16.2.2 写入模式、冷热生命周期与保留

写入链先处理身份，再路由事实

本章确定性流程：

ingest_attempt
  -> validate event_id payload consistency
  -> choose canonical attempt
  -> insert event_registry
  -> insert delivery_event parent
  -> PostgreSQL routes by occurred_at

顺序很重要。如果先向分区事件表写入，再尝试全局注册，两个并发事务可能把同一业务 ID 写进不同叶表。生产可使用单事务、注册表 INSERT ... ON CONFLICT、显式状态机或消息 inbox/outbox 协调，但必须让全局身份争用发生在可证明唯一的位置。

为迟到写入保留窗口

按事件时间分区时，“旧”不等于“不再写”。生产 ADR 至少定义：

normal_lateness: 15m
accepted_lateness: 7d
manual_backfill: ticketed
partition_read_only_after: 14d
retention_after: 400d

数字取决于业务，重要的是分开：

正常迟到：自动接受并更新聚合；
允许迟到：接受但触发更正或告警；
超窗回补：需要显式作业、审计和容量窗口；
冻结：应用角色不再写，但受控管理员可能回补；
保留到期：可删除或归档。

若把“昨天的分区”每天 00:00 立刻设只读，e001 这类迟到事件会在正常链路失败。

不要让 DEFAULT 分区变成垃圾桶

DEFAULT 分区可以避免缺分区导致写入失败，但会引入新的责任：

为什么正常日期落入 default？
补建正式分区时怎样迁移而不长时间阻塞？
default 上的约束是否允许 attach 新分区？
查询是否意外长期扫描 default？
异常未来时间和损坏年份是否被悄悄接受？

一种稳健策略：

提前创建 N 个未来分区
监控 max upper bound 与当前时间的距离
没有 DEFAULT，缺口立即失败并报警
异常事件进入独立 quarantine

另一种策略可以保留受控 DEFAULT，但必须把行数、年龄和迁移作业作为一等监控。不存在普适答案。

分区粒度由约束共同决定

按小时、日、周或月选择时，至少估算：

每分区行数与字节
高频查询时间跨度
保留/归档的最小动作单位
迟到回补范围
每分区索引数
总分区数与规划开销
autovacuum/analyze 节奏
备份、恢复与副本应用成本

例如一年按日 365 张、每张 4 个索引，已经有约 1,460 个叶索引；若按小时，一年约 8,760 张表和数万个索引。小分区不自动更快，空或微小分区也有目录、锁、统计与规划成本。

本章用三张日分区只为让边界和计划可见，不构成生产粒度建议。

索引是叶分区成本

本章每张事件分区维护：

primary key (occurred_at, event_id)
B-tree     (courier_id, occurred_at, event_id)
GiST       (location geometry)
GiST       (location_geog geography)

三个叶表一共 12 个索引对象。分区越多，DDL、REINDEX、统计、磁盘 inode、缓存和故障面越大。

父分区索引能管理对应的叶索引集合，但 PostGIS、不同历史策略或在线构建流程仍可能需要逐叶控制。无论自动还是手工创建，都要从 pg_index 验证 indisvalid/indisready/indislive，不能只看 DDL 命令返回成功。

热、温、冷不是表空间颜色

可以按生命周期决定：

状态	可能动作
热	正常写、完整索引、频繁 analyze
温	低频回补、限制写角色、保留关键索引
冷	detach/归档、压缩、外部存储或只读集群
到期	经审批删除并保留删除证据

但每个动作都要回答恢复路径。DROP TABLE old_partition 很快，却会同时删除数据和局部索引；只有备份、归档与法规合同允许时才是正确保留策略。

PostgreSQL 支持 DETACH PARTITION，可让数据先脱离父表再归档或处理。在线动作的锁、并发、约束验证和版本差异应在接近生产的环境验证，本章 PoC 不演示线上表迁移。

删除与回补会影响 vacuum

时间序列通常“追加为主”，不等于没有 MVCC 成本：

重复处理可能执行冲突更新；
迟到修正会更新旧聚合；
围栏重算可能写结果表；
保留若使用大批 DELETE 会产生死元组和 WAL；
索引页仍会分裂、膨胀或缓存失衡。

分区级删除可以避免海量行删除，但活跃分区仍需 vacuum/analyze。后续第 28 章专门处理 vacuum、冻结与膨胀；本章只要求把这些成本列入 ADR。

16.2.3 原生分区、聚合与可选时序扩展

先列需求，再选能力

“这是时序数据，所以安装 TimescaleDB”不是决策。先问：

需求	PostgreSQL 原生基线	何时考虑专用扩展
时间范围裁剪	`RANGE` 分区	分片/自动 chunk 管理明显减负
普通时间聚合	`date_bin`、GROUP BY	continuous aggregate 有量化收益
预计算	物化视图、增量任务	自动刷新与失效模型更合适
保留	detach/drop 分区	policy 自动化降低运维风险
压缩/列式收益	外部归档、其他扩展/方案	压缩率与查询代价已实测
高写入	批量、COPY、schema/索引优化	chunk 并行与架构收益已验证

原生方案的优势：

能力随 PostgreSQL 一起交付；
依赖与升级边界较小；
SQL、备份和故障模型更接近核心数据库；
可以先建立可信基线。

扩展的优势可能包括自动 chunk、保留策略、压缩、时间函数和连续聚合，但也增加：

package supply
shared_preload_libraries (when required)
restart coordination
extension version matrix
backup/restore compatibility
major upgrade path
licensing and feature-tier review
replica node consistency

“少写运维脚本”有价值，但必须与新依赖成本一起衡量。

本章为何推迟 TimescaleDB

fixture 只有 12 行、三天数据。它能证明：

事件时间分区合同；
半开边界；
裁剪正反例；
聚合语义；
迟到和历史围栏连接。

它不能证明：

压缩比；
continuous aggregate 刷新成本；
大规模 chunk 规划；
写吞吐或副本延迟；
自动保留比受控分区作业更可靠。

所以 spatiotemporal-adr.md 把 TimescaleDB 标为 deferred，而不是反对。重开条件是压缩、保留、连续聚合或运维收益出现量化证据。

可选扩展仍要完整走交付链

在 Pigsty 中，时序扩展不是一句 CREATE EXTENSION：

Download / package availability
  -> Install on every L1 node
  -> Config / preload if required
  -> restart or rolling change
  -> CREATE EXTENSION in target database
  -> catalog and functional validation

Pigsty 当前 TimescaleDB 扩展页应作为目标 release 的供应入口；实际版本和 PG major/OS 可用性要在 inventory 中核对，不能从本章快照推断未来版本。

聚合真值仍来自时间合同

无论使用：

GROUP BY date_bin
materialized view
continuous aggregate
external stream processor

都必须固定：

event time 还是 processing time；
bucket origin 和 timezone；
[) 边界；
迟到水位线；
更正是否回写旧桶；
去重在哪一层发生；
结果版本与重建方式。

扩展能自动化计算，不会替业务决定这些语义。

用 A/B 迁移而不是信仰迁移

若要引入时序扩展，建议保留原生基线：

same frozen/replayed input
same semantic query set
same expected aggregate checksum
native path vs extension path

然后分别比较：

ingest throughput
query latency distribution
storage and WAL
compression/decompression
background job impact
replica lag
backup and restore
operational actions and failure recovery

只有语义结果一致后，性能数字才可比较。

16.2.4 不在本章重复在线分区化和维护细节

本章边界

本章从空 schema 创建三张固定分区，目的是教学验证，不处理已有大表的在线分区化。以下主题需要单独的迁移设计：

在写入不中断时建立新分区父表；
双写、触发器或逻辑复制；
历史数据分批回填；
ATTACH PARTITION 前的约束证明；
索引并发构建与父索引 attach；
外键、序列、权限、RLS 和依赖对象迁移；
切流、回退、校验和与旧表退役。

它们属于迁移、锁和运维章节，而不是时空语义入门。这里不提供一条貌似通用的 “在线改分区”命令，以免读者在生产大表上照抄。

分区维护也不应塞进应用请求

不要让第一条新日期写入在业务事务里执行 CREATE TABLE。DDL 会涉及锁、 catalog、权限、审计和副本传播。更合适的是受控作业：

discover current coverage
  -> propose future partitions
  -> create with exact owner/tablespace/options
  -> create/attach indexes
  -> analyze
  -> verify bounds and privileges
  -> emit evidence

删除旧分区也需要独立审批、备份/归档确认和 active worker 防护。

本章必须保留的判断力

即使篇幅有限，也不能删掉：

event/ingest/valid time 分离；
选择分区键的 ADR；
[) 边界；
全局 event_id 与分区主键分离；
直接谓词与包裹谓词的计划对照；
迟到写旧分区的成本；
原生能力与扩展收益的证据门槛；
分区数量乘以索引数量的运维成本。

可以删的是某一扩展的参数百科或某一版本的命令清单。基础判断一旦省掉，读者会把工具选择误当成时间模型。

本节验收

psql "service=pg36-admin" \
  -f static/labs/ch16/partition-catalog.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/time-pruned-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/time-wrapped-plan.sql

应看到：

delivery_event_20260307 rows=1
delivery_event_20260308 rows=7
delivery_event_20260309 rows=4

direct predicate -> only 20260308
wrapped predicate -> Append over all three

如果逻辑行数正确但计划扫描全部分区，先修谓词和边界；如果行路由错误，先修分区定义或事件时间。不要用更多索引掩盖语义错误。

上一节：时间语义先于时序扩展 · 返回本章目录 · 下一节：空间类型与坐标参考 · 查看全书目录 · 查看索引中心

16.3 空间类型与坐标参考

PostGIS 不只是“经纬度函数包”。它把空间对象、坐标参考、拓扑关系、距离模型、索引操作符和目录元数据带进 PostgreSQL。

学习顺序应当是：

业务对象
  -> 坐标参考与单位
  -> geometry / geography
  -> 合法性与边界规则
  -> 谓词
  -> 索引

若从“建一个 GiST”开始，很容易得到能够执行却单位错误的查询。

16.3.1 geometry、geography 与测量语义

两种类型回答不同计算问题

PostGIS 的核心空间类型：

类型	计算表面	距离/面积单位	典型用途
`geometry`	指定 CRS 的平面坐标	CRS 单位	拓扑、局部投影、丰富函数、空间索引
`geography`	地球曲面模型	米/平方米	全球经纬度上的距离、半径、面积

geometry(Point, 4326) 的坐标是经纬度角度。下面这条距离：

ST_Distance(point_a_4326, point_b_4326)

返回的是坐标系单位，也就是角度，不是米。把结果乘一个固定“每度米数”只在非常有限的局部近似下成立，且经度尺度随纬度变化。

将同一点作为 geography：

ST_Distance(
  point_a_4326::geography,
  point_b_4326::geography
)

才得到以米为单位的地表距离语义。PostGIS 官方空间查询章节说明 geometry 的平面计算与 geography 的大地计算差异。

不是所有列都要存两份

本章将规范值保存在 geometry：

location geometry(Point, 4326) NOT NULL

再生成 geography：

location_geog geography(Point, 4326)
  GENERATED ALWAYS AS (
    location::geography
  ) STORED

好处：

只有一个可写坐标事实；
geography 与 geometry 不会因应用漏更新而漂移；
geography 可建独立 GiST，米制查询不必每次临时转换；
生成表达式和类型可以从目录审计。

代价：

多一列存储；
写入要计算生成值；
多一个索引意味着更多 WAL、磁盘和缓存；
schema 将业务允许的 CRS 固定为 4326。

若米制查询很少，可以只存 geometry 并在查询中转换；若大多数查询都在一个适当局部投影内，也可以统一使用投影 geometry。要由查询和单位合同决定，不是机械地“双列最保险”。

typmod 把对象类型与 SRID 放进 schema

比较：

location geometry

和：

location geometry(Point, 4326)

后者让数据库拒绝非 Point 或非 4326 的值，使表结构本身表达坐标合同。本章围栏同样固定：

zone_geom geometry(Polygon, 4326)

如果业务允许 MultiPolygon，应明确写：

geometry(MultiPolygon, 4326)

或在接入时将 Polygon 规范化为 MultiPolygon。不要直到某个区域含离岛才临时修改客户端。

X/Y 是坐标轴，不是自动的“纬/经”

EPSG:4326 常见 WKT/GeoJSON 使用：

X = longitude
Y = latitude

本章点：

ST_SetSRID(
  ST_MakePoint(-74.00000, 40.71000),
  4326
)

即经度 -74、纬度 40.71。把两者颠倒仍可能落在各自合法数值范围内，数据库不一定能发现。

接入合同应写清：

format: longitude,latitude
x: longitude
y: latitude
srid: 4326
longitude_range: [-180, 180]
latitude_range: [-90, 90]

并用已知控制点做端到端验证，而不是只做数值范围检查。

geography 不是“更准确”的万能开关

geography 很适合：

“距离配送中心 1 km 内”；
跨较大区域的地表距离；
用经纬度数据直接得到米制结果。

但 geometry 仍常用于：

ST_Covers、ST_Intersects 等拓扑；
本地工程坐标和高精度投影；
更广的 PostGIS 函数集合；
需要明确平面模型的地图与分析。

问题不是哪种类型高级，而是哪种计算模型与业务问题一致。

本章距离证据

distance-semantics.sql 对三个中心寻找最近事件，并用 geography 计算米：

hub	最近事件	距离（四舍五入米）
airport	`e007`	0
central	`e001`	0
east	`e004`	423

三者都在 1 km 内。这里的 423 只验证单位与查询链，不是测绘级距离承诺。

16.3.2 SRID、投影、单位与坐标转换

SRID 是坐标参考身份

同样一对数：

(500000, 4500000)

在不同 CRS 中代表完全不同位置和单位。SRID 让 PostGIS 知道坐标属于哪个参考系统，并能查找转换定义。

检查：

SELECT
  ST_SRID(location),
  GeometryType(location)
FROM shop_ch16.delivery_event;

本章所有事件、中心和围栏都是 4326。

`ST_SetSRID` 只贴标签

ST_SetSRID(geom, 4326)

不会改变任何坐标数值。它适用于“这些数本来就是 4326，只是对象没有声明” 的场景。

真正转换：

ST_Transform(geom, target_srid)

会依据源 CRS 与目标 CRS 重新计算坐标。PostGIS ST_Transform 文档明确区分转换坐标与仅修改 SRID 标签。

危险反例：

-- 原始数值其实是 Web Mercator，却被错误贴成 WGS84
ST_SetSRID(mercator_numbers, 4326)

这不是近似误差，而是数据身份损坏。以后再 ST_Transform 只会把错误输入转换得更复杂。

混合 SRID 应当显式失败

本章故意执行：

SELECT ST_Intersects(
  ST_SetSRID(ST_MakePoint(0, 0), 4326),
  ST_SetSRID(ST_MakePoint(0, 0), 3857)
);

固定结果：

SQLSTATE XX000
Operation on mixed SRID geometries

srid-mismatch.sql 把错误作为验收证据。不要捕获这个错误后自动 ST_SetSRID 到另一边；系统无法仅凭数值知道哪边身份正确。

CRS 决定单位与失真

常见选择：

选择	优点	风险
EPSG:4326 geometry	交换广泛，保存经纬度自然	平面距离是角度
EPSG:4326 geography	米制地表距离直接	函数/性能模型与 geometry 不同
本地投影 geometry	局部距离、面积和形状可控	适用区域有限，需转换治理
EPSG:3857 geometry	Web 地图显示生态常见	距离/面积失真，非通用测量 CRS

Web Mercator 适合瓦片显示，不应仅因为前端地图使用它，就把业务距离也定义在 3857 平面上。

选投影需要：

业务覆盖区域；
容许失真；
距离、面积、方向还是拓扑；
数据供应者的 CRS；
跨区/跨国查询；
权威测绘要求。

这通常要由 GIS 专业人员与业务共同评审，而不是数据库管理员猜一个 EPSG 编号。

转换表达式与索引要一致

若查询反复写：

WHERE ST_DWithin(
  ST_Transform(location, :local_srid),
  :query_point,
  1000
)

原始 location GiST 通常不能直接服务这个转换表达式。选项包括：

存储/生成规范投影列并建索引；
建表达式索引；
先用原 CRS 的安全包围盒缩小候选，再精确转换；
使用 geography 的米制谓词。

表达式索引必须与查询表达式结构、SRID 和函数可索引条件一致。每行临时转换还会增加 CPU。不能只验证结果，不看计划。

坐标转换也要版本化

CRS 定义、网格文件和转换路径可能随 PROJ/PostGIS/操作系统包变化。高精度业务应在证据中保存：

PostGIS_Full_Version()
PROJ version
source and target SRID
transformation method/grid availability
control points and expected tolerance

本章没有外部权威坐标或网格文件，因此只固定 PostGIS 版本、SRID 和合成控制点，不声称测绘精度。

16.3.3 点、线、面、边界与有效几何

对象维度决定问题

对象	本章/配送中的例子	典型问题
Point	配送事件、中心	在哪里、离多远
LineString	轨迹、道路	长度、相交、沿线位置
Polygon	围栏	覆盖、包含、面积
Multi*	多片区域、分段轨迹	多个几何组成一个业务对象

用两列经纬度只能表示 Point，且无法让数据库知道它们共同构成一个空间对象。 PostGIS 类型让约束、函数和索引围绕整个对象工作。

Polygon 有内部、边界和外部

点对面的关系至少有三种：

interior
boundary
exterior

所以：

ST_Contains(polygon, point)
ST_Covers(polygon, point)

不是同义词。本章 e003 在边界：

ST_Covers   = true
ST_Contains = false
ST_Touches  = true

PostGIS ST_Covers 说明它允许 B 的点位于 A 的内部或边界，并会自动利用包围盒比较。

业务要先决定：

边界订单属于两个区、任一区还是某个优先区；
边界容差如何处理 GPS 噪声；
围栏之间是否允许重叠；
点恰好在洞边界如何解释；
版本换挡时边界与时间边界谁先判定。

SQL 只能实现已选规则。

有效几何是谓词的前提

自相交 Polygon、未闭合 ring、错误洞关系等无效几何可能让拓扑谓词产生意外结果。写入时至少验证：

CHECK (ST_IsValid(zone_geom))

本章还固定：

CHECK (NOT ST_IsEmpty(zone_geom))
CHECK (ST_SRID(zone_geom) = 4326)

必要时用：

ST_IsValidReason(geom)

定位原因。ST_MakeValid 可以尝试修复，但修复可能改变对象类型、拆成多个面或改变业务边界，不能在生产接入中无审计地自动覆盖原值。

空、NULL 与未知不同

NULL geometry  -> 未提供/未知
EMPTY geometry -> 已知为空的空间对象

它们在函数和聚合中的行为不同。本章业务事件和围栏都要求 NOT NULL 且非空，避免把“没有位置”误当成“位于任何区域之外”。如果设备可能不上传定位，应另设质量状态：

location_status = missing / invalid / approximate / verified

不要用 (0,0) 作为缺失哨兵；它是几内亚湾中的真实坐标。

轨迹不是无序点集

若把多个 Point 组成 LineString，顺序必须来自稳定合同：

ST_MakeLine(location ORDER BY occurred_at, event_id)

还应分段：

courier/session；
最大时间间隔；
设备重启；
不合理速度跳变；
SRID/质量状态。

跨越长间隔直接连线会制造从 A 到 B 的虚假直线。轨迹的 LineString 是一种派生产品，应可从原始事件和版本化规则重建。

经纬度数据也有许可证与版本

真实边界、道路、地址或兴趣点通常来自外部数据源。上线前要保存：

provider and dataset
version / snapshot date
license and attribution
allowed redistribution/use
source CRS
transformation chain
import checksum
update and rollback policy

本章坐标全部为项目自造的合成数据， fixture-manifest.json 明确 external_geodata=false。这让实验离线可复现，也意味着它不能证明真实地图数据质量。

建表前的空间合同

business_object: delivery event
geometry_type: Point
canonical_srid: 4326
coordinate_order: longitude, latitude
geometry_role: topology and index
geography_role: meter distance
null_policy: forbidden for canonical event
validity_policy: ST_IsValid and non-empty
boundary_policy: ST_Covers
external_dataset: none

这份合同完整后，下一节的谓词与索引才有确定含义。

本节验收

psql "service=pg36-admin" \
  -f static/labs/ch16/boundary-semantics.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/distance-semantics.sql

再检查生成列：

SELECT
  attname,
  attgenerated,
  format_type(atttypid, atttypmod)
FROM pg_attribute
WHERE attrelid =
      'shop_ch16.delivery_event'::regclass
  AND attname IN ('location', 'location_geog');

verify.sql 要求 location_geog.attgenerated = 's'，所有 geometry 与 geography 的 SRID 都是 4326，围栏全部非空且有效。任一项漂移，时空查询即使返回“看起来正确”的五行也不能通过。

上一节：时序表与时间分区 · 返回本章目录 · 下一节：空间谓词与索引 · 查看全书目录 · 查看索引中心

16.4 空间谓词与索引

空间查询不应从函数名猜语义。先把业务问题归类：

布尔拓扑：是否相交/覆盖/包含？
范围邻近：是否在给定距离内？
度量：具体距离或面积是多少？
排名：最近的 K 个是谁？

这四类问题的返回类型、边界、单位、索引方式和停止条件不同。

16.4.1 包含、相交、邻近与最近邻

拓扑谓词回答关系，不回答距离

常用关系：

谓词	问题
`ST_Intersects(a,b)`	两者是否共享任何点？
`ST_Disjoint(a,b)`	两者是否完全不相交？
`ST_Contains(a,b)`	B 是否位于 A 内部，且内部有共同点？
`ST_Within(a,b)`	A 是否位于 B 内，是 contains 的反向关系
`ST_Covers(a,b)`	B 是否没有任何点位于 A 外部？
`ST_Touches(a,b)`	是否只在边界接触、内部不相交？

对“事件属于围栏”：

ST_Covers(zone.zone_geom, event.location)

参数顺序是：

area first, point second

若使用反向表达，可写 ST_CoveredBy(point, area)。不要因为 ST_Intersects 参数对称，就假设所有空间谓词都对称。

边界政策决定 contains 还是 covers

固定探针：

SELECT
  ST_Covers(zone_geom, location),
  ST_Contains(zone_geom, location),
  ST_Touches(zone_geom, location)
FROM ...
WHERE event_id = 'e003';

结果：

covers=true, contains=false, touches=true

这不是函数争论，而是业务选择：

业务规则	更接近的表达
边界也算在服务区	`ST_Covers`
必须严格位于内部	`ST_Contains`
只找边界点	`ST_Touches`
任何接触都算	`ST_Intersects`

boundary-semantics.sql 还证明同一点在围栏扩张前后得到不同结果，说明时间版本不能被省略。

邻近筛选使用 `ST_DWithin`

“在中心 1 km 内”是布尔资格：

WHERE ST_DWithin(
  event.location_geog,
  hub.location::geography,
  1000
)

对 geography，距离参数以米为单位。对 geometry，距离参数使用 CRS 单位。 PostGIS ST_DWithin 说明该函数包含可利用索引的包围盒比较，然后执行距离判断。

不要写成：

WHERE ST_Distance(a, b) <= 1000

再期待同样的索引路径。ST_Distance 必须为候选计算具体值；ST_DWithin 针对阈值问题设计，规划器可使用对应空间操作符缩小候选。

距离值与距离资格分开

常见响应同时需要资格与显示距离：

SELECT
  event_id,
  ST_Distance(location_geog, :hub_geog) AS meters
FROM shop_ch16.delivery_event
WHERE ST_DWithin(location_geog, :hub_geog, 1000)
ORDER BY meters, event_id;

先用 ST_DWithin 过滤，再为较小集合计算/排序距离。SQL 表达式仍可能被优化器重排，但逻辑合同清楚，且索引条件可见。

距离函数还涉及：

geography 使用 spheroid 还是 sphere；
2D 还是 3D；
误差容忍；
坐标精度与 GPS 噪声；
是否按路网距离而非直线距离。

本章只验证 2D 地表直线距离，不做路线规划。

稳定 tie-breaker 不能省

多个对象可能与查询点等距。实验和分页都要追加：

ORDER BY distance, event_id

否则同距离对象的顺序未定义。空间索引也不会替你创造业务唯一顺序。

最近点不等于最近路径

两点直线很近，可能隔着河流、围墙或单行路网。若问题是配送 ETA/路径，应引入：

权威路网；
拓扑连接；
交通规则和时间版本；
路径算法；
地图匹配；
实际行驶数据校准。

PostGIS 点距离只能作为几何近似，不能自动变成路由引擎。

16.4.2 包围盒过滤与精确计算

空间索引保存可搜索近似

复杂 Polygon 可能有成千上万个顶点。每行都执行精确拓扑会很贵。常见路径：

bounding box candidate filter
  -> exact geometry predicate

包围盒是包住对象的轴对齐矩形。两个对象的包围盒不相交，则对象必不相交；包围盒相交，只说明它们可能相交。

因此：

bbox reject = 可以安全排除
bbox match  = 仍需精确判断

命名谓词会加入索引友好的初筛

PostGIS 对一组常见命名谓词自动加入包围盒条件，例如本章使用的：

ST_Covers
ST_DWithin

固定 GiST 计划中可以看见：

Index Cond:
  location_geog && _st_expand(query_geography, 1500)

Filter:
  st_dwithin(location_geog, query_geography, 1500, true)

Index Cond 缩小候选，Filter 执行精确距离。本章联合计划中：

Index Cond:
  location @ zone.zone_geom

Filter:
  st_covers(zone.zone_geom, location)

内部操作符展示可能随版本和计划格式变化，工程上应关注“索引候选 + 精确谓词”结构，而不是把某一行文本当 API。

PostGIS 官方空间索引与查询列出会自动利用空间索引的函数，并解释两阶段比较。

手工 `&&` 只回答包围盒

WHERE geometry_a && geometry_b

只测试二维包围盒重叠。它适合：

明确只需要视窗候选；
分阶段调试；
为后续自定义精确计算生成候选。

它不等于 ST_Intersects。对凹多边形、带洞区域或长斜线，包围盒会包含大量实际不相交对象。

不要为了“更快”把精确谓词删掉，除非业务合同本来就只需要 bbox。

lossy/recheck 是正常行为

GiST 等索引可能返回需要 heap recheck 的候选。看到计划中的：

Recheck Cond
Rows Removed by Filter

不表示索引错误，而是近似索引和精确关系的正常分工。应观察：

候选数量与最终命中数量；
recheck 比例；
几何复杂度；
选择率估计；
heap page 命中；
查询半径和区域大小。

若一个巨大 Polygon 的 bbox 覆盖整座城市，空间索引无法凭 bbox 排除很多点。可考虑细分几何、预计算层级网格或业务分区，但任何近似都要保留精确复核或明确误差合同。

无效几何会破坏前提

官方 ST_Covers 文档提醒不要对无效 geometry 期待可靠结果。索引只会让错误候选更快地产生。接入时：

CHECK (ST_IsValid(zone_geom))

并保留 ST_IsValidReason 证据，比查询时临时修复更可控。

扩张半径与单位必须一致

geometry 上：

ST_Expand(point_4326, 1000)

会按度扩张 1000，不是 1000 米，几乎覆盖全球。不要把 geography 的米参数直觉套到 geometry 函数。

本章 geometry SP-GiST probe 使用：

ST_DWithin(location, point_4326, 0.02)

这里 0.02 是度，仅用于证明 operator class 路径；业务 1 km 查询使用 geography 和 1000 米。

二阶段也适用于跨类型方案

若业务必须在局部投影做高精度计算，可以：

cheap canonical-CRS bbox
  -> smaller candidate set
  -> ST_Transform
  -> exact projected calculation

但粗筛边界必须是保守的，不能漏掉真值。跨 CRS 的安全包围盒设计需要处理投影非线性和区域边缘，不能简单转换两个角点就默认安全。

16.4.3 GiST/SP-GiST 计划与选择率验证

访问方法不是单独的“空间索引类型”

PostgreSQL 索引能力由：

access method + operator class + data type + operator/query

共同决定。本章目录：

对象	access method	operator class
围栏 geometry	GiST	`gist_geometry_ops_2d`
事件 geometry	GiST	`gist_geometry_ops_2d`
事件 geography	GiST	`gist_geography_ops`
中心 Point	SP-GiST	`spgist_geometry_ops_2d`
围栏有效期约束	GiST	`gist_text_ops, range_ops`

因此“建了 GiST”信息不完整。还要知道列、opclass、维度和目标查询。

GiST 与 SP-GiST 的直觉边界

GiST 是通用搜索树框架，PostGIS 常用它管理几何包围盒，也支持 geography 和 KNN 等相应 operator class 能力。

SP-GiST 将空间递归划分，适合某些可分区的数据结构与分布。本章用 spgist_geometry_ops_2d 为三个 Point 建索引，并只证明 ST_DWithin 产生该索引路径。

不要从 access method 名字推导所有能力。本章未声称这个 SP-GiST opclass 服务 <-> KNN；最近邻是否走索引必须对目标版本、类型、opclass 和实际查询看计划。

建索引

CREATE INDEX event_20260308_location_gist_idx
ON shop_ch16.delivery_event_20260308
USING gist (
  location shop_ch16_ext.gist_geometry_ops_2d
);

CREATE INDEX event_20260308_geog_gist_idx
ON shop_ch16.delivery_event_20260308
USING gist (
  location_geog shop_ch16_ext.gist_geography_ops
);

CREATE INDEX delivery_hub_location_spgist_idx
ON shop_ch16.delivery_hub
USING spgist (
  location shop_ch16_ext.spgist_geometry_ops_2d
);

本地实验把 PostGIS 安装到 shop_ch16_ext，所以 opclass 与操作符都显式 schema 限定。生产可选择 public 或受控扩展 schema，但搜索路径、迁移工具和 ORM 必须与之兼容。

目录验收

psql "service=pg36-admin" \
  -f static/labs/ch16/index-catalog.sql

index-catalog.sql 固定 13 个管理索引，并验证：

access method
all operator classes
indisvalid
indisready
indislive
index bytes
object marker

其中：

3 geography GiST
4 geometry GiST (3 event + 1 geofence)
1 geometry SP-GiST
1 mixed text/range GiST exclusion
4 B-tree

主键自动索引另由 34 个关系对象白名单验收，不混进“本章主动选择的 13 个索引”计数。

为什么计划探针关闭顺序扫描

fixture 只有 3 个中心、4 个围栏和 12 个事件。正常成本模型选择 Seq Scan 很合理。为了证明路径存在，探针执行：

SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, ...);

固定结果：

spatial-gist-plan
  -> event_20260308_geog_gist_idx

spatial-spgist-plan
  -> delivery_hub_location_spgist_idx

joint-plan
  -> geofence_version_no_overlap
  -> event_20260308_location_gist_idx

这只证明：

query/operator/opclass/index are compatible

不证明：

planner should choose it at realistic scale
index is faster
estimated selectivity is accurate
cache/WAL/write cost is acceptable

生产选择率验证

生产候选应在代表性数据上运行：

EXPLAIN (
  ANALYZE,
  BUFFERS,
  WAL,
  SETTINGS,
  VERBOSE
) ...

对比：

estimated rows vs actual rows
index candidates vs exact matches
heap/index blocks
cache warm/cold
radius/区域大小分布
不同租户与城市的数据倾斜
并发下延迟

空间选择率高度依赖数据分布。城市中心密集、郊区稀疏；巨大围栏和小围栏的 bbox 过滤能力不同。一个平均值无法代表所有查询。

统计与维护

装载或大批更新后：

ANALYZE shop_ch16.delivery_event;
ANALYZE shop_ch16.geofence_version;

还要观察：

autovacuum/analyze 是否覆盖每个叶分区；
历史分区统计是否陈旧；
PostGIS 列统计目标是否足够；
索引膨胀与重建窗口；
写放大与 WAL；
副本 replay 延迟；
新分区是否漏建空间索引。

父表有索引声明不等于每个未来分区都满足预期，自动化应从目录持续核对。

本节反例清单

以下说法都不足以作为上线结论：

“EXPLAIN 里出现 GiST，所以很快”
“用了 geography，所以最准确”
“SP-GiST 比 GiST 新，所以更好”
“ST_Distance 能算距离，所以能用索引筛半径”
“包围盒命中就是空间相交”
“12 行强制 Index Scan 比 Seq Scan 快”

正确结论必须包含语义、类型/SRID、operator class、计划、代表性规模和实际测量。

上一节：空间类型与坐标参考 · 返回本章目录 · 下一节：时空联合查询是本章收束目标 · 查看全书目录 · 查看索引中心

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

时空查询不是“时间 WHERE + 空间 WHERE”这么简单。历史围栏场景至少有三项同时成立：

event.occurred_at 在请求时间段内
zone.valid_during 包含 event.occurred_at
zone.geometry 覆盖 event.location

第一项选择事件分区，第二项选择当时规则版本，第三项执行空间关系。少任何一项，答案都可能看起来合理却在历史边界上出错。

16.5.1 某时段、某区域内的配送事件

先把业务问题写完整

目标：

找出 2026-03-08 UTC 日内，事件发生时属于 central 围栏的配送事件。

完整 SQL：

SELECT
  event.event_id,
  event.occurred_at,
  zone.zone_id,
  zone.version
FROM shop_ch16.delivery_event AS event
JOIN shop_ch16.geofence_version AS zone
  ON zone.valid_during @> event.occurred_at
 AND ST_Covers(zone.zone_geom, event.location)
WHERE event.occurred_at >=
        TIMESTAMPTZ '2026-03-08 00:00:00+00'
  AND event.occurred_at <
        TIMESTAMPTZ '2026-03-09 00:00:00+00'
  AND zone.zone_id = 'central'
ORDER BY event.occurred_at, event.event_id;

固定结果：

e002 central v1
e003 central v1
e005 central v2
e006 central v2
e008 central v2

e004 与 e005 位于同一点附近：

e004 occurred 11:55 -> central v1 -> outside
e005 occurred 12:00 -> central v2 -> inside

如果查询只连接 max(version)，两条都会按 v2 判断，历史答案被今天的规则重写。

时间范围约束放在事件时间

应用可能请求“纽约当地 3 月 8 日”。接口层先将当地日解析为两个 timestamptz 参数：

lower = 2026-03-08 05:00:00Z
upper = 2026-03-09 04:00:00Z

SQL 仍是：

event.occurred_at >= :lower
AND event.occurred_at < :upper

不要在列上转换时区或取 date。参数计算与存储查询分层后，既保留当地日语义，也保留分区裁剪机会。

围栏版本也使用半开区间

zone.valid_during @> event.occurred_at

@> 依据 range 自身端点规则。v1 的上界不包含 12:00，v2 的下界包含 12:00，因此不需要：

event.occurred_at BETWEEN valid_from AND valid_to

BETWEEN 两端都包含，会让相邻版本在换挡时刻同时命中。用 range 可以把端点合同保存在数据中。

空间边界可能产生多归属

本章允许相邻围栏共享边界，ST_Covers 又包含边界，所以 e003 同时命中：

central v1
east v1

这意味着：

count(*) FROM event_zone_membership

可以大于事件数。固定 12 个事件得到 14 条 membership。若聚合“各区事件数” 后求和，不能假设等于全局事件数。

需要唯一归属时，可以定义：

zone priority
smallest area first
explicit ownership of shared boundary
pre-topologized non-overlapping polygons
deterministic row_number() tie-break

但任何规则都会改变业务含义，应版本化并进入 ADR，而不是在报表 SQL 中随机 DISTINCT ON。

视图是可复用语义，不是性能保证

本章创建：

CREATE VIEW shop_ch16.event_zone_membership AS
SELECT ...
FROM delivery_event AS event
JOIN geofence_version AS zone
  ON zone.valid_during @> event.occurred_at
 AND ST_Covers(zone.zone_geom, event.location);

应用读取：

SELECT event_id, zone_id, zone_version
FROM shop_ch16.event_zone_membership
WHERE occurred_at >= :lower
  AND occurred_at <  :upper
  AND zone_id = :zone;

普通 view 保存查询定义，规划器通常会展开优化；它不缓存结果，也不保证每次选择相同计划。权限上，本章只授予 pg36_app 对父表、中心和三个视图的 SELECT，不授予任何写权限。

app-query.sql 以应用角色返回固定五行； app-write.sql 更新事件固定失败为 SQLSTATE 42501。

参数、权限与租户必须先过滤

真实查询还可能需要：

AND event.tenant_id = :tenant
AND zone.tenant_id = :tenant
AND event.courier_id = ANY(:allowed_couriers)

空间命中不能越过租户和授权边界。若使用 RLS，要验证：

view 的 security invoker/definer 行为；
空间函数是否泄露错误或执行时间信息；
查询计划是否在权限过滤后仍可接受；
plan cache 对不同租户选择率的影响。

本章单租户 fixture 不声称覆盖这些生产边界。

空间输入也要设限

若 API 允许用户上传任意 Polygon：

顶点数可能巨大；
geometry 可能无效；
SRID 可能错误；
bbox 可能覆盖全球；
拓扑计算可消耗大量 CPU；
WKT/GeoJSON 大小可能成为滥用入口。

接口应限制字节、顶点、对象类型、SRID、区域范围和 statement timeout，并在受控流程中验证/规范化。不能因为 PostGIS 函数是 SQL，就把它当廉价谓词。

16.5.2 轨迹、停留、地理围栏与迟到修正

轨迹首先是有序事件序列

最小查询：

SELECT
  courier_id,
  event_id,
  occurred_at,
  location,
  lag(occurred_at) OVER courier_order AS previous_at,
  lag(location)    OVER courier_order AS previous_location
FROM shop_ch16.delivery_event
WINDOW courier_order AS (
  PARTITION BY courier_id
  ORDER BY occurred_at, source_sequence, event_id
);

稳定顺序由三项共同提供：

occurred_at
source_sequence
event_id

单用 timestamp 可能同值；单用来源序列无法跨来源解释实际时间；event ID 用于最后确定 tie。

先分段，再连线

生成轨迹：

ST_MakeLine(location ORDER BY occurred_at, event_id)

只对已确定的 segment 安全。分段条件可能包括：

courier/session 改变；
相邻事件间隔超过阈值；
设备重启或 sequence 回退；
推算速度超过物理上限；
位置质量从 verified 变成 missing；
数据跨过不可连接的业务状态。

若从 10:00 的北京点直接连到 18:00 的上海点，LineString 只画出一条直线，并没有证明实际路径。

停留是派生规则

“在一个区域停留十分钟”需要同时定义：

distance threshold
minimum duration
sampling gap tolerance
entry/exit boundary policy
GPS accuracy
late event correction
segment identity

一种简单候选：

consecutive points within R meters
and max(time)-min(time) >= D
and every gap <= G

但稀疏采样只能证明观测点，不能证明两点之间始终停留。生产应把结果标为推断，保存算法版本和输入范围。

地理围栏事件有三种生成方式

方式	特点
查询时计算 membership	总能使用最新修正，查询成本高
写入时计算并存结果	读快，但迟到/围栏修订要更正
批/流增量派生	可控重算，增加状态与作业

本章 view 使用查询时计算，最容易证明语义。生产可以物化：

event_zone_result (
  event_id,
  zone_id,
  zone_version,
  predicate_version,
  computed_at,
  source_checksum,
  ...
)

不能只存 event_id, zone_id。至少要知道使用哪个围栏版本、哪套边界算法和哪批输入。

入围/出围不是两个独立点

若连续位置从 outside 变成 inside，可派生 enter；inside 变 outside 可派生 exit。但 GPS 抖动会在边界反复切换。常见稳健化：

进入与退出使用不同阈值（hysteresis）；
要求连续 N 个样本；
使用定位精度圆而不是无误差 Point；
对边界附近状态标记 uncertain；
限制最大采样间隔；
保存原始点以便重算。

ST_Covers 只定义单点关系，不自动解决状态机抖动。

迟到事件会插入历史中间

本章 e004 比 e005 早发生却后到。若系统先看到 e005 并已生成轨迹/围栏状态，e004 到达后应：

insert raw/canonical fact
identify affected courier + time neighborhood
recompute local segment or bucket
version or retract previous derived result
emit correction evidence

不能只在列表末尾追加。否则 processing order 被误当成 event order。

围栏修订也会重写历史

若业务在 3 月 10 日修订“central v2 从 3 月 8 日 12:00 生效”，至少有两种政策：

retroactive truth:
  重算历史 event-zone membership

as-known-at-the-time:
  保留当时系统认知，并另存修订版本

前者适合最终业务事实，后者适合审计。需要两者时，应同时建 valid time 与 system time，而不是在原行上静默覆盖。

重算范围要可证明

对于变更围栏 Polygon：

affected time = old/new valid range union
affected space = old/new bbox union
candidate events = time range AND bbox
exact changes = compare old/new predicates

这正是时空联合过滤的另一个用途。先用时间与 bbox 缩小候选，再对旧/新几何执行精确关系，可避免全表重算；但必须保留旧 geometry 或可恢复版本。

派生结果不应覆盖原始事实

建议层次：

raw attempts
  -> canonical events
  -> normalized/quality-assessed locations
  -> zone memberships / trajectories / stays
  -> aggregates and alerts

每层保存：

输入版本或 checksum；
算法/规则版本；
计算时刻；
可重建路径；
更正/撤回身份。

把“是否在围栏内”直接写回唯一事件行且不留版本，会让历史无法审计。

16.5.3 时间裁剪、空间索引与二阶段过滤

两条独立缩小路径

联合查询的候选空间可以理解为：

all events
  -> partition pruning by requested event-time range
  -> spatial bbox candidates within surviving partitions
  -> exact zone valid-time + ST_Covers filters
  -> final rows

固定联合计划：

Nested Loop
  -> Index Scan geofence_version_no_overlap
       Index Cond: zone_id = 'central'
  -> Index Scan event_20260308_location_gist_idx
       Index Cond: location @ zone_geom
       Filter:
         occurred_at in day8
         zone.valid_during @> occurred_at
         st_covers(zone_geom, location)

最关键的不是 Nested Loop，而是：

only delivery_event_20260308 appears
geometry GiST supplies candidates
valid-time and exact covers remain visible filters

joint-plan.sql 保存完整证据。

SQL 书写顺序不等于执行顺序

把时间谓词写在 WHERE 第一行不会强制数据库先执行它。PostgreSQL 规划器会根据等价变换与成本选择路径。我们能做的是：

写出可推导的直接分区键范围；
使用有索引语义的空间谓词；
保持统计新鲜；
在真实参数分布下检查计划；
必要时调整模型、索引或查询边界；
不把关闭 planner 开关当生产提示。

“先时间后空间”是逻辑与候选设计，不是靠 SQL 行顺序控制算子。

prepared statement 也要看参数计划

应用通常使用参数：

WHERE occurred_at >= $1
  AND occurred_at <  $2
  AND zone_id = $3

PostgreSQL 可能使用 custom 或 generic plan。执行期裁剪可以根据参数移除分区，但不同参数选择率仍可能使通用计划不理想。生产验证应包括：

EXPLAIN EXECUTE with narrow range
EXPLAIN EXECUTE with wide range
generic/custom plan behavior
plan cache and connection pool settings

不要只在 psql 常量查询上验收，然后假设 ORM prepared statement 完全相同。

先过滤围栏还是先过滤事件取决于基数

本章只有两个 central 版本和七个 day8 事件，Nested Loop 很自然。现实中：

一个 zone + 短时间：先找 zone 再扫事件空间索引可能好；
许多 zone + 一个事件：对事件点查围栏索引可能好；
巨大 polygon：bbox 候选可能很多；
大半径 geography：空间选择率可能很低；
多租户：tenant/zone 复合过滤会改变基数。

应从业务参数分布测量，不应固定 join order。

范围排他索引兼任查找路径

geofence_version_no_overlap 原本为约束创建：

(zone_id gist_text_ops, valid_during range_ops)

联合计划也用它查 zone_id。一个索引可以同时承担约束与查询，但这不保证它覆盖所有查询。若主要模式是：

WHERE zone_id = ?
  AND valid_during @> ?

应在真实规模验证该复合 GiST 的选择率和代价，再决定是否需要其他索引。

大查询要显式预算

若请求：

all zones
all events
five years
global polygon

时间与空间索引都无法制造高选择率。接口必须限制：

最大时间跨度；
最大区域/半径；
zone 数；
返回行数与分页；
statement timeout；
并发与资源组；
是否异步导出。

索引不是资源治理替代品。

验收逻辑结果与计划结果

先验收结果：

psql "service=pg36-admin user=pg36_app" \
  -f static/labs/ch16/app-query.sql

应为：

e002 central 1
e003 central 1
e005 central 2
e006 central 2
e008 central 2

再验收计划：

psql "service=pg36-admin" \
  -f static/labs/ch16/joint-plan.sql

最后验收全量 membership：

psql "service=pg36-admin" \
  -f static/labs/ch16/zone-membership.sql

必须是 14 行，并保留 e003、e005、e006 的双区域命中。只对五行 central 结果做截图不足以证明边界、多归属和版本语义。

本节收束

一条可交付的时空查询结论应包含：

event-time bounds and timezone
valid-time range policy
geometry/geography and SRID
boundary predicate
multi-membership policy
logical expected rows/checksum
partition pruning evidence
spatial index candidate evidence
exact predicate evidence
representative-scale performance limits
late/revision recomputation policy

这十项比“用了 PostGIS + 分区”更接近生产合同。

上一节：空间谓词与索引 · 返回本章目录 · 下一节：时空扩展的交付与观察 · 查看全书目录 · 查看索引中心

16.6 时空扩展的交付与观察

本地执行一条 CREATE EXTENSION postgis，只能证明当前实例已有可用控制文件与动态库。生产交付要回答：

所有数据库节点是否有同一包？
扩展是否需要 preload/restart？
在哪些数据库、哪个 schema 创建？
谁持有 extension，谁能调用？
备份恢复目标是否预装兼容版本？
主备切换后新主是否具备同一二进制能力？
升级、回退和监控由谁负责？

Pigsty 提供扩展供应与数据库声明的实现路径；PostgreSQL/PostGIS 目录仍是最终验收事实。

16.6.1 安装 PostGIS 与可选时序扩展

四个阶段不能合并

Pigsty 把扩展生命周期概括为：

Download -> Install -> Config -> Create

对应工程问题：

阶段	验收
下载/解析	目标 Pigsty、OS、PG major 有哪个包版本
安装	每个 L1 节点都有控制文件、SQL 和动态库
配置	preload、GUC、重启和资源参数一致
创建	目标数据库 `pg_extension` 中有正确对象

只做 Create，在当前主库可能成功，但切换到缺二进制的副本后函数会失败；只装包，则数据库里还没有类型、函数和 operator class。

参考 Pigsty 当前扩展概览、包别名与创建扩展。

package alias 与 SQL extension name 不一定相同

例子：

package alias: postgis
SQL extension: postgis

package alias: timescaledb
SQL extension: timescaledb

package alias: pgvector
SQL extension: vector

不要从 SQL 名猜操作系统包名。包还随：

Pigsty release
Linux distribution
architecture
PostgreSQL major
repository snapshot

变化。生产 inventory 应同时记录 package alias、解析后的实际包、版本和 SQL extension。

本章 Pigsty 声明

pigsty-declaration.example.yml 是合并片段，不是完整生产配置：

all:
  vars:
    pg_version: 18

    pg_extensions:
      - postgis

    pg_databases:
      - name: pg36_shop
        owner: pg36_owner
        schemas:
          - { name: app_ext, owner: pg36_owner }
        extensions:
          - { name: btree_gist, schema: app_ext }
          - { name: postgis, schema: app_ext }

三层含义：

pg_extensions
  -> cluster 节点供应哪些额外软件包

pg_databases[].schemas
  -> 数据库内准备哪些受控 schema

pg_databases[].extensions
  -> 在该数据库创建哪些 SQL extension

btree_gist 属于 PostgreSQL contrib，通常随主包集合供应；仍要从目标节点的 pg_available_extension_versions 验证，不能只根据经验省掉。

本地实验为何不用 `public`

本地 PoC 安装到：

shop_ch16_ext

并将数据放在：

shop_ch16

好处是扩展对象与业务对象边界清楚，reset 可分别验证依赖；代价是操作符、类型和 opclass 常要显式 schema 限定：

location shop_ch16_ext.gist_geometry_ops_2d
location OPERATOR(shop_ch16_ext.<->) other
point::shop_ch16_ext.geography

生产可选择 public、app_ext 或其他标准，但要评审：

extension 是否支持指定/迁移 schema；
ORM、迁移器和 SQL 是否会限定类型/操作符；
search_path 是否包含可被低权限用户写入的 schema；
备份恢复是否重建同一 namespace；
多数据库是否遵循同一约定。

PostGIS 在本章版本中不可 relocatable，创建时 schema 选择更应提前确定。

trusted 与 superuser 边界

目录快照：

extension	version	trusted	relocatable	本地 owner
`btree_gist`	1.8	true	true	`pg36_owner`
`postgis`	3.6.4	false	false	管理员

btree_gist 是 trusted extension，满足数据库权限的非超级用户可以安装； PostGIS 非 trusted，本章由管理员创建。应用角色 pg36_app 永远不获得 CREATE 或扩展 owner 权限，只得到两个 schema 的 USAGE 与受控对象 SELECT。

目录证据来自：

psql "service=pg36-admin" \
  -f static/labs/ch16/extension-catalog.sql

不要把“应用需要调用 PostGIS 函数”误解为“应用要拥有 PostGIS”。

PostGIS 不要求 preload，TimescaleDB 要单独评审

本章 PostGIS 路径不修改 shared_preload_libraries。可选 TimescaleDB 分支示意：

pg_extensions:
  - postgis
  - timescaledb

pg_libs: 'timescaledb, pg_stat_statements, auto_explain'

pg_databases:
  - name: pg36_shop
    extensions:
      - { name: timescaledb, schema: public }

这段故意没有在基线启用。TimescaleDB 涉及包、preload、重启和数据库对象，必须走集群变更窗口。以目标 Pigsty release 的 TimescaleDB 扩展页为准。

声明后回到 SQL 验收

SELECT
  e.extname,
  e.extversion,
  n.nspname,
  pg_get_userbyid(e.extowner),
  e.extrelocatable
FROM pg_extension AS e
JOIN pg_namespace AS n
  ON n.oid = e.extnamespace
WHERE e.extname IN ('postgis', 'btree_gist');

功能探针至少包括：

SELECT PostGIS_Full_Version();
SELECT ST_SRID(ST_SetSRID(ST_MakePoint(0, 0), 4326));
SELECT tstzrange(now(), now() + interval '1 hour', '[)');

再执行本章边界、距离、索引计划和排他约束。版本存在不等于业务路径可用。

16.6.2 核对版本、依赖、备份和升级边界

版本是矩阵，不是一个数字

发布证据应保存：

Pigsty release
OS distribution and architecture
PostgreSQL major/minor
PostGIS extension version
PostGIS library/full version
GEOS / PROJ / GDAL versions when relevant
btree_gist version
package NEVRA/deb identity
all L1 node checksums or package versions

本章正式证据固定：

PostgreSQL 18.6
PostGIS 3.6.4
btree_gist 1.8
Pigsty reference 4.4
Pigsty L1 run not executed

最后一行很重要：直接 PostgreSQL 验收不能冒充 Pigsty 集群验收。

Pigsty 当前 PostGIS 扩展目录页用于查看目标 release 的包可用性；版本会演进，不能把本章数字当长期默认。

主备所有 L1 节点必须一致

物理复制会把数据库页和 WAL 变更带到副本，却不会分发操作系统扩展包。备库执行扩展查询、恢复后开放查询或升主继续服务时，仍依赖本地兼容的控制文件、动态库及其依赖。

上线前为每个节点保存矩阵：

host	role	PG	package	control file	shared library	preload
pg-1	primary
pg-2	replica
pg-3	replica

任一行不同，应先修供应层。不要等故障切换后才发现新主缺 postgis 动态库。

扩展依赖是数据库对象图

CREATE EXTENSION postgis 注册大量：

types
functions
operators
operator classes/families
casts
metadata tables/views

它们通过 pg_depend 与 pg_extension 关联。本章 reset 在删除扩展前验证 shop_ch16_ext 的关系、类型、函数、操作符和 opclass 都是合法扩展成员或扩展表的自动对象。若出现外来对象，停止而不是 DROP ... CASCADE。

这避免两个风险：

把用户误建在扩展 schema 的对象一起删除；
扩展对象身份漂移后仍声称复位安全。

备份不是只备 geometry 列

恢复要同时具备：

compatible PostgreSQL
compatible extension packages
CREATE EXTENSION path/control files
same or supported extension version
database data and extension membership
required CRS/grid resources
roles, schemas, privileges, search_path

pg_dump 会按扩展成员关系处理对象；恢复环境必须先能供应相容扩展。物理备份同样要求目标运行环境可加载相应库。

发布前至少做一次隔离恢复：

新建与生产隔离的 Pigsty/PG 环境；
安装声明版本；
恢复角色、schema、扩展和数据；
核对 PostGIS_Full_Version()；
运行 SRID、有效性、边界、距离与空间索引计划；
对关键表做逻辑行数和 checksum；
演练主备切换后的相同查询。

“备份任务成功”不证明 PostGIS 查询已可恢复。

扩展升级与 PostgreSQL 大版本升级分开设计

可能的变化轴：

PostGIS package version
ALTER EXTENSION ... UPDATE
GEOS/PROJ dependency
PostgreSQL major
Pigsty release
OS major

一次同时改变所有轴，失败后很难归因。稳健流程：

read target compatibility notes
freeze source evidence
test package/extension upgrade in clone
run functional and checksum suite
test backup/restore
test replica and failover
measure plan and performance regression
prepare supported rollback
roll through L1 nodes under change control

某些 extension update 不可简单降级。回退可能依赖恢复旧集群/备份或蓝绿切流，不能默认执行 ALTER EXTENSION 反向版本。

扩展 schema 与 `search_path` 是安全边界

本章上下文固定：

SET search_path = pg_catalog;

所有数据对象、类型、函数与操作符显式限定。这样可以避免低权限用户在 search_path 前端 schema 创建同名函数，影响管理员脚本解析。

生产未必需要如此冗长，但管理员自动化应：

使用可信固定 search_path；
显式限定关键对象；
禁止 PUBLIC 在扩展/应用 schema CREATE；
审计 extension owner；
不让应用角色成为 schema owner。

空间函数调用量大，名称解析安全不能被“写起来太长”省掉。

版本断言要分兼容与精确

本章教学实验要求精确 PostGIS 3.6.4，因为计划文本、依赖目录和 checksum 需要可复现。生产策略可以是：

desired exact version per release
allowed source versions for upgrade
blocked known-bad versions

不要在 setup 中悄悄接受“任何 3.x”。也不要把本章精确版本断言误当成 PostGIS 永远只能使用 3.6.4。

16.6.3 观察分区、索引、写入与聚合成本

先建对象清单

本章的固定对象规模：

2 managed schemas
2 extensions
34 relations in shop_ch16
  tables/partitions
  indexes
  views
13 explicitly managed non-primary indexes

verify.sql 使用精确白名单和 marker。生产不一定需要把所有对象硬编码进单个 DO block，但必须有期望状态与漂移检测。

分区覆盖与行分布

每日检查：

SELECT
  child.relname,
  pg_get_expr(child.relpartbound, child.oid)
FROM pg_inherits AS inheritance
JOIN pg_class AS child
  ON child.oid = inheritance.inhrelid
WHERE inheritance.inhparent =
      'schema.events'::regclass;

监控：

future coverage horizon
missing/overlapping bounds
rows and bytes per partition
min/max event time
late writes by partition age
default/quarantine rows
new partition owner/privileges/indexes

本章固定 1/7/4 只用于回归；生产应关注趋势和异常分布。

父分区大小可能是零

size-catalog.sql 得到：

delivery_event_parent_total      = 0
delivery_event_partitions_total  > 0

分区父表不存 heap 行，只查：

pg_total_relation_size('parent')

可能严重低估整棵分区树。容量查询要遍历 pg_partition_tree/ pg_inherits 汇总叶表与叶索引。

写入成本不是一行 heap

每个事件写入：

ingest_attempt heap + PK + lookup index
event_registry heap + PK
one event partition heap
partition PK
courier/time B-tree
geometry GiST
geography GiST
generated geography computation
WAL for all changed pages
replica replay

本章为了可见性保留完整链；生产应测每一项是否需要。删除一个索引可能降低写放大，却也改变关键查询。决策来自读写 workload，不来自“空间列都建 GiST”。

观察索引状态与使用

目录状态：

SELECT
  indexrelid::regclass,
  indisvalid,
  indisready,
  indislive
FROM pg_index
WHERE indrelid IN (...);

运行统计：

SELECT *
FROM pg_stat_user_indexes
WHERE schemaname = 'shop_ch16';

但 idx_scan = 0 不能立刻证明索引无用：

统计可能重置；
它可能为约束服务；
查询可能只在事故/月底运行；
小表规划器合理选择 Seq Scan；
standby 查询不一定反映在 primary 指标。

删除前应结合查询样本、约束职责、时间窗口和回退计划。

空间候选比率

对代表性 query 记录：

index candidate rows
exact result rows
rows removed by filter/recheck
heap blocks
execution time distribution
geometry complexity
query radius/area

候选/命中比很高，说明 bbox 粗筛弱。可能原因：

巨大或细长 geometry；
查询区域过大；
数据高度密集；
无效/异常 geometry；
不合适的 CRS/opclass；
统计估计失真。

不要只盯索引大小。

聚合与迟到更正

监控时间桶：

events per bucket
late events per bucket
recomputed buckets
correction lag
failed/queued refresh
watermark by source

本章 quarter_hour_volume 是普通 view，每次现算。若改成物化或 continuous aggregate，还要观察刷新窗口、失效范围、后台 worker、锁、WAL 与旧结果更正。

PostgreSQL/Pigsty 观测面

常用原生证据：

pg_stat_activity
pg_stat_statements
pg_stat_user_tables
pg_stat_user_indexes
pg_stat_wal
pg_stat_replication
pg_stat_progress_create_index
pg_locks
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)

Pigsty 将其中许多指标接入监控与仪表盘。平台视图适合发现趋势，SQL 与系统目录适合确认对象和查询事实。告警链接应能回到具体 cluster/database/schema/ partition/index，而不是只有一个“PostGIS 慢”标签。

生产基准矩阵

至少覆盖：

维度	样本
时间范围	15 分钟、1 日、30 日、全保留
空间范围	小半径、城市区、多边形、超大区域
数据密度	中心区、郊区、极端热点
状态	热缓存、冷缓存、并发写
事件	正常、迟到、批量回补
计划	常量、prepared custom/generic
节点	primary、read replica、failover 后

记录 P50/P95/P99、吞吐、CPU、I/O、WAL、锁、副本延迟和结果 checksum。

观测不能改变语义

若性能不达标，优化顺序应是：

结果与时间/空间合同是否正确；
参数范围是否合理；
分区裁剪是否生效；
候选/精确阶段是否存在；
类型、SRID、谓词和 opclass 是否匹配；
统计是否可信；
索引、分区粒度或预计算是否需要调整；
是否有引入扩展/分片/异步路径的量化理由。

不要为了让曲线好看，把 ST_Covers 换成 bbox-only 或丢弃迟到事件而不修改业务合同。

上一节：时空联合查询是本章收束目标 · 返回本章目录 · 下一节：实战：配送事件的时空 PoC · 查看全书目录 · 查看索引中心

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

本节把前六节压成一个可审计的 1.4-proposal：

frozen attempts/geofences/hubs
  -> exact extension and schema ownership
  -> canonical event deduplication
  -> UTC native partition routing
  -> geometry/geography modeling
  -> temporal, boundary, distance, and plan evidence
  -> expected failures and application privileges
  -> full catalog/data checksum
  -> reset guards
  -> transactional exact reset
  -> rebuild and second review

正式证据来自 Homebrew PostgreSQL 18.6 的受控开发数据库。Pigsty 4.5 的声明与交付职责已经映射，但没有执行 Pigsty L1，因此结果明确标为：

pigsty_l1=not-run

破坏边界

task.sh all 会删除并重建带精确 marker 的 shop_ch16、 shop_ch16_ext，以及后者中的 PostGIS/btree_gist。它只适用于本书本地/开发 fixture。生产环境不得执行这条删后重建路径。

16.7.1 生成确定性事件与地理数据

前置连接

沿用第 4 章的 libpq service：

[pg36-admin]
host=/path/to/socket-or-host
port=5432
dbname=pg36_shop
user=postgres

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

不要把密码写进命令行、脚本、Git 或 evidence。

context.sql 要求：

database = pg36_shop
writable instance
PostgreSQL major = 14..18
session user = superuser
can SET ROLE pg36_owner
ch04-v1 physical model exists
pg36_app is constrained non-superuser LOGIN
PostGIS 3.6.4 is available or exact managed install exists
btree_gist 1.8 is available or exact managed install exists
existing ch16 schemas/extensions, if any, have exact identities

任何一项不符都停止。脚本不会“接受最接近的 PostGIS”后继续生成一套无法与 golden 比较的证据。

资产清单

核心文件：

static/labs/ch16/
├── frozen-attempts.csv
├── frozen-geofences.csv
├── frozen-hubs.csv
├── fixture.sql
├── fixture-manifest.json
├── context.sql
├── setup.sql
├── verify.sql
├── final-state.sql
├── reset.sql
├── review.py
├── task.sh
├── spatiotemporal-adr.md
├── baseline-v1.4-proposal.json
└── pigsty-declaration.example.yml

另有时间、分区、边界、距离、目录、权限与计划探针。

冻结文件身份

fixture-manifest.json 固定：

attempts
  rows=13
  distinct_events=12
  sha256=7fa1aadbba029061fbc7eb34c9f6285eabb38b438e7d0c0c8c6b820cdc738ccf

geofences
  rows=4
  zones=3
  sha256=b2da791c7adba720cf9f4fc1123546eb08036bb60ed8ed778ae5b8dc60435c66

hubs
  rows=3
  sha256=bd56e0f8f978495bc1e677270f285628163a3edc1656fe99a2a7173ffe8e18af

fixture.sql
  sha256=b254bf5d695cf1ab738fc71b573544ef526146355b18d60b99f342ca1536a860

这些 SHA-256 识别输入；release candidate checksum 识别发布合同，两者不是同一个东西。

为什么使用合成坐标

冻结数据像一个简化城市网格，但不是权威地图：

central hub (-74.00000, 40.71000)
east hub    (-73.98000, 40.71000)
airport hub (-73.87500, 40.65000)

这样做可以：

离线运行；
不引入地图许可证；
人工看懂边界点与围栏扩张；
每次得到相同 WKT 和 checksum；
隔离数据库机制与外部地理数据质量。

因此本章不能证明地址、道路、行政区或真实 GPS 精度。

单步建立

./static/labs/ch16/task.sh setup

setup 的碰撞保护先验证已有对象：

shop_ch16
  owner=pg36_owner
  exact schema marker
  all relations have marker
  no routines/operators/opclasses/opfamilies

shop_ch16_ext
  owner=pg36_owner
  exact schema marker
  exactly btree_gist 1.8 + postgis 3.6.4
  exact extension owner/trusted boundary
  no unmanaged relations/types/functions/operators/opclasses

通过后，整个重建位于一个事务：

BEGIN;
  drop exact old objects without CASCADE
  create extension schema
  create extensions
  create data schema/tables/partitions/indexes
  load fixture
  create views/grants/comments
  analyze
COMMIT;

第一次开发执行曾在视图语法处失败，这促使 setup 加入事务边界。如今任何中途错误都会回滚，不留下会挡住下一次运行的半成品。

扩展创建

SET ROLE pg36_owner;
CREATE EXTENSION btree_gist
  WITH SCHEMA shop_ch16_ext
  VERSION '1.8';
RESET ROLE;

CREATE EXTENSION postgis
  WITH SCHEMA shop_ch16_ext
  VERSION '3.6.4';

PostGIS 由管理员创建，btree_gist extension owner 是业务 owner。两个 extension 和 schema 都带相同精确 marker：

pg36 ch16 spatiotemporal lab; safe to rebuild

marker 不是安全令牌的替代品；reset 还会核对 target、完整对象清单、依赖、数据 checksum 和活跃 worker。

时间与空间 schema

数据层：

fixture_meta
ingest_attempt
event_registry
geofence_version
delivery_hub
delivery_event (partitioned parent)
  ├── delivery_event_20260307
  ├── delivery_event_20260308
  └── delivery_event_20260309
event_lateness view
event_zone_membership view
quarter_hour_volume view

关键约束：

ingest coordinates within lon/lat ranges
received_at >= occurred_at
event_registry payload consistency
geofence tstzrange is [), non-empty
geofence SRID=4326, non-empty, valid
same zone validity ranges cannot overlap
event geometry is Point/4326
partition primary key includes occurred_at
generated geography derives from geometry

确定性去重

loader 先统计同一 event_id 的 payload variant：

count(
  DISTINCT concat_ws(
    '|',
    occurred_at,
    courier_id,
    event_type,
    longitude,
    latitude,
    source_sequence
  )
)

只有 payload_variants = 1 才进入 registry。canonical 按：

ORDER BY event_id, received_at, attempt_id

选择。固定：

e003 attempts=a003,a004
canonical=a003
attempt_count=2

再由 canonical attempt 生成 Point 并写入父分区表。

建成摘要

status=fixture-ready
attempts=13
events=12
geofence_versions=4
postgis=3.6.4

这只是 setup 摘要，不是完整验收。

逐字节回读

PG36_EVIDENCE_DIR="$PWD/evidence/ch16-cycle" \
  ./static/labs/ch16/task.sh evaluate

自动化从数据库执行三条 COPY ... TO STDOUT CSV HEADER，再：

cmp frozen-attempts.csv evidence/attempts.csv
cmp frozen-geofences.csv evidence/geofences.csv
cmp frozen-hubs.csv evidence/hubs.csv

导出显式固定：

行顺序；
UTC RFC3339 风格时间；
经纬度五位小数；
WKT；
CSV header。

没有 ORDER BY 的数据库导出不具备逐字节比较意义。

16.7.2 验证 SRID 错误、裁剪失效和空间索引

先看时间事实

psql "service=pg36-admin" \
  -f static/labs/ch16/temporal-analysis.sql

应为：

dst_e002_local=2026-03-08 01:55:00
dst_e003_local=2026-03-08 03:05:00
dst_elapsed_seconds=600
duplicate_event=e003:2
late_event_ids=e001,e004
out_of_order_pair=e004->e005
partition_boundary=e008=...20260308;e009=...20260309
utc_day8_events=7

任何一个值改变，都意味着 fixture、时区、去重或路由合同漂移。

验证分区目录

psql "service=pg36-admin" \
  -f static/labs/ch16/partition-catalog.sql

固定：

partition	bound	rows
`delivery_event_20260307`	`[03-07,03-08)`	1
`delivery_event_20260308`	`[03-08,03-09)`	7
`delivery_event_20260309`	`[03-09,03-10)`	4

同时验证每张叶表 owner 与 marker。

对照裁剪正反例

psql "service=pg36-admin" \
  -f static/labs/ch16/time-pruned-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/time-wrapped-plan.sql

正例：

Seq Scan on delivery_event_20260308

反例：

Append
  20260307 rows removed
  20260308 seven rows
  20260309 rows removed

两条 SQL 都返回七行。计划对照证明“结果正确”与“裁剪正确”是两项验收。

验证围栏边界与换版

psql "service=pg36-admin" \
  -f static/labs/ch16/boundary-semantics.sql

固定：

scenario	event	zone/version	covers	contains	touches
at expansion	e005	central/2	t	t	f
before expansion	e004	central/1	f	f	f
shared boundary	e003	central/1	t	f	t
shared boundary	e003	east/1	t	f	t

这四行比一个“地图截图”更容易自动回归。

混合 SRID 必须失败

psql "service=pg36-admin" \
  -v VERBOSITY=verbose \
  -f static/labs/ch16/srid-mismatch.sql

预期进程退出码 3，stderr 含：

XX000
Operation on mixed SRID geometries

自动化将“正确拒绝”当成功证据。若 SQL 意外返回 false/true，说明坐标身份保护失效。

重叠有效期必须失败

psql "service=pg36-admin" \
  -v VERBOSITY=verbose \
  -f static/labs/ch16/overlap-geofence.sql

预期：

23P01
violates exclusion constraint geofence_version_no_overlap

失败语句不留下 version 99，随后 verify.sql 仍要求围栏恰好四行。

应用写入必须失败

psql "service=pg36-admin user=pg36_app" \
  -v VERBOSITY=verbose \
  -f static/labs/ch16/app-write.sql

预期：

42501 permission denied for table delivery_event

应用可读取 central day8 五行，却不能更新事件或绕过生成/分区/去重链。

GiST 与 SP-GiST 路径

psql "service=pg36-admin" \
  -f static/labs/ch16/spatial-gist-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/spatial-spgist-plan.sql

固定计划包含：

event_20260308_geog_gist_idx
  Index Cond: geography bbox expansion
  Filter: ST_DWithin(...,1500)

delivery_hub_location_spgist_idx
  Index Cond: geometry bbox expansion
  Filter: ST_DWithin(...,0.02)

两条探针临时关闭 Seq Scan；它们只证明路径可用。

时空联合计划

psql "service=pg36-admin" \
  -f static/labs/ch16/joint-plan.sql

必须同时出现：

geofence_version_no_overlap
event_20260308_location_gist_idx
ST_Covers filter

且不出现 20260307/20260309 事件分区。

索引目录不能只看名字

psql "service=pg36-admin" \
  -f static/labs/ch16/index-catalog.sql

13 行全部要求：

indisvalid=true
indisready=true
indislive=true
index_bytes>0
marker exact
access method/opclass exact

特别是：

geography -> gist_geography_ops
geometry  -> gist_geometry_ops_2d
hub point -> spgist_geometry_ops_2d
exclusion -> gist_text_ops + range_ops

完整 SQL 断言

./static/labs/ch16/task.sh verify

verify.sql 检查：

两个 schema 的 owner/marker；
34 个关系对象精确白名单；
extension schema 中没有未管理成员；
两项扩展版本、owner、trusted/relocatable 边界；
fixture 身份与 13/12/4/3 基数；
去重 canonical；
1/7/4 路由与两个边界事件；
DST 600 秒、迟到和乱序；
[)、非重叠、有效 geometry；
14 条 membership 和四个边界事实；
generated geography；
13 个索引状态/opclass；
应用权限；
业务校验和。

成功：

status=ok
fixture=frozen-byte-identical
time=event+ingest+validity
space=geometry+geography+4326
partition=utc-range-1+7+4
membership=14
business_checksum=53f51cef1f0bed1a5c2fc89bfad109f4

16.7.3 输出 ADR、PoC 证据与生产代价清单

一键双周期验收

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

all 执行：

cycle-1 setup + collect + review
  -> wrong token reset guard
  -> wrong target reset guard
  -> active worker reset guard
  -> exact reset
  -> cycle-2 setup + collect + review

第二周期不是重复表演。它证明 reset 后：

数据 schema 消失；
扩展 schema 消失；
PostGIS/btree_gist 被精确移除；
第 14 章 pg_trgm/vector 保留；
同一输入能重建同一业务 checksum；
计划、权限和失败边界仍成立。

evidence 结构

evidence/ch16-final/
├── cycle-1/
│   ├── manifest.txt
│   ├── attempts.csv
│   ├── geofences.csv
│   ├── hubs.csv
│   ├── temporal-analysis.csv
│   ├── partition-catalog.csv
│   ├── time-buckets.csv
│   ├── zone-membership.csv
│   ├── boundary-semantics.csv
│   ├── distance-semantics.csv
│   ├── extension-catalog.csv
│   ├── index-catalog.csv
│   ├── security-catalog.csv
│   ├── size-catalog.csv
│   ├── *-plan.txt
│   ├── *-failure stderr/exit
│   ├── final-state.csv
│   ├── verify.txt
│   └── review.txt
├── reset-wrong-token.*
├── reset-wrong-target.*
├── reset-active-worker.*
├── reset-exact.*
└── cycle-2/
    └── same evidence set

manifest 身份

每周期 manifest 保存：

captured_at
action/service
validation_path=direct-postgresql
pigsty_reference=4.4
pigsty_l1=not-run
model_version=ch04-v1
partition_timezone=UTC
coordinate_contract=EPSG:4326-synthetic
server/database/admin/recovery
extension versions
preserved ch14 extensions
baseline canonical checksum
fixture manifest canonical checksum
all source file SHA-256

动态采集时间不进入业务 checksum。它说明“证据何时采集”，不改变数据真值。

最终状态

final-state.sql 固定：

attempts=13
events=12
duplicate_registry=e003:2
late_events=e001,e004
partition_counts=1,7,4
memberships=14
central_day8=e002,e003,e005,e006,e008
extensions=btree_gist:1.8,postgis:3.6.4
business_checksum=53f51cef1f0bed1a5c2fc89bfad109f4

checksum 覆盖：

attempts
registry
geofences and WKT
hubs and WKT
canonical events and WKT
zone memberships

它不包含执行计划、物理 OID、索引页或采集时间，因此 reset/rebuild 后仍应相同。

自动审校

review.py 不连接数据库，只审查 evidence 与冻结 source：

三份导出字节相同且 hash/行数匹配 manifest；
DST、迟到、乱序与路由事实精确；
分区、桶与 membership 守恒；
边界、距离结果精确；
扩展、索引、权限、体积目录满足合同；
直接/包裹时间计划形成反例；
GiST/SP-GiST/联合计划包含目标路径；
三个失败的退出码与 SQLSTATE 正确；
final state 与完整 verify 通过；
baseline JSON 与业务 checksum 一致。

这样可以把“数据库输出的确生成了”与“输出符合我们预先定义的结论”分开。

reset 三道动作护栏

精确复位要求：

export PG36_RESET_TOKEN=RESET_CH16_SPATIOTEMPORAL_LAB
export PG36_RESET_TARGET='pg36_shop/shop_ch16+shop_ch16_ext'

./static/labs/ch16/task.sh reset

错误 token：

P3660

错误 target：

P3661

活跃 pg36-ch16-* worker：

P3663

通过动作护栏后，reset 在事务内先 \ir verify.sql。也就是说，只有完整状态仍与本章合同相同时才开始 DROP。

为什么不用 CASCADE

删除顺序：

views
event parent (and its owned partitions/indexes)
other data tables
data schema
postgis
btree_gist
extension schema

全部使用 RESTRICT 默认语义。若外部对象意外依赖本章扩展，DROP 会失败，事务整体回滚，数据和扩展不会处于半删状态。

ADR 的核心决策

spatiotemporal-adr.md 记录：

occurred_at is event time and partition key
received_at remains ingest evidence
valid_during is non-overlapping tstzrange
UTC daily native RANGE is baseline
TimescaleDB is deferred
EPSG:4326 is canonical
geometry serves topology/index
geography serves meter distance
ST_Covers includes boundary

同时列出否决方案、代价与重开条件。ADR 的价值不是替 SQL 写说明，而是保留 “为什么这样选”和“什么新证据会让我们重选”。

生产代价清单

本 PoC 未证明：

production ingest throughput
P50/P95/P99 time-space query latency
WAL and replica lag
GiST/SP-GiST build/reindex duration
autovacuum and statistics behavior
real polygon complexity/selectivity
GPS and authoritative map quality
backup/restore on Pigsty L1
failover behavior
PostGIS/Pigsty upgrade path
TimescaleDB benefit

发布前应将这些项目变成有负责人、环境、阈值、证据路径和停止线的验收计划。

最终正式输出

两轮 Homebrew PostgreSQL 18.6 结果：

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

这个 checksum 对应 baseline-v1.4-proposal.json 的规范化 JSON。修改合同后必须生成新 proposal checksum，不能继续引用旧结果。

16.7.4 超预算时先删扩展专属细节，不删基础判断力

本章的教学最小闭环

如果书稿、课程或项目时间不足，最小闭环仍必须保留：

event / ingest / valid time
timestamptz + explicit timezone
DST counterexample
late / out-of-order / duplicate distinction
[) range policy
partition-key ADR
pruning positive and negative plans
geometry / geography / SRID / units
ST_SetSRID vs ST_Transform
boundary predicate counterexample
ST_DWithin vs ST_Distance vs KNN
bbox candidate + exact predicate
one joint time-space query
one expected SRID failure
one exact reset/rebuild path

删掉其中任一组，读者很可能只记住命令，不会形成判断力。

第一优先可删：扩展参数百科

可压缩：

TimescaleDB 某一版本的全部 GUC；
所有 PostGIS 子扩展列表；
每个索引 opclass 的完整矩阵；
某发行版每个包文件名；
罕见 geometry 类型函数目录。

它们变化快，也可从目标版本官方文档查到。正文应保留如何核对，而不是复制百科。

第二优先可删：未实测的高级方案

本章没有假装实现：

路网最短路；
地图匹配；
轨迹压缩；
3D/4D 几何；
raster；
全球多投影治理；
双时态修订系统；
continuous aggregate 基准。

这些可以成为后续项目，但不应挤掉本章已经可复现的基础闭环。

不能把 Pigsty 映射删成一句话

即使篇幅少，也至少保留：

package supply
preload/config when required
CREATE EXTENSION
all L1 nodes
catalog/functional validation
backup/restore and upgrade

否则读者会把本地 CREATE EXTENSION 当成生产交付。

不能把反例全删掉

本章四个关键反例：

夏令时墙上差 70 分钟，实际 600 秒；
包裹分区键后逻辑结果相同，但三分区全扫；
边界点 covers=true、contains=false；
混合 SRID 必须失败。

成功路径告诉读者“怎么写”；反例让读者知道“为什么这样写”。若只留成功截图，认知无法迁移到新业务。

生产预算不足时的正确停止线

如果没有预算完成：

代表性规模压测
Pigsty L1 节点一致性
备份恢复
故障切换
扩展升级演练
真实地图许可证/质量评审

结论应停在：

semantic and mechanical PoC passed
production release not approved

不能因为 PoC 代码整洁就降低生产验收标准。

迁移练习

读者可复制 fixture 为 ch16-spatiotemporal-v2，任选一项扩展：

改用一个真实但许可明确的公开边界数据集；
增加 GPS accuracy 与 uncertain membership；
实现围栏修订的 system time；
对 native partition 与 TimescaleDB 做同输入 A/B；
加入真实数量级并比较 GiST/SP-GiST；
为当地业务日生成可裁剪 UTC 边界；
增加唯一归属消歧规则。

必须：

新建 manifest/version；
保留旧 fixture；
写明新许可证与生成方法；
更新 expected facts/checksum；
加入至少一个新反例；
重新做 reset/rebuild 两周期；
不沿用本章 release checksum。

做到这一步，读者不只是会调用 PostGIS，而是能把时空需求变成可验证的 PostgreSQL/Pigsty 工程合同。

上一节：时空扩展的交付与观察 · 返回本章目录 · 下一章：合纵连横：分析加速与分布式选型 · 查看全书目录 · 查看索引中心

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

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

本章完成后

贯穿本章的配送事件

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

围栏边界不是实现细节

逻辑正确与物理路径分开

实验资产

快速运行

学习路径

16.1 时间语义先于时序扩展

16.2 时序表与时间分区

16.3 空间类型与坐标参考

16.4 空间谓词与索引

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

16.6 时空扩展的交付与观察

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

权威参考

16.1 时间语义先于时序扩展

16.1.1 事件时间、处理时间与有效时间

一条事实至少可能有四只钟

本章的最小模型

timestamptz 表示绝对时刻

timestamp 也有正当用途

有效时间是区间，不是两个互不相关字段

事务时间是另一条轴

选择默认时间的判断表

16.1.2 时区、迟到、乱序与重复事件

时区是显示规则，也是输入解析规则

夏令时反例

迟到不等于乱序

迟到策略不能藏在 SQL 里

重复也有两种

来源序列补足时间排序

输入时钟也要被观测

16.1.3 范围类型、窗口与时间对齐

半开区间让相邻边界只有一个归属

用约束保护有效期

时间桶必须固定原点

UTC 桶与本地日历桶不是同一产品

窗口函数不是时间窗口状态机

可执行验收

本节判断线

16.2 时序表与时间分区

16.2.1 从 ch04 的分区 ADR 选择时间键

分区键首先决定数据归属

父表合同

半开日分区

UTC 边界与当地业务日

裁剪取决于谓词与分区边界

从目录验证，而不是从表名猜

16.2.2 写入模式、冷热生命周期与保留

写入链先处理身份，再路由事实

为迟到写入保留窗口

不要让 DEFAULT 分区变成垃圾桶

分区粒度由约束共同决定

索引是叶分区成本

热、温、冷不是表空间颜色

删除与回补会影响 vacuum

16.2.3 原生分区、聚合与可选时序扩展

先列需求，再选能力

本章为何推迟 TimescaleDB

可选扩展仍要完整走交付链

聚合真值仍来自时间合同

用 A/B 迁移而不是信仰迁移

16.2.4 不在本章重复在线分区化和维护细节

本章边界

分区维护也不应塞进应用请求

本章必须保留的判断力

本节验收

16.3 空间类型与坐标参考

16.3.1 geometry、geography 与测量语义

两种类型回答不同计算问题

不是所有列都要存两份

typmod 把对象类型与 SRID 放进 schema

X/Y 是坐标轴，不是自动的“纬/经”

geography 不是“更准确”的万能开关

本章距离证据

16.3.2 SRID、投影、单位与坐标转换

SRID 是坐标参考身份

`timestamptz` 表示绝对时刻

`timestamp` 也有正当用途

`ST_SetSRID` 只贴标签

邻近筛选使用 `ST_DWithin`

手工 `&&` 只回答包围盒