PostgreSQL 常用扩展 #

扩展概述 #

PostgreSQL 的扩展系统是其强大功能的重要来源。

text

┌─────────────────────────────────────────────────────────────┐
│                    扩展类型                                  │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  数据类型扩展                                                │
│  ├── PostGIS    - 地理空间数据                              │
│  ├── hstore     - 键值存储                                  │
│  └── citext     - 大小写不敏感文本                          │
│                                                             │
│  功能扩展                                                    │
│  ├── pg_stat_statements - SQL 统计                         │
│  ├── pg_trgm     - 相似度搜索                               │
│  └── pgcrypto    - 加密函数                                 │
│                                                             │
│  性能扩展                                                    │
│  ├── pg_prewarm  - 预热缓存                                 │
│  └── pg_buffercache - 缓存监控                              │
│                                                             │
└─────────────────────────────────────────────────────────────┘

扩展管理 #

查看和安装扩展 #

sql

-- 查看可用扩展
SELECT * FROM pg_available_extensions;

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

-- 安装扩展
CREATE EXTENSION extension_name;

-- 安装特定版本
CREATE EXTENSION extension_name VERSION '1.0';

-- 安装到特定模式
CREATE EXTENSION extension_name SCHEMA my_schema;

-- 更新扩展
ALTER EXTENSION extension_name UPDATE;

-- 删除扩展
DROP EXTENSION extension_name;

PostGIS #

安装和基本使用 #

sql

-- 安装 PostGIS
CREATE EXTENSION postgis;

-- 检查版本
SELECT PostGIS_Version();

-- 创建空间数据表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

-- 插入点数据
INSERT INTO locations (name, geom) VALUES
    ('Beijing', ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)),
    ('Shanghai', ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)),
    ('Guangzhou', ST_SetSRID(ST_MakePoint(113.2644, 23.1291), 4326));

-- 创建空间索引
CREATE INDEX idx_locations_geom ON locations USING gist(geom);

空间查询 #

sql

-- 计算距离（米）
SELECT 
    name,
    ST_Distance(
        geom::geography,
        ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography
    ) AS distance_meters
FROM locations
ORDER BY distance_meters;

-- 查找附近的位置
SELECT name 
FROM locations 
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
    500000  -- 500 公里
);

-- 创建多边形
CREATE TABLE areas (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Polygon, 4326)
);

INSERT INTO areas (name, geom) VALUES
    ('Zone A', ST_MakePolygon(ST_GeomFromText(
        'LINESTRING(116.0 39.0, 117.0 39.0, 117.0 40.0, 116.0 40.0, 116.0 39.0)', 4326
    )));

-- 点是否在多边形内
SELECT l.name
FROM locations l, areas a
WHERE ST_Within(l.geom, a.geom);

pg_stat_statements #

安装和配置 #

sql

-- 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 需要在 postgresql.conf 中配置
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

-- 重启 PostgreSQL 后生效

查询统计 #

sql

-- 查看最慢的查询
SELECT 
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) over())::numeric, 2) AS percent,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查看执行次数最多的查询
SELECT 
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- 查看最耗 IO 的查询
SELECT 
    calls,
    shared_blks_hit,
    shared_blks_read,
    query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

-- 重置统计
SELECT pg_stat_statements_reset();

pg_trgm #

安装和使用 #

sql

-- 安装扩展
CREATE EXTENSION pg_trgm;

-- 相似度计算
SELECT similarity('hello', 'hallo');
-- 0.6

-- 显示相似度
SELECT show_trgm('hello');
-- {"  h"," he",ell,hel,llo,"lo "}

-- 相似度搜索
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO products (name) VALUES
    ('iPhone 15 Pro'),
    ('iPhone 14 Pro'),
    ('Samsung Galaxy'),
    ('Google Pixel');

-- 创建 GIN 索引
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);

-- 相似度搜索
SELECT name, similarity(name, 'iphone') AS sim
FROM products
WHERE name % 'iphone'
ORDER BY sim DESC;

-- 使用 % 操作符（相似度阈值）
SELECT name FROM products WHERE name % 'iphone';

-- 使用 <% 操作符（相似度排序）
SELECT name FROM products WHERE name <% 'iphone' ORDER BY name <-> 'iphone';

相似度阈值设置 #

sql

-- 查看当前阈值
SELECT show_limit();

-- 设置阈值（0-1）
SET pg_trgm.similarity_threshold = 0.3;

-- 或在配置文件中设置
-- pg_trgm.similarity_threshold = 0.3

pgcrypto #

加密函数 #

sql

-- 安装扩展
CREATE EXTENSION pgcrypto;

-- MD5 哈希
SELECT md5('password');

-- SHA-256 哈希
SELECT encode(digest('password', 'sha256'), 'hex');

-- bcrypt 密码哈希
SELECT crypt('password', gen_salt('bf'));

-- 验证密码
SELECT crypt('password', '$2a$06$...') = crypt('password', '$2a$06$...');

-- 生成随机盐
SELECT gen_salt('bf');  -- bcrypt
SELECT gen_salt('bf', 10);  -- 指定成本因子

-- AES 加密
SELECT encrypt('secret data', 'my_key', 'aes');
SELECT decrypt(encrypt('secret data', 'my_key', 'aes'), 'my_key', 'aes');

-- PGP 加密
SELECT pgp_sym_encrypt('secret data', 'my_password');
SELECT pgp_sym_decrypt(pgp_sym_encrypt('secret data', 'my_password'), 'my_password');

存储密码示例 #

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    password_hash TEXT
);

-- 插入用户（使用 bcrypt）
INSERT INTO users (username, password_hash)
VALUES ('alice', crypt('mypassword', gen_salt('bf')));

-- 验证密码
SELECT * FROM users 
WHERE username = 'alice' 
  AND password_hash = crypt('mypassword', password_hash);

hstore #

键值存储 #

sql

-- 安装扩展
CREATE EXTENSION hstore;

-- 创建表
CREATE TABLE user_attributes (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    attrs HSTORE
);

-- 插入数据
INSERT INTO user_attributes (user_id, attrs) VALUES
    (1, '"theme"=>"dark", "language"=>"zh-CN", "notifications"=>"true"'),
    (2, '"theme"=>"light", "language"=>"en-US"');

-- 获取值
SELECT attrs -> 'theme' FROM user_attributes WHERE user_id = 1;

-- 设置值
UPDATE user_attributes 
SET attrs = attrs || '"timezone"=>"Asia/Shanghai"'::hstore
WHERE user_id = 1;

-- 删除键
UPDATE user_attributes 
SET attrs = delete(attrs, 'timezone')
WHERE user_id = 1;

-- 查询包含键的行
SELECT * FROM user_attributes WHERE attrs ? 'theme';

-- 查询键值匹配
SELECT * FROM user_attributes WHERE attrs @> '"theme"=>"dark"';

-- 获取所有键
SELECT skeys(attrs) FROM user_attributes;

-- 获取所有值
SELECT svals(attrs) FROM user_attributes;

-- 转换为 JSON
SELECT hstore_to_json(attrs) FROM user_attributes;

citext #

大小写不敏感文本 #

sql

-- 安装扩展
CREATE EXTENSION citext;

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username CITEXT UNIQUE,
    email CITEXT
);

-- 插入数据
INSERT INTO users (username, email) VALUES
    ('Alice', 'ALICE@example.com'),
    ('Bob', 'bob@example.com');

-- 大小写不敏感查询
SELECT * FROM users WHERE username = 'alice';  -- 可以找到 Alice
SELECT * FROM users WHERE email = 'Alice@Example.com';  -- 可以找到 ALICE@example.com

-- UNIQUE 约束也是大小写不敏感的
-- INSERT INTO users (username) VALUES ('alice');  -- 会失败，因为 Alice 已存在

uuid-ossp #

UUID 生成 #

sql

-- 安装扩展
CREATE EXTENSION "uuid-ossp";

-- 生成 UUID v4（随机）
SELECT uuid_generate_v4();

-- 生成 UUID v1（基于时间戳）
SELECT uuid_generate_v1();

-- 在表中使用
CREATE TABLE sessions (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PostgreSQL 13+ 内置函数
SELECT gen_random_uuid();

pg_buffercache #

缓存监控 #

sql

-- 安装扩展
CREATE EXTENSION pg_buffercache;

-- 查看缓存使用情况
SELECT 
    c.relkind,
    c.relname,
    count(*) AS buffers,
    pg_size_pretty(count(*) * 8192::bigint) AS size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY c.relkind, c.relname
ORDER BY count(*) DESC
LIMIT 20;

-- 查看表的缓存命中率
SELECT 
    relname,
    heap_blks_read,
    heap_blks_hit,
    round(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2) AS hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read + heap_blks_hit > 0
ORDER BY hit_ratio DESC;

pg_prewarm #

缓存预热 #

sql

-- 安装扩展
CREATE EXTENSION pg_prewarm;

-- 预热表到缓存
SELECT pg_prewarm('employees');

-- 预热特定类型
SELECT pg_prewarm('employees', 'main');  -- 主数据
SELECT pg_prewarm('employees', 'fsm');   -- Free Space Map
SELECT pg_prewarm('employees', 'vm');    -- Visibility Map

-- 预热索引
SELECT pg_prewarm('idx_employees_email');

-- 预热到指定缓冲区
SELECT pg_prewarm('employees', 'buffer');

tablefunc #

交叉表（透视表） #

sql

-- 安装扩展
CREATE EXTENSION tablefunc;

-- 创建销售数据
CREATE TABLE sales (
    year INTEGER,
    quarter INTEGER,
    amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
    (2024, 1, 10000), (2024, 2, 15000), (2024, 3, 12000), (2024, 4, 18000),
    (2025, 1, 11000), (2025, 2, 16000), (2025, 3, 13000), (2025, 4, 19000);

-- 交叉表查询
SELECT * FROM crosstab(
    'SELECT year, quarter, amount FROM sales ORDER BY 1, 2',
    'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(year INTEGER, q1 DECIMAL, q2 DECIMAL, q3 DECIMAL, q4 DECIMAL);

-- 输出：
-- year |   q1   |   q2   |   q3   |   q4
-- -----+--------+--------+--------+--------
-- 2024 | 10000  | 15000  | 12000  | 18000
-- 2025 | 11000  | 16000  | 13000  | 19000

学习路径 #

text

扩展阶段
├── JSON操作
├── 全文搜索
└── 常用扩展（本文）

总结 #

PostgreSQL 的扩展系统极大地增强了其功能。通过合理使用扩展，可以：

使用 PostGIS 处理地理空间数据
使用 pg_stat_statements 分析查询性能
使用 pg_trgm 实现模糊搜索
使用 pgcrypto 进行数据加密
使用 hstore 存储键值对
使用 citext 处理大小写不敏感文本

选择合适的扩展可以大大简化开发工作，提高系统性能！