Postgresql

PostgreSQL — 高级关系型数据库系统

Workflow — 使用流程

遇到 PostgreSQL 需求时，按以下顺序决策:

1. 明确需求类型
   ├── DDL (建表/改表)        → 见 SQL 语法速查
   ├── DML (查询/插入/更新)    → 见 SQL 语法速查
   ├── 函数/数据处理            → 见 函数速查
   ├── 查询性能优化             → 见 references/06-index-types.md + examples/03-performance-tuning.md
   └── 高可用/备份/复制         → 见 references/08-replication-backup.md + examples/04-streaming-replication.md

2. 确定模型: 关系型 → 标准表+B-Tree | JSON文档 → JSONB+GIN | 全文搜索 → tsvector+GIN | 地理 → PostGIS+GiST

3. 索引策略: 等值→B-Tree | 范围→B-Tree | 全文→GIN | JSON→GIN | 向量→IVFFlat/HNSW | 大表时序→BRIN

4. 数据量评估: <100GB→单实例 | 100GB-1TB→分区 | 1TB-10TB→分区+只读副本 | >10TB→Citus/逻辑复制

5. 运维策略: autovacuum + pg_stat_statements + WAL归档 + PgBouncer

When to Use (and When NOT to)

核心原则：PostgreSQL 是全能型关系型数据库，但不是所有场景的最佳选择。

Boundary — 能力边界

SQL 语法速查

深度 SQL 内容见 references/ 各文件，此处为索引。

• DDL: CREATE TABLE (含分区、继承), ALTER TABLE, 数据类型 (JSONB/TSVECTOR/CITEXT/数组等), 约束 (CHECK/EXCLUDE/UNIQUE/FOREIGN KEY)
• DML: INSERT ... ON CONFLICT (UPSERT), UPDATE ... FROM, DELETE ... USING, TRUNCATE, RETURNING 子句
• CTE: 公用表表达式 (WITH), 递归 CTE (WITH RECURSIVE) — 见 examples/02-cte-recursive.md
• 连接: INNER/LEFT/RIGHT/FULL/CROSS JOIN, LATERAL 子查询
• 事务: READ COMMITTED (默认), REPEATABLE READ, SERIALIZABLE, SAVEPOINT, FOR UPDATE/SHARE/NOWAIT/SKIP LOCKED, 咨询锁

函数速查

深度内容见 references/:

高级特性索引

Gotchas — 常见陷阱与反模式

FAQ

Q1: PostgreSQL vs MySQL 主要区别？ PostgreSQL: 完全 ACID, JSONB 可索引, 6 种索引类型, 递归 CTE, 流复制+逻辑复制, 丰富 EXTENSION。MySQL: Web 应用为主, 简单查询, InnoDB 事务, 间隙锁并发控制。

Q2: JSONB vs JSON？ 始终选 JSONB。二进制格式, 支持 GIN 索引, 查询更快。JSON 仅在你需要保留空格和键顺序时使用。

Q3: UUID 为什么不适合做主键？ UUID v4 随机值导致 B-Tree 页频繁分裂, 比 BIGSERIAL 慢 2-3 倍。方案: UUID v7 (时间排序), BIGSERIAL, 或 ULID/Snowflake。

Q4: 如何在线迁移 PostgreSQL？ 逻辑复制 (PG 10+, 推荐) > pglogical 扩展 > pg_dump+pg_restore (需停机)。逻辑复制支持跨大版本、选择性复制。

Q5: work_mem 怎么设？ 每个排序操作分配, 最大内存 = work_mem × (连接数 × 并发排序数)。64GB 机器建议 64-128MB。监控 temp_files 指标, 有磁盘排序则调大。

Q6: pg_repack vs VACUUM FULL？ VACUUM FULL 锁表 (ACCESS EXCLUSIVE)。pg_repack 不锁写, 适合在线环境, 优先选择。

Q7: 死锁怎么处理？ PG 自动检测并回滚一个事务。预防: 保持锁顺序一致、缩短事务、用 NOWAIT/SKIP LOCKED, 监控 pg_stat_database.deadlocks。

Q8: 如何选择分区键？ 条件: 查询频繁出现 (分区裁剪)、数据均匀分布、稳定不变。常见: 时间 (RANGE)、地区 (LIST)、ID 哈希 (HASH)。分区数建议 10-200。

Q9: 连接数设多少？ 每个连接 5-10MB, 一般 200-500 够用。超过 500 必须用 PgBouncer。(max_connections × work_mem × 0.5) + shared_buffers + 系统开销 < 内存 80%。

Q10: 何时用 SERIALIZABLE？ 金融转账、库存扣减、强一致性报表。注意: 失败率随冲突上升, 应用层需重试逻辑。

Q11: 查询没走索引的原因？ 统计信息过旧→ANALYZE | 类型不匹配→隐式转换 | 选择性低→规划器认为全表更优 | 函数包裹索引列→避免 WHERE DATE(col) = 写法。

Q12: 怎么判断要不要分区？ 表 > 100GB | 存在明显按时间/地区查询模式 | 旧数据定期归档 | VACUUM 跟不上更新。不满足则分区复杂度 > 收益。

Q13: 如何大版本升级？ pg_upgrade 最推荐: pg_upgrade -b old_bin -B new_bin -d old_data -D new_data, --link 模式最快。升级后执行 ANALYZE。

Q14: 逻辑复制 vs 流复制？ 流复制: 物理块级, 全库, 大版本必须一致, 用于 HA。逻辑复制: SQL 变更, 选表/行, 跨大版本, 用于数据同步/迁移。

Q15: VACUUM 后表大小没变小？ VACUUM (不带 FULL) 只标记空间可重用, 不还给 OS。真正缩小需 VACUUM FULL 或 pg_repack。

Keywords

postgresql, postgres, psql, SQL, DDL, DML, ACID, MVCC, 事务, 索引, B-Tree, GIN, GiST, BRIN, JSONB, hstore, 数组, 全文搜索, tsvector, tsquery, 窗口函数, CTE, 递归CTE, LATERAL, PL/pgSQL, 存储过程, 触发器, 物化视图, 分区表, PostGIS, pgvector, pg_stat_statements, pgcrypto, citext, pg_trgm, FDW, postgres_fdw, EXPLAIN, VACUUM, autovacuum, pg_dump, pg_restore, pg_basebackup, WAL归档, PITR, 流复制, 逻辑复制, PUBLICATION, SUBSCRIPTION, Patroni, repmgr, PgBouncer, RLS, 行级安全, 性能优化, 备份恢复, 高可用, 死锁, 连接池

References

• PostgreSQL 官方文档
• PostgreSQL 中文文档
• PostgreSQL 性能调优 — pgtune
• Patroni 文档
• PgBouncer 官方文档
• pgvector 文档
• PostGIS 文档

内部参考

• references/01-functions-string.md — 字符串/正则函数详解
• references/02-functions-datetime.md — 日期/时间函数详解
• references/03-functions-aggregate-window.md — 聚合/窗口函数详解
• references/04-functions-jsonb.md — JSONB 函数与操作详解
• references/05-fulltext-search.md — 全文搜索详解
• references/06-index-types.md — 索引类型与视图详解
• references/07-partition-fdw.md — 分区表与 FDW 详解
• references/08-replication-backup.md — 复制/备份/权限详解
• examples/01-jsonb-query.md — JSONB 查询示例
• examples/02-cte-recursive.md — 递归 CTE 示例
• examples/03-performance-tuning.md — 性能调优示例
• examples/04-streaming-replication.md — 流复制搭建示例

JSONB 查询示例

场景：电商用户配置系统

本示例演示如何使用 JSONB 存储和查询用户偏好配置。

建表与数据

-- 建表
CREATE TABLE user_configs (
    id      BIGSERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    config  JSONB NOT NULL DEFAULT '{}'
);

-- 插入示例数据
INSERT INTO user_configs (user_id, config) VALUES
(1, '{
    "theme": "dark",
    "notifications": {"email": true, "sms": false, "push": true},
    "preferences": {"language": "zh-CN", "timezone": "Asia/Shanghai"},
    "tags": ["developer", "premium"],
    "trust_score": 4.5
}'),
(2, '{
    "theme": "light",
    "notifications": {"email": false, "sms": true, "push": false},
    "preferences": {"language": "en", "timezone": "America/New_York"},
    "tags": ["basic"]
}');

-- 创建 GIN 索引
CREATE INDEX idx_config_gin ON user_configs USING GIN (config);

查询示例

-- 1. 查询所有使用深色主题的用户
SELECT user_id, config ->> 'theme' AS theme
FROM user_configs
WHERE config @> '{"theme": "dark"}';

-- 2. 查询开启了邮件通知的用户
SELECT user_id FROM user_configs
WHERE config @> '{"notifications": {"email": true}}';

-- 3. 查询有 trust_score 字段的用户
SELECT user_id FROM user_configs WHERE config ? 'trust_score';

-- 4. 查询语言为中文的高级用户 (tags 包含 "premium")
SELECT user_id FROM user_configs
WHERE config @> '{"preferences": {"language": "zh-CN"}}'
  AND config @> '{"tags": ["premium"]}';

-- 5. 更新嵌套字段（开启 SMS 通知）
UPDATE user_configs SET config = JSONB_SET(
    config, '{notifications, sms}', 'true'::JSONB
) WHERE user_id = 1;

-- 6. 追加标签
UPDATE user_configs SET config = config || '{"tags": ["vip"]}'
WHERE user_id = 1;

-- 7. 展开 JSONB 查看所有键值对
SELECT key, value FROM user_configs c,
JSONB_EACH(c.config) WHERE user_id = 1;

-- 8. 聚合用户配置为 JSONB 数组
SELECT JSONB_AGG(config) AS all_configs FROM user_configs;

递归 CTE 示例

场景 1：组织架构树

查询从根节点到所有子节点的完整部门树。

-- 建表
CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    parent_id   INTEGER REFERENCES departments(id)
);

-- 插入层级数据
INSERT INTO departments (id, name, parent_id) VALUES
    (1, '总公司', NULL),
    (2, '技术部', 1),
    (3, '市场部', 1),
    (4, '后端组', 2),
    (5, '前端组', 2),
    (6, '数据组', 2),
    (7, '广告组', 3),
    (8, 'PR 组', 3);

-- 递归 CTE: 展开整个树
WITH RECURSIVE org_tree AS (
    -- 基础: 根节点
    SELECT id, name, parent_id, 1 AS level, ARRAY[id] AS path
    FROM departments
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归: 子节点
    SELECT d.id, d.name, d.parent_id, t.level + 1, t.path || d.id
    FROM departments d
    JOIN org_tree t ON d.parent_id = t.id
)
SELECT id, name, level, repeat('  ', level - 1) || name AS tree_display
FROM org_tree ORDER BY path;

场景 2：商品分类全路径

将树形分类扁平化并显示完整路径。

-- 建表
CREATE TABLE categories (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    parent_id   INTEGER REFERENCES categories(id)
);

INSERT INTO categories (id, name, parent_id) VALUES
    (1, '电子产品', NULL),
    (2, '手机', 1),
    (3, '电脑', 1),
    (4, '智能手机', 2),
    (5, '功能机', 2),
    (6, '笔记本', 3),
    (7, '台式机', 3);

-- 从指定节点开始，查询所有子分类及其全路径
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, name AS full_path
    FROM categories WHERE id = 1  -- 从 "电子产品" 开始

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           ct.full_path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY full_path;

场景 3：斐波那契数列

WITH RECURSIVE fib(a, b) AS (
    SELECT 0::BIGINT, 1::BIGINT
    UNION ALL
    SELECT b, a + b FROM fib WHERE b < 1000
)
SELECT a FROM fib;

场景 4：销售统计占比

使用非递归 CTE 计算每个分类的销售额占比。

WITH category_sales AS (
    SELECT c.name AS category, SUM(oi.quantity * oi.price) AS total
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    JOIN categories c ON c.id = p.category_id
    GROUP BY c.name
),
grand_total AS (
    SELECT SUM(total) AS total FROM category_sales
)
SELECT cs.category, cs.total,
       ROUND(cs.total / gt.total * 100, 2) AS pct
FROM category_sales cs, grand_total gt
ORDER BY cs.total DESC;

性能调优示例

场景 1：定位慢查询

使用 EXPLAIN ANALYZE 诊断查询性能问题。

-- 创建测试表
CREATE TABLE orders (
    id BIGSERIAL, user_id INTEGER NOT NULL, status TEXT,
    total_amount NUMERIC(12,2), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 插入测试数据（假设已有数百万行）

-- 诊断慢查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';
-- 如果看到 Seq Scan → 需要加索引

-- 创建复合索引
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- 再次验证
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';
-- 现在应该看到 Index Scan

场景 2：JOIN 性能优化

-- 慢查询：大表 JOIN + GROUP BY
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name;

-- 检查输出中的 Sort Method
-- 如果看到 "external merge Disk: 1536kB" → work_mem 不足

-- 临时增加 work_mem（当前会话）
SET work_mem = '256MB';

-- 或者创建覆盖索引
CREATE INDEX idx_orders_user_id_covering ON orders (user_id) INCLUDE (id);

场景 3：VACUUM 与膨胀监控

-- 查看表膨胀情况
SELECT relname, n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC LIMIT 20;

-- 为高频更新表调优 autovacuum
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 1000
);

-- 检查事务 ID 回卷风险
SELECT datname, age(datfrozenxid) AS age,
       ROUND(100 * age(datfrozenxid)::NUMERIC / 2000000000, 2) AS pct_wraparound
FROM pg_database ORDER BY age DESC;

场景 4：pg_stat_statements 分析

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- TOP 10 最耗时查询
SELECT queryid, LEFT(query, 80) AS query_preview, calls,
       ROUND(total_exec_time::NUMERIC, 2) AS total_ms,
       ROUND(mean_exec_time::NUMERIC, 2) AS avg_ms,
       ROUND(shared_blks_hit::NUMERIC / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 2) AS hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- TOP 10 I/O 密集查询
SELECT queryid, LEFT(query, 80) AS query_preview,
       shared_blks_read, temp_blks_read
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC LIMIT 10;

-- TOP 10 临时文件使用（work_mem 不足）
SELECT queryid, LEFT(query, 80) AS query_preview,
       temp_blk_read_time, temp_blk_write_time
FROM pg_stat_statements
WHERE temp_blk_read_time > 0
ORDER BY temp_blk_read_time DESC LIMIT 10;

场景 5：配置调优参考

# 64GB 内存服务器参考配置
shared_buffers = 12GB            # 物理内存 20-25%
work_mem = 64MB                  # 每个排序操作
maintenance_work_mem = 1GB       # VACUUM/CREATE INDEX
effective_cache_size = 12GB      # 规划器缓存估计
wal_buffers = 16MB
max_connections = 200            # 超过则用 PgBouncer
checkpoint_timeout = 15min
max_wal_size = 16GB
default_statistics_target = 100  # 大表可调至 500-1000

流复制搭建示例

场景：搭建一主一从的高可用架构

主库配置

# postgresql.conf 配置
listen_addresses = 'localhost,192.168.1.100'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on

# 重启主库
systemctl restart postgresql

创建复制用户

-- 在主库执行
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'Str0ng!Pass';

# 在 pg_hba.conf 添加
echo 'host replication replicator 192.168.1.0/24 md5' >> /var/lib/pgsql/data/pg_hba.conf

# 重新加载配置
psql -c "SELECT pg_reload_conf();"

从库搭建

# 安装相同版本 PostgreSQL

# 停从库
systemctl stop postgresql

# 清空从库数据目录
rm -rf /var/lib/pgsql/data/*

# 从主库拉取基础备份
pg_basebackup -h 192.168.1.100 -U replicator \
    -D /var/lib/pgsql/data -P -v --wal-method=stream

# PG 12+: 创建 standby 信号文件
touch /var/lib/pgsql/data/standby.signal

# 配置主库连接信息
cat > /var/lib/pgsql/data/postgresql.auto.conf << EOF
primary_conninfo = 'host=192.168.1.100 port=5432 user=replicator password=Str0ng!Pass'
EOF

# 启动从库
systemctl start postgresql

验证复制

-- 在主库检查复制状态
SELECT pid, application_name, state, sync_state,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- 在从库检查接收状态
SELECT pid, status, receive_start_lsn, received_lsn,
       latest_end_lsn, latest_end_time
FROM pg_stat_wal_receiver;

-- 测试: 在主库创建表并插入数据
CREATE TABLE test_replication (id SERIAL PRIMARY KEY, data TEXT, ts TIMESTAMPTZ DEFAULT NOW());
INSERT INTO test_replication (data) VALUES ('hello from primary');

-- 在从库验证（从库为只读模式）
SELECT * FROM test_replication;

故障转移

# 手动提升从库为主库
# 在从库执行
pg_ctl promote -D /var/lib/pgsql/data
# 或
systemctl stop postgresql
# 删除 standby.signal 后启动
rm /var/lib/pgsql/data/standby.signal
systemctl start postgresql

# 此时原从库变为可读写

常见问题排查

# 查看复制日志
tail -f /var/lib/pgsql/data/log/postgresql-*.log

# 检查网络连通性
psql -h 192.168.1.100 -U replicator -d postgres -c "SELECT 1"

# 检查 WAL 发送进程
ps aux | grep wal_sender

# 检查磁盘空间（WAL 堆积会导致磁盘满）
df -h /var/lib/pgsql/data/

字符串/正则函数详解

字符串函数

-- FORMAT — 格式化字符串
SELECT FORMAT('ORD-%s-%04d', TO_CHAR(NOW(), 'YYYYMMDD'), 123);
-- 结果: ORD-20240529-0123

-- SPLIT_PART — 分割字符串
SELECT SPLIT_PART('北京市海淀区中关村', '区', 1);
-- 结果: 北京市海淀

-- STRING_AGG — 字符串聚合（将分类名称合并为逗号分隔字符串）
SELECT STRING_AGG(DISTINCT c.name, ', ' ORDER BY c.name) AS categories
FROM products p
JOIN product_categories pc ON pc.product_id = p.id
JOIN categories c ON c.id = pc.category_id
WHERE p.id = 1001;

-- CONCAT / CONCAT_WS
SELECT CONCAT_WS(', ', province, city, district, detail) AS full_address FROM addresses;

-- LEFT / RIGHT
SELECT LEFT('Hello World', 5);   -- Hello
SELECT RIGHT('Hello World', 5);  -- World

-- REPEAT / REVERSE
SELECT REPEAT('*', 5);           -- *****
SELECT REVERSE('PostgreSQL');    -- LQSregtsoP

-- POSITION / STRPOS
SELECT POSITION('SQL' IN 'PostgreSQL');  -- 7
SELECT STRPOS('PostgreSQL', 'SQL');       -- 7

-- SUBSTRING (支持正则)
SELECT SUBSTRING('abc123def' FROM '[0-9]+');  -- 123

-- TRANSLATE
SELECT TRANSLATE('hello', 'aeiou', '12345');  -- h2ll4

正则函数

-- REGEXP_MATCH — 正则匹配（提取邮箱）
SELECT REGEXP_MATCH(
    '联系邮箱: alice@example.com, 备用: bob@test.com',
    '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}',
    'g'
);
-- 结果: {alice@example.com,bob@test.com}

-- REGEXP_REPLACE — 正则替换（脱敏手机号）
SELECT REGEXP_REPLACE('13812345678', '(\d{3})\d{4}(\d{4})', '\1****\2');
-- 结果: 138****5678

PL/pgSQL 函数

-- 标量函数
CREATE OR REPLACE FUNCTION calculate_discount(
    price NUMERIC, discount_pct NUMERIC, max_discount NUMERIC DEFAULT 100
) RETURNS NUMERIC
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
    RETURN GREATEST(price * (1 - discount_pct / 100), price - max_discount);
END;
$$;

-- 表函数 (RETURNS TABLE)
CREATE OR REPLACE FUNCTION get_user_orders(
    p_user_id INTEGER, p_status TEXT DEFAULT NULL, p_limit INTEGER DEFAULT 100
) RETURNS TABLE (order_id BIGINT, total_amount NUMERIC(12,2), status TEXT, created_at TIMESTAMPTZ)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.total_amount, o.status, o.created_at
    FROM orders o
    WHERE o.user_id = p_user_id AND (p_status IS NULL OR o.status = p_status)
    ORDER BY o.created_at DESC LIMIT p_limit;
END;
$$;

-- 函数 (FUNCTION) vs 过程 (PROCEDURE)
-- FUNCTION: 必须返回值, SELECT 中调用
-- PROCEDURE (PG 11+): 无返回值, CALL 调用, 支持事务控制

-- 函数重载
CREATE OR REPLACE FUNCTION format_price(price NUMERIC) RETURNS TEXT
LANGUAGE SQL IMMUTABLE AS $$ SELECT '¥' || ROUND(price, 2)::TEXT; $$;

CREATE OR REPLACE FUNCTION format_price(price NUMERIC, currency TEXT) RETURNS TEXT
LANGUAGE SQL IMMUTABLE AS $$ SELECT currency || ROUND(price, 2)::TEXT; $$;

-- PL/pgSQL 控制结构
CREATE OR REPLACE FUNCTION process_order(p_order_id BIGINT) RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_order orders%ROWTYPE;
    v_log TEXT := '';
BEGIN
    SELECT * INTO STRICT v_order FROM orders WHERE id = p_order_id;

    IF v_order.status = 'pending' THEN
        v_log := '待处理';
    ELSIF v_order.status = 'paid' THEN
        v_log := '已支付';
    END IF;

    RETURN v_log;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN '订单不存在';
    WHEN OTHERS THEN RETURN '错误: ' || SQLERRM;
END;
$$;

触发器

-- 自动更新 updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER
LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$;

CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION update_updated_at_column();

-- 审计日志触发器
CREATE OR REPLACE FUNCTION audit_order_changes() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO order_audit_log (order_id, new_data, action) VALUES (NEW.id, row_to_json(NEW)::JSONB, 'INSERT');
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO order_audit_log (order_id, old_data, new_data, action) VALUES (NEW.id, row_to_json(OLD)::JSONB, row_to_json(NEW)::JSONB, 'UPDATE');
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO order_audit_log (order_id, old_data, action) VALUES (OLD.id, row_to_json(OLD)::JSONB, 'DELETE');
    END IF;
    RETURN NEW;
END;
$$;

-- 事件触发器 (DDL)
CREATE OR REPLACE FUNCTION prevent_table_drop() RETURNS EVENT_TRIGGER
LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION '禁止删除表'; END; $$;
CREATE EVENT TRIGGER prevent_drop_trigger ON sql_drop EXECUTE FUNCTION prevent_table_drop();

数字函数

-- RANDOM — 随机抽样
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;

-- GENERATE_SERIES — 生成序列
SELECT GENERATE_SERIES('2024-01-01'::DATE, '2024-01-10'::DATE, '1 day');
SELECT GENERATE_SERIES(1, 10, 2);  -- 1, 3, 5, 7, 9

-- WIDTH_BUCKET — 等宽分桶
SELECT WIDTH_BUCKET(age, 0, 100, 10) AS bucket, MIN(age), MAX(age), COUNT(*)
FROM users GROUP BY bucket ORDER BY bucket;

-- ROUND / TRUNC / CEIL / FLOOR / POWER / SQRT / ABS / DIV / MOD / GCD / LCM
SELECT ROUND(123.456, 2), TRUNC(123.456, 2), CEIL(123.001), FLOOR(123.999);
SELECT POWER(2,10), SQRT(144), ABS(-42), DIV(10,3), MOD(10,3), GCD(12,18), LCM(12,18);

日期/时间函数详解

-- AGE — 计算时间差
SELECT AGE('2024-05-29', '2023-01-15');
-- 结果: 1 year 4 mons 14 days

-- 计算年龄
SELECT id, EXTRACT(YEAR FROM AGE(birth_date)) AS age FROM users;

-- DATE_TRUNC — 时间截断（支持: microsecond, millisecond, second, minute, hour, day, week, month, quarter, year, decade, century, millennium）
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS order_count
FROM orders GROUP BY month;

-- EXTRACT — 提取日期部分
SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DOW FROM created_at) AS day_of_week,  -- 0=Sunday
    EXTRACT(HOUR FROM created_at) AS hour,
    EXTRACT(QUARTER FROM created_at) AS quarter
FROM orders;

-- TO_CHAR — 日期格式化
SELECT
    TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS formatted_time,
    TO_CHAR(created_at, 'YYYY年MM月DD日') AS chinese_date,
    TO_CHAR(created_at, 'Day, DD Month YYYY') AS english_date,
    TO_CHAR(created_at, 'IW') AS iso_week_number
FROM orders;

-- JUSTIFY_DAYS / JUSTIFY_HOURS / JUSTIFY_INTERVAL
SELECT JUSTIFY_DAYS(30::INTERVAL);   -- 30 days → 1 mon
SELECT JUSTIFY_HOURS(100::INTERVAL); -- 100:00:00 → 4 days 04:00:00

-- MAKE_DATE / MAKE_TIMESTAMPTZ / MAKE_INTERVAL (PG 10+)
SELECT MAKE_DATE(2024, 6, 1);
SELECT MAKE_TIMESTAMPTZ(2024, 6, 1, 10, 30, 0, 'Asia/Shanghai');
SELECT MAKE_INTERVAL(days => 10, hours => 5);

-- DATE 运算
SELECT NOW(), NOW() + INTERVAL '1 day', NOW() - INTERVAL '3 hours';
SELECT CURRENT_DATE, CURRENT_TIME;

-- 时区转换
SELECT
    NOW() AT TIME ZONE 'Asia/Shanghai',
    NOW() AT TIME ZONE 'UTC',
    '2024-06-01 10:00:00+08'::TIMESTAMPTZ AT TIME ZONE 'America/New_York';

-- 日期范围查询最佳实践
-- ❌ 避免: WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
-- ✅ 推荐: WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

聚合/窗口函数详解

聚合函数

-- ARRAY_AGG — 聚合为数组
SELECT o.id, ARRAY_AGG(p.name ORDER BY p.name) AS products
FROM orders o JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id GROUP BY o.id;

-- STRING_AGG — 聚合为字符串
SELECT article_id, STRING_AGG(DISTINCT tag, ', ' ORDER BY tag) AS tags
FROM article_tags GROUP BY article_id;

-- JSON_AGG / JSONB_AGG — 聚合为 JSON
SELECT o.id, JSONB_AGG(JSONB_BUILD_OBJECT('product_id', oi.product_id, 'qty', oi.quantity)) AS items
FROM orders o JOIN order_items oi ON oi.order_id = o.id GROUP BY o.id;

-- MODE — 众数
SELECT MODE() WITHIN GROUP (ORDER BY category_id) FROM products;

-- PERCENTILE_CONT / PERCENTILE_DISC — 百分位数
SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS q1,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_amount) AS p90
FROM orders;

-- GROUPING SETS / CUBE / ROLLUP
SELECT COALESCE(department, 'ALL') AS dept, COALESCE(role, 'ALL') AS role,
       COUNT(*) AS cnt, AVG(salary)::NUMERIC(10,2) AS avg_sal
FROM employees
GROUP BY GROUPING SETS ((department, role), (department), (role), ());

SELECT category, brand, COUNT(*) FROM products GROUP BY CUBE (category, brand);

SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month,
       COUNT(*) FROM orders GROUP BY ROLLUP (year, month) ORDER BY year, month;

窗口函数

-- ROW_NUMBER — 行号
SELECT id, name, category_id, price,
       ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
FROM products;

-- RANK / DENSE_RANK — 排名
SELECT salesperson, amount,
       RANK() OVER (ORDER BY amount DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM monthly_sales;

-- NTILE — 分桶
SELECT id, total_spent, NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customers;

-- LAG / LEAD — 前后行访问
SELECT dt, revenue,
       LAG(revenue, 1) OVER (ORDER BY dt) AS prev_day,
       LAG(revenue, 7) OVER (ORDER BY dt) AS prev_week,
       ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY dt))
             / NULLIF(LAG(revenue, 1) OVER (ORDER BY dt), 0) * 100, 2) AS dod_pct,
       LEAD(revenue, 1) OVER (ORDER BY dt) AS next_day
FROM daily_revenue;

-- FIRST_VALUE / LAST_VALUE
SELECT category_id, name, price,
       FIRST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price) AS min_price,
       LAST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_price
FROM products;

-- NTH_VALUE
SELECT DISTINCT category_id,
       NTH_VALUE(name, 3) OVER (PARTITION BY category_id ORDER BY price DESC
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_expensive
FROM products;

-- 窗口帧控制
SELECT dt, revenue,
       AVG(revenue) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d,
       AVG(revenue) OVER (ORDER BY dt ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30d
FROM daily_revenue;

-- 累积求和
SELECT dt, revenue, SUM(revenue) OVER (ORDER BY dt) AS cumulative_revenue
FROM daily_revenue;

-- 分组累计
SELECT category_id, dt, revenue,
       SUM(revenue) OVER (PARTITION BY category_id ORDER BY dt ROWS UNBOUNDED PRECEDING) AS cum_by_cat
FROM daily_revenue_by_category;

-- 窗口函数 + FILTER 条件聚合
SELECT dt,
       COUNT(*) AS total,
       COUNT(*) FILTER (WHERE status = 'paid') AS paid,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders GROUP BY dt;

数组函数

-- ARRAY — 构建数组
SELECT ARRAY[1, 2, 3], ARRAY['a', 'b', 'c'];

-- ANY / ALL — 元素检查
SELECT * FROM articles WHERE '数据库' = ANY(tags);

-- UNNEST — 展开数组
SELECT UNNEST(tags) AS tag, COUNT(*) AS freq
FROM articles GROUP BY tag ORDER BY freq DESC;

-- ARRAY_APPEND / ARRAY_PREPEND / ARRAY_REMOVE / ARRAY_CAT
SELECT ARRAY_APPEND(ARRAY[1,2], 3);      -- {1,2,3}
SELECT ARRAY_PREPEND(0, ARRAY[1,2]);      -- {0,1,2}
SELECT ARRAY_REMOVE(ARRAY[1,2,3], 2);     -- {1,3}
SELECT ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}

-- 数组信息
SELECT ARRAY_NDIMS(ARRAY[[1,2],[3,4]]), ARRAY_LENGTH(ARRAY[1,2,3], 1);

-- ARRAY_POSITION / ARRAY_POSITIONS
SELECT ARRAY_POSITION(ARRAY['a','b','c','b'], 'b');    -- 2
SELECT ARRAY_POSITIONS(ARRAY['a','b','c','b'], 'b');    -- {2,4}

-- STRING_TO_ARRAY / ARRAY_TO_STRING
SELECT STRING_TO_ARRAY('a,b,c', ','), ARRAY_TO_STRING(ARRAY['a','b','c'], '|');

-- 数组切片
SELECT tags[1:3] FROM articles;

-- @> / <@ — 包含, && — 重叠
SELECT * FROM articles WHERE tags @> ARRAY['SQL', '高级'];
SELECT * FROM articles WHERE tags && ARRAY['数据库', 'JSON'];

-- 数组 GIN 索引
CREATE INDEX idx_articles_tags_gin ON articles USING GIN (tags);

JSONB 函数与操作详解

访问操作符

-- -> 返回 JSONB, ->> 返回 TEXT
SELECT
    config -> 'theme' AS theme_jsonb,              -- "dark"
    config ->> 'theme' AS theme_text,              -- dark
    config -> 'notifications' -> 'email' AS email_jsonb,
    config #>> '{preferences, language}' AS lang

FROM user_configs WHERE user_id = 1;

-- #> / #>> 路径访问
SELECT config #> '{preferences, timezone}' AS tz,
       config #>> '{notifications, push}' AS push
FROM user_configs WHERE user_id = 1;

包含与存在操作

-- @> — 包含（业务场景：查询包含特定配置的用户）
SELECT user_id, config FROM user_configs
WHERE config @> '{"notifications": {"email": true}}';

-- ? — 是否存在键
SELECT user_id FROM user_configs WHERE config ? 'trust_score';

-- ?| — 存在任意键
SELECT user_id FROM user_configs WHERE config ?| ARRAY['trust_score', 'vip_level'];

-- ?& — 包含所有键
SELECT user_id FROM user_configs WHERE config ?& ARRAY['theme', 'notifications'];

修改函数

-- || — JSONB 合并
UPDATE user_configs SET config = config || '{"vip_level": 2}' WHERE user_id = 1;

-- JSONB_SET — 设置路径值
UPDATE user_configs SET config = JSONB_SET(config, '{notifications, email}', 'false'::JSONB)
WHERE user_id = 1;

-- JSONB_INSERT — 插入不覆盖 (PG 9.6+)
SELECT JSONB_INSERT('{"a":1,"b":2}'::JSONB, '{c}', '3'::JSONB);

-- JSONB_STRIP_NULLS — 移除 null
SELECT JSONB_STRIP_NULLS('{"a":1,"b":null}'::JSONB);  -- {"a": 1}

构建函数

-- JSONB_BUILD_OBJECT / JSONB_BUILD_ARRAY
SELECT JSONB_BUILD_OBJECT(
    'id', 101, 'name', 'Alice',
    'roles', JSONB_BUILD_ARRAY('admin', 'editor'),
    'meta', JSONB_BUILD_OBJECT('last_login', NOW())
);

展开函数

-- JSONB_EACH — 展开为 (key, value) 行集
SELECT * FROM JSONB_EACH((SELECT config FROM user_configs WHERE user_id = 1));

-- JSONB_EACH_TEXT — 展开为 (key, text_value)
SELECT * FROM JSONB_EACH_TEXT((SELECT config FROM user_configs WHERE user_id = 1));

-- JSONB_OBJECT_KEYS — 仅返回键
SELECT * FROM JSONB_OBJECT_KEYS((SELECT config FROM user_configs WHERE user_id = 1));

-- JSONB_EXTRACT_PATH — 提取路径
SELECT JSONB_EXTRACT_PATH(config, 'preferences', 'language') FROM user_configs;

类型检查与格式化

-- JSONB_TYPEOF (PG 14+)
SELECT JSONB_TYPEOF(config -> 'theme'),   -- string
       JSONB_TYPEOF(config -> 'tags'),     -- array
       JSONB_TYPEOF(config -> 'trust_score') -- number
FROM user_configs WHERE user_id = 1;

-- JSONB_PRETTY (PG 14+)
SELECT JSONB_PRETTY(config) FROM user_configs WHERE user_id = 1;

GIN 索引

-- 标准 GIN
CREATE INDEX idx_config_gin ON user_configs USING GIN (config);

-- jsonb_path_ops（更小更快，不支持 ? 操作符）
CREATE INDEX idx_config_path ON user_configs USING GIN (config jsonb_path_ops);

完整示例表

CREATE TABLE user_configs (
    id      BIGSERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    config  JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO user_configs (user_id, config) VALUES
(1, '{"theme":"dark","notifications":{"email":true},"preferences":{"language":"zh-CN","timezone":"Asia/Shanghai"},"tags":["developer","premium"],"trust_score":4.5}'),
(2, '{"theme":"light","notifications":{"email":false},"preferences":{"language":"en","timezone":"America/New_York"},"tags":["basic"]}');

全文搜索详解

基础概念

PostgreSQL 全文搜索基于 tsvector (文本搜索向量) 和 tsquery (文本搜索查询)，配合 GIN 索引实现高效搜索。

建表与索引

CREATE TABLE documents (
    id          SERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    body        TEXT NOT NULL,
    body_tsv    TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);

CREATE INDEX idx_documents_body_tsv ON documents USING GIN (body_tsv);

INSERT INTO documents (title, body) VALUES
    ('PostgreSQL Full Text Search',
     'PostgreSQL provides full-text search capabilities out of the box.'),
    ('Indexing Strategies',
     'Proper indexing is crucial for database performance. GIN indexes are optimized for full-text search.'),
    ('Database Performance Tuning',
     'Performance tuning involves many aspects including query optimization, indexing strategy, and hardware configuration.');

核心函数

-- to_tsvector — 文本转搜索向量（停用词被移除, 动词被词根化）
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- to_tsquery — 文本转搜索查询
SELECT to_tsquery('english', 'search & indexing');
-- 'search' & 'index'

-- plainto_tsquery — 简单转换（空格分隔的单词自动加 &）
SELECT plainto_tsquery('english', 'full text search');
-- 'full' & 'text' & 'search'

匹配查询

-- @@ — 全文搜索匹配操作符
SELECT id, title FROM documents
WHERE body_tsv @@ to_tsquery('english', 'search & index');

-- plainto_tsquery 简化写法
SELECT id, title FROM documents
WHERE body_tsv @@ plainto_tsquery('english', 'full text search');

-- 直接对原始列搜索（不依赖 tsvector 列）
SELECT id, title FROM documents
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'search');

排序与高亮

-- ts_rank / ts_rank_cd — 相关性排序
SELECT id, title, ts_rank(body_tsv, query) AS rank
FROM documents, to_tsquery('english', 'search & indexing') AS query
WHERE body_tsv @@ query ORDER BY rank DESC;

-- ts_headline — 高亮摘要
SELECT id, ts_headline('english', body, query,
    'StartSel=<mark>, StopSel=</mark>, MaxWords=30, MinWords=10') AS highlighted
FROM documents, plainto_tsquery('english', 'full text search') AS query
WHERE body_tsv @@ query;

短语搜索

-- <-> : 相邻单词
SELECT * FROM documents
WHERE body_tsv @@ to_tsquery('english', 'full <-> text <-> search');

-- <N> : 相隔最多 N 个词
SELECT * FROM documents
WHERE body_tsv @@ to_tsquery('english', 'performance <2> tuning');
-- 匹配 "performance tuning" 或 "performance and tuning"

中文全文搜索

-- 需要 zhparser 或 jieba 扩展
-- CREATE EXTENSION zhparser;
-- CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
-- ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l WITH simple;
-- SELECT to_tsvector('chinese', '数据库性能优化技巧');

多语言与自定义配置

-- simple: 不做词干分析
SELECT to_tsvector('simple', 'running runs ran');  -- 'running':1 'runs':2 'ran':3

-- english: 词干分析
SELECT to_tsvector('english', 'running runs ran');  -- 'run':1,2,3

-- 创建自定义字典
CREATE TEXT SEARCH DICTIONARY my_dict (TEMPLATE = pg_catalog.simple, ...);

索引类型与视图详解

6种索引类型

索引创建

-- B-Tree
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- 复合索引列顺序: 等值在前，范围在后，选择性高的在前
-- ✅ WHERE user_id = 1 AND status = 'paid'  → 索引 (user_id, status) 最优
-- ❌ WHERE status = 'paid' AND created_at > '2024-01-01' → 索引 (created_at, status) 更好

-- 部分索引（仅索引活跃用户，更小更快）
CREATE INDEX idx_users_active ON users (email) WHERE is_active = TRUE;

-- 覆盖索引 / INCLUDE (PG 11+, 避免回表)
CREATE INDEX idx_orders_covering ON orders (user_id, status) INCLUDE (total_amount, created_at);

-- Hash 索引
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- GIN 索引
CREATE INDEX idx_articles_body_tsv ON articles USING GIN (body_tsv);
CREATE INDEX idx_config_gin ON user_configs USING GIN (config);
CREATE INDEX idx_articles_tags_gin ON articles USING GIN (tags);
CREATE INDEX idx_config_path ON user_configs USING GIN (config jsonb_path_ops);

-- GiST 索引
CREATE INDEX idx_room_bookings_period ON room_bookings USING GIST (period);

-- BRIN 索引（大表时序数据，极大节省空间）
CREATE INDEX idx_orders_brin ON orders USING BRIN (created_at) WITH (pages_per_range = 32);

-- CONCURRENTLY — 在线建索引（不阻塞写）
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

索引维护

-- DROP INDEX CONCURRENTLY — 在线删除
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_old;

-- REINDEX — 重建索引（索引膨胀时）
REINDEX INDEX idx_orders_user_id;
REINDEX TABLE orders;
REINDEX DATABASE mydb;

-- REINDEX CONCURRENTLY — 在线重建 (PG 12+)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

视图 (View)

-- 普通视图 — 虚拟表
CREATE VIEW user_order_summary AS
SELECT u.id, u.username, COUNT(o.id) AS total_orders,
       COALESCE(SUM(o.total_amount), 0) AS total_spent,
       MAX(o.created_at) AS last_order_date
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- WITH CHECK OPTION — 确保更新满足视图条件
CREATE VIEW paid_orders AS SELECT * FROM orders WHERE status = 'paid'
WITH CHECK OPTION;

-- 物化视图 — 物理存储的快照
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT DATE_TRUNC('month', o.created_at) AS month, p.category_id,
       COUNT(DISTINCT o.id) AS order_count, SUM(oi.quantity * oi.price) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY month, p.category_id WITH DATA;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW mv_monthly_sales;

-- 并发刷新（需唯一索引, PG 9.4+）
CREATE UNIQUE INDEX idx_mv_monthly_sales_unique ON mv_monthly_sales (month, category_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

分区表与 FDW 详解

分区表 (PG 10+)

RANGE 分区

CREATE TABLE orders_partitioned (
    id BIGSERIAL, user_id INTEGER NOT NULL, total_amount NUMERIC(12,2),
    status TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 默认分区
CREATE TABLE orders_default PARTITION OF orders_partitioned DEFAULT;

LIST 分区

CREATE TABLE customers_partitioned (
    id BIGSERIAL, name TEXT NOT NULL, region TEXT NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE customers_asia PARTITION OF customers_partitioned
    FOR VALUES IN ('CN', 'JP', 'KR', 'SG');
CREATE TABLE customers_americas PARTITION OF customers_partitioned
    FOR VALUES IN ('US', 'CA', 'BR', 'MX');

HASH 分区

CREATE TABLE logs_partitioned (
    id BIGSERIAL, level TEXT, message TEXT, logged_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY HASH (id);

CREATE TABLE logs_p0 PARTITION OF logs_partitioned FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_p1 PARTITION OF logs_partitioned FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE logs_p2 PARTITION OF logs_partitioned FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE logs_p3 PARTITION OF logs_partitioned FOR VALUES WITH (MODULUS 4, REMAINDER 3);

子分区 (PG 11+)

CREATE TABLE sales (id BIGSERIAL, sale_date DATE NOT NULL, region TEXT NOT NULL, amount NUMERIC(12,2))
PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01')
    PARTITION BY LIST (region);

分区维护

-- 添加新分区
CREATE TABLE orders_2024_04 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- 分离分区（变成独立表）
ALTER TABLE orders_partitioned DETACH PARTITION orders_2024_01;

-- 附加分区
ALTER TABLE orders_partitioned ATTACH PARTITION orders_2024_01
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 分区裁剪自动生效
EXPLAIN SELECT * FROM orders_partitioned
WHERE created_at >= '2024-02-15' AND created_at < '2024-03-01';
-- 只在 orders_2024_02 分区上扫描

-- 分区表索引自动应用到所有分区
CREATE INDEX ON orders_partitioned (user_id);
CREATE INDEX ON orders_partitioned (created_at DESC);

分区最佳实践

• 每个分区 1-10GB 为宜
• 时间分区常用：日、周、月、季
• 定期分离旧分区用于归档
• 分区数不宜超过 1000
• 分区键直接影响分区裁剪能力

FDW (Foreign Data Wrapper)

postgres_fdw — 跨 PG 数据库

CREATE EXTENSION postgres_fdw;

CREATE SERVER remote_prod FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.100', port '5432', dbname 'prod_db');

CREATE USER MAPPING FOR current_user SERVER remote_prod
OPTIONS (user 'readonly_user', password 'secret');

CREATE FOREIGN TABLE remote_orders (
    id BIGINT, user_id INTEGER, total_amount NUMERIC(12,2), status TEXT, created_at TIMESTAMPTZ
) SERVER remote_prod OPTIONS (schema_name 'public', table_name 'orders');

-- 查询远程表
SELECT * FROM remote_orders WHERE created_at > NOW() - INTERVAL '1 hour';

-- 批量导入外部表结构
IMPORT FOREIGN SCHEMA public FROM SERVER remote_prod INTO local_schema
LIMIT TO (users, orders, products);

file_fdw — 读取 CSV

CREATE EXTENSION file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE csv_orders (
    id BIGINT, user_id INTEGER, amount NUMERIC(10,2), order_date DATE
) SERVER file_server OPTIONS (filename '/data/orders.csv', format 'csv', header 'true');

SELECT SUM(amount) FROM csv_orders WHERE order_date >= '2024-01-01';

FDW 性能考量

• 适合小数据量或低频跨库查询
• 大数据量传输建议用逻辑复制或 ETL
• WHERE 条件尽量 push down 到远程

复制/备份/权限详解

流复制 (Streaming Replication)

主库配置

# postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on

-- pg_hba.conf
-- host replication replicator 192.168.1.0/24 md5

CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';

从库搭建

# 清空从库数据目录
rm -rf /var/lib/postgresql/data/*

# 从主库拉取基础备份
pg_basebackup -h 192.168.1.100 -U replicator \
    -D /var/lib/postgresql/data -P -v --wal-method=stream

# PG 12+: 创建 standby.signal
touch /var/lib/postgresql/data/standby.signal

# 配置主库连接
echo "primary_conninfo = 'host=192.168.1.100 port=5432 user=replicator password=strong_password'" \
    >> /var/lib/postgresql/data/postgresql.auto.conf

# 启动从库
systemctl start postgresql

# 检查复制状态（主库）
SELECT * FROM pg_stat_replication;
# 从库
SELECT * FROM pg_stat_wal_receiver;

同步 vs 异步

-- 同步复制：主库等待从库确认
-- 配置: synchronous_standby_names = 'FIRST 1 (slave1, slave2)'
-- 数据零丢失，但写入延迟增加

-- 检查同步状态
SELECT application_name, state, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
-- sync: 同步, async: 异步, potential: 候选同步

逻辑复制 (PG 10+)

-- 发布端
CREATE PUBLICATION my_pub FOR ALL TABLES;
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;
CREATE PUBLICATION paid_orders_pub FOR TABLE orders WHERE (status = 'paid');  -- PG 15+

-- 订阅端
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=192.168.1.100 port=5432 dbname=mydb user=replicator password=strong_password'
PUBLICATION my_pub;

-- 管理
ALTER SUBSCRIPTION my_sub ENABLE;
ALTER SUBSCRIPTION my_sub DISABLE;
ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION;
DROP SUBSCRIPTION my_sub;

-- 监控
SELECT * FROM pg_stat_subscription;

备份与恢复

pg_dump — 逻辑备份

# SQL 格式
pg_dump -h localhost -U postgres -d mydb > mydb.sql

# 自定义格式（推荐）
pg_dump -h localhost -U postgres -d mydb -Fc -f mydb.dump

# 并行导出目录格式
pg_dump -h localhost -U postgres -d mydb -Fd -j 4 -f /backup/mydb/

# 只导出结构
pg_dump -h localhost -U postgres -d mydb -s -f mydb_schema.sql

# 指定表
pg_dump -h localhost -U postgres -d mydb -t orders -t users -f mydb_tables.sql

# 全局对象（角色、表空间）
pg_dumpall -h localhost -U postgres -g -f global_objects.sql

pg_restore — 恢复

pg_restore -h localhost -U postgres -d mydb /backup/mydb.dump
pg_restore -h localhost -U postgres -d mydb -j 4 /backup/mydb.dump  # 并行
pg_restore -h localhost -U postgres -d mydb -t users /backup/mydb.dump  # 指定表

# SQL 文件恢复
psql -h localhost -U postgres -d mydb < mydb.sql

WAL 归档与 PITR

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /archive/%f'
wal_keep_size = 1GB

# 创建基础备份
pg_basebackup -h localhost -U postgres -D /backup/base -P -v --wal-method=stream

# PITR 恢复步骤:
# 1. 停止 PG
# 2. 用基础备份恢复数据目录
# 3. 创建 recovery.signal (PG 12+)
# 4. 配置 restore_command 和 recovery_target_time
# 5. 启动 PG

pg_basebackup — 物理备份

# 基础用法
pg_basebackup -h localhost -U replicator -D /backup/pg_base -P -v --wal-method=stream

# 压缩 tar 格式
pg_basebackup -h localhost -U replicator -D /backup/pg_base -Ft -z -P -v

# 验证备份 (PG 13+)
pg_verifybackup /backup/pg_base

高可用工具

# Patroni (基于 etcd/consul/ZK)
# 自动故障转移 + 自动恢复
patroni /etc/patroni/patroni.yml

# repmgr
repmgr -f /etc/repmgr.conf primary register
repmgr -f /etc/repmgr.conf standby clone
repmgr -f /etc/repmgr.conf standby register
repmgr -f /etc/repmgr.conf standby switchover

# PgBouncer — 连接池
# Pgpool-II — 连接池 + 读写分离 + 负载均衡

权限管理

-- ROLE 管理
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';
CREATE ROLE readonly_role;
CREATE ROLE readwrite_role;

-- 角色层级
GRANT readonly_role TO app_user;

-- Schema 权限
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_role;

-- 默认权限（未来新建表自动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;

-- 序列、函数
GRANT USAGE, SELECT ON ALL SEQUENCES TO readwrite_role;
GRANT EXECUTE ON ALL FUNCTIONS TO readwrite_role;

-- RLS 行级安全（多租户隔离）
CREATE TABLE tenant_orders (id BIGSERIAL PRIMARY KEY, tenant_id INTEGER NOT NULL, ...);
ALTER TABLE tenant_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tenant_orders
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

ALTER TABLE tenant_orders FORCE ROW LEVEL SECURITY;
SET app.tenant_id = '1001';  -- 应用层设置

扩展

-- PostGIS: CREATE EXTENSION postgis;
-- pgvector: CREATE EXTENSION vector;
-- pg_stat_statements: CREATE EXTENSION pg_stat_statements;
-- uuid-ossp: CREATE EXTENSION "uuid-ossp";
-- pgcrypto: CREATE EXTENSION pgcrypto;
-- citext: CREATE EXTENSION citext;
-- pg_trgm: CREATE EXTENSION pg_trgm;
-- unaccent: CREATE EXTENSION unaccent;