扩展生态 · 一句话给数据库装新能力 · PostgreSQL 特性详解

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

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

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

CREATE EXTENSION：一句话的事

装扩展就一句，幂等：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

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

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

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

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']

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

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 加盐哈希：

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...

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

citext / unaccent：文本的两个痛点

citext——大小写不敏感的文本类型。邮箱、用户名这种「[email protected] 和 [email protected] 应视为同一个」的字段，用 citext 后比较和唯一约束自动忽略大小写：

CREATE EXTENSION citext;
-- email 列设为 citext + UNIQUE 后:
-- 插入 '[email protected]',再插 '[email protected]' → 被 UNIQUE 拒掉 ✓

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

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 视为同一模板），统计各自的总耗时、调用次数、平均时间——定位慢查询的第一工具，几乎是生产库的标配。

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

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

hstore：JSONB 之前的老兵

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

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

今天新项目基本不必再用 hstore——JSONB 在它能做的一切之上，还支持嵌套、数组、更丰富的运算符和索引。了解 hstore 主要是为了看懂老代码。它的存在也说明了一件事：PostgreSQL 的很多内置能力，当年都是先以扩展形态出现、验证成熟后才「转正」的。

还有一批「重量级」扩展

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

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 的护城河与选型建议。

参考文献

[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 —— 向量检索扩展。

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

本页目录