605 PostgreSQL 高级特性与优化¶

# Ubuntu 安装 PostgreSQL 17
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'  # 添加官方源
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -  # 导入GPG密钥
sudo apt update                        # 更新包索引
sudo apt install postgresql-17         # 安装 PG17

# Docker 安装
docker run -d \
  --name postgres \                    # 容器名
  -p 5432:5432 \                       # 映射端口
  -e POSTGRES_PASSWORD=mypassword \    # 设置密码
  -e POSTGRES_DB=mydb \                # 创建默认数据库
  -v pgdata:/var/lib/postgresql/data \ # 数据持久化
  postgres:17                          # 使用 PG17 镜像

# 验证
psql -U postgres -c "SELECT version();"  # 查看版本

-- postgresql.conf 核心调优参数
-- 白话：根据服务器配置调整，假设 16GB 内存的服务器

-- 内存设置
shared_buffers = '4GB'           -- 共享缓冲区（建议总内存的 25%）
effective_cache_size = '12GB'    -- 有效缓存（建议总内存的 75%）
work_mem = '32MB'                -- 单个查询排序/哈希操作的内存
maintenance_work_mem = '512MB'   -- VACUUM/CREATE INDEX 的内存

-- 并行查询
max_parallel_workers_per_gather = 4  -- 每次查询最多用 4 个并行进程
max_parallel_workers = 8             -- 总并行进程数

-- 连接
max_connections = 200            -- 最大连接数（建议 CPU核心×4）

-- WAL 写入优化（PG17增强）
wal_compression = 'zstd'         -- WAL 使用 zstd 压缩

-- 白话：窗口函数能在不分组的情况下做聚合计算，分析师的最爱
-- 例：计算每个部门的薪资排名
SELECT 
    name,                              -- 员工姓名
    department,                         -- 部门
    salary,                             -- 薪资
    RANK() OVER (                       -- 排名函数
        PARTITION BY department         -- 按部门分组
        ORDER BY salary DESC            -- 按薪资降序
    ) as salary_rank,                   -- 部门内薪资排名
    AVG(salary) OVER (                  -- 平均值
        PARTITION BY department         -- 按部门分组
    ) as dept_avg_salary,               -- 部门平均薪资
    salary - AVG(salary) OVER (         -- 与部门平均薪资的差值
        PARTITION BY department
    ) as diff_from_avg                  -- 高于/低于平均多少
FROM employees;

-- 白话：CTE 就是给子查询起名字，代码更清晰，PG17还能传递统计信息提升性能
-- 例：递归查询组织架构树
WITH RECURSIVE org_tree AS (
    -- 基础条件：找到顶级管理者
    SELECT id, name, manager_id, 1 AS level   -- level 表示层级
    FROM employees
    WHERE manager_id IS NULL                   -- 没有上级 = 顶级

    UNION ALL

    -- 递归条件：找到每个人的下属
    SELECT e.id, e.name, e.manager_id, t.level + 1  -- 层级+1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id    -- 通过 manager_id 关联
)
SELECT * FROM org_tree ORDER BY level, name;  -- 按层级排序输出

-- PG17 新增 JSON_TABLE，可以把 JSON 数据像表一样查询
-- 白话：API 返回的 JSON 数据直接在数据库里拆解分析

-- 创建包含 JSON 的表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL               -- JSONB 类型（二进制JSON，查询更快）
);

-- 插入 JSON 数据
INSERT INTO orders (data) VALUES
('{"customer": "张三", "items": [{"name": "书", "price": 30}, {"name": "笔", "price": 5}]}');

-- 查询 JSON 字段
SELECT 
    data->>'customer' AS customer,     -- ->> 提取文本值
    data->'items'->0->>'name' AS first_item,  -- 提取数组第一个元素
    jsonb_array_length(data->'items') AS item_count  -- 数组长度
FROM orders;

-- PG17: JSON_TABLE 函数（把 JSON 数组展开成行）
SELECT * FROM orders,
JSON_TABLE(
    data->'items',                     -- 要展开的 JSON 数组
    '$[*]'                             -- 遍历每个元素
    COLUMNS (
        item_name TEXT PATH '$.name',  -- 提取 name 字段
        item_price NUMERIC PATH '$.price'  -- 提取 price 字段
    )
) AS items;

-- 白话：不用 Elasticsearch 也能在 PG 里做全文搜索
-- 创建全文搜索索引
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector TSVECTOR             -- 全文搜索向量列
);

-- 自动更新搜索向量
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('chinese', COALESCE(NEW.title, '')), 'A') ||  -- 标题权重高
        setweight(to_tsvector('chinese', COALESCE(NEW.body, '')), 'B');     -- 正文权重低
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建 GIN 索引加速搜索
CREATE INDEX idx_search ON articles USING GIN(search_vector);

-- 搜索
SELECT title, ts_rank(search_vector, query) AS rank  -- 按相关度排名
FROM articles, to_tsquery('chinese', '机器 & 学习') AS query  -- 搜索"机器"且"学习"
WHERE search_vector @@ query           -- @@ 是全文搜索匹配操作符
ORDER BY rank DESC                      -- 最相关的排前面
LIMIT 10;

-- B-Tree：默认索引，适合等值和范围查询（最常用）
CREATE INDEX idx_email ON users(email);  -- 精确查找邮箱

-- Hash：只支持等值查询，比 B-Tree 更快
CREATE INDEX idx_status ON orders USING HASH(status);  -- 状态查询

-- GIN：适合全文搜索、JSONB、数组
CREATE INDEX idx_tags ON posts USING GIN(tags);  -- 数组字段

-- BRIN：适合自然有序的大表（时间序列）
CREATE INDEX idx_created ON events USING BRIN(created_at);  -- 时间字段
-- BRIN 索引非常小，适合按时间递增的日志表

-- 部分索引（只索引满足条件的行，节省空间）
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true;                -- 只索引活跃用户

-- EXPLAIN ANALYZE 是性能优化的核心工具
-- 白话：让 PG 告诉你它打算怎么执行查询，以及实际花了多长时间
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)  -- PG17 还能显示 I/O 时间
SELECT * FROM orders 
WHERE customer_id = 1001 
  AND created_at > '2024-01-01';

-- 看输出关键指标：
-- Seq Scan = 全表扫描（慢！需要加索引）
-- Index Scan = 用了索引（快！）
-- Rows Removed by Filter = 过滤掉的行数（太多说明索引不够精确）
-- Execution Time = 实际执行时间

-- 白话：PG 删除/更新数据时不会立即回收空间，需要 VACUUM 清理
-- PG17 的 VACUUM 内存消耗降低了 20 倍

VACUUM ANALYZE orders;            -- 清理 + 更新统计信息
VACUUM FULL orders;               -- 深度清理（会锁表，慎用！）

-- 查看需要 VACUUM 的表
SELECT relname, n_dead_tup, last_autovacuum  -- 死元组数量和上次清理时间
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000          -- 超过1万条死元组
ORDER BY n_dead_tup DESC;

# 白话：PG 每个连接都要 fork 进程，连接多了很耗资源
# pgBouncer 复用连接，100个应用连接只需要 20 个PG连接
sudo apt install pgbouncer        # 安装连接池

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432                # pgBouncer 端口
pool_mode = transaction           # 事务级别池化（推荐）
max_client_conn = 1000            # 最大客户端连接
default_pool_size = 20            # 每个数据库的连接池大小

FATAL: too many connections for role "myuser"

ERROR: deadlock detected

# 查看数据库大小
SELECT pg_size_pretty(pg_database_size('mydb'));  -- 数据库总大小
SELECT pg_size_pretty(pg_total_relation_size('orders'));  -- 单表大小（含索引）
# 用 VACUUM FULL 回收空间