PostgreSQL¶
从基础到高级到分布式，涵盖安装、管理、优化、高可用、分布式等核心内容。 版本基线说明：本文默认基于 PostgreSQL 15（15.x）编写。
目录¶
-
安装与初始化
用户与权限管理
数据库与表操作
数据类型（扩展：数组、范围、几何、复合类型、枚举、域）
约束
基本事务
-
索引（扩展：覆盖索引、扫描类型、设计策略）
视图与物化视图
存储过程与函数
触发器
事务与并发控制（扩展：MVCC原理、死锁、乐观锁）
锁机制
JSONB 操作
全文搜索
MERGE
-
查询优化器原理（新增：统计信息、成本估算、执行计划）
EXPLAIN 分析
索引优化
查询优化
连接池
配置调优
表分区
VACUUM 与维护
存储引擎与物理结构（新增）
表空间管理
TOAST 机制
页面结构
行存储格式
文件布局
-
流复制（扩展：同步/异步、级联、延迟）
主从架构
故障切换
备份与恢复
-
Citus 扩展
逻辑复制
外部数据源 FDW
常见分布式方案对比
安全篇（新增）
SSL/TLS 配置
行级安全策略 (RLS)
审计日志
密码策略
-
系统视图
慢查询分析
锁分析
连接池监控
一、基础篇¶
1.1 安装与初始化¶
# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib
# 启动 & 开机自启
sudo systemctl start postgresql
sudo systemctl enable postgresql
# 切换到 postgres 系统用户
sudo -i -u postgres
psql # 进入交互终端
# 修改 postgres 用户密码
ALTER USER postgres WITH PASSWORD 'your_password';
常用 psql 命令
命令 |
说明 |
|---|---|
|
列出所有数据库 |
|
切换数据库 |
|
列出所有表 |
|
查看表结构 |
|
列出所有索引 |
|
列出所有用户 |
|
显示执行耗时 |
|
退出 |
1.2 用户与权限管理¶
-- 创建用户
CREATE USER myuser WITH PASSWORD 'mypassword';
-- 创建超级用户
CREATE USER admin WITH SUPERUSER PASSWORD 'adminpass';
-- 创建数据库并指定 owner
CREATE DATABASE mydb OWNER myuser;
-- 授权
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
GRANT SELECT, INSERT, UPDATE ON TABLE mytable TO myuser;
GRANT USAGE ON SCHEMA public TO myuser;
-- 撤销权限
REVOKE INSERT ON TABLE mytable FROM myuser;
-- 删除用户
DROP USER myuser;
-- 查看权限
\dp tablename
1.3 数据库与表操作¶
-- 数据库
CREATE DATABASE mydb;
DROP DATABASE mydb;
-- Schema（命名空间）
CREATE SCHEMA myschema;
SET search_path TO myschema, public;
-- 建表
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email TEXT NOT NULL,
age INTEGER CHECK (age >= 0),
balance NUMERIC(12, 2) DEFAULT 0.00,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT NOW(),
profile JSONB
);
-- 修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN email TO user_email;
ALTER TABLE users ALTER COLUMN age SET DEFAULT 18;
-- 删除表
DROP TABLE IF EXISTS users;
1.4 数据类型¶
1.4.1 基础类型速查¶
分类 |
类型 |
说明 |
存储大小 |
|---|---|---|---|
整数 |
|
2/4/8 字节 |
2/4/8 bytes |
自增 |
|
自增整数（实际是序列+整数） |
4/8 bytes |
浮点 |
|
浮点数 |
4/8 bytes |
精确小数 |
|
推荐用于金额，p精度s小数位 |
变长 |
字符串 |
|
变长字符串，TEXT无长度限制 |
变长 |
布尔 |
|
true/false/null |
1 byte |
时间 |
|
时间类型 |
4/8/8/8 bytes |
UUID |
|
唯一标识符 |
16 bytes |
JSON |
|
推荐 JSONB（可索引、二进制存储） |
变长 |
数组 |
|
任意类型数组 |
变长 |
枚举 |
|
自定义枚举 |
4 bytes |
网络 |
|
网络地址 |
7/7/6 bytes |
1.4.2 数组类型详解¶
PostgreSQL 支持任意维度的数组，这是其强大特性之一。
-- 数组定义
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT,
scores INT[], -- 一维数组
matrix INT[][], -- 二维数组
tags TEXT[] DEFAULT '{}'::TEXT[] -- 默认空数组
);
-- 数组插入
INSERT INTO students (name, scores, tags) VALUES
('张三', ARRAY[85, 90, 78], ARRAY['数学', '物理']),
('李四', '{92,88,95}', ARRAY['化学', '生物']);
-- 数组访问
SELECT scores[1] FROM students; -- 访问第一个元素（从1开始）
SELECT scores[1:2] FROM students; -- 切片访问
SELECT array_dims(matrix) FROM students; -- 查看数组维度
-- 数组操作符
SELECT * FROM students WHERE scores @> ARRAY[90]; -- 包含90
SELECT * FROM students WHERE scores && ARRAY[85,92]; -- 有交集
SELECT array_length(scores, 1) FROM students; -- 数组长度
SELECT unnest(scores) FROM students; -- 展开数组
-- 数组函数
SELECT array_append(scores, 100) FROM students; -- 追加元素
SELECT array_remove(scores, 85) FROM students; -- 移除元素
SELECT array_agg(name) FROM students; -- 聚合为数组
SELECT string_to_array('a,b,c', ','); -- 字符串转数组
SELECT array_to_string(tags, ', ') FROM students; -- 数组转字符串
1.4.3 范围类型 (Range Types)¶
范围类型用于表示区间，支持多种操作。
-- 范围类型列表
-- INT4RANGE : 整数范围
-- INT8RANGE : 大整数范围
-- NUMRANGE : 数字范围
-- TSRANGE : 无时区时间戳范围
-- TSTZRANGE : 有时区时间戳范围
-- DATERANGE : 日期范围
-- 创建范围类型列
CREATE TABLE events (
id SERIAL PRIMARY KEY,
name TEXT,
period TSTZRANGE, -- 活动时间范围
price_range NUMRANGE -- 价格范围
);
-- 插入范围数据
INSERT INTO events (name, period, price_range) VALUES
('春节活动', '[2025-01-28 00:00:00+08, 2025-02-04 23:59:59+08)', '[100.00, 500.00]'),
('夏季促销', '(2025-06-01, 2025-08-31)', '[50, 200)');
-- 范围操作符
SELECT * FROM events WHERE period @> '2025-02-01 12:00:00+08'::TIMESTAMPTZ; -- 包含时间点
SELECT * FROM events WHERE period && '[2025-01-30, 2025-02-05)'::TSTZRANGE; -- 有重叠
SELECT * FROM events WHERE price_range @> 150.00; -- 包含值
SELECT * FROM events WHERE price_range << '[300, 400]'::NUMRANGE; -- 严格在左侧
-- 范围函数
SELECT lower(period), upper(period) FROM events; -- 获取上下界
SELECT lower_inc(period), upper_inc(period) FROM events; -- 是否包含边界
SELECT isempty(period) FROM events; -- 是否为空范围
SELECT period * '[2025-02-01, 2025-02-10)'::TSTZRANGE FROM events; -- 交集
SELECT period + '[2025-02-05, 2025-02-15)'::TSTZRANGE FROM events; -- 并集
-- 范围索引（GiST索引）
CREATE INDEX idx_event_period ON events USING GIST(period);
1.4.4 几何类型¶
-- 几何类型
-- POINT : 点 (x, y)
-- LINE : 无限直线
-- LSEG : 线段
-- BOX : 矩形
-- PATH : 路径（开放或闭合）
-- POLYGON : 多边形
-- CIRCLE : 圆
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name TEXT,
position POINT, -- 位置点
area BOX, -- 区域矩形
boundary POLYGON -- 边界多边形
);
-- 插入几何数据
INSERT INTO locations (name, position, area) VALUES
('商店A', point(116.4, 39.9), box(point(116.3, 39.8), point(116.5, 40.0)));
-- 几何操作
SELECT position <-> point(116.5, 40.0) FROM locations; -- 计算距离
SELECT area @> position FROM locations; -- 点是否在矩形内
SELECT area && box(point(116.2, 39.7), point(116.6, 40.1)) FROM locations; -- 矩形重叠
SELECT area @ area FROM locations; -- 面积
-- 几何索引
CREATE INDEX idx_location_position ON locations USING GIST(position);
1.4.5 复合类型 (Composite Types)¶
复合类型类似于结构体，可以嵌套使用。
-- 创建复合类型
CREATE TYPE address_type AS (
street TEXT,
city TEXT,
zip_code VARCHAR(10),
country TEXT DEFAULT 'China'
);
-- 使用复合类型
CREATE TABLE companies (
id SERIAL PRIMARY KEY,
name TEXT,
address address_type -- 复合类型列
);
-- 插入复合类型数据
INSERT INTO companies (name, address) VALUES
('科技公司', ROW('中关村大街1号', '北京', '100080', 'China')),
('贸易公司', ('南京路100号', '上海', '200001', 'China')); -- 简写形式
-- 访问复合类型字段
SELECT (address).street, (address).city FROM companies;
SELECT * FROM companies WHERE (address).city = '北京';
-- 复合类型函数
CREATE FUNCTION get_full_address(addr address_type) RETURNS TEXT AS $$
BEGIN
RETURN addr.street || ', ' || addr.city || ', ' || addr.zip_code;
END;
$$ LANGUAGE plpgsql;
SELECT get_full_address(address) FROM companies;
1.4.6 枚举类型¶
-- 创建枚举类型
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
CREATE TYPE user_role AS ENUM ('admin', 'editor', 'viewer', 'guest');
-- 使用枚举类型
CREATE TABLE orders_ext (
id SERIAL PRIMARY KEY,
status order_status DEFAULT 'pending',
priority INT
);
-- 枚举操作
INSERT INTO orders_ext (status) VALUES ('pending'), ('processing');
SELECT * FROM orders_ext WHERE status = 'pending';
SELECT enum_range(NULL::order_status); -- 查看所有枚举值
-- 添加枚举值（只能在最后添加）
ALTER TYPE order_status ADD VALUE 'refunded';
-- 枚举排序（按定义顺序）
SELECT * FROM orders_ext ORDER BY status; -- pending < processing < shipped ...
1.4.7 自定义域 (Domain)¶
域是基于现有类型的约束封装，可复用。
-- 创建域（带约束的类型）
CREATE DOMAIN email_type AS TEXT
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
CREATE DOMAIN positive_int AS INTEGER
CHECK (VALUE > 0);
CREATE DOMAIN percentage AS NUMERIC(5,2)
CHECK (VALUE >= 0 AND VALUE <= 100);
-- 使用域
CREATE TABLE products_ext (
id SERIAL PRIMARY KEY,
name TEXT,
contact_email email_type, -- 必须符合邮箱格式
stock positive_int DEFAULT 0, -- 必须为正整数
discount percentage DEFAULT 0 -- 必须在0-100之间
);
-- 测试约束
INSERT INTO products_ext (name, contact_email, stock, discount)
VALUES ('产品A', 'test@example.com', 100, 15.5); -- 成功
-- INSERT INTO products_ext (name, contact_email) VALUES ('产品B', 'invalid'); -- 失败：邮箱格式错误
-- INSERT INTO products_ext (name, stock) VALUES ('产品C', -1); -- 失败：负数
1.4.8 时间类型深入¶
-- 时间类型对比
-- DATE : 日期 (2025-03-05)
-- TIME : 时间 (14:30:00)
-- TIMETZ : 带时区时间 (14:30:00+08)
-- TIMESTAMP : 日期时间 (2025-03-05 14:30:00)
-- TIMESTAMPTZ : 带时区日期时间 (推荐，内部存储UTC)
-- INTERVAL : 时间间隔
-- 时间操作
SELECT NOW(); -- 当前时间戳
SELECT NOW()::DATE; -- 转日期
SELECT NOW()::TIME; -- 转时间
SELECT CURRENT_DATE; -- 当前日期
SELECT CURRENT_TIME; -- 当前时间
-- 时区处理
SET TIME ZONE 'Asia/Shanghai'; -- 设置会话时区
SELECT NOW() AT TIME ZONE 'UTC'; -- 转UTC
SELECT NOW() AT TIME ZONE 'America/New_York'; -- 转纽约时间
-- 时间计算
SELECT NOW() + INTERVAL '1 day'; -- 加1天
SELECT NOW() - INTERVAL '3 hours'; -- 减3小时
SELECT DATE_TRUNC('month', NOW()); -- 截断到月
SELECT EXTRACT(YEAR FROM NOW()); -- 提取年份
SELECT EXTRACT(EPOCH FROM NOW()); -- 转Unix时间戳
SELECT AGE(NOW(), '2020-01-01'::TIMESTAMP); -- 计算年龄/间隔
-- 时间格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- 格式化输出
SELECT TO_TIMESTAMP('2025-03-05', 'YYYY-MM-DD'); -- 字符串转时间
-- INTERVAL类型
SELECT INTERVAL '1 year 2 months 3 days';
SELECT MAKE_INTERVAL(years => 1, months => 2, days => 3);
SELECT JUSTIFY_DAYS(INTERVAL '35 days'); -- 转换为月+日
SELECT JUSTIFY_HOURS(INTERVAL '27 hours'); -- 转换为天+小时
1.4.9 JSONB 高级操作¶
-- JSONB 路径操作
SELECT profile -> 'address' ->> 'city' FROM users; -- 嵌套访问
SELECT profile #> '{address,city}' FROM users; -- 路径访问
SELECT profile #>> '{tags,0}' FROM users; -- 访问数组元素
-- JSONB 查询操作符
SELECT * FROM users WHERE profile @> '{"level": 5}'; -- 包含对象
SELECT * FROM users WHERE profile ? 'email'; -- 存在key
SELECT * FROM users WHERE profile ?| ARRAY['email', 'phone']; -- 存在任一key
SELECT * FROM users WHERE profile ?& ARRAY['email', 'phone']; -- 存在所有key
SELECT * FROM users WHERE profile @? '$.tags[*] ? (@ == "vip")'; -- JSON Path
-- JSONB 函数
SELECT jsonb_each(profile) FROM users; -- 展开为键值对
SELECT jsonb_each_text(profile) FROM users; -- 展开为文本键值对
SELECT jsonb_array_elements(profile->'tags') FROM users; -- 展开数组
SELECT jsonb_object_keys(profile) FROM users; -- 获取所有key
SELECT jsonb_pretty(profile) FROM users; -- 格式化输出
-- JSONB 更新
UPDATE users SET profile = jsonb_set(
profile,
'{level}',
'10'::jsonb
);
UPDATE users SET profile = profile || '{"new_field": "value"}'::jsonb;
UPDATE users SET profile = profile - 'old_field'; -- 删除字段
UPDATE users SET profile = profile #- '{address,zip}'; -- 删除嵌套字段
-- JSONB 聚合
SELECT
user_id,
jsonb_agg(order_data) as all_orders,
jsonb_object_agg(order_id, order_data) as order_map
FROM orders_json
GROUP BY user_id;
1.4.10 类型转换与函数¶
-- 显式类型转换
SELECT CAST('123' AS INTEGER);
SELECT '123'::INTEGER;
SELECT INTEGER '123';
-- 常用转换函数
SELECT TO_NUMBER('1234.56', '9999.99'); -- 字符串转数字
SELECT TO_CHAR(1234.56, '999,999.99'); -- 数字格式化
SELECT TO_DATE('2025-03-05', 'YYYY-MM-DD'); -- 字符串转日期
SELECT TO_TIMESTAMP('2025-03-05 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
-- 判断函数
SELECT pg_typeof(123); -- 获取类型
SELECT typname FROM pg_type WHERE oid = pg_typeof(123)::oid;
-- 生成UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
SELECT gen_random_uuid(); -- 生成随机 UUID
1.5 约束¶
-- 主键
id BIGSERIAL PRIMARY KEY
-- 外键（级联删除）
user_id BIGINT REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
-- 唯一约束
UNIQUE (email)
-- 非空
NOT NULL
-- 检查约束
CHECK (age BETWEEN 0 AND 150)
-- 组合唯一
CONSTRAINT uq_user_product UNIQUE (user_id, product_id)
-- 添加/删除约束
ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 0);
ALTER TABLE users DROP CONSTRAINT check_age;
1.6 基本事务¶
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 回滚
BEGIN;
DELETE FROM orders WHERE id = 999;
ROLLBACK;
-- 保存点（部分回滚）
BEGIN;
INSERT INTO orders VALUES (...);
SAVEPOINT sp1;
INSERT INTO order_items VALUES (...);
ROLLBACK TO sp1;
COMMIT;
二、进阶篇¶
2.1 索引¶
-- 普通索引（B-Tree，默认）
CREATE INDEX idx_users_email ON users(email);
-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);
-- 组合索引（高区分度的列放前面）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- 部分索引（只索引符合条件的行）
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;
-- 表达式索引
CREATE INDEX idx_lower_email ON users(LOWER(email));
-- GIN 索引（JSONB、数组、全文搜索）
CREATE INDEX idx_profile ON users USING GIN(profile);
-- BRIN 索引（大表、时序数据，极小空间）
CREATE INDEX idx_created ON logs USING BRIN(created_at);
-- 并发创建（不锁表）
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);
-- 删除索引
DROP INDEX CONCURRENTLY idx_users_email;
索引类型对比
类型 |
适用场景 |
存储结构 |
|---|---|---|
B-Tree |
默认，等值/范围查询 |
平衡树 |
Hash |
仅等值查询（不常用） |
哈希表 |
GIN |
JSONB、数组、全文搜索 |
B-Tree + 倒排列表 |
GiST |
地理空间、范围类型 |
平衡树（通用搜索树） |
BRIN |
时序大表，节省空间 |
块范围摘要 |
SP-GiST |
空间分区树 |
分区树结构 |
2.1.1 索引原理与结构¶
B-Tree 索引结构
[10 | 30 | 50]
/ | \
[1|5|9] [15|20|25] [35|40|45] [55|60]
每个节点包含多个键值和指针
所有叶子节点在同一层，保证查询稳定性
支持范围查询和排序
索引页结构
-- 查看索引页信息
SELECT * FROM pg_class WHERE relname = 'idx_users_email';
SELECT pg_relation_size('idx_users_email'); -- 索引大小
SELECT pg_size_pretty(pg_relation_size('idx_users_email'));
2.1.2 覆盖索引 (Covering Index / Index Only Scan)¶
覆盖索引包含查询所需的所有列，避免回表查询。
-- 传统索引：查询后需要回表获取数据
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- SELECT amount FROM orders WHERE user_id = 1; -- 需要回表
-- 覆盖索引：包含所有需要的列
CREATE INDEX idx_orders_user_amount ON orders(user_id, amount);
-- SELECT amount FROM orders WHERE user_id = 1; -- Index Only Scan
-- INCLUDE 语法：非键列包含在索引中
CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (amount, status);
-- 优点：amount 和 status 不参与索引排序，减少索引维护开销
验证覆盖索引效果
EXPLAIN (ANALYZE, BUFFERS)
SELECT amount FROM orders WHERE user_id = 1;
-- 期望看到：Index Only Scan
-- 避免：Index Scan + Heap Fetches
2.1.3 索引扫描类型详解¶
-- 1. Index Scan（索引扫描 + 回表）
-- 适用：需要获取非索引列的数据
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- 2. Index Only Scan（仅索引扫描）
-- 适用：查询列全在索引中
EXPLAIN SELECT user_id, amount FROM orders WHERE user_id = 1;
-- 3. Bitmap Index Scan（位图索引扫描）
-- 适用：返回大量行或复合条件
EXPLAIN SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5);
-- 4. Sequential Scan（全表扫描）
-- 适用：表小、返回行数多、无合适索引
EXPLAIN SELECT * FROM orders WHERE status = 'completed'; -- 如果90%都是completed
扫描类型选择因素
扫描类型 |
适用场景 |
特点 |
|---|---|---|
Index Scan |
返回行数少 |
随机IO，逐行回表 |
Index Only Scan |
覆盖索引可用 |
最快，无回表 |
Bitmap Heap Scan |
返回行数多 |
顺序IO，批量回表 |
Seq Scan |
大比例数据或表很小 |
顺序读取全表 |
2.1.4 组合索引设计策略¶
-- 列顺序原则：等值查询列在前，范围查询列在后
-- 错误示例：范围查询在前
CREATE INDEX idx_orders_bad ON orders(created_at, user_id);
-- WHERE created_at > '2024-01-01' AND user_id = 1 -- 只能用到 created_at
-- 正确示例：等值在前
CREATE INDEX idx_orders_good ON orders(user_id, created_at);
-- WHERE user_id = 1 AND created_at > '2024-01-01' -- 两列都能用到
-- 高选择性列在前
CREATE INDEX idx_orders_selective ON orders(status, user_id);
-- status 只有几个值，user_id 有上百万个值
-- 如果按 status 筛选返回很多行，索引效果差
-- 正确做法
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
最左前缀原则
CREATE INDEX idx_abc ON table_name(a, b, c);
-- 能用上索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3 -- 只用a
-- 不能用上索引
WHERE b = 2
WHERE b = 2 AND c = 3
WHERE c = 3
2.1.5 部分索引与条件索引¶
-- 部分索引：只索引满足条件的行，节省空间
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- 只索引待处理订单，如果待处理只占5%，索引大小减少95%
-- 排除NULL值
CREATE INDEX idx_users_email_notnull ON users(email)
WHERE email IS NOT NULL;
-- 软删除场景
CREATE INDEX idx_active_products ON products(name)
WHERE deleted_at IS NULL;
-- 多条件部分索引
CREATE INDEX idx_high_value_orders ON orders(user_id, amount)
WHERE status = 'completed' AND amount > 1000;
2.1.6 表达式索引与函数索引¶
-- 大小写不敏感搜索
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com');
-- 日期函数索引
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;
-- 字符串函数索引
CREATE INDEX idx_users_domain ON users(SUBSTRING(email FROM POSITION('@' IN email) + 1));
-- 计算列索引
CREATE INDEX idx_orders_total ON orders((quantity * unit_price));
SELECT * FROM orders WHERE quantity * unit_price > 1000;
2.1.7 唯一索引与约束¶
-- 唯一约束（自动创建唯一索引）
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
-- 多列唯一
ALTER TABLE order_items ADD CONSTRAINT uq_order_product
UNIQUE (order_id, product_id);
-- 条件唯一
CREATE UNIQUE INDEX idx_unique_active_email ON users(email)
WHERE is_active = TRUE;
-- 允许非活跃用户有重复邮箱，但活跃用户邮箱必须唯一
-- NULL 值处理
-- 标准唯一索引：允许多个NULL（NULL != NULL）
-- 如果需要唯一且非NULL
CREATE UNIQUE INDEX idx_users_email_notnull ON users(email)
WHERE email IS NOT NULL;
2.1.8 索引维护与优化¶
-- 查看索引使用情况
SELECT
schemaname,
tablename,
indexrelname,
idx_scan, -- 索引扫描次数
idx_tup_read, -- 通过索引读取的元组数
idx_tup_fetch, -- 通过索引获取的活元组数
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- 查找未使用的索引（谨慎删除）
SELECT
schemaname,
tablename,
indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_toast%'
AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
-- 查找重复索引
SELECT
t.tablename,
array_agg(i.indexname ORDER BY i.indexname) as indexes,
pg_size_pretty(SUM(pg_relation_size(i.indexname::regclass))) as total_size
FROM pg_indexes i
JOIN pg_indexes t ON i.tablename = t.tablename
AND i.indexdef = t.indexdef
AND i.indexname != t.indexname
GROUP BY t.tablename;
-- 重建索引（解决膨胀）
REINDEX INDEX idx_users_email;
REINDEX TABLE users; -- 重建表的所有索引
-- 并发重建（不锁表）
REINDEX INDEX CONCURRENTLY idx_users_email;
-- 删除索引
DROP INDEX idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email; -- 并发删除
2.1.9 索引与写入性能¶
-- 批量插入时临时禁用索引（危险操作，谨慎使用）
-- 1. 删除索引
DROP INDEX idx_orders_user_id;
-- 2. 批量插入
INSERT INTO orders ... ;
-- 3. 重建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 或者使用 COPY（自动优化）
COPY orders FROM '/path/to/data.csv' CSV;
-- 大批量导入可使用 UNLOGGED 表进行中间处理
2.2 视图与物化视图¶
-- 普通视图（每次查询实时计算）
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email FROM users WHERE is_active = TRUE;
-- 物化视图（结果存磁盘，查询快）
CREATE MATERIALIZED VIEW user_order_stats AS
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders GROUP BY user_id;
-- 刷新（阻塞）
REFRESH MATERIALIZED VIEW user_order_stats;
-- 并发刷新（不锁表，需唯一索引）
CREATE UNIQUE INDEX ON user_order_stats(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_stats;
2.3 存储过程与函数¶
-- 函数（有返回值）
CREATE OR REPLACE FUNCTION get_user_total(uid BIGINT)
RETURNS NUMERIC AS $$
DECLARE
total NUMERIC;
BEGIN
SELECT SUM(amount) INTO total FROM orders WHERE user_id = uid;
RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;
-- 调用
SELECT get_user_total(1);
-- 存储过程（无返回值）
CREATE OR REPLACE PROCEDURE transfer(from_id INT, to_id INT, amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
UPDATE accounts SET balance = balance - amount WHERE id = from_id;
UPDATE accounts SET balance = balance + amount WHERE id = to_id;
COMMIT;
END;
$$;
-- 调用
CALL transfer(1, 2, 100.00);
-- 删除
DROP FUNCTION get_user_total(BIGINT);
2.4 触发器¶
-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 绑定触发器
CREATE TRIGGER trg_update_time
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_time();
-- 触发器类型
-- BEFORE / AFTER：操作前后触发
-- INSERT / UPDATE / DELETE：触发的操作类型
-- FOR EACH ROW / STATEMENT：行级 / 语句级
-- 删除触发器
DROP TRIGGER trg_update_time ON users;
2.5 事务与并发控制¶
2.5.1 MVCC 多版本并发控制¶
PostgreSQL 使用 MVCC（Multi-Version Concurrency Control）实现高并发，读写不阻塞。
MVCC 核心机制
-- 每行数据包含隐藏的系统列
SELECT
ctid, -- 物理位置（块号,行号）
xmin, -- 插入事务ID
xmax, -- 删除事务ID（0表示未删除）
cmin, -- 插入命令ID
cmax -- 删除命令ID
FROM orders
LIMIT 5;
可见性规则
事务可见性判断：
1. xmin 已提交且 xmax 为 0 → 可见
2. xmin 是当前事务 → 可见（自己插入的）
3. xmax 是当前事务 → 不可见（自己删除的）
4. xmax 已提交 → 不可见（别人删除的）
5. xmin 未提交 → 不可见（别人插入但未提交）
快照（Snapshot）
-- 查看当前事务ID
SELECT txid_current();
-- 查看当前快照
SELECT pg_current_snapshot();
-- 结果示例：12:15:12,13,14
-- 格式：xmin:xmax:xip_list
-- xmin: 最老的活跃事务ID
-- xmax: 下一个待分配事务ID
-- xip_list: 活跃事务ID列表
2.5.2 事务隔离级别详解¶
-- 设置隔离级别
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 或在事务中设置
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
隔离级别对比
级别 |
脏读 |
不可重复读 |
幻读 |
实现机制 |
性能 |
|---|---|---|---|---|---|
READ UNCOMMITTED |
❌ |
可能 |
可能 |
同RC |
- |
READ COMMITTED（默认） |
❌ |
可能 |
可能 |
语句级快照 |
高 |
REPEATABLE READ |
❌ |
❌ |
❌（PG中） |
事务级快照 |
中 |
SERIALIZABLE |
❌ |
❌ |
❌ |
可串行化快照 |
低 |
各隔离级别行为演示
-- 会话A
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1; -- 结果: 1000
-- 会话B（同时）
BEGIN;
UPDATE accounts SET balance = 2000 WHERE id = 1;
COMMIT;
-- 会话A（继续）
SELECT balance FROM accounts WHERE id = 1; -- READ COMMITTED: 2000（不可重复读）
-- REPEATABLE READ: 1000（快照）
COMMIT;
可串行化异常（Serialization Anomaly）
-- 两个事务同时读取并修改不重叠的数据集
-- 在 SERIALIZABLE 级别下会检测到冲突
-- 会话A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts;
-- 结果: 10000
INSERT INTO accounts VALUES (999, 1000);
COMMIT; -- 可能失败：could not serialize access due to read/write dependencies
-- 会话B（同时）
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts;
-- 结果: 10000
INSERT INTO accounts VALUES (998, 2000);
COMMIT; -- 后提交的可能失败
2.5.3 锁机制详解¶
锁类型层级
表级锁（8种）:
- ACCESS SHARE: SELECT
- ROW SHARE: SELECT FOR UPDATE/FOR SHARE
- ROW EXCLUSIVE: INSERT/UPDATE/DELETE
- SHARE UPDATE EXCLUSIVE: VACUUM/ANALYZE/CREATE INDEX CONCURRENTLY
- SHARE: CREATE INDEX
- SHARE ROW EXCLUSIVE: 触发器维护
- EXCLUSIVE: REFRESH MATERIALIZED VIEW CONCURRENTLY
- ACCESS EXCLUSIVE: DROP/ALTER/TRUNCATE/REINDEX/VACUUM FULL
行级锁（4种）:
- FOR UPDATE: 修改锁
- FOR NO KEY UPDATE: 无键更新锁
- FOR SHARE: 共享锁
- FOR KEY SHARE: 键共享锁
锁冲突矩阵
锁类型 |
ACCESS SHARE |
ROW SHARE |
ROW EXCLUSIVE |
SHARE |
EXCLUSIVE |
ACCESS EXCLUSIVE |
|---|---|---|---|---|---|---|
ACCESS SHARE |
✓ |
✓ |
✓ |
✓ |
✗ |
✗ |
ROW SHARE |
✓ |
✓ |
✓ |
✓ |
✗ |
✗ |
ROW EXCLUSIVE |
✓ |
✓ |
✓ |
✗ |
✗ |
✗ |
SHARE |
✓ |
✓ |
✗ |
✓ |
✗ |
✗ |
EXCLUSIVE |
✗ |
✗ |
✗ |
✗ |
✗ |
✗ |
ACCESS EXCLUSIVE |
✗ |
✗ |
✗ |
✗ |
✗ |
✗ |
行级锁使用
-- FOR UPDATE: 排他锁，阻止其他事务修改或加锁
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 其他事务: FOR UPDATE/FOR SHARE/UPDATE/DELETE 会被阻塞
COMMIT;
-- FOR SHARE: 共享锁，阻止修改但允许其他共享锁
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR SHARE;
-- 其他事务: FOR SHARE 可以，FOR UPDATE/UPDATE/DELETE 会被阻塞
COMMIT;
-- FOR NO KEY UPDATE: 比 FOR UPDATE 弱，不阻塞 FOR KEY SHARE
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR NO KEY UPDATE;
COMMIT;
-- FOR KEY SHARE: 最弱，只阻止 ACCESS EXCLUSIVE
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR KEY SHARE;
COMMIT;
-- SKIP LOCKED: 跳过已锁定行（队列/秒杀场景）
BEGIN;
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- 如果有其他事务锁定了某行，直接跳过，取下一行
COMMIT;
-- NOWAIT: 立即返回错误，不等待
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;
-- 如果已被锁定，立即报错：could not obtain lock on row in relation "orders"
表级锁使用
-- 显式表锁（谨慎使用）
BEGIN;
LOCK TABLE orders IN SHARE MODE;
-- 允许SELECT，阻止INSERT/UPDATE/DELETE
COMMIT;
BEGIN;
LOCK TABLE orders IN EXCLUSIVE MODE;
-- 只允许SELECT，阻止所有修改
COMMIT;
-- ACCESS EXCLUSIVE（最严格，ALTER/DROP等自动获取）
-- 阻止所有并发操作
2.5.4 死锁检测与处理¶
死锁产生条件
-- 死锁示例
-- 会话A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 锁定id=1
-- ... 会话B更新id=2 ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 等待id=2（死锁）
-- 会话B
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2; -- 锁定id=2
-- ... 会话A更新id=1 ...
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- 等待id=1（死锁）
死锁检测
-- 查看死锁信息
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.relation = blocked_locks.relation
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
-- 查看锁等待
SELECT
pid,
state,
wait_event_type,
wait_event,
query,
now() - query_start AS query_duration
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;
死锁预防策略
-- 1. 统一访问顺序
-- 所有事务按相同顺序访问资源
UPDATE accounts SET ... WHERE id IN (1, 2) ORDER BY id;
-- 2. 使用短事务
BEGIN;
-- 最小化事务范围
COMMIT;
-- 3. 使用乐观锁（版本号）
ALTER TABLE accounts ADD COLUMN version INTEGER DEFAULT 0;
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5; -- 假设当前版本是5
-- 检查更新行数，如果为0说明版本冲突，需要重试
2.5.5 乐观锁实现¶
-- 方案1: 版本号机制
CREATE TABLE products_lock (
id SERIAL PRIMARY KEY,
name TEXT,
stock INTEGER,
version INTEGER DEFAULT 0
);
-- 读取时获取版本号
SELECT id, name, stock, version FROM products_lock WHERE id = 1;
-- 假设返回: id=1, stock=100, version=5
-- 更新时检查版本号
UPDATE products_lock
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;
-- 检查影响行数
-- 如果 affected_rows = 0，说明数据已被修改，需要重试
-- 如果 affected_rows = 1，更新成功
-- 方案2: 时间戳机制
ALTER TABLE products_lock ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();
UPDATE products_lock
SET stock = stock - 1, updated_at = NOW()
WHERE id = 1 AND updated_at = '2025-03-05 10:00:00';
-- 方案3: 使用 advisory lock（应用级锁）
-- 获取会话级 advisory lock
SELECT pg_advisory_lock(id) FROM products_lock WHERE id = 1;
-- 执行业务逻辑
UPDATE products_lock SET stock = stock - 1 WHERE id = 1;
-- 释放锁
SELECT pg_advisory_unlock(id) FROM products_lock WHERE id = 1;
-- 或者使用事务级 advisory lock（自动释放）
SELECT pg_advisory_xact_lock(1); -- 事务结束自动释放
2.5.6 事务最佳实践¶
-- 1. 保持事务简短
BEGIN;
-- 只做必要的操作
COMMIT;
-- 2. 错误处理
BEGIN;
-- 操作1
-- 操作2
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
-- 3. 使用保存点实现部分回滚
BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT after_order;
INSERT INTO order_items (...) VALUES (...);
-- 如果失败，只回滚到保存点
ROLLBACK TO SAVEPOINT after_order;
COMMIT;
-- 4. 设置合适的超时
SET statement_timeout = '30s'; -- 语句超时
SET lock_timeout = '10s'; -- 锁等待超时
SET idle_in_transaction_session_timeout = '5min'; -- 事务空闲超时
-- 5. 批量操作使用单事务
BEGIN;
INSERT INTO logs VALUES (...);
INSERT INTO logs VALUES (...);
-- ... 批量插入
COMMIT;
2.6 高级锁机制与监控¶
-- 行级锁（悲观锁）
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 跳过已锁定行（秒杀/队列场景）
SELECT * FROM orders WHERE status = 'pending'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- 共享锁（允许并发读，阻止写）
SELECT * FROM orders WHERE id = 1 FOR SHARE;
-- 表级锁
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
-- 查看锁等待
SELECT pid, query, wait_event, state
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
-- 终止阻塞进程
SELECT pg_terminate_backend(pid);
2.7 JSONB 操作¶
-- 插入
INSERT INTO users(profile) VALUES ('{"age": 25, "tags": ["vip", "new"]}');
-- 查询操作符
SELECT profile -> 'age' FROM users; -- 返回 JSON
SELECT profile ->> 'age' FROM users; -- 返回文本
SELECT profile #> '{address,city}' FROM users; -- 嵌套路径
-- 条件查询
SELECT * FROM users WHERE profile @> '{"tags": ["vip"]}';
SELECT * FROM users WHERE profile ? 'age'; -- 是否存在 key
-- 更新
UPDATE users SET profile = profile || '{"level": 5}';
UPDATE users SET profile = profile - 'age'; -- 删除 key
-- GIN 索引加速
CREATE INDEX idx_profile_gin ON users USING GIN(profile);
2.8 全文搜索¶
-- 基本全文搜索
SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'PostgreSQL & tutorial');
-- 添加全文搜索列（推荐）
ALTER TABLE articles ADD COLUMN fts_content TSVECTOR;
UPDATE articles SET fts_content = to_tsvector('english', content);
CREATE INDEX idx_fts ON articles USING GIN(fts_content);
-- 触发器自动更新全文列
CREATE TRIGGER trig_fts
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(fts_content, 'pg_catalog.english', content);
-- 高亮显示
SELECT ts_headline('english', content, to_tsquery('PostgreSQL'))
FROM articles;
2.9 MERGE¶
MERGE 是标准化“匹配后更新 / 不匹配插入 / 可选删除”语法，可替代部分复杂 UPSERT 逻辑。
-- 目标表
CREATE TABLE user_balance (
user_id BIGINT PRIMARY KEY,
balance NUMERIC(12,2) NOT NULL DEFAULT 0
);
-- 源数据（可来自临时表、CTE、外部导入）
CREATE TEMP TABLE delta_balance (
user_id BIGINT,
delta NUMERIC(12,2)
);
INSERT INTO delta_balance VALUES (1, 100), (2, -30), (3, 50);
-- MERGE: 匹配则更新，不匹配则插入
MERGE INTO user_balance AS t
USING delta_balance AS s
ON t.user_id = s.user_id
WHEN MATCHED THEN
UPDATE SET balance = t.balance + s.delta
WHEN NOT MATCHED THEN
INSERT (user_id, balance) VALUES (s.user_id, GREATEST(s.delta, 0));
与 INSERT ... ON CONFLICT 的区别：
ON CONFLICT更适合“单行或批量 UPSERT”。MERGE更适合“源表到目标表”的批处理同步，分支条件表达能力更强。
三、性能优化篇¶
3.1 查询优化器原理¶
PostgreSQL 使用基于成本的查询优化器（Cost-Based Optimizer, CBO），通过统计信息估算不同执行计划的成本，选择最优方案。
3.1.1 统计信息¶
表级统计信息
-- 查看表统计信息
SELECT
schemaname,
tablename,
n_live_tup, -- 活元组数（大致行数）
n_dead_tup, -- 死元组数
n_mod_since_analyze, -- 上次ANALYZE后修改的行数
last_vacuum, -- 上次VACUUM时间
last_autovacuum, -- 上次自动VACUUM时间
last_analyze, -- 上次ANALYZE时间
last_autoanalyze -- 上次自动ANALYZE时间
FROM pg_stat_user_tables
WHERE tablename = 'orders';
-- 手动更新统计信息
ANALYZE orders;
ANALYZE orders (user_id, status); -- 只更新指定列
ANALYZE VERBOSE orders; -- 显示详细信息
列级统计信息
-- 查看列统计信息
SELECT
attname, -- 列名
n_distinct, -- 不同值数量（-1表示唯一）
most_common_vals, -- 最常见值列表
most_common_freqs, -- 最常见值频率
histogram_bounds, -- 直方图边界（等频分桶）
correlation, -- 物理顺序与逻辑顺序相关性
avg_width -- 平均宽度（字节）
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
-- 高频值示例
-- most_common_vals: {completed,pending,shipped}
-- most_common_freqs: {0.7,0.2,0.08}
-- 表示70%是completed，20%是pending，8%是shipped
扩展统计信息
-- 创建多列统计（用于相关列）
CREATE STATISTICS orders_user_status_stats ON user_id, status FROM orders;
ANALYZE orders;
-- 查看扩展统计
SELECT * FROM pg_stats_ext WHERE tablename = 'orders';
-- 功能类型
-- dependencies: 列间函数依赖
-- ndistinct: 多列组合的不同值数
-- mcv: 多列组合的最常见值
-- 创建全功能统计
CREATE STATISTICS orders_stats (dependencies, ndistinct, mcv)
ON user_id, status, created_at FROM orders;
3.1.2 成本估算模型¶
成本组成
总成本 = 启动成本 + (CPU成本 + IO成本) × 估计行数
成本参数
-- 查看成本参数
SHOW seq_page_cost; -- 顺序扫描页成本（默认1.0）
SHOW random_page_cost; -- 随机扫描页成本（默认4.0，SSD建议1.1）
SHOW cpu_tuple_cost; -- 处理每行CPU成本（默认0.01）
SHOW cpu_index_tuple_cost;-- 处理索引行CPU成本（默认0.005）
SHOW cpu_operator_cost; -- 操作符CPU成本（默认0.0025）
SHOW effective_cache_size;-- 有效缓存大小（影响索引选择）
成本计算示例
-- 假设表有10000行，每页存储100行，共100页
-- 全表扫描成本
-- seq_page_cost * 页数 + cpu_tuple_cost * 行数
-- 1.0 * 100 + 0.01 * 10000 = 100 + 100 = 200
-- 索引扫描成本（假设返回100行，分布在50页）
-- random_page_cost * 页数 + cpu_index_tuple_cost * 索引行数 + cpu_tuple_cost * 行数
-- 4.0 * 50 + 0.005 * 100 + 0.01 * 100 = 200 + 0.5 + 1 = 201.5
-- 如果random_page_cost设为1.1（SSD）
-- 1.1 * 50 + 0.005 * 100 + 0.01 * 100 = 55 + 0.5 + 1 = 56.5（索引更优）
3.1.3 执行计划详解¶
EXPLAIN 选项
-- 基本执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- 详细执行计划（推荐）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1;
-- 选项说明
-- ANALYZE: 实际执行并显示真实时间
-- BUFFERS: 显示缓冲区命中情况
-- COSTS: 显示成本估算
-- TIMING: 显示实际时间（ANALYZE时默认开启）
-- FORMAT: TEXT / JSON / XML / YAML
-- JSON格式（便于程序解析）
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 1;
执行计划节点解读
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.username, SUM(o.amount)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.username;
GroupAggregate (cost=1234.56..2345.67 rows=100 width=36) (actual time=10.234..25.678 rows=50 loops=1)
Group Key: u.username
Buffers: shared hit=500 read=100
-> Sort (cost=1234.56..1256.78 rows=1000 width=20) (actual time=8.123..9.456 rows=800 loops=1)
Sort Key: u.username
Sort Method: quicksort Memory: 64kB
Buffers: shared hit=500 read=100
-> Hash Join (cost=234.56..890.12 rows=1000 width=20) (actual time=2.345..6.789 rows=800 loops=1)
Hash Cond: (o.user_id = u.id)
Buffers: shared hit=400 read=100
-> Seq Scan on orders o (cost=0.00..567.89 rows=1000 width=16) (actual time=0.123..3.456 rows=1000 loops=1)
Filter: (status = 'completed'::text)
Rows Removed by Filter: 9000
Buffers: shared hit=300 read=100
-> Hash (cost=123.45..123.45 rows=1000 width=12) (actual time=2.123..2.123 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 64kB
Buffers: shared hit=100
-> Seq Scan on users u (cost=0.00..123.45 rows=1000 width=12) (actual time=0.012..1.234 rows=1000 loops=1)
Buffers: shared hit=100
关键指标解读
指标 |
说明 |
优化建议 |
|---|---|---|
cost=启动…总成本 |
优化器估算的成本 |
关注总成本，越小越好 |
actual time=启动…总时间 |
实际执行时间（毫秒） |
与估算对比，差距大说明统计信息不准 |
rows |
估计/实际返回行数 |
差距大需要ANALYZE |
loops |
循环次数 |
嵌套循环的外层循环数 |
Buffers: shared hit |
共享缓冲区命中 |
命中率高性能好 |
Buffers: shared read |
从磁盘读取 |
越少越好 |
Buffers: temp read/write |
临时文件IO |
出现说明work_mem不足 |
3.1.4 连接算法选择¶
Nested Loop（嵌套循环）
-- 适用场景：小表驱动，大表被驱动，且有索引
-- 成本：外表行数 × (内表索引成本 + 内表回表成本)
EXPLAIN (ANALYZE)
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id < 100; -- 小结果集
-- 执行计划
-- Nested Loop
-- -> Seq Scan on users (外表，小)
-- -> Index Scan on orders (内表，有索引)
Hash Join（哈希连接）
-- 适用场景：大表连接，等值连接
-- 成本：构建哈希表成本 + 探测成本
-- 内存不足时会分批处理（Batches > 1）
EXPLAIN (ANALYZE)
SELECT * FROM orders o1
JOIN orders o2 ON o1.user_id = o2.user_id;
-- 执行计划
-- Hash Join
-- Hash Cond: (o1.user_id = o2.user_id)
-- -> Seq Scan on orders o1
-- -> Hash
-- -> Seq Scan on orders o2
Merge Join（归并连接）
-- 适用场景：已排序数据，范围连接
-- 成本：排序成本 + 归并成本
EXPLAIN (ANALYZE)
SELECT * FROM orders o1
JOIN orders o2 ON o1.user_id = o2.user_id
ORDER BY o1.user_id;
-- 执行计划
-- Merge Join
-- Merge Cond: (o1.user_id = o2.user_id)
-- -> Index Scan on orders o1 (已排序)
-- -> Index Scan on orders o2 (已排序)
连接选择对比
连接类型 |
适用场景 |
特点 |
|---|---|---|
Nested Loop |
小表驱动，内表有索引 |
随机IO，适合小结果集 |
Hash Join |
大表等值连接 |
需要内存建哈希表，通常最快 |
Merge Join |
已排序数据，范围连接 |
需要排序，适合有序数据 |
3.1.5 优化器提示与干预¶
启用/禁用特定计划
-- 临时禁用特定扫描方式（调试用）
SET enable_seqscan = off; -- 禁用全表扫描
SET enable_indexscan = off; -- 禁用索引扫描
SET enable_bitmapscan = off; -- 禁用位图扫描
SET enable_hashjoin = off; -- 禁用哈希连接
SET enable_nestloop = off; -- 禁用嵌套循环
SET enable_mergejoin = off; -- 禁用归并连接
-- 恢复默认
RESET enable_seqscan;
强制使用索引
-- 使用索引提示（PostgreSQL本身不支持hint，需安装pg_hint_plan扩展）
-- 安装 pg_hint_plan
-- CREATE EXTENSION pg_hint_plan;
-- 使用提示
/*+ IndexScan(orders idx_orders_user_id) */
SELECT * FROM orders WHERE user_id = 1;
/*+ HashJoin(o u) */
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
/*+ Set(enable_seqscan off) */
SELECT * FROM orders WHERE user_id = 1;
连接顺序控制
-- 通过 CTE 强制连接顺序
WITH user_orders AS (
SELECT * FROM orders WHERE user_id = 1 -- 先过滤
)
SELECT u.*, o.*
FROM users u
JOIN user_orders o ON u.id = o.user_id;
-- 使用 JOIN 顺序
SELECT *
FROM small_table s -- 驱动表
JOIN large_table l ON s.id = l.small_id -- 被驱动表
WHERE s.filter = 'value'; -- 先过滤小表
3.1.6 常见执行计划问题¶
问题1：Seq Scan 过多
-- 现象：大表全表扫描
-- 原因：缺少索引、统计信息不准、返回行数过多
-- 解决方案
-- 1. 添加索引
CREATE INDEX idx_orders_status ON orders(status);
-- 2. 更新统计信息
ANALYZE orders;
-- 3. 如果确实需要大部分数据，Seq Scan是正常的
问题2：Rows 估计不准
-- 现象：estimated rows 与 actual rows 差距大
-- 原因：统计信息过期、数据分布不均、多列条件相关
-- 解决方案
-- 1. 增加统计目标
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;
-- 2. 创建扩展统计
CREATE STATISTICS orders_stats ON user_id, status FROM orders;
ANALYZE orders;
-- 3. 增加直方图桶数（全局）
ALTER SYSTEM SET default_statistics_target = 500;
问题3：临时文件使用
-- 现象：Buffers: temp read/write
-- 原因：work_mem 不足，需要磁盘排序/哈希
-- 解决方案
-- 1. 增加 work_mem（会话级）
SET work_mem = '256MB';
-- 2. 优化查询，减少排序数据量
-- 3. 使用索引避免排序
问题4：Nested Loop 过慢
-- 现象：大表Nested Loop，loops次数多
-- 原因：外表过大，内表无索引
-- 解决方案
-- 1. 确保内表有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 2. 禁用Nested Loop让优化器选择Hash Join
SET enable_nestloop = off;
-- 3. 减少外表数据量（先过滤）
3.2 EXPLAIN 分析实践¶
-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- 实际执行并统计
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1;
-- 关键指标
-- Seq Scan：全表扫描（慢，考虑加索引）
-- Index Scan：索引扫描（快）
-- Bitmap Heap Scan：位图扫描（中等）
-- Hash Join / Nested Loop / Merge Join：连接方式
-- cost=启动成本..总成本
-- actual time=实际启动..实际总时间
-- rows：实际返回行数
-- Buffers: hit=缓存命中 read=磁盘读取
3.2 索引优化¶
选择合适的索引类型：等值用 B-Tree，JSONB 用 GIN，时序大表用 BRIN
组合索引列顺序：区分度高的列放前，WHERE 条件中频繁出现的列放前
避免索引失效：
不要在索引列上用函数（除非建表达式索引）
避免隐式类型转换
LIKE 'xxx%'可以走索引，LIKE '%xxx'不行
定期检查无用索引：
-- 查找未使用的索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- 查找重复索引
SELECT * FROM pg_indexes WHERE tablename = 'orders';
3.3 查询优化¶
避免 SELECT *：只查需要的列，减少 I/O
使用 EXISTS 代替 IN：子查询数据量大时更快
批量操作：批量 INSERT/UPDATE 代替逐条操作
合理使用 CTE：默认会参与优化，必要时可用
MATERIALIZED/NOT MATERIALIZED控制行为分页优化：深分页用 keyset 分页代替 OFFSET
-- 慢（深分页）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- 快（keyset 分页）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
3.4 连接池¶
直接用应用连接 PostgreSQL 代价较高，推荐使用连接池。
PgBouncer（最常用）
# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction # session / transaction / statement
max_client_conn = 1000
default_pool_size = 20
模式 |
说明 |
适用场景 |
|---|---|---|
session |
连接生命周期与会话一致 |
使用会话级特性 |
transaction |
事务结束归还连接 |
推荐，大多数场景 |
statement |
语句结束归还连接 |
不能用事务 |
3.5 配置调优¶
编辑 postgresql.conf，根据服务器内存调整：
# 内存（以 16GB 内存为例）
shared_buffers = 4GB # 建议物理内存的 25%
effective_cache_size = 12GB # 建议物理内存的 75%
work_mem = 64MB # 每个排序/哈希操作的内存
maintenance_work_mem = 1GB # VACUUM/索引创建使用
# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
# 并发
max_connections = 200 # 结合连接池使用
max_worker_processes = 8
# 查询优化器
random_page_cost = 1.1 # SSD 设为 1.1，HDD 设为 4
effective_io_concurrency = 200 # SSD 设为 200
# 日志（排查慢查询）
log_min_duration_statement = 1000 # 记录超过 1s 的查询
log_checkpoints = on
3.6 表分区¶
-- 范围分区（按时间）
CREATE TABLE orders (
id BIGSERIAL,
created_at TIMESTAMP NOT NULL,
amount NUMERIC
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- 列表分区（按状态）
CREATE TABLE orders_by_status PARTITION BY LIST (status);
CREATE TABLE orders_pending PARTITION OF orders_by_status FOR VALUES IN ('pending');
CREATE TABLE orders_done PARTITION OF orders_by_status FOR VALUES IN ('done');
-- 哈希分区（均匀分布）
CREATE TABLE users PARTITION BY HASH (id);
CREATE TABLE users_0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
分区注意事项
分区键要出现在查询条件中，否则会全分区扫描
每个分区可以建独立索引
分区表支持并行查询，需结合执行计划验证是否生效
3.7 VACUUM 与维护¶
PostgreSQL 使用 MVCC，更新/删除产生死元组，需要 VACUUM 回收。
-- 手动 VACUUM
VACUUM users;
VACUUM ANALYZE users; -- 同时更新统计信息
VACUUM FULL users; -- 完全回收空间（锁表，慎用）
-- 自动 VACUUM（默认开启，关键参数）
-- autovacuum = on
-- autovacuum_vacuum_scale_factor = 0.2 （死元组超过 20% 触发）
-- autovacuum_analyze_scale_factor = 0.1 （修改超过 10% 触发统计更新）
-- 查看表膨胀情况
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / nullif(n_live_tup,0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
-- 更新统计信息
ANALYZE users;
四、存储引擎与物理结构¶
4.1 表空间管理¶
表空间（Tablespace）是数据文件的物理存储位置，用于数据分布和IO优化。
-- 创建表空间
CREATE TABLESPACE fastspace
LOCATION '/ssd/postgresql/data';
-- 创建带表空间的表
CREATE TABLE orders_fast (
id BIGSERIAL PRIMARY KEY,
data TEXT
) TABLESPACE fastspace;
-- 移动表到不同表空间
ALTER TABLE orders SET TABLESPACE fastspace;
-- 移动索引到不同表空间
ALTER INDEX idx_orders_user_id SET TABLESPACE fastspace;
-- 设置默认表空间
SET default_tablespace = 'fastspace';
-- 查看表空间
SELECT * FROM pg_tablespace;
\db -- psql快捷命令
-- 查看表和索引的表空间
SELECT
schemaname,
tablename,
tablespace
FROM pg_tables
WHERE schemaname = 'public';
表空间使用场景
场景 |
方案 |
|---|---|
冷热数据分离 |
热数据放SSD，冷数据放HDD |
大表独立存储 |
大表单独表空间，便于管理 |
IO分散 |
不同表空间放不同磁盘，分散IO |
4.2 TOAST 机制¶
TOAST（The Oversized-Attribute Storage Technique）用于存储超大字段。
TOAST 触发条件
行大小超过 2KB（TOAST_TUPLE_THRESHOLD）时触发
- 压缩存储（先尝试）
- 行外存储（压缩后仍大）
TOAST 策略
-- 查看列的TOAST策略
SELECT
attname,
CASE attstorage
WHEN 'p' THEN 'PLAIN'
WHEN 'e' THEN 'EXTERNAL'
WHEN 'x' THEN 'EXTENDED'
WHEN 'm' THEN 'MAIN'
END AS toast_strategy
FROM pg_attribute
WHERE attrelid = 'large_table'::regclass
AND attnum > 0
AND NOT attisdropped;
-- 策略说明
-- PLAIN: 不压缩，行内存储（小数据）
-- EXTERNAL: 不压缩，行外存储（大文本/二进制）
-- EXTENDED: 压缩，必要时行外存储（默认）
-- MAIN: 压缩，尽量行内存储
-- 修改TOAST策略
ALTER TABLE documents ALTER COLUMN content SET STORAGE EXTERNAL;
TOAST 表查看
-- TOAST表命名规则: pg_toast.pg_toast_<oid>
SELECT
relname AS table_name,
pg_size_pretty(pg_relation_size(oid)) AS size,
pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relname LIKE 'pg_toast_%'
AND relkind = 't' -- toast table
ORDER BY pg_relation_size(oid) DESC;
-- 查看表的TOAST大小
SELECT
c.relname,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
pg_size_pretty(pg_relation_size(t.oid)) AS toast_size
FROM pg_class c
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relname = 'large_table';
4.3 页面结构¶
PostgreSQL 数据存储在固定大小的页面（Page）中，默认 8KB。
页面结构
+------------------+
| Page Header | 24 bytes
| (pd_lower, |
| pd_upper, |
| pd_special...) |
+------------------+
| ItemIdData[] | 行指针数组
| (行偏移量) |
+------------------+
| Free Space | 空闲空间
+------------------+
| Tuple Data | 实际数据行
| (从后向前填充) |
+------------------+
| Special Space | 特殊空间（索引用）
+------------------+
页面相关函数
-- 查看表的页面数
SELECT
relname,
relpages,
pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relname = 'orders';
-- 查看每页的行数（估算）
SELECT
relname,
reltuples::bigint / NULLIF(relpages, 0) AS rows_per_page
FROM pg_class
WHERE relname = 'orders';
-- 查看页面布局（需安装pageinspect扩展）
CREATE EXTENSION pageinspect;
-- 查看页面头部信息
SELECT * FROM page_header_get('orders'::regclass, 0);
-- 查看页面中的行
SELECT * FROM heap_page_items(get_raw_page('orders', 0));
-- 查看页面空闲空间
SELECT
pg_size_pretty(pg_freespace('orders', 0)) AS free_space_page_0,
pg_size_pretty(pg_freespace('orders', 1)) AS free_space_page_1;
填充因子（Fillfactor）
-- 创建低填充因子表（预留更新空间）
CREATE TABLE update_heavy_table (
id SERIAL PRIMARY KEY,
data TEXT
) WITH (fillfactor = 70);
-- 修改现有表填充因子
ALTER TABLE orders SET (fillfactor = 80);
-- 填充因子说明
-- 100: 完全填充（只读表）
-- 90: 默认（一般表）
-- 70-80: 频繁更新的表（预留空间）
-- 50-60: 频繁更新的宽表
4.4 行存储格式¶
Heap Tuple 结构
+------------+
| t_xmin | 插入事务ID (4 bytes)
| t_xmax | 删除事务ID (4 bytes)
| t_cid | 命令ID (4 bytes)
| t_infomask | 标志位 (2 bytes)
| t_hoff | 头大小 (1 byte)
| t_bits | NULL位图 (变长)
+------------+
| 列数据 | 实际数据
+------------+
查看行物理信息
-- 使用pageinspect查看行详情
SELECT
lp,
lp_off,
lp_len,
t_xmin,
t_xmax,
t_field3 AS t_cid,
t_ctid
FROM heap_page_items(get_raw_page('orders', 0))
WHERE lp = 1; -- 第1行
-- 查看行的可见性
SELECT
ctid,
xmin,
xmax,
CASE
WHEN xmax = 0 THEN '活跃'
ELSE '已删除'
END AS status
FROM orders
LIMIT 5;
4.5 文件布局¶
数据文件组织
$PGDATA/
├── base/ # 数据库文件
│ ├── 1/ # template1 (oid=1)
│ ├── 4/ # template0 (oid=4)
│ ├── 5/ # postgres (oid=5)
│ └── 16384/ # 用户数据库
│ ├── 1259 # pg_class (系统表)
│ ├── 16385 # 用户表 (oid=16385)
│ ├── 16385_fsm # 空闲空间映射
│ ├── 16385_vm # 可见性映射
│ └── 16385_init # 初始化文件
├── global/ # 共享系统表
├── pg_wal/ # WAL文件
├── pg_stat/ # 统计信息
└── postgresql.conf # 配置文件
查找表的物理文件
-- 查看表的OID和物理文件路径
SELECT
c.relname,
c.oid,
pg_relation_filepath(c.oid) AS filepath
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'orders'
AND n.nspname = 'public';
-- 结果示例: base/16384/16385
-- 对应: $PGDATA/base/数据库OID/表OID
WAL 文件结构
-- WAL文件名: 时间线(8位) + LSN高32位(8位) + LSN低32位(8位)
-- 示例: 000000010000000000000001
-- 查看当前WAL位置
SELECT pg_current_wal_lsn();
-- 查看WAL文件列表
SELECT * FROM pg_ls_waldir() ORDER BY name;
-- WAL段大小（默认16MB）
SHOW wal_segment_size;
五、高可用篇¶
5.1 流复制（Streaming Replication）¶
复制模式对比
模式 |
同步级别 |
数据安全 |
性能影响 |
适用场景 |
|---|---|---|---|---|
异步复制 |
主库不等待 |
可能丢失 |
最小 |
读写分离 |
同步复制 |
等待备库确认 |
零丢失 |
较大 |
金融核心 |
法定数复制 |
等待多数确认 |
高安全 |
中等 |
多数据中心 |
主节点配置（postgresql.conf）
# 基础复制配置
wal_level = replica
max_wal_senders = 10
wal_keep_size = 2GB
max_replication_slots = 10
hot_standby = on
# 同步复制配置（可选）
synchronous_commit = remote_apply # on/remote_write/remote_apply/local/off
synchronous_standby_names = 'FIRST 1 (s1, s2)' # 法定数同步
# synchronous_standby_names = 'ANY 2 (s1, s2, s3)' # 任意2个
主节点（pg_hba.conf）
# 类型 数据库 用户 来源地址 认证方式
host replication replicator 192.168.1.0/24 scram-sha-256
host all replicator 192.168.1.0/24 scram-sha-256
创建复制用户
CREATE USER replicator REPLICATION LOGIN PASSWORD 'reppass';
-- 查看复制连接
SELECT * FROM pg_stat_replication;
-- 查看复制槽
SELECT * FROM pg_replication_slots;
从节点初始化
# 方式1: 使用pg_basebackup
pg_basebackup -h master_ip -U replicator -D /var/lib/postgresql/data -P -R -X stream -c fast
# 参数说明
# -P: 显示进度
# -R: 创建standby.signal和连接配置
# -X stream: 流式传输WAL
# -c fast: 快速检查点
# 方式2: 手动初始化（大库）
rsync -av --exclude=pg_wal master:/var/lib/postgresql/data/ /var/lib/postgresql/data/
touch /var/lib/postgresql/data/standby.signal
cat > /var/lib/postgresql/data/postgresql.auto.conf <<EOF
primary_conninfo = 'host=master_ip port=5432 user=replicator password=reppass'
recovery_target_timeline = 'latest'
EOF
5.2 同步复制配置¶
同步级别详解
# postgresql.conf
# off: 不等待WAL写入，最快但不安全
synchronous_commit = off
# local: 等待本地WAL写入（默认）
synchronous_commit = local
# on: 等待备库收到WAL（remote_write）
synchronous_commit = on
# remote_write: 等待备库写入OS缓存
synchronous_commit = remote_write
# remote_apply: 等待备库应用（可见）
synchronous_commit = remote_apply
同步备库配置
# 指定同步备库
synchronous_standby_names = 's1' # 单备库同步
# 法定数同步（FIRST）
synchronous_standby_names = 'FIRST 2 (s1, s2, s3)'
# 等待前2个列出的备库确认
# 任意同步（ANY）
synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
# 等待任意2个备库确认
# 多个同步组
synchronous_standby_names = 'FIRST 1 (s1, s2), FIRST 1 (s3, s4)'
从节点配置（postgresql.conf）
# 从节点标识（用于同步复制识别）
primary_conninfo = 'host=master_ip port=5432 user=replicator password=reppass application_name=s1'
# 热备配置
hot_standby = on
hot_standby_feedback = on # 反馈备库状态，避免冲突
# 延迟应用（可选）
recovery_min_apply_delay = 1h # 延迟1小时应用（防误删）
5.3 级联复制¶
级联复制允许备库从其他备库接收数据，减轻主库压力。
主库 (Primary)
↓
备库1 (Standby1) ← 同步复制
↓
备库2 (Standby2) ← 级联复制
↓
备库3 (Standby3) ← 级联复制
级联复制配置
# 备库1配置（作为级联源）
# postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
# 备库2配置（级联目标）
primary_conninfo = 'host=standby1_ip port=5432 user=replicator password=reppass'
5.4 延迟备库¶
延迟备库用于数据恢复，防止误操作立即传播。
# postgresql.conf
recovery_min_apply_delay = 1h # 延迟1小时
# 时间单位: ms/s/min/h/d
# recovery_min_apply_delay = 30min
延迟备库使用场景
-- 1. 误删除恢复
-- 主库误删: DELETE FROM orders WHERE id < 1000000;
-- 延迟备库（1小时延迟）仍有数据
-- 2. 切换到延迟备库
-- 停止延迟应用
SELECT pg_wal_replay_pause();
-- 在延迟时间点导出数据
pg_dump -h standby_ip -t orders ...
-- 恢复延迟应用
SELECT pg_wal_replay_resume();
5.5 主从架构¶
主节点配置（postgresql.conf）
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
hot_standby = on
主节点（pg_hba.conf）
host replication replicator 192.168.1.0/24 scram-sha-256
创建复制用户
CREATE USER replicator REPLICATION LOGIN PASSWORD 'reppass';
从节点初始化
pg_basebackup -h master_ip -U replicator -D /var/lib/postgresql/data -P -R
-R 参数会自动生成 standby.signal 和连接配置，从节点启动后自动连接主节点。
5.6 复制拓扑与状态检查¶
写请求 → 主节点 (Primary)
↓ WAL 流复制
读请求 → 从节点 (Standby) [hot_standby=on，可读]
查看复制状态
-- 主节点查看
SELECT * FROM pg_stat_replication;
-- 从节点查看
SELECT * FROM pg_stat_wal_receiver;
-- 查看主从延迟
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
5.7 故障切换¶
手动切换
# 从节点提升为主节点
pg_ctl promote -D /var/lib/postgresql/data
# 或
SELECT pg_promote();
自动切换工具
工具 |
说明 |
|---|---|
Patroni |
最流行，基于 etcd/Consul/ZooKeeper |
repmgr |
轻量，监控和自动切换 |
pg_auto_failover |
微软出品，简单易用 |
Stolon |
云原生，Kubernetes 友好 |
5.8 备份与恢复¶
# 逻辑备份（pg_dump，单库）
pg_dump -U postgres -d mydb -F c -f mydb.dump # 自定义格式
pg_dump -U postgres -d mydb -F p -f mydb.sql # 纯 SQL
# 全库备份
pg_dumpall -U postgres -f all.sql
# 恢复
pg_restore -U postgres -d mydb mydb.dump
psql -U postgres -d mydb -f mydb.sql
# 物理备份（pg_basebackup）
pg_basebackup -h localhost -U postgres -D /backup/base -Ft -z -P
# PITR（时间点恢复）
# 1. 开启 WAL 归档
archive_mode = on
archive_command = 'cp %p /archive/%f'
# 2. 恢复时指定时间点（使用 signal 文件机制）
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2025-01-01 12:00:00'
# 3. 在数据目录创建恢复信号文件
touch /var/lib/postgresql/data/recovery.signal
六、分布式篇¶
5.1 Citus 扩展（分片）¶
Citus 是 PostgreSQL 的分布式扩展，支持水平分片。
-- 安装后加载扩展
CREATE EXTENSION citus;
-- 分布式表（按 user_id 分片）
SELECT create_distributed_table('orders', 'user_id');
-- 参考表（小表，全节点复制）
SELECT create_reference_table('products');
-- 查看分片信息
SELECT * FROM citus_shards;
-- 分片数量建议：worker 节点数 × 4
Citus 架构
客户端
↓
Coordinator 节点（查询路由、计划）
↓
Worker 节点1 Worker 节点2 Worker 节点3
（各持有部分分片）
5.2 逻辑复制¶
逻辑复制可以选择性地复制部分表，支持跨大版本、跨平台。
-- 发布端（Publisher）
ALTER SYSTEM SET wal_level = logical;
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- 或全库
CREATE PUBLICATION my_pub FOR ALL TABLES;
-- 行过滤（Row Filter）
CREATE PUBLICATION my_pub_active_orders
FOR TABLE orders
WHERE (status IN ('paid', 'shipped'));
-- 列过滤（Column List）
CREATE PUBLICATION my_pub_user_cols
FOR TABLE users (id, username, updated_at);
-- 订阅端（Subscriber）
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=publisher_ip dbname=mydb user=replicator password=reppass'
PUBLICATION my_pub;
-- 查看状态
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;
应用场景
数据库迁移（零停机）
数据聚合（多源汇总）
读写分离（选择性同步）
5.3 外部数据源 FDW¶
FDW（Foreign Data Wrapper）允许查询外部数据源，就像本地表一样。
-- 连接另一个 PostgreSQL
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.2', port '5432', dbname 'remotedb');
CREATE USER MAPPING FOR myuser
SERVER remote_server
OPTIONS (user 'remote_user', password 'remote_pass');
-- 创建外部表
CREATE FOREIGN TABLE remote_orders (
id BIGINT,
amount NUMERIC
) SERVER remote_server OPTIONS (schema_name 'public', table_name 'orders');
-- 直接查询外部表
SELECT * FROM remote_orders WHERE id = 1;
常用 FDW 扩展
扩展 |
连接目标 |
|---|---|
postgres_fdw |
另一个 PostgreSQL |
mysql_fdw |
MySQL |
redis_fdw |
Redis |
mongo_fdw |
MongoDB |
file_fdw |
CSV 文件 |
5.4 分布式方案对比¶
方案 |
类型 |
适用场景 |
优点 |
缺点 |
|---|---|---|---|---|
流复制 |
主从 |
读写分离、高可用 |
原生支持、简单 |
只能单主写 |
Citus |
分片 |
海量数据、高并发写 |
水平扩展 |
跨分片查询复杂 |
逻辑复制 |
数据同步 |
迁移、多活 |
灵活，可选表 |
不支持 DDL 复制 |
Patroni + HAProxy |
高可用 |
自动故障切换 |
自动化程度高 |
运维复杂 |
PL/Proxy |
分片代理 |
简单分库 |
轻量 |
功能有限 |
七、安全篇¶
7.1 SSL/TLS 配置¶
SSL 用于加密客户端与服务器之间的通信，防止数据被窃听。
服务器端配置
# postgresql.conf
# 启用SSL
ssl = on
# SSL证书路径
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt' # 可选：客户端证书验证
# SSL模式
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = on
# 强制SSL连接
# 方法1: pg_hba.conf中配置hostssl
# 方法2: 设置ssl_min_protocol_version
ssl_min_protocol_version = 'TLSv1.2'
生成自签名证书（测试用）
# 创建证书目录
mkdir -p /etc/ssl/certs /etc/ssl/private
# 生成服务器私钥
openssl genrsa -out /etc/ssl/private/server.key 2048
chmod 600 /etc/ssl/private/server.key
# 生成证书签名请求
openssl req -new -key /etc/ssl/private/server.key \
-out /tmp/server.csr \
-subj "/C=CN/ST=Beijing/L=Beijing/O=MyOrg/CN=db.example.com"
# 生成自签名证书
openssl x509 -req -days 365 -in /tmp/server.csr \
-signkey /etc/ssl/private/server.key \
-out /etc/ssl/certs/server.crt
# 设置权限
chown postgres:postgres /etc/ssl/certs/server.crt /etc/ssl/private/server.key
客户端 SSL 模式
# libpq 连接字符串中的 sslmode 参数
disable - 不使用SSL
allow - 优先非SSL，如果失败尝试SSL
prefer - 优先SSL，如果失败使用非SSL（默认）
require - 必须使用SSL，不验证证书
verify-ca - 必须使用SSL，验证服务器证书由可信CA签发
verify-full - 必须使用SSL，验证证书和主机名
# 连接示例
psql "host=db.example.com dbname=mydb sslmode=require"
psql "host=db.example.com dbname=mydb sslmode=verify-full sslrootcert=/path/to/ca.crt"
7.2 行级安全策略 (RLS)¶
RLS 允许控制用户只能访问表中的特定行。
启用 RLS
-- 创建示例表
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT,
owner TEXT DEFAULT CURRENT_USER,
department TEXT
);
-- 启用行级安全
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- 注意：表所有者默认绕过RLS，如需强制应用：
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
创建策略
-- 策略1: 用户只能看到自己的文档
CREATE POLICY user_own_documents ON documents
FOR ALL
TO PUBLIC
USING (owner = CURRENT_USER);
-- 策略2: 不同操作不同规则
CREATE POLICY document_select_policy ON documents
FOR SELECT
TO PUBLIC
USING (owner = CURRENT_USER OR department = 'public');
CREATE POLICY document_modify_policy ON documents
FOR UPDATE
TO PUBLIC
USING (owner = CURRENT_USER)
WITH CHECK (owner = CURRENT_USER);
-- 策略3: 管理员可以看到所有
CREATE POLICY admin_all_documents ON documents
FOR ALL
TO admin_role
USING (true)
WITH CHECK (true);
复杂 RLS 示例
-- 多租户场景
CREATE TABLE tenants (
id SERIAL PRIMARY KEY,
name TEXT,
domain TEXT
);
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT,
tenant_id INTEGER REFERENCES tenants(id)
);
CREATE TABLE orders_rls (
id SERIAL PRIMARY KEY,
tenant_id INTEGER REFERENCES tenants(id),
user_id INTEGER REFERENCES users(id),
amount NUMERIC
);
-- 启用RLS
ALTER TABLE orders_rls ENABLE ROW LEVEL SECURITY;
-- 创建当前租户ID函数
CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS INTEGER AS $$
DECLARE
tenant_id INTEGER;
BEGIN
-- 从会话变量获取（应用层设置）
BEGIN
tenant_id := current_setting('app.current_tenant')::INTEGER;
EXCEPTION WHEN OTHERS THEN
tenant_id := NULL;
END;
RETURN tenant_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- 设置租户ID（应用连接时执行）
SET app.current_tenant = '123';
-- 创建策略
CREATE POLICY tenant_isolation ON orders_rls
FOR ALL
TO PUBLIC
USING (tenant_id = current_tenant_id())
WITH CHECK (tenant_id = current_tenant_id());
-- 查询时自动过滤
SELECT * FROM orders_rls; -- 只返回tenant_id=123的数据
7.3 审计日志¶
PostgreSQL 提供多种审计方式：标准日志、pgAudit扩展、触发器。
标准日志配置
# postgresql.conf
# 基本日志
logging_collector = on
log_destination = 'stderr,csvlog'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
# 记录所有DDL
log_statement = 'ddl' # none / ddl / mod / all
# 记录慢查询
log_min_duration_statement = 1000 # 超过1秒的查询
# 记录连接/断开
log_connections = on
log_disconnections = on
# 记录检查点
log_checkpoints = on
# 记录锁等待
log_lock_waits = on
pgAudit 扩展（推荐）
-- 安装pgAudit（需先安装扩展）
CREATE EXTENSION pgaudit;
-- 配置（postgresql.conf）
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write,ddl' -- 记录写操作和DDL
pgaudit.log_catalog = off -- 不记录目录查询
pgaudit.log_parameter = on -- 记录参数
pgaudit.log_relation = on -- 记录表名
pgaudit.log_statement_once = off -- 每个子语句都记录
-- 会话级审计设置
SET pgaudit.log = 'all';
SET pgaudit.log_relation = on;
触发器审计
-- 创建审计表
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name TEXT,
operation TEXT, -- INSERT/UPDATE/DELETE
old_data JSONB,
new_data JSONB,
changed_by TEXT DEFAULT CURRENT_USER,
changed_at TIMESTAMP DEFAULT NOW()
);
-- 创建审计触发器函数
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit_log (table_name, operation, old_data)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD));
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW));
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit_log (table_name, operation, new_data)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW));
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- 应用审计触发器
CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON documents
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
7.4 密码策略¶
密码验证配置
# postgresql.conf
# 密码加密方式
password_encryption = 'scram-sha-256' # 推荐
# 认证超时
authentication_timeout = 1min
强密码检查（使用check_password扩展）
-- 创建密码检查函数
CREATE OR REPLACE FUNCTION check_passwordStrength(
username TEXT,
password TEXT
) RETURNS VOID AS $$
BEGIN
-- 最小长度
IF length(password) < 8 THEN
RAISE EXCEPTION 'Password must be at least 8 characters';
END IF;
-- 包含数字
IF password !~ '\d' THEN
RAISE EXCEPTION 'Password must contain at least one digit';
END IF;
-- 包含大小写字母
IF password !~ '[a-z]' OR password !~ '[A-Z]' THEN
RAISE EXCEPTION 'Password must contain both upper and lower case letters';
END IF;
-- 不能包含用户名
IF lower(password) LIKE '%' || lower(username) || '%' THEN
RAISE EXCEPTION 'Password cannot contain username';
END IF;
END;
$$ LANGUAGE plpgsql;
-- 在创建用户时调用
CREATE OR REPLACE FUNCTION create_user_with_policy(
p_username TEXT,
p_password TEXT
) RETURNS VOID AS $$
BEGIN
PERFORM check_passwordStrength(p_username, p_password);
EXECUTE format('CREATE USER %I WITH PASSWORD %L', p_username, p_password);
END;
$$ LANGUAGE plpgsql;
连接安全
# pg_hba.conf 安全配置示例
# 类型 数据库 用户 来源地址 认证方式
# 本地连接使用peer认证（操作系统用户匹配）
local all all peer
# 拒绝特定IP
host all all 10.0.0.100/32 reject
# 内网优先使用 SCRAM
host all all 10.0.0.0/24 scram-sha-256
# 应用服务器使用 SSL + SCRAM
hostssl all app_user 192.168.1.0/24 scram-sha-256
# 管理员只能从特定IP（建议SCRAM）
host all postgres 192.168.1.10/32 scram-sha-256
# 拒绝所有其他连接
host all all 0.0.0.0/0 reject
八、运维与监控¶
8.1 常用系统视图¶
-- 当前活跃连接
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state != 'idle';
-- 表统计信息
SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables ORDER BY seq_scan DESC;
-- 索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes ORDER BY idx_scan;
-- 数据库大小
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;
-- 表大小（含索引）
SELECT tablename,
pg_size_pretty(pg_total_relation_size(tablename::text)) AS total_size
FROM pg_tables WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::text) DESC;
-- 慢查询（需开启 pg_stat_statements）
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time/calls AS avg_ms, rows
FROM pg_stat_statements
ORDER BY avg_ms DESC LIMIT 20;
8.2 慢查询分析¶
启用 pg_stat_statements
-- 1. 配置 postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
-- 2. 重启 PostgreSQL
-- 3. 创建扩展
CREATE EXTENSION pg_stat_statements;
慢查询分析查询
-- 最耗时的查询（总时间）
SELECT
query,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS avg_ms,
round(stddev_exec_time::numeric, 2) AS stddev_ms,
rows,
round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- 平均耗时最长的查询
SELECT
query,
calls,
round(mean_exec_time::numeric, 2) AS avg_ms,
rows
FROM pg_stat_statements
WHERE calls > 10 -- 排除只执行几次的查询
ORDER BY mean_exec_time DESC
LIMIT 20;
-- 调用最频繁的查询
SELECT
query,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(total_exec_time / calls::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;
-- 重置统计（谨慎使用）
SELECT pg_stat_statements_reset();
8.3 锁分析¶
查看当前锁
-- 查看所有锁
SELECT
l.locktype,
l.relation::regclass AS table_name,
l.mode,
l.granted,
a.usename,
a.pid,
a.query,
a.state
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
ORDER BY l.relation::regclass::text;
-- 查看锁等待链
WITH RECURSIVE lock_tree AS (
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement,
1 AS level
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.relation = blocked_locks.relation
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
UNION ALL
SELECT
lt.blocked_pid,
lt.blocked_user,
blocking_locks.pid,
blocking_activity.usename,
lt.blocked_statement,
blocking_activity.query,
lt.level + 1
FROM lock_tree lt
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.pid = lt.blocking_pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE lt.level < 5
)
SELECT * FROM lock_tree;
-- 查看长时间运行的查询
SELECT
pid,
usename,
application_name,
state,
query_start,
now() - query_start AS duration,
query
FROM pg_stat_activity
WHERE state != 'idle'
AND now() - query_start > interval '5 minutes'
ORDER BY query_start;
8.4 连接池监控¶
PgBouncer 监控
-- 连接到 pgbouncer 管理数据库
psql -p 6432 pgbouncer
-- 查看连接池状态
SHOW POOLS;
-- 查看客户端连接
SHOW CLIENTS;
-- 查看服务器连接
SHOW SERVERS;
-- 查看统计
SHOW STATS;
-- 查看配置
SHOW CONFIG;
连接数监控
-- 当前连接数统计
SELECT
datname,
usename,
application_name,
state,
COUNT(*)
FROM pg_stat_activity
GROUP BY datname, usename, application_name, state
ORDER BY COUNT(*) DESC;
-- 连接数趋势（需定期采样）
SELECT
COUNT(*) AS total_connections,
COUNT(*) FILTER (WHERE state = 'active') AS active,
COUNT(*) FILTER (WHERE state = 'idle') AS idle,
COUNT(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction,
COUNT(*) FILTER (WHERE wait_event_type = 'Lock') AS waiting_on_lock
FROM pg_stat_activity;
-- 检查最大连接数
SHOW max_connections;
SELECT setting::int FROM pg_settings WHERE name = 'max_connections';
-- 计算连接使用率
SELECT
COUNT(*) AS current_connections,
(SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
round(100.0 * COUNT(*) / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 2) AS usage_percent
FROM pg_stat_activity;
8.5 日志配置¶
# postgresql.conf
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
# 记录慢查询
log_min_duration_statement = 1000 # 超过 1000ms 记录
# 记录连接
log_connections = on
log_disconnections = on
# 记录锁等待
log_lock_waits = on
deadlock_timeout = 1s
8.6 监控工具¶
工具 |
说明 |
|---|---|
pgAdmin |
官方 GUI 管理工具 |
pg_activity |
类似 top 的实时监控 |
pgBadger |
日志分析报告 |
Prometheus + postgres_exporter |
指标采集，配合 Grafana |
Zabbix |
企业级监控 |
datadog |
云监控服务 |
8.7 日常维护清单¶
任务 |
频率 |
命令/方式 |
|---|---|---|
更新统计信息 |
每天 |
|
清理死元组 |
每天 |
|
检查膨胀表 |
每周 |
|
清理无用索引 |
每月 |
|
检查复制延迟 |
实时 |
|
备份验证 |
每周 |
恢复测试 |
慢查询分析 |
每周 |
|
九、扩展开发¶
9.1 常用扩展介绍¶
PostgreSQL 支持丰富的扩展，可通过 CREATE EXTENSION 启用。
-- 查看可用扩展
SELECT * FROM pg_available_extensions ORDER BY name;
-- 查看已安装扩展
SELECT * FROM pg_extension;
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 模糊搜索
CREATE EXTENSION IF NOT EXISTS postgis; -- 地理空间
CREATE EXTENSION IF NOT EXISTS hstore; -- 键值存储
常用扩展列表
扩展名 |
功能 |
示例 |
|---|---|---|
uuid-ossp |
UUID生成 |
|
pg_trgm |
相似度搜索 |
|
postgis |
地理空间 |
空间数据类型和函数 |
hstore |
键值对 |
|
unaccent |
去重音 |
|
fuzzystrmatch |
字符串匹配 |
|
intarray |
数组操作 |
数组索引和运算符 |
ltree |
树结构 |
层次数据存储 |
pg_stat_statements |
查询统计 |
慢查询分析 |
pgcrypto |
加密 |
|
9.2 自定义 SQL 函数¶
-- 简单函数
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 返回表函数
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id INTEGER)
RETURNS TABLE (
order_id INTEGER,
order_date DATE,
amount NUMERIC
) AS $$
BEGIN
RETURN QUERY
SELECT id, created_at::DATE, total_amount
FROM orders
WHERE user_id = p_user_id
ORDER BY created_at DESC;
END;
$$ LANGUAGE plpgsql STABLE;
-- 使用
SELECT * FROM get_user_orders(1);
-- 聚合函数（使用内置聚合）
CREATE OR REPLACE FUNCTION array_accumulate(INTEGER[])
RETURNS INTEGER[] AS $$
SELECT array_agg(x ORDER BY x)
FROM unnest($1) x;
$$ LANGUAGE sql IMMUTABLE;
-- 变参函数
CREATE OR REPLACE FUNCTION concat_ws_custom(sep TEXT, VARIADIC parts TEXT[])
RETURNS TEXT AS $$
DECLARE
result TEXT := '';
i INT;
BEGIN
FOR i IN 1..array_length(parts, 1) LOOP
IF parts[i] IS NOT NULL AND parts[i] != '' THEN
IF result != '' THEN
result := result || sep;
END IF;
result := result || parts[i];
END IF;
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- 使用
SELECT concat_ws_custom(', ', 'a', NULL, 'b', '', 'c'); -- 结果: 'a, b, c'
9.3 扩展结构¶
一个完整的 PostgreSQL 扩展包含以下文件：
my_extension/
├── my_extension.control # 扩展控制文件
├── my_extension--1.0.sql # 版本1.0的SQL脚本
├── my_extension--1.0--1.1.sql # 1.0升级到1.1的脚本
└── Makefile # 编译脚本（C扩展需要）
扩展控制文件示例
# my_extension.control
default_version = '1.0'
comment = 'My custom extension'
explicit_only = false # 是否允许在CREATE EXTENSION时指定版本
relocatable = true # 是否可移动到不同schema
schema = public # 默认schema
requires = 'plpgsql,uuid-ossp' # 依赖的其他扩展
扩展 SQL 脚本示例
-- my_extension--1.0.sql
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION my_extension" to load this file. \quit
-- 创建函数
CREATE OR REPLACE FUNCTION my_extension_hello()
RETURNS TEXT AS $$
BEGIN
RETURN 'Hello from my_extension!';
END;
$$ LANGUAGE plpgsql;
-- 创建表
CREATE TABLE IF NOT EXISTS my_extension_log (
id SERIAL PRIMARY KEY,
message TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
-- 创建触发器函数
CREATE OR REPLACE FUNCTION my_extension_log_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO my_extension_log (message)
VALUES (TG_OP || ' on ' || TG_TABLE_NAME);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
安装自定义扩展
# 1. 复制到扩展目录
sudo cp -r my_extension /usr/share/postgresql/14/extension/
# 2. 在数据库中安装
CREATE EXTENSION my_extension;
# 3. 升级扩展
ALTER EXTENSION my_extension UPDATE TO '1.1';
9.4 使用 C 语言编写扩展¶
对于性能敏感的操作，可以使用 C 语言编写扩展。
// my_c_extension.c
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(my_c_function);
Datum
my_c_function(PG_FUNCTION_ARGS)
{
text *input = PG_GETARG_TEXT_PP(0);
int32 len = VARSIZE_ANY_EXHDR(input);
char *str = VARDATA_ANY(input);
// 处理逻辑
text *result = (text *) palloc(VARHDRSZ + len);
SET_VARSIZE(result, VARHDRSZ + len);
memcpy(VARDATA(result), str, len);
PG_RETURN_TEXT_P(result);
}
# Makefile
MODULES = my_c_extension
EXTENSION = my_c_extension
DATA = my_c_extension--1.0.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
9.5 背景工作进程¶
PostgreSQL 支持自定义后台工作进程。
// background_worker.c
#include "postgres.h"
#include "postmaster/bgworker.h"
#include "storage/ipc.h"
#include "storage/latch.h"
#include "storage/proc.h"
#include "utils/guc.h"
PG_MODULE_MAGIC;
void _PG_init(void);
static void my_worker_main(Datum main_arg);
void
_PG_init(void)
{
BackgroundWorker worker;
memset(&worker, 0, sizeof(worker));
sprintf(worker.bgw_name, "my background worker");
worker.bgw_flags = BGWORKER_SHMEM_ACCESS;
worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
worker.bgw_restart_time = BGW_NEVER_RESTART;
sprintf(worker.bgw_library_name, "my_extension");
sprintf(worker.bgw_function_name, "my_worker_main");
worker.bgw_main_arg = (Datum) 0;
RegisterBackgroundWorker(&worker);
}
static void
my_worker_main(Datum main_arg)
{
// 后台任务逻辑
while (!got_sigterm)
{
// 执行定期任务
pg_usleep(1000000L); // 睡眠1秒
}
proc_exit(0);
}