# 特种索引陷阱、排他约束与高阶 SQL 武器库

> GIN 写放大与表达式索引失配、函数易变性契约、EXCLUDE 排他约束与触发器实践。

---

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

---

把数据库当笨重的文件柜而把复杂状态校验全堆进应用层，既浪费了内核级并发防线又放大了网络往返。吃透 GIN/GiST 算子边界、函数易变性契约、`EXCLUDE` 排他约束与连接池状态铁律，才能榨出 PostgreSQL 十倍工程效能。

## 一、特种索引选型、GIN 复杂度陷阱与高级模糊检索 {#indexes-gin-and-fuzzy-search}

### 如何按负载匹配 PG 索引，并避开空值降序暗坑？ {#index-selection-and-nulls-order}

索引须按负载量体裁衣：追加写时序大表选轻量 BRIN，JSONB 与数组用倒排 GIN，空间近邻用 GiST，局部热点过滤用部分索引，左模糊或超长串改建 `reverse()` 或哈希表达式索引。实操严守两道红线：B-Tree 单列禁超约 2KB 页限制；PG 的 `DESC` 默认将空值排最前（`NULLS FIRST`），可空列建降序索引与写查询必须同步声明 `NULLS LAST` 方能防失效。

- [PostgreSQL 规约（2024版） · 0x02 设计规范](/pg/pg-convention/#0x02-设计规范)
- [PostgreSQL开发规约（2018版） · 0x03 索引规范](/pg/pg-convention-2018/#0x03-索引规范)

### 为何 GIN 数组重叠搜索会退化为平方级复杂度？ {#gin-overlap-quadratic-catalog}

建了 GIN 索引仍往 `&&` 塞超长词表会导致查询断崖崩塌。顺着 `pg_opclass`、`pg_amop` 到 `pg_operator.oprcode` 系统目录链反查，可定位 `array_ops` 重叠算子底层调用 `arrayfuncs.c` 的 `arrayoverlap`：其内部既未排序归并也未建哈希表，而是跑双层嵌套 `for` 循环逐一判等，双侧大数组下 CPU 呈 \(O(n^2)\) 爆炸，应用侧必须硬截断查询词数。

- [GIN搜索的O(n²)复杂度 · Details](/pg/gin/#details)

### 如何免外部组件，用原生 PG 做亚毫秒模糊搜索？ {#custom-chinese-fuzzy-search}

`LIKE` 解不了双侧模糊与穿插符号，官方 `pg_trgm` 又对中文单双字失明、GiST 慢达 200ms 且与生产标准 `LC_CTYPE = C` 冲突。无需外挂 ES，破局在于“词素扩充”：写 `IMMUTABLE` 函数将中日韩单双字切片、英文三元组与拼音首字母统一打成 `TEXT[]` 建 GIN 表达式索引，先用 `@>` 或 `&&` 在 1ms 内倒排召回，再配 `levenshtein` 编辑距离精排即可闭环。

- [高级模糊查询的实现 · LIKE模式匹配](/pg/fuzzymatch/#like模式匹配)
- [高级模糊查询的实现 · 特殊问题](/pg/fuzzymatch/#特殊问题)
- [高级模糊查询的实现 · 基本框架](/pg/fuzzymatch/#基本框架)

### 向空库注入生产统计信息后，为何执行计划仍失真？ {#pg-regresql-planner-hook}

把生产库 `pg_statistic` 与 `pg_class` 导入空载测试库做离线 SQL 审计时，`EXPLAIN` 成本与连接顺序依然对不上，是因为 PG 规划器仍会去探测表文件磁盘真实物理页数并按比例缩放行数估算，压低了空表成本。挂载 `pg_regresql` 扩展的 `get_relation_info_hook` 拦截规划器，强制信任 `pg_class` 中的 `relpages` 与 `reltuples` 覆盖物理探测值，方能锁定可靠回归基线。

- [504 个扩展，PG 生态的天花板在哪？ · 29. pg_regresql: 让规划器信任 pg_class 统计信息](/pg/extension-504/#29-pg_regresql-让规划器信任-pg_class-统计信息)

## 二、函数易变性、排他约束与高阶 SQL 武器库 {#volatility-exclude-and-advanced-sql}

### 地道 SQL 如何敬畏三值逻辑并削减往返开销？ {#three-valued-logic-and-idioms}

`NULL` 遇普通等号或不等号一律返回 `UNKNOWN`，可空列安全判等必须用 `IS NOT DISTINCT FROM`，并分清总行数 `count(*)` 与忽略空值的 `count(col)`。微观性能上，判存在严禁跑全量 `count` 而应改用短路即停的 `EXISTS`；多值过滤用单数组参数 `= ANY(ARRAY[...])` 替代超长 `IN`，再借 `UPSERT` 与 `RETURNING` 将多次网络往返折叠为单条原子语句。

- [PostgreSQL 规约（2024版） · 0x03 查询规范](/pg/pg-convention/#0x03-查询规范)

### 如何用专属语法取分组极值并靠索引消除排序？ {#distinct-on-group-top1}

取“每台机器最新上报记录”这类分组 Top-1 问题无需套窗口函数或自关联：PG 独有的 `DISTINCT ON (node_id)` 配确定性 `ORDER BY node_id, ts DESC NULLS LAST`，即可由 `Unique` 算子在有序流上直接截出首行。要消除全表显式排序走上 Index Only Scan，复合 B-Tree 索引的列序与 `NULLS LAST` 子句必须与查询 `ORDER BY` 严丝合缝对齐。

- [Distinct On 去除重复数据 · Distinct On应用案例](/pg/sql-distinct-on/#distinct-on应用案例)
- [Distinct On 去除重复数据 · 使用索引加速Distinct On查询](/pg/sql-distinct-on/#使用索引加速distinct-on查询)

### 自定义函数为何毁索引，三级易变性契约差在哪？ {#function-volatility-contract}

建函数省略易变性标签会默认落入最保守的 `VOLATILE`，优化器视其可能改库或同参异果，严禁放入单次求值的索引扫描条件（`Index Cond`）且全表扫描逐行重算。无写入且单语句快照内恒定的只读函数须标 `STABLE`；输出仅取决于入参的纯函数须标 `IMMUTABLE` 以解锁规划期常量折叠与表达式索引，但绝不可将查表函数谎报为 `IMMUTABLE`。

- [函数易变性等级分类 · 具体解释](/pg/sql-func-volatility/#具体解释)
- [函数易变性等级分类 · 执行计划缓存](/pg/sql-func-volatility/#执行计划缓存)

### 如何用一行排他约束根除会议撞期与网段重叠？ {#exclude-constraint-gist}

应用层“先查后插”防时段交叉或网段重叠在高并发竞态下必被击穿，普通 `UNIQUE` 又只能防相等。PG 独有的 `EXCLUDE` 排他约束依托 GiST 索引将判重泛化为任意交换算子冲突检测：网段表声明 `EXCLUDE USING gist (ips WITH &&)`，会议室排期启用 `btree_gist` 后声明 `EXCLUDE USING GIST (room_id WITH =, range WITH &&)`，一行 DDL 即可从引擎层封死脏数据并自动建好检索索引。

- [用 Exclude 实现互斥约束 · Eclude约束的语法](/pg/sql-exclude/#eclude约束的语法)
- [用 Exclude 实现互斥约束 · 应用案例：会议室预定](/pg/sql-exclude/#应用案例会议室预定)
- [GeoIP 地理逆查询优化 · 0x04 EXCLUDE约束](/pg/geoip/#0x04-exclude约束)

### 高频存储过程如何根治临时表膨胀与回滚丢日志？ {#pg-variables-and-autonomous-log}

存储过程高频建毁临时表会反复读写 `pg_class` 等系统表，诱发严重的系统目录膨胀（Catalog Bloat）。改用 `pg_variables` 扩展的进程内存级会话标量与记录集，即可零目录开销缓存中间态；而基于它缓存 `dblink` 连接实现的 `pgelog` 伪自治事务，更能确保 `EXCEPTION` 块写入的诊断日志在外层主事务 `ROLLBACK` 后照常落盘留存（仅需留意额外连接开销）。

- [504 个扩展，PG 生态的天花板在哪？ · 31. pg_variables: 比临时表更快的会话变量](/pg/extension-504/#31-pg_variables-比临时表更快的会话变量)

### 纯 SQL 能否闭环推荐系统，自连接边界在哪？ {#sql-itemcf-recsys-boundary}

无需外接机器学习平台，五分钟即可用原生 SQL 跑通 ItemCF 协同过滤：将行为建模为 `(user_id, item_id, rating)` 三元组，用单条 CTE 同用户自连接算出共现余弦相似度表，再把加权预测、已读剔除与热门混排封入返回 `JSONB` 的 `STABLE` 函数实时服务。但纯 SQL 硬算二次方物品对有硬边界——10 万条评分全量计算耗时近 4 分钟，上规模须做离线长尾剪枝或用 C 内核扩展。

- [SQL实现ItemCF推荐系统 · 物品相似度的计算](/pg/pg-recsys/#物品相似度的计算)
- [SQL实现ItemCF推荐系统 · 第三步：进行推荐！](/pg/pg-recsys/#第三步进行推荐)
- [SQL实现ItemCF推荐系统 · What’s more](/pg/pg-recsys/#whats-more)

## 三、触发器实战、状态同步与客户端连接池铁律 {#triggers-notify-and-pooling}

### 生产大表用触发器与逻辑复制，须防哪些锁与暗坑？ {#trigger-locks-and-replica-role}

触发器处处是暗锋：PG 外键约束靠隐藏内部触发器（`tgisinternal = true`）实现，维护停用必须敲 `DISABLE TRIGGER USER`，敲成 `ALL` 会静默废除外键防线；`CREATE TRIGGER` 需争抢阻塞增删改的 `Share Row Exclusive` 重锁，且 `COPY` 导入同样会逐行引爆行级触发器；逻辑复制回放默认置 `replica` 角色，须靠 `Origin / REPLICA` 模式解耦主从重复副作用。

- [PostgreSQL的触发器使用注意事项 · 修改](/pg/sql-trigger/#修改)
- [PostgreSQL的触发器使用注意事项 · 触发器模式](/pg/sql-trigger/#触发器模式)
- [PostgreSQL的触发器使用注意事项 · 触发器与锁](/pg/sql-trigger/#触发器与锁)

### 如何用触发器实现防篡改审计，且不拖垮业务写入？ {#audit-trigger-security-fillfactor}

行级触发器捕获新旧行镜像与 `current_query()` 可零侵入记账，但须做好权限隔离与兜底：将日志表放入收回 `PUBLIC` 写权限的 `audit` 模式，函数声明 `SECURITY DEFINER` 并锁死 `SET search_path = pg_catalog, audit` 封死提权劫持；只追加的审计表设 `FILLFACTOR = 100` 压实空间，末尾用 `EXCEPTION WHEN OTHERS` 降级为告警返回 `NULL` 防审计故障拖回滚主业务。

- [用触发器审计数据变化](/pg/audit-change/)

### 多实例如何免挂外部队列，靠 PG 原生同步缓存？ {#listen-notify-cache-sync}

多副本内存缓存（如 Go `sync.Map`）靠定时全量拉取滞后又费 I/O，外挂 Redis Pub/Sub 又防不住后台脚本直改库漏通知。直接用行级 `AFTER` 触发器调 `pg_notify`，即可由内核对所有写入入口透明广播变更信号；同步中小规模元数据时切忌在载荷塞整行 JSON，仅广播“操作类型加主键 ID”让客户端按主键回源查最新行覆盖，既极简又强一致。

- [GO与PG实现缓存同步 · 传统方法](/pg/notify-trigger-based-repl/#传统方法)
- [GO与PG实现缓存同步 · 原理](/pg/notify-trigger-based-repl/#原理)

### 高并发流量如何靠快慢隔离与连接池纪律防雪崩？ {#traffic-isolation-and-pgbouncer}

生产应用必须经域名与 6432 端口 PgBouncer 事务池接入，铁腕执行“读写分离、快慢物理隔离”：在线请求压在 1ms 级并配客户端毫秒级主动超时，强杀超 10 分钟的 `idle in transaction` 僵尸事务，超 1 秒慢查询与 ETL 彻底剥离至专用 `Offline` 从库；事务池下严禁执行 `SET` 篡改 `search_path` 或角色，严防归池连接跨请求传染脏状态。

- [PostgreSQL 规约（2024版） · 0x03 查询规范](/pg/pg-convention/#0x03-查询规范)
- [PostgreSQL开发规约（2018版） · 0x04 查询规范](/pg/pg-convention-2018/#0x04-查询规范)

### Go 操作连接池时，如何防范句柄泄漏与空值崩溃？ {#go-sql-pool-leak-null}

据 `go-database-sql.org` 实践，`sql.DB` 是惰性建连的全局连接池单例（须 `Ping` 验证），按请求开关句柄必爆 `TIME_WAIT` 风暴。未关闭的 `*sql.Rows` 会永久霸占物理连接，遍历必配 `defer rows.Close()` 与 `rows.Err()`，无返回行的写操作严禁误调 `Query()`（一律用 `Exec()`）；对付可空列 `Scan` 零值崩溃，正道是在表层加 `NOT NULL` 或查询侧用 `COALESCE` 兜底。

- [Go数据库教程：database/sql · 3. 访问数据](/pg/pg-go-driver/#3-访问数据)
- [Go数据库教程：database/sql · 4.3 修改数据](/pg/pg-go-driver/#43-修改数据)
- [Go数据库教程：database/sql · 6. 处理空值](/pg/pg-go-driver/#6-处理空值)

### Go 预编译语句为何会爆炸，有状态操作如何隔离？ {#go-prepare-and-tx-state}

协议层预编译语句绑定单条物理连接，而在 Go `sql.DB` 上调 `Prepare` 后一旦原连接繁忙，`Stmt` 会在其他空闲连接上静默重做 `Prepare`，高并发下雪崩式复制句柄撑爆服务端上限。同理，直接对池连续跑 `LOCK TABLE` 与 `INSERT` 会落到不同连接丢锁；依赖单连接状态的操作必须绑在 `sql.Tx` 上，禁裸发 `BEGIN/COMMIT`，且 `Tx.Prepare` 须在提交前显式关闭。

- [Go数据库教程：database/sql · 4.4.3 底层内幕](/pg/pg-go-driver/#443-底层内幕)
- [Go数据库教程：database/sql · 9.3 不合预期的连接状态](/pg/pg-go-driver/#93-不合预期的连接状态)