# 时空扩展的交付与观察

LLMS 索引： [llms.txt](/llms.txt)

---

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

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

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

## 16.6.1 安装 PostGIS 与可选时序扩展 {#item-16-6-1}

### 四个阶段不能合并

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

```text
Download -> Install -> Config -> Create
```

对应工程问题：

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

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

参考 Pigsty 当前
[扩展概览](https://pigsty.io/docs/pgsql/ext/)、
[包别名](https://pigsty.io/docs/pgsql/ext/pkg/) 与
[创建扩展](https://pigsty.io/docs/pgsql/ext/create/)。

### package alias 与 SQL extension name 不一定相同

例子：

```text
package alias: postgis
SQL extension: postgis

package alias: timescaledb
SQL extension: timescaledb

package alias: pgvector
SQL extension: vector
```

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

```text
Pigsty release
Linux distribution
architecture
PostgreSQL major
repository snapshot
```

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

### 本章 Pigsty 声明

[`pigsty-declaration.example.yml`](/labs/ch16/pigsty-declaration.example.yml)
是合并片段，不是完整生产配置：

```yaml
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 }
```

三层含义：

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

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

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

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

### 本地实验为何不用 `public`

本地 PoC 安装到：

```text
shop_ch16_ext
```

并将数据放在：

```text
shop_ch16
```

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

```sql
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。

目录证据来自：

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

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

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

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

```yaml
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 扩展页](https://pigsty.io/ext/e/timescaledb/)
为准。

### 声明后回到 SQL 验收

```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');
```

功能探针至少包括：

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

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

## 16.6.2 核对版本、依赖、备份和升级边界 {#item-16-6-2}

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

发布证据应保存：

```text
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
```

本章正式证据固定：

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

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

Pigsty 当前
[PostGIS 扩展目录页](https://pigsty.io/ext/e/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` 注册大量：

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

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

这避免两个风险：

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

### 备份不是只备 geometry 列

恢复要同时具备：

```text
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` 会按扩展成员关系处理对象；恢复环境必须先能供应相容扩展。物理
备份同样要求目标运行环境可加载相应库。

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

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

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

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

可能的变化轴：

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

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

```text
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` 是安全边界

本章上下文固定：

```sql
SET search_path = pg_catalog;
```

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

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

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

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

### 版本断言要分兼容与精确

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

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

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

## 16.6.3 观察分区、索引、写入与聚合成本 {#item-16-6-3}

### 先建对象清单

本章的固定对象规模：

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

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

### 分区覆盖与行分布

每日检查：

```sql
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;
```

监控：

```text
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`](/labs/ch16/size-catalog.sql) 得到：

```text
delivery_event_parent_total      = 0
delivery_event_partitions_total  > 0
```

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

```sql
pg_total_relation_size('parent')
```

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

### 写入成本不是一行 heap

每个事件写入：

```text
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”。

### 观察索引状态与使用

目录状态：

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

运行统计：

```sql
SELECT *
FROM pg_stat_user_indexes
WHERE schemaname = 'shop_ch16';
```

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

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

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

### 空间候选比率

对代表性 query 记录：

```text
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；
- 统计估计失真。

不要只盯索引大小。

### 聚合与迟到更正

监控时间桶：

```text
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 观测面

常用原生证据：

```text
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。

### 观测不能改变语义

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

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

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

---

[上一节：时空联合查询是本章收束目标](../05/) · [返回本章目录](../) · [下一节：实战：配送事件的时空 PoC](../07/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)