pgsql-17 扩展插件总览

2025-09-25 约 2429 字预计阅读 5 分钟

1. 概述

可扩展性是 PostgreSQL 最核心的竞争力，也是它区别于 MySQL 的根本设计哲学。PostgreSQL 从底层就支持用户自定义数据类型、操作符、索引方法和函数，因此形成了极其繁荣的**扩展（Extension）**生态——前面学过的 PostGIS（空间）、pgvector（向量）、zhparser（中文分词）都是扩展。

本文系统梳理生产环境最常用的一批扩展，帮助建立"遇到问题先看有没有现成扩展"的思维。

对比 MySQL：MySQL 采用可插拔存储引擎架构（InnoDB/MyISAM），但没有 PostgreSQL 这种通用的扩展机制——你不能像 CREATE EXTENSION 那样一行命令就给 MySQL 加上向量检索、地理空间、定时任务或跨库查询能力。这是 PG 生态远比 MySQL 丰富的根本原因。

2. 扩展的基础操作

-- 查看已安装的扩展
SELECT * FROM pg_extension;

-- 查看当前 PG 可用（已在系统层安装、可 CREATE）的扩展
SELECT name, default_version, comment FROM pg_available_extensions ORDER BY name;

-- 启用 / 升级 / 卸载扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;
ALTER EXTENSION pg_trgm UPDATE;
DROP EXTENSION pg_trgm;

说明：contrib 系列扩展（pg_trgm、pg_stat_statements、hstore 等）随 postgresql-contrib 包一起提供，装好包就能直接 CREATE EXTENSION；PostGIS、pgvector、TimescaleDB 等需单独安装。

3. 性能诊断类

3.1 pg_stat_statements —— 慢查询分析基石

统计每条 SQL 的执行次数、总耗时、平均耗时、扫描行数等，是定位性能瓶颈的头号工具，几乎所有生产库都会开启。

# postgresql.conf 需预加载
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

-- 找出最耗时的 10 条 SQL
SELECT
    substring(query, 1, 60) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2)  AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 重置统计
SELECT pg_stat_statements_reset();

3.2 auto_explain —— 自动记录慢查询执行计划

把超过阈值的查询的执行计划自动写入日志，无需手动 EXPLAIN。

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'   # 超过1秒的查询记录计划
auto_explain.log_analyze = on

3.3 hypopg —— 虚拟索引

在不真正创建索引（不占空间、不锁表）的情况下，验证"如果建了这个索引，查询会不会走它"，用于索引方案评估。

CREATE EXTENSION hypopg;
SELECT hypopg_create_index('CREATE INDEX ON orders (customer_id)');
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;  -- 看是否用虚拟索引

4. 文本与搜索类

4.1 pg_trgm —— 模糊搜索/相似度

基于**三元组（trigram）**做相似度匹配，两大用途：让 LIKE '%x%' 走索引、拼写容错搜索。

CREATE EXTENSION pg_trgm;

-- 让前后通配的 LIKE 用上索引
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);
SELECT * FROM users WHERE name LIKE '%zhang%';   -- 现在能用索引

-- 相似度排序（搜索框纠错）
SELECT name, similarity(name, 'zhangsan') AS sim
FROM users
WHERE name % 'zhangsan'
ORDER BY sim DESC;

4.2 citext —— 大小写不敏感文本

邮箱、用户名等常需忽略大小写比较。用 citext 类型后，'Foo@x.com' = 'foo@x.com' 直接为真，省去到处 lower()。

CREATE EXTENSION citext;
CREATE TABLE accounts (email citext UNIQUE);
INSERT INTO accounts VALUES ('User@Example.com');
SELECT * FROM accounts WHERE email = 'user@example.com';  -- 命中

4.3 unaccent —— 去重音

把 café 归一化为 cafe，常与全文搜索配合处理多语言检索。

5. 数据类型增强类

5.1 uuid-ossp / pgcrypto —— 生成 UUID

分布式系统常用 UUID 做主键（避免自增 ID 的中心化瓶颈与信息泄露）。

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();   -- 随机 UUID

-- PG 13+ 内置 gen_random_uuid()（来自 pgcrypto），更推荐，无需装扩展
SELECT gen_random_uuid();

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data JSONB
);

5.2 hstore —— 键值对

在 JSONB 出现前的键值存储类型，如今多数场景已被 JSONB 取代，但轻量 KV 仍可用。

CREATE EXTENSION hstore;
CREATE TABLE products (id INT, attrs hstore);
INSERT INTO products VALUES (1, 'color=>red, size=>L');
SELECT attrs->'color' FROM products;   -- red

5.3 pgcrypto —— 加密函数

提供哈希、对称/非对称加密，用于密码存储、字段级加密。

CREATE EXTENSION pgcrypto;
-- 密码哈希（bcrypt）
INSERT INTO users(pwd) VALUES (crypt('secret', gen_salt('bf')));
-- 校验
SELECT * FROM users WHERE pwd = crypt('secret', pwd);

6. 运维与自动化类

6.1 pg_cron —— 数据库内定时任务

无需外部 crontab，直接在数据库里跑定时 SQL（清理、聚合、刷新物化视图）。

CREATE EXTENSION pg_cron;
-- 每天凌晨2点清理过期会话
SELECT cron.schedule('cleanup', '0 2 * * *',
    $$DELETE FROM sessions WHERE created_at < now() - interval '7 days'$$);
-- 每5分钟刷新物化视图
SELECT cron.schedule('refresh_mv', '*/5 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary$$);
-- 查看/删除任务
SELECT * FROM cron.job;
SELECT cron.unschedule('cleanup');

6.2 pg_repack —— 在线消除表膨胀

在不长时间锁表的情况下重建表/索引，回收膨胀空间（VACUUM FULL 会锁表，pg_repack 是其在线替代）。表膨胀原理见 VACUUM与表膨胀。

7. 分布式与外部数据类

7.1 postgres_fdw —— 跨库查询（外部数据包装器）

FDW（Foreign Data Wrapper）让你像查本地表一样查另一个 PostgreSQL 实例的表，实现跨库 JOIN、数据同步。还有 mysql_fdw、file_fdw（读 CSV）、oracle_fdw 等。

CREATE EXTENSION postgres_fdw;
-- 定义远程服务器
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '10.0.0.2', port '5432', dbname 'sales');
-- 映射用户
CREATE USER MAPPING FOR current_user SERVER remote_db
    OPTIONS (user 'reader', password 'xxx');
-- 引入远程表
IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER remote_db INTO ext;
-- 像本地表一样查询，甚至和本地表 JOIN
SELECT * FROM ext.orders o JOIN local_customers c ON o.cust_id = c.id;

7.2 Citus —— 水平分片/分布式

把单机 PostgreSQL 扩展成分布式集群，透明地把大表分片到多个节点，适合超大规模 OLTP/OLAP。

8. 场景化"数据库"类扩展

PostgreSQL 靠扩展可以"变身"成各种专用数据库，这是其"一库多用"的魅力：

扩展	让 PG 变成	场景
PostGIS	空间数据库	LBS、地图（见第15篇）
pgvector	向量数据库	AI/RAG（见第16篇）
TimescaleDB	时序数据库	IoT、监控指标
Apache AGE	图数据库	社交关系、知识图谱
ZomboDB	ES 桥接	把 ES 当索引用

8.1 TimescaleDB 示例

CREATE EXTENSION timescaledb;
CREATE TABLE metrics (time TIMESTAMPTZ, device_id INT, value DOUBLE PRECISION);
-- 转成超表（自动按时间分片），写入/时间范围查询大幅加速
SELECT create_hypertable('metrics', 'time');
-- 连续聚合（自动增量维护的物化视图）

9. 面试高频问答

Q1：PostgreSQL 的扩展机制为什么是它的核心优势？ PG 从底层支持自定义类型/操作符/索引方法/函数，因此能通过 CREATE EXTENSION 一行命令为数据库增加空间、向量、时序、图、定时任务、跨库查询等能力，而 MySQL 没有这种通用扩展机制。

Q2：如何定位数据库里最慢的 SQL？ 开启 pg_stat_statements 扩展，按 total_exec_time / mean_exec_time 排序找出热点 SQL；再配合 auto_explain 自动记录慢查询的执行计划。

Q3：怎样让 LIKE '%关键词%' 用上索引？ 安装 pg_trgm 扩展，在列上建 GIN (col gin_trgm_ops) 索引，前后通配的 LIKE 即可走索引；还能用 similarity() 做拼写容错搜索。

Q4：分布式主键为什么常用 UUID？怎么生成？ 自增 ID 依赖单点、易被枚举猜测；UUID 可在各节点本地生成、无中心化瓶颈。PG 13+ 用内置 gen_random_uuid()，或用 uuid-ossp 扩展的 uuid_generate_v4()。

Q5：跨库查询怎么做？ 用 postgres_fdw（或 mysql_fdw 等）建立外部服务器和用户映射，把远程表映射为外部表，即可像本地表一样查询甚至 JOIN。

10. 下一步

学习视图与物化视图，掌握查询复用与结果缓存。

目录