# 扩展生态 · 一句话给数据库装新能力

> CREATE EXTENSION 一句话，给 PostgreSQL 装上模糊搜索(pg_trgm)、加密(pgcrypto)、大小写不敏感文本(citext)、去重音(unaccent)、慢查询分析(pg_stat_statements)乃至地理空间(PostGIS)。可插拔的扩展机制，是 PostgreSQL 长盛不衰、能长成「平台」的关键。

- 作者：David（道雾轩）
- 专栏：PostgreSQL 特性详解（https://daiw.org/manual/postgresql.md）
- 最后更新：2026-07-13
- 原文：https://daiw.org/manual/postgresql/extensions
- 转载与引用：请注明出处并附原文链接（https://daiw.org/about/copyright）

# 扩展生态 · 一句话给数据库装新能力

前面十几章讲的都是 PostgreSQL 的**内置**能力。但它最深的护城河，其实是**扩展机制**：一句 `CREATE EXTENSION`，就能给数据库装上一整套新类型、新函数、新索引，甚至新的存储引擎。这让 PostgreSQL 不只是个数据库，而是一个能不断长出新能力的**平台**。

## CREATE EXTENSION：一句话的事

装扩展就一句，幂等：

```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
```

<Callout type="info">
  能不能装，取决于服务端有没有预编译好那个扩展（通常是一个 `.so` 动态库）。自建的 PostgreSQL 可以随便编译安装；但**托管 PG（阿里云 RDS、Neon、Supabase 等）只能用它们白名单里的扩展**——因为扩展里的 C 代码运行在数据库进程内，托管商必须管控安全。上生产前，先确认目标环境的扩展白名单。实测那台 Neon 实例可用的就有 `citext`、`hstore`、`pg_trgm`、`pgcrypto`、`postgis`、`unaccent` 等一大票。
</Callout>

## pg_trgm：让 `LIKE '%关键词%'` 走索引

这是最实用的扩展之一。普通 B-tree 索引对 `LIKE '%postgr%'` 这种**前置通配**无能为力——只能全表扫。`pg_trgm` 把字符串拆成**三字符片段(trigram)** 建索引，于是模糊匹配也能走索引：

```sql
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON items USING gin (name gin_trgm_ops);

-- 现在这条能走索引了(以前必然全表扫)
SELECT name FROM items WHERE name ILIKE '%postgr%';
-- -> ['PostgreSQL', 'Postgres', 'PostgresPro']
```

它还能算**相似度**，做「你是不是想搜……」的模糊匹配和拼写纠错：

```sql
SELECT word, similarity(word, 'Postgres') FROM dict ORDER BY 2 DESC;
-- Postgres: 0.727 | PostgreSQL: 0.615 | PostgresPro: 0.571
```

`WHERE '%关键词%'` 慢、又不想上 Elasticsearch 时，`pg_trgm` 常常是那个刚好够用的答案。

## pgcrypto：加密与哈希

`pgcrypto` 提供一整套密码学函数。最常用的是**安全地存密码**——用 `crypt` + `gen_salt('bf')` 做 bcrypt 加盐哈希：

```sql
CREATE EXTENSION pgcrypto;

-- 存:bcrypt 加盐哈希(每次结果不同,不可逆)
INSERT INTO users(pw) VALUES (crypt('secret', gen_salt('bf', 6)));
-- $2a$06$7TGjUCeNUwjaXfD7mj9tCe...

-- 验:重新 crypt 比对
SELECT pw = crypt('secret', pw) AS ok FROM users;  -- 校验密码 ✓

SELECT digest('hello', 'sha256');   -- 2cf24dba5fb0a30e26e83b2ac5b9e29e...
```

<Callout type="warn">
  即便有 `pgcrypto`，「密码哈希到底放数据库算还是应用层算」也值得斟酌——明文密码若经 SQL 传到数据库，可能落进查询日志。更稳妥的做法常是在**应用层**用成熟的密码库哈希后再存。`pgcrypto` 更适合的是**字段级加密**（把敏感列加密存储）和**校验和 / 摘要**计算。
</Callout>

## citext / unaccent：文本的两个痛点

**`citext`**——大小写不敏感的文本类型。邮箱、用户名这种「Alice@x.com 和 alice@x.com 应视为同一个」的字段，用 `citext` 后比较和唯一约束自动忽略大小写：

```sql
CREATE EXTENSION citext;
-- email 列设为 citext + UNIQUE 后:
-- 插入 'Alice@example.com',再插 'alice@example.com' → 被 UNIQUE 拒掉 ✓
```

**`unaccent`**——去掉重音符号，让 `café` 能被 `cafe` 搜到：

```sql
CREATE EXTENSION unaccent;
SELECT unaccent('Café crème brûlée');   -- Cafe creme brulee
```

配合全文检索，`unaccent` 常用来做「忽略变音符号」的搜索，对欧洲语言尤其有用。

## pg_stat_statements：慢查询分析的标配

`pg_stat_statements` 把执行过的每条 SQL **按模板归并**（把 `WHERE id = 1` 和 `id = 2` 视为同一模板），统计各自的总耗时、调用次数、平均时间——**定位慢查询的第一工具**，几乎是生产库的标配。

```sql
-- 找最耗时的 5 类查询
SELECT query, calls, mean_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
```

<Callout type="info">
  它有个和别的扩展不同的地方：**必须写进 `shared_preload_libraries` 配置、重启数据库才生效**。因为它要在服务启动时就挂载、拦截每一条查询——不能像 `pg_trgm` 那样运行时 `CREATE EXTENSION` 就完事。实测那台实例上它「可装但未启用」，正是因为这需要 DBA 改配置重启。凡是需要 `shared_preload_libraries` 的扩展（还有 `timescaledb`、`pg_cron` 等），托管环境往往要在控制台单独开。
</Callout>

## hstore：JSONB 之前的老兵

`hstore` 是键值对类型(`'a=>1, b=>2'`)，在 JSONB 出现之前是 PG 存「半结构化数据」的主力：

```sql
SELECT attrs->'b', akeys(attrs) FROM t;  -- '2', ['a','b','c']
```

<Callout type="info">
  今天新项目基本**不必再用 `hstore`**——[JSONB](https://daiw.org/manual/postgresql/jsonb) 在它能做的一切之上，还支持嵌套、数组、更丰富的运算符和索引。了解 `hstore` 主要是为了看懂老代码。它的存在也说明了一件事：PostgreSQL 的很多内置能力，当年都是先以扩展形态出现、验证成熟后才「转正」的。
</Callout>

## 还有一批「重量级」扩展

扩展生态里还有几个能单独改变技术选型的大家伙：

- **PostGIS** —— 地理空间(GIS)的事实标准。几何类型、空间索引、几百个空间函数，让 PG 成为顶级的空间数据库。
- **TimescaleDB** —— 时序数据库，把 PG 变成能扛超高写入的时序引擎（自动分区 + 压缩）。
- **pgvector** —— 向量检索，存 embedding、做相似度搜索，是这两年 AI 应用给 PG 加的新翅膀。
- **pg_cron** —— 在数据库里跑定时任务，`REFRESH` 物化视图、清理老分区都靠它。

## 横向对比

| 维度 | PostgreSQL | MySQL | Oracle / SQL Server |
| --- | --- | --- | --- |
| 扩展机制 | **一等公民**，`CREATE EXTENSION` | 插件机制弱、少 | 内置功能多，但不开放同等扩展生态 |
| 地理空间 | PostGIS（业界标杆） | 基础空间类型 | 有（商业） |
| 时序 | TimescaleDB | 无原生 | 有（商业选项） |
| 向量检索 | pgvector | 8.0.32+ 起步 | 逐步跟进 |
| 第三方生态 | **极其丰富** | 有限 | 相对封闭 |

这是 PostgreSQL 最难被替代的地方：**别的数据库是「厂商给你什么就用什么」，PostgreSQL 是「社区不断给它长出新能力」。** 从 PostGIS 到 pgvector，每一波技术浪潮里，PG 几乎总能第一时间靠扩展跟上——这份生命力，是二十多年开放生态攒下的。

## 应用场景

- **模糊搜索 / 拼写纠错**：`pg_trgm` 给 `ILIKE '%x%'` 加索引、算相似度。
- **字段级加密 / 校验和**：`pgcrypto` 加密敏感列、算摘要。
- **大小写 / 变音不敏感**：`citext`（用户名邮箱）、`unaccent`（多语言搜索）。
- **性能诊断**：`pg_stat_statements` 找出最烧钱的查询。
- **专门负载**：空间数据上 PostGIS、时序上 TimescaleDB、AI 向量上 pgvector——一个 PG 顶好几个专用库。

## 小结与下一章

`CREATE EXTENSION` 让 PostgreSQL 能按需长出新能力：模糊搜索、加密、空间、时序、向量……它不是一个功能固定的数据库，而是一个**可扩展的数据平台**。这套机制，是它能穿越一波波技术浪潮始终在场的根本原因。

十九个特性讲完了。最后一章，我们把它们收拢成一张「PostgreSQL 到底强在哪」的总表，并给出务实的选型建议。👉 [收尾：PostgreSQL 的护城河与选型建议](https://daiw.org/manual/postgresql/recap)。

## 参考文献

- [1] PostgreSQL 官方文档.「Appendix F. Additional Supplied Modules and Extensions.」https://www.postgresql.org/docs/current/contrib.html —— 官方附带扩展总览。
- [2] PostgreSQL 官方文档.「F.33. pg_trgm.」https://www.postgresql.org/docs/current/pgtrgm.html —— trigram 索引与相似度。
- [3] PostgreSQL 官方文档.「F.25. pgcrypto / F.10. citext / F.47. unaccent.」https://www.postgresql.org/docs/current/pgcrypto.html —— 加密、大小写不敏感、去重音扩展。
- [4] PostGIS 官方站.https://postgis.net —— 空间扩展。pgvector：https://github.com/pgvector/pgvector —— 向量检索扩展。