PHP小码农

Appearance

常用扩展（pg_stat_statements / pgcrypto） ​

PostgreSQL 官方发行版附带的 contrib 扩展包，是「装完数据库就该顺手装上」的一批工具。它们覆盖性能诊断、加密、模糊匹配、UUID 生成、数组加速等日常高频场景，而且大多是 trusted 扩展 —— 普通用户在业务库里就能自己装。

但 contrib 里几十个扩展鱼龙混杂：有的必须写进 shared_preload_libraries 才能用，有的装了之后 pg_dump 行为会变，有的（如 tsearch2）早已废弃却被老教程反复推荐。本篇按场景挑出最常用的一批，逐个给出语法、示例与踩坑提示，让你选型时一眼看清「该装哪个、怎么装、有什么代价」。

前置知识

建议先阅读 CREATE EXTENSION 扩展管理，理解扩展的安装语法、trusted 属性、shared_preload_libraries 与 schema 归属规则。

基础概念 ​

contrib 是什么 ​

contrib 是 PostgreSQL 源码树里的 contrib/ 目录，编译安装后被打包成 postgresql-contrib（Debian/Ubuntu）或 postgresqlXX-contrib（RHEL 系）之类的附加包。这些扩展随官方源码发布、由官方维护，但不在内核里，需要显式安装并 CREATE EXTENSION。

sql
-- 有输出说明 contrib 已安装
SELECT count(*) FROM pg_available_extensions;
bash
# Debian / Ubuntu
dpkg -l | grep postgresql-contrib

# RHEL / Rocky / AlmaLinux
rpm -qa | grep postgresql.*contrib

# macOS (Homebrew)
brew list | grep postgresql

按场景分类速查 ​

场景扩展是否需要预加载是否 trusted
SQL 性能统计pg_stat_statements必须否
加密与哈希pgcrypto否是
UUID 生成uuid-ossp / 内置 gen_random_uuid()否是
键值对存储hstore否是
模糊/相似匹配pg_trgm否是
大小写不敏感文本citext否是
音标去重unaccent否是
索引类型扩展btree_gin / btree_gist否是
数组与集合intarray / tablefunc否是
缓存预热pg_prewarm通常需要否
索引/数据校验amcheck否否
执行计划日志auto_explain必须否
缓冲区/页面观察pg_buffercache / pageinspect / pg_visibility通常需要否
地理距离（轻量）earthdistance + cube否是
外部数据包装postgres_fdw / file_fdw否否
大对象 / 网络类型lo / isn否是

安装与查看 ​

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- "uuid-ossp" 名字里带连字符，必须加双引号
CREATE EXTENSION uuid-ossp;
-- ERROR:  syntax error at or near "-"
CREATE EXTENSION "uuid-ossp";
-- CREATE EXTENSION

-- 查看已安装扩展及版本
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;
-- citext 1.6 / hstore 1.8 / pg_trgm 1.6 / pg_stat_statements 1.11 / pgcrypto 1.3 / "uuid-ossp" 1.1

语法与用法 ​

pg_stat_statements 的启用流程 ​

这是唯一「必须三步骤」的常用扩展：

bash
# 步骤 1：写配置（在 postgresql.conf 里，或用 ALTER SYSTEM）
psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'"

# 步骤 2：重启（reload 不够！）
pg_ctl restart -D $PGDATA
sql
-- 步骤 3：在目标库里创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 验证
SELECT count(*) FROM pg_stat_statements;

pgcrypto 的四种能力 ​

sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT encode(digest('hello', 'sha256'), 'hex');                    -- 1. 单向哈希
SELECT encode(hmac('message', 'secret-key', 'sha256'), 'hex');      -- 2. HMAC 签名
SELECT crypt('my_password', gen_salt('bf', 12));                    -- 3. 密码哈希（加盐 + 慢哈希）
SELECT pgp_sym_encrypt('机密内容', 'passphrase');                   -- 4. 对称加密

pg_trgm 的相似度查询 ​

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT similarity('postgresql', 'postgres');   -- 相似度打分（0~1）
SELECT 'postgresql' % 'postgres';              -- true（% 即阈值判断）
SELECT 'postgresql' % 'mysql';                 -- false

-- 模糊搜索 + 索引
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
SELECT * FROM users WHERE name % 'jon' ORDER BY similarity(name, 'jon') DESC LIMIT 10;

hstore 的键值对操作 ​

sql
CREATE EXTENSION IF NOT EXISTS hstore;

SELECT 'a=>1, b=>2'::hstore;                          -- 构造
SELECT hstore(ARRAY['a','b'], ARRAY['1','2']);        -- 从数组构造
SELECT ('a=>1'::hstore) -> 'a';                       -- 取值
SELECT ('a=>1'::hstore) ? 'a';                        -- 是否存在键
SELECT ('a=>1, b=>2'::hstore) ?& ARRAY['a','b'];      -- 是否同时存在
SELECT hstore_to_json('a=>1, b=>2'::hstore);          -- 转 JSON

详细说明 ​

pg_stat_statements：语句统计的完整视角 ​

这是生产环境第一个该装的扩展。它聚合所有被执行的 SQL，给出调用次数、累计耗时、I/O、WAL 等指标。注意 PostgreSQL 13 起 total_time 已改名为 total_exec_time，规划耗时拆到 total_plan_time。

关键列：

列含义
userid / dbid执行者 / 数据库的 OID
toplevel是否顶层语句（非函数内嵌套）
queryid / query归一化后的语句指纹 / 文本
calls执行次数
total_plan_time / mean_plan_time规划总耗时 / 平均耗时（毫秒）
total_exec_time / mean_exec_time / min_exec_time / max_exec_time执行耗时统计（毫秒）
rows返回或影响的行数
shared_blks_hit / shared_blks_read / shared_blks_written共享缓冲区计数
temp_blks_read / temp_blks_written临时文件块读写
wal_records / wal_fpi / wal_bytesWAL 相关
jit_*JIT 编译统计

最常用的「找最耗资源的 SQL」查询，以及按「平均耗时高且调用多」「产生大量临时文件」两种口径筛选：

sql
-- 1) 总耗时 top 20
SELECT calls,
  round(total_exec_time::numeric, 2)  AS total_ms,
  round(mean_exec_time::numeric, 2)   AS mean_ms,
  round(100 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct,
  rows, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- 2) 平均耗时高且调用多
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 80)
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC LIMIT 20;

-- 3) 产生临时文件（work_mem 可能不够）
SELECT calls, temp_blks_read + temp_blks_written AS temp_blks, left(query, 80)
FROM pg_stat_statements
WHERE temp_blks_read + temp_blks_written > 0
ORDER BY temp_blks DESC LIMIT 20;

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint, minmax_only boolean) 可整体或部分清零：

sql
SELECT pg_stat_statements_reset();                     -- 全部清零
SELECT pg_stat_statements_reset(minmax_only => true);  -- 只清 min/max 统计

重要安全限制：只有超级用户和 pg_read_all_stats 角色能看到别人的 SQL 文本；其他用户看到的同类语句，query 列会被替换成 <insufficient privilege>，可这样授权：

sql
GRANT pg_read_all_stats TO app_developer;

跟踪规模相关 GUC（改这些同样需要重启）：

text
pg_stat_statements.max             默认 5000，内存中保留的语句数
pg_stat_statements.track           默认 top，可选 all / none
pg_stat_statements.track_utility   是否跟踪 DDL 等工具类语句
pg_stat_statements.track_planning  是否跟踪规划耗时
pg_stat_statements.save            重启后是否保留统计

pgcrypto：哈希、加盐与加密 ​

pgcrypto 是 trusted 扩展，普通用户即可安装。

sql
SELECT encode(digest('hello', 'sha256'), 'hex')    AS hex;
SELECT encode(digest('hello', 'sha256'), 'base64') AS b64;
SELECT encode(hmac('order_id=123&amount=99', 'my-secret', 'sha256'), 'hex');

digest(data, type) 支持的 type：md5、sha1、sha224、sha256、sha384、sha512。密码哈希（推荐做法）：

sql
SELECT crypt('user_password', gen_salt('bf', 12));

gen_salt(type [, iter_count]) 支持的算法：

type算法说明
bfBlowfish可调迭代，推荐；默认迭代 6
sha256crypt / sha512cryptSHA-2 crypt默认迭代 5000
md5 / xdes / des老式算法弱，不推荐

对称加密与算法选项：

sql
SELECT pgp_sym_decrypt(pgp_sym_encrypt('机密', 'passphrase'), 'passphrase');

SELECT pgp_sym_encrypt(
  '信用卡号 4111-1111-1111-1111',
  'passphrase',
  'cipher-algo=aes256, compress-algo=1, s2k-mode=3'
);
选项默认可选值
cipher-algoaes128bf、aes128、aes192、aes256、3des、cast5
compress-algo0（不压缩）1（ZIP）、2（ZLIB）
s2k-mode30~3

ASCII armor（把二进制转文本，便于放进文本字段，用 armor() / dearmor()）：

sql
SELECT armor(pgp_sym_encrypt('secret', 'passphrase'));
SELECT pgp_sym_decrypt(dearmor(armored_text), 'passphrase');

uuid-ossp 与内置 gen_random_uuid() ​

PostgreSQL 13 起内置了 gen_random_uuid()，无需任何扩展，这是首选。需要 v1/v5 等版本时才装 uuid-ossp：

sql
CREATE TABLE orders (
  id     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  amount numeric NOT NULL
);

需要 v1/v5 等版本时才装 uuid-ossp：

sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v1();    -- 基于 MAC + 时间，可泄露主机信息
SELECT uuid_generate_v1mc();  -- v1 变体，随机多播 MAC
SELECT uuid_generate_v4();    -- 随机
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com');  -- 基于名字的 SHA-1
函数版本随机性是否需要扩展建议
gen_random_uuid()v4强随机否默认选它
uuid_generate_v4()v4强随机是老代码兼容
uuid_generate_v5()v5确定性（同名同值）是需要幂等 ID 时用

UUID 主键的代价：随机分布导致 B-tree 插入位置随机，写放大明显。高写入量场景可考虑时间有序 UUID。

pg_trgm：模糊匹配与相似检索 ​

pg_trgm 的核心是「三元组（trigram）」：把字符串切成连续的 3 字符片段，用片段交集衡量相似度。

sql
SELECT show_trgm('postgres');   -- {"  p"," po",gre,ost,"p  ",pos,res,sgr,tgr}
SELECT similarity('postgresql', 'postgres');         -- 约 0.545
SELECT word_similarity('postgres', 'PostgreSQL 18'); -- 词级相似度

操作符：

操作符含义
%相似度超过 pg_trgm.similarity_threshold
<% / %>词级相似（以某一侧为参照）
<<% / %>>严格词级相似
<->距离（1 - 相似度），可 ORDER BY

阈值由 GUC 控制（旧版的 set_limit() / show_limit() 已废弃）：

sql
SHOW pg_trgm.similarity_threshold;             -- 默认 0.3
SHOW pg_trgm.word_similarity_threshold;        -- 默认 0.6
SET pg_trgm.similarity_threshold = 0.4;

两种索引各有侧重，建好索引后分别这样查：

sql
-- GIN：查询更快、构建更慢，不支持 <-> KNN 排序；适合 name % 'x' 模糊搜索
CREATE INDEX idx_name_gin ON users USING gin (name gin_trgm_ops);
SELECT id, name, similarity(name, 'johnson') AS sim
FROM users WHERE name % 'johnson' ORDER BY sim DESC LIMIT 10;

-- GiST：支持 ORDER BY <-> 近邻排序，精度受 siglen 影响
CREATE INDEX idx_name_gist ON users USING gist (name gist_trgm_ops(siglen=32));
SELECT id, name FROM users ORDER BY name <-> 'johnson' LIMIT 10;

hstore：无需 schema 的键值对 ​

hstore 存的是「text → text」的映射，适合「属性数量不固定」的场景，例如商品的自定义标签。

sql
CREATE TABLE products (
  id    bigserial PRIMARY KEY,
  name  text NOT NULL,
  attrs hstore
);

INSERT INTO products (name, attrs) VALUES
  ('手机', 'color=>黑色, storage=>256G'),
  ('笔记本', 'color=>银色, ram=>16G, storage=>512G');

操作符与函数速查：

操作符含义
->取单个键的值
?是否存在某键
?&是否同时存在所有键
?|是否存在任一键
@> / <@是否包含 / 被包含
-删除键（可传数组）
||合并
#=更新指定键
sql
SELECT akeys(attrs), avals(attrs) FROM products WHERE id = 1;
SELECT * FROM each('a=>1, b=>2'::hstore);          -- 每行一个键值对
SELECT hstore_to_jsonb(attrs) FROM products WHERE id = 1;
SELECT exist('a=>1'::hstore, 'a');                 -- 键是否存在
SELECT defined('a=>NULL'::hstore, 'a');            -- 键存在且值非 NULL

-- GIN：适合 @> 包含查询与 ? 存在查询
CREATE INDEX idx_products_attrs_gin ON products USING gin (attrs);

hstore 的边界：只能存文本，没有嵌套结构。经验法则 —— 结构固定且有查询需求 → 建列；结构松散且整体读写 → jsonb；扁平键值对且需要 @> 索引 → hstore。

btree_gin / btree_gist：让组合索引覆盖多类型 ​

问题场景：btree 索引不能直接和 gin 混用。假如要在 (status, tags) 上建索引，status 是 text（适合 btree），tags 是 text[]（需要 GIN），单一索引类型满足不了。

sql
CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE TABLE articles (
  id     bigserial PRIMARY KEY,
  status text,
  tags   text[],
  title  text
);

-- GIN 里同时支持标量等值与数组包含
CREATE INDEX idx_articles_gin ON articles USING gin (status, tags);

EXPLAIN ANALYZE
SELECT * FROM articles
WHERE status = 'published' AND tags @> ARRAY['postgresql'];

btree_gist 面向 GiST，更重要的能力是支持排他约束（EXCLUDE constraint）与区间类型组合：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 会议室预订：同房间同时间段不允许重叠
CREATE TABLE room_bookings (
  room_id int,
  during  tsrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

INSERT INTO room_bookings VALUES (1, '[2026-01-01 09:00, 2026-01-01 10:00)');
INSERT INTO room_bookings VALUES (1, '[2026-01-01 09:30, 2026-01-01 10:30)');
-- ERROR:  conflicting key value violates exclusion constraint

这是「会议室/车位/资源预订」类的经典实现，纯内核做不到。

文本处理：citext 与 unaccent ​

原生 text 比较是大小写敏感的，常见做法是建函数索引：

sql
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));

citext 是一个大小写不敏感的字符串类型，让这件事更自然：

sql
CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE accounts (
  id    bigserial PRIMARY KEY,
  email citext UNIQUE NOT NULL
);

INSERT INTO accounts (email) VALUES ('Alice@Example.com');
INSERT INTO accounts (email) VALUES ('alice@example.com');
-- ERROR:  duplicate key value violates unique constraint

SELECT * FROM accounts WHERE email = 'ALICE@EXAMPLE.COM';  -- 命中

代价：每次比较都要调用 lower()，比原生 text 慢，且行为依赖 lc_ctype / 排序规则。

unaccent 处理多语言数据：用户输入 cafe 而数据里是 café。

sql
CREATE EXTENSION IF NOT EXISTS unaccent;

SELECT unaccent('café');       -- cafe
SELECT unaccent('München');    -- Munchen
SELECT unaccent('Ångström');   -- Angstrom

它还可以作为文本搜索字典使用：

sql
CREATE TEXT SEARCH DICTIONARY my_unaccent (TEMPLATE = unaccent, RULES = 'unaccent');

CREATE TEXT SEARCH CONFIGURATION my_search (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION my_search
  ALTER MAPPING FOR hword, hword_part, word
  WITH my_unaccent, simple;

SELECT to_tsvector('my_search', 'café au lait');

在索引里用它需要包一层（unaccent 的 volatility 可能不满足函数索引要求）：

sql
CREATE OR REPLACE FUNCTION f_unaccent(text)
RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS $$
  SELECT public.unaccent('public.unaccent', $1)
$$;

CREATE INDEX idx_places_name_unaccent ON places (f_unaccent(lower(name)));

这是在绕过 IMMUTABLE 检查，需要你自行保证规则文件不变。

观察与诊断：pg_buffercache / pageinspect / amcheck / pg_prewarm / auto_explain ​

这一类扩展直接读取物理文件与共享内存，因此大多需要超级用户，且主要用于排障而非业务。

pg_buffercache 看共享缓冲区里缓存了什么：

sql
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT c.relname, count(*) AS buffers,
       pg_size_pretty(count(*) * current_setting('block_size')::bigint) AS size,
       round(avg(usagecount), 2) AS avg_usage
FROM pg_buffercache b JOIN pg_class c ON c.relfilenode = b.relfilenode
GROUP BY 1 ORDER BY buffers DESC LIMIT 20;

pageinspect 直接读数据页头部，用于验证页结构与诊断损坏；pg_visibility 检查可见性映射（VM）判断 VACUUM 效果；pgrowlocks 看行锁；pg_freespacemap 看页内空闲空间：

sql
CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION IF NOT EXISTS pg_visibility;
CREATE EXTENSION IF NOT EXISTS pgrowlocks;

SELECT * FROM page_header(get_raw_page('users', 0));      -- lsn / checksum / flags / lower / upper / ...
SELECT * FROM pg_visibility_map_summary('users');         -- 全可见/全冻结页统计
SELECT * FROM pgrowlocks('accounts');

amcheck 用来校验索引/堆的物理一致性，是排查「索引损坏」的利器：

sql
CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_check('idx_users_name');                          -- 结构校验
SELECT bt_index_check('idx_users_name', heapallindexed => true);  -- 额外校验堆-索引一致性
SELECT bt_index_parent_check('idx_users_name');                   -- 校验父子页一致性

遇到损坏时的处理思路：先 REINDEX INDEX ...；若也失败，用 pg_dump 导出重建，并同步检查硬件与日志里的 checksum failure。

pg_prewarm 把表或索引主动读进缓存，避免冷启动时的第一批慢查询；auto_explain 自动把慢查询的执行计划写进日志，无需改应用代码：

sql
CREATE EXTENSION IF NOT EXISTS pg_prewarm;

SELECT pg_prewarm('users');           -- 读进 OS 缓存
SELECT pg_prewarm('users', 'buffer'); -- 读进共享缓冲区
text
# postgresql.conf，两者都需放进 shared_preload_libraries
pg_prewarm.autoprewarm = on
pg_prewarm.autoprewarm_interval = 300s

auto_explain.log_min_duration = '1s'
auto_explain.log_analyze  = on
auto_explain.log_buffers  = on
auto_explain.log_format   = 'json'

auto_explain 也可以按会话临时打开（前提是已预加载）：

sql
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '0';
SET auto_explain.log_analyze = on;
SELECT count(*) FROM big_table;

日志中会得到形如 LOG: duration: 1234.567 ms plan: {...} 的 JSON 计划。

轻量地理：cube 与 earthdistance ​

不想上 PostGIS 的重量级部署时，cube + earthdistance 可以做「经纬度点之间的距离计算」与「就近检索」。

sql
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

CREATE TABLE shops (
  id   bigserial PRIMARY KEY,
  name text NOT NULL,
  lat  double precision NOT NULL,
  lon  double precision NOT NULL
);
INSERT INTO shops (name, lat, lon) VALUES ('天安门店', 39.9087, 116.3975);

-- ll_to_earth 把经纬度转成 cube 的 3D 点，从而能建 GiST 索引
CREATE INDEX idx_shops_earth ON shops USING gist (ll_to_earth(lat, lon));

-- 查找 5 公里内的门店：earth_box 用索引粗筛，earth_distance 精筛
SELECT name,
       round(earth_distance(ll_to_earth(39.9087, 116.3975), ll_to_earth(lat, lon))::numeric, 0) AS meters
FROM shops
WHERE earth_box(ll_to_earth(39.9087, 116.3975), 5000) @> ll_to_earth(lat, lon)
  AND earth_distance(ll_to_earth(39.9087, 116.3975), ll_to_earth(lat, lon)) < 5000
ORDER BY meters;

这个「先箱后距」的模式是地理检索的通用套路，PostGIS 的 ST_DWithin 内部同理。earthdistance 的局限：只支持「点」，不支持多边形、线、相交判断；距离模型粗糙；没有 SRID 概念。需求一旦超出「点对点距离」，就该换 PostGIS。

数据访问与类型扩展：intarray / tablefunc / isn / tsearch2 ​

这几个扩展或其同类（postgres_fdw、file_fdw、lo）的完整用法超出本篇范围，这里只做定位与最小示例。file_fdw 只读，且只能由超级用户创建外部表（因为能读服务器文件系统）；postgres_fdw 用于跨 PostgreSQL 实例访问表，详见 FDW 性能调优；lo 提供早期的大对象客户端接口，新项目如需大对象优先用 bytea（详见 bytea 与 lo 的取舍）。

intarray 为 integer[] 提供集合运算与索引加速：

sql
CREATE EXTENSION IF NOT EXISTS intarray;

SELECT sort(uniq(ARRAY[3,1,2,1]));      -- {1,2,3}
SELECT ARRAY[1,2,3] & ARRAY[2,3,4];     -- 交集 {2,3}
SELECT ARRAY[1,2,3] | ARRAY[2,3,4];     -- 并集 {1,2,3,4}
SELECT ARRAY[1,2,3] @> ARRAY[1,2];      -- true
SELECT ARRAY[1,2,3] && ARRAY[3,4];      -- true（有交集）

CREATE INDEX idx_tags_gin ON articles USING gin (tag_ids gin__int_ops);

tablefunc 提供交叉表（pivot）等表生成函数：

sql
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$SELECT quarter, region, amount FROM sales ORDER BY 1, 2$$,
  $$VALUES ('east'), ('west')$$
) AS ct(quarter text, east numeric, west numeric);
--  quarter | east | west
-- ---------+------+------
--  Q1      |  100 |  150
--  Q2      |  120 |  160

isn 提供 ISBN、EAN、UPC、ISMN、ISSN 等标准编号类型，自带校验位验证：

sql
CREATE EXTENSION IF NOT EXISTS isn;

SELECT '978-0-201-89683-8'::isbn;
SELECT isbn13('9780201896838');

tsearch2 是 PostgreSQL 8.3 之前的内置全文检索扩展，早已废弃，新老项目都不应引入：内核从 8.3 起就内置了 tsvector / tsquery / GIN，能力完全覆盖且更优，而 tsearch2 的二进制在现代发行版里基本已不存在。

sql
SELECT * FROM pg_available_extensions WHERE name = 'tsearch2';
-- 0 rows（现代版本里已无此扩展）

-- 正确姿势：用内核能力
SELECT to_tsvector('simple', 'PostgreSQL 全文检索 tutorial');
SELECT plainto_tsquery('simple', 'postgresql tutorial');

CREATE INDEX idx_articles_fts
  ON articles USING gin (to_tsvector('simple', title || ' ' || body));

实战示例 ​

场景一：新项目标准扩展清单 ​

sql
-- 项目初始化脚本 db/00_extensions.sql（可重入）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;   -- 性能诊断，需先预加载并重启
CREATE EXTENSION IF NOT EXISTS pgcrypto;             -- 加密
CREATE EXTENSION IF NOT EXISTS pg_trgm;              -- 模糊匹配
CREATE EXTENSION IF NOT EXISTS unaccent;             -- 音标去重
CREATE EXTENSION IF NOT EXISTS hstore;               -- 键值对
CREATE EXTENSION IF NOT EXISTS citext;               -- 大小写不敏感
CREATE EXTENSION IF NOT EXISTS btree_gin;            -- 混合索引

-- 验证：查已装版本
SELECT name, installed_version FROM pg_available_extensions
WHERE installed_version IS NOT NULL ORDER BY name;

场景二：慢 SQL 排查工作流 ​

sql
-- 1. 找总耗时 top 10（含占比）
SELECT calls, round(total_exec_time::numeric, 1) AS total_ms,
       round(100 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct,
       left(query, 100) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- 2. 针对可疑 queryid 看完整语句与详细指标
SELECT * FROM pg_stat_statements WHERE queryid = 1234567890123456789;

-- 3. 重置并观察优化效果
SELECT pg_stat_statements_reset();

场景三：用户密码表完整实现 ​

sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE accounts (
  id            bigserial PRIMARY KEY,
  email         citext UNIQUE NOT NULL,
  password_hash text NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- 注册：校验长度后用 crypt + gen_salt 存慢哈希
CREATE OR REPLACE FUNCTION register_account(p_email text, p_password text)
RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE v_id bigint;
BEGIN
  IF length(p_password) < 8 THEN RAISE EXCEPTION 'password too short'; END IF;
  INSERT INTO accounts (email, password_hash)
  VALUES (p_email, crypt(p_password, gen_salt('bf', 12)))
  RETURNING id INTO v_id;
  RETURN v_id;
END;
$$;

-- 登录校验：用存的哈希做 salt 重算，相等即通过
CREATE OR REPLACE FUNCTION verify_account(p_email text, p_password text)
RETURNS boolean LANGUAGE sql STABLE AS $$
  SELECT EXISTS (SELECT 1 FROM accounts
    WHERE email = p_email AND password_hash = crypt(p_password, password_hash));
$$;

SELECT register_account('Alice@Example.com', 's3cretpassword');
SELECT verify_account('alice@example.com', 's3cretpassword');  -- true
SELECT verify_account('alice@example.com', 'wrong');           -- false

场景四：会议室预订排他约束 ​

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE rooms (
  id   int PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE bookings (
  id      bigserial PRIMARY KEY,
  room_id int NOT NULL REFERENCES rooms(id),
  during  tsrange NOT NULL,
  who     text NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

INSERT INTO rooms VALUES (1, 'A 会议室'), (2, 'B 会议室');

-- 正常预订
INSERT INTO bookings (room_id, during, who)
VALUES (1, '[2026-02-01 09:00, 2026-02-01 10:00)', 'alice');

-- 相邻不重叠，允许
INSERT INTO bookings (room_id, during, who)
VALUES (1, '[2026-02-01 10:00, 2026-02-01 11:00)', 'bob');

-- 重叠，被拒
INSERT INTO bookings (room_id, during, who)
VALUES (1, '[2026-02-01 09:30, 2026-02-01 10:30)', 'carol');
-- ERROR:  conflicting key value violates exclusion constraint

-- 换个会议室就允许
INSERT INTO bookings (room_id, during, who)
VALUES (2, '[2026-02-01 09:30, 2026-02-01 10:30)', 'carol');

场景五：就近检索（earthdistance） ​

sql
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

CREATE TABLE stores (
  id   bigserial PRIMARY KEY,
  name text NOT NULL,
  lat  double precision NOT NULL,
  lon  double precision NOT NULL
);

INSERT INTO stores (name, lat, lon) VALUES
  ('北京国贸店', 39.9087, 116.3975),
  ('北京中关村店', 39.9836, 116.3164),
  ('上海外滩店', 31.2397, 121.4900),
  ('天津和平路店', 39.1285, 117.2015);

CREATE INDEX idx_stores_earth ON stores USING gist (ll_to_earth(lat, lon));

-- 以北京国贸为中心，找 30 公里内的门店
WITH center AS (
  SELECT ll_to_earth(39.9087, 116.3975) AS p
)
SELECT s.name,
       round(earth_distance(center.p, ll_to_earth(s.lat, s.lon))::numeric / 1000, 2) AS km
FROM stores s, center
WHERE earth_box(center.p, 30000) @> ll_to_earth(s.lat, s.lon)
  AND earth_distance(center.p, ll_to_earth(s.lat, s.lon)) < 30000
ORDER BY km;
-- 北京国贸店 0.00 km / 北京中关村店 8.63 km / 天津和平路店 118.65 km

注意事项 ​

注意一：pg_stat_statements 只写配置不重启，等于没装。

bash
pg_ctl reload -D $PGDATA   # 无效！reload 不会加载共享库
pg_ctl restart -D $PGDATA  # 这才有效

注意二：tsearch2 早已废弃，不要照抄老教程。 改用内置的 tsvector / tsquery / GIN：

sql
-- 错误！现代 PostgreSQL 里根本没有这个扩展
CREATE EXTENSION tsearch2;
-- ERROR:  extension "tsearch2" is not available

注意三：set_limit() 已废弃。 旧 API 应替换为 GUC：

sql
-- 不推荐（旧 API）
SELECT set_limit(0.4);
-- 推荐
SET pg_trgm.similarity_threshold = 0.4;

注意四：GIN 索引不支持 trigram 的 KNN 排序。 gin_trgm_ops 配 GIN，gist_trgm_ops 配 GiST，写错会直接报错：

sql
-- 错误！GIN 不支持 <-> 排序
CREATE INDEX idx_bad ON users USING gin (name gist_trgm_ops);

注意五：pgcrypto 的 crypt() 不能用于通用哈希。 通用哈希用 digest()，密码存储用 crypt(..., gen_salt('bf', 12))：

sql
-- 错误！crypt 是密码哈希格式专用
SELECT crypt('hello', 'sha256');

注意六：随机 UUID 做主键会放大写压力。 高写入表不建议直接用全随机 UUID 主键：

sql
-- 不推荐
CREATE TABLE events (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), ...);

注意七：citext 只对声明为 citext 的列生效。 列定义成 text 时不会改变比较行为：

sql
-- 错误！列是 text，citext 不会改变比较行为
CREATE TABLE t (email text);   -- 应为 citext

注意八：unaccent 用于函数索引需要包一层 IMMUTABLE 包装函数（见前文 f_unaccent）：

sql
-- 错误！unaccent 的 volatility 可能达不到索引要求
CREATE INDEX idx_bad ON places (unaccent(lower(name)));

注意九：pg_buffercache / pageinspect 等观察类扩展需要超级用户。

sql
-- 普通用户执行
SELECT * FROM pg_buffercache;
-- ERROR:  permission denied for function pg_buffercache_pages

注意十：不要在生产高峰用 pg_prewarm 预热超大表。 会把大量数据挤进缓存、冲击正常业务：

sql
-- 不推荐
SELECT pg_prewarm('huge_table');

最佳实践 ​

  1. 把标准扩展清单写进项目初始化脚本（CREATE EXTENSION IF NOT EXISTS），保证新环境一次到位、脚本可重入。

  2. pg_stat_statements 在生产环境必装，并授权 pg_read_all_stats 给需要的开发/运维角色。

  3. 密码存储只认 crypt(..., gen_salt('bf', N))，并用 crypt(input, stored_hash) 校验；永远不要自己拼哈希。

  4. UUID 主键优先用内置 gen_random_uuid()，除非必须 v1/v5 才引入 uuid-ossp。

  5. 模糊搜索用 pg_trgm 的 GIN 索引；需要「按相似度排序」时才换 GiST 走 KNN。

  6. hstore 与 jsonb 不要混用：二维扁平键值对用 hstore，任何需要嵌套/数组/类型保留的场景用 jsonb。

  7. 区间排他约束用 btree_gist + EXCLUDE，而不是在应用层用「先查再插」的竞态逻辑。

  8. 大小写不敏感只用在真正需要的列上（如 email），单字段用 lower() 函数索引往往更可控。

  9. 定期用 amcheck 抽查核心业务的索引，尤其在发生过硬件告警、checksum failure 之后。

  10. 地理需求做分级：「点对点距离 + 就近排序」用 earthdistance；一旦涉及多边形、线、相交、坐标系转换，直接上 PostGIS。

  11. 观察类与诊断类扩展只给运维/DBA 使用，不要让业务账号依赖它们的输出。

  12. 不要在 template1 里预装第三方重型扩展；contrib 扩展相对安全，但版本升级仍会波及所有基于模板创建的新库。

下一节

下一篇 PostGIS 地理扩展 会进入第三方扩展的世界，从 geometry / geography 类型的选择，到 ST_DWithin 的就近检索与 <-> 空间 KNN 排序，把地理信息系统的核心用法讲清楚。

参考链接 ​