跳转到主要内容

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

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

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

“备份任务成功”不证明 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。

观测不能改变语义

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

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

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

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

最后修改 2026年8月28日 · chore: remove GitHub Pages workflow (83aa397)