postgresql-database-engineering

PostgreSQL 数据库工程

一套全面的 PostgreSQL 数据库工程技能，涵盖从查询优化和索引策略到高可用性、复制和生产数据库管理的方方面面。此技能可帮助你设计、优化和维护可大规模运行的高性能 PostgreSQL 数据库。

何时使用此技能

在以下情形中使用此技能：

• 为高性能应用设计数据库架构
• 优化慢查询并提升数据库性能
• 针对复杂查询模式实施索引策略
• 为大型表（1 亿行以上）设置分区
• 配置流复制和高可用性
• 针对生产工作负载调整 PostgreSQL 配置
• 实施备份和恢复流程
• 调试性能问题和查询瓶颈
• 使用 pgBouncer 或 PgPool 设置连接池
• 监控数据库健康状况和性能指标
• 规划数据库迁移和架构变更
• 实施数据库安全措施和访问控制
• 横向或纵向扩展 PostgreSQL 数据库
• 管理 VACUUM 操作和数据库维护
• 设置逻辑复制以分发数据

核心概念

PostgreSQL 架构

PostgreSQL 使用基于进程的架构，包含多个关键组件：

• Postmaster 进程：管理连接的主服务器进程
• 后端进程：每个客户端连接对应一个进程，负责处理查询
• 共享内存：共享缓冲区、WAL 缓冲区、锁表
• 后台工作进程：自动清理、检查点进程、WAL 写入进程、统计信息收集器
• 预写日志（WAL）：用于持久性和复制的事务日志
• 存储层：用于存储大型值的 TOAST、用于空闲空间的 FSM、用于可见性的 VM

MVCC（多版本并发控制）

PostgreSQL 的基础并发机制：

• 快照：每个事务都能看到一致的数据快照
• 元组版本：多个行版本可并存，以支持并发访问
• 事务 ID：xmin（创建事务）、xmax（删除事务）
• 可见性规则：决定事务可见哪些行版本
• VACUUM：回收死元组占用的空间，并防止事务 ID 回卷
• FREEZE：将旧行标记为对所有事务可见

关键影响：

• 无读锁——读操作不会阻塞写操作
• 写操作不会阻塞读操作
• 更新会创建新的行版本
• 定期执行 VACUUM 至关重要
• 死元组会一直累积，直到执行清理

事务隔离级别

PostgreSQL 支持四种隔离级别：

1. 读未提交：在 PostgreSQL 中按读已提交处理
2. 读已提交（默认）：读取语句开始时已提交的数据
3. 可重复读：读取事务开始时的数据快照
4. 可串行化：通过 SSI 实现真正的可串行化隔离

如何选择隔离级别：

• 读已提交：适用于大多数应用，性能最佳
• 可重复读：适用于需要数据一致性的报告和分析
• 可串行化：适用于金融交易和对一致性要求严格的场景

索引类型

PostgreSQL 提供多种索引类型，以适应不同使用场景：

1. B-Tree（默认）

• 适用场景：相等查询、范围查询、排序
• 支持：<、<=、=、>=、>、BETWEEN、IN、IS NULL
• 最适合：大多数通用索引场景
• 示例：主键、外键、时间戳

2. Hash

• 适用场景：仅用于相等比较
• 支持：= 运算符
• 最适合：通过相等条件查询的大型表
• 限制：PG 10 之前不会写入 WAL 日志，不支持范围查询

3. GiST（通用搜索树）

• 适用场景：几何数据、全文搜索、自定义类型
• 支持：重叠、包含、最近邻
• 最适合：空间数据、范围、全文搜索
• 示例：PostGIS 几何对象、tsvector、范围类型

4. GIN（通用倒排索引）

• 适用于：多值列（数组、JSONB、全文）
• 支持：包含、存在运算符
• 最适合：JSONB 查询、数组操作、全文搜索
• 权衡：更新较慢，查询较快

5. BRIN（块范围索引）

• 适用于：具有自然排序的超大表
• 支持：对已排序数据进行范围查询
• 最适合：时序数据、仅追加表
• 优势：索引体积极小，可扩展至数十亿行

6. SP-GiST（空间分区 GiST）

• 适用于：非平衡数据结构
• 支持：点、范围、IP 地址
• 最适合：四叉树、k-d 树、基数树

查询规划与优化

PostgreSQL 的查询规划器会确定执行策略：

规划器组件：

• 统计信息：用于估算基数的表和列统计信息
• 成本模型：CPU、I/O 和内存成本估算
• 计划类型：顺序扫描、索引扫描、位图扫描、连接
• 连接方法：嵌套循环、哈希连接、归并连接
• 优化：查询重写、谓词下推、连接重排

关键统计信息：

• n_distinct：不同值的数量（用于估算选择率）
• correlation：物理行顺序相关性
• most_common_vals：用于倾斜分布的最常见值（MCV）列表
• histogram_bounds：值分布直方图

理解 EXPLAIN：

• 成本：启动成本 .. 总成本（任意单位）
• 行数：估算的行数
• 宽度：平均行大小（字节）
• 实际时间：实际执行时间（使用 ANALYZE 时）
• 循环次数：节点执行的次数

分区策略

用于管理大型数据集的表分区：

范围分区

• 适用于：时序数据、顺序值
• 示例：按日期范围分区（每天、每月、每年）
• 优势：便于管理数据生命周期，查询更快

列表分区

• 适用于：离散的分类值
• 示例：按国家、地区、状态分区
• 优势：实现逻辑数据隔离和分区裁剪

哈希分区

• 适用于：均匀分布数据
• 示例：按 hash(user_id) 分区
• 优势：分区大小均衡，支持并行查询

分区裁剪：

• 规划器会排除无关分区
• 大幅缩小查询范围
• 对分区性能至关重要

分区级操作：

• 分区级连接：直接连接匹配的分区
• 分区级聚合：在分区内聚合
• 并行处理分区

复制与高可用性

PostgreSQL 的复制选项：

流复制（物理复制）

• 类型：通过二进制 WAL 将数据流式传输到备用服务器
• 模式：异步、同步、基于仲裁
• 适用于：高可用性、扩展读取能力
• 故障转移：借助 Patroni、repmgr 等工具自动进行

同步与异步：

• 同步：零数据丢失，延迟较高
• 异步：延迟较低，可能丢失数据
• 仲裁：平衡安全性与性能

逻辑复制

• 类型：行级变更流
• 适用于：选择性复制、升级、多主复制
• 优势：可复制特定表，支持跨版本
• 限制：不复制 DDL，存在开销

级联复制

• 备用服务器从其他备用服务器进行复制
• 降低主服务器的负载
• 支持地理分布

连接池

高效管理数据库连接：

pgBouncer

• 类型：轻量级连接池工具
• 模式：会话、事务、语句池化
• 适用于：连接数较多的应用
• 优势：减少连接开销，限制资源使用

池化模式：

• 会话：客户端在整个会话期间保持连接
• 事务：每个事务使用一个连接
• 语句：每条语句使用一个连接（较少使用）

PgPool-II

• 类型：功能丰富的中间件
• 功能：连接池、负载均衡、查询缓存
• 适用场景：读写分离、连接管理
• 优势：高级路由、内存缓存

VACUUM 和维护

关键维护操作：

VACUUM

• 用途：回收死元组占用的空间、更新统计信息
• 类型：常规 VACUUM、VACUUM FULL
• 时机：大量更新/删除后，或通过 autovacuum 定期执行
• 影响：常规 VACUUM 不会阻塞

ANALYZE

• 用途：更新规划器统计信息
• 时机：数据变更、架构修改后
• 影响：影响极小，大多数表上执行速度很快

REINDEX

• 用途：重建索引、修复膨胀
• 时机：索引损坏、严重膨胀时
• 影响：会锁定表；请使用 REINDEX CONCURRENTLY（PG 12+）

Autovacuum

• 用途：自动执行 VACUUM 和 ANALYZE
• 配置：基于阈值触发
• 调优：平衡资源使用与响应速度
• 监控：跟踪 autovacuum 运行情况，防止事务 ID 回卷

性能调优

关键配置参数：

内存设置

shared_buffers: 25% of RAM (start point)
effective_cache_size: 50-75% of RAM
work_mem: Per-operation memory (sort, hash)
maintenance_work_mem: VACUUM, CREATE INDEX memory

检查点和 WAL

checkpoint_timeout: How often to checkpoint
max_wal_size: WAL size before checkpoint
checkpoint_completion_target: Spread checkpoint I/O
wal_buffers: WAL write buffer size

查询规划器

random_page_cost: Relative cost of random I/O
effective_io_concurrency: Concurrent I/O operations
default_statistics_target: Histogram detail level

连接设置

max_connections: Maximum client connections
connection_limit: Per-database/user limits

索引策略

选择合适的索引

决策矩阵：

查询模式	索引类型	原因
WHERE id = 5	B-tree	等值查找
WHERE created_at > '2024-01-01'	B-tree	范围查询
ORDER BY name	B-tree	支持排序
WHERE tags @> ARRAY['sql']	GIN	数组包含查询
WHERE data->>'status' = 'active'	GIN (jsonb_path_ops)	JSONB 查询
WHERE to_tsvector(content) @@ query	GIN	全文搜索
WHERE location <-> point(0,0)	GiST	最近邻查询
WHERE timestamp BETWEEN ... (large table)	BRIN	顺序时间序列查询
WHERE ip_address << '192.168.0.0/16'	GiST or SP-GiST	IP 范围查询

复合索引

用于复杂查询的多列索引：

列排序规则：

1. 等值列放在前面
2. 排序/范围列放在最后
3. 高选择性列放在前面
4. 与查询模式完全匹配

示例：

-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY created_at
-- Optimal index: (status, created_at)
CREATE INDEX idx_users_status_created ON users(status, created_at);

部分索引

为行的子集创建索引：

优势：

• 索引更小
• 更新未编入索引的行时速度更快
• 针对性地优化查询

使用场景：

• 仅为活动记录创建索引：WHERE deleted_at IS NULL
• 为近期数据创建索引：WHERE created_at > NOW() - INTERVAL '90 days'
• 为特定状态创建索引：WHERE status IN ('pending', 'processing')

表达式索引

为计算值创建索引：

示例：

-- Case-insensitive search
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Date truncation
CREATE INDEX idx_events_date ON events(DATE(created_at));

-- JSONB field
CREATE INDEX idx_data_status ON documents((data->>'status'));

覆盖索引（INCLUDE）

包含非键列，以支持仅索引扫描：

CREATE INDEX idx_users_email_include
ON users(email)
INCLUDE (first_name, last_name, created_at);

**优势：**查询完全通过索引满足，无需查找表

索引维护

监控索引使用情况：

-- Unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

检测膨胀：

-- Index bloat estimation
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

查询优化

使用 EXPLAIN ANALYZE

了解查询执行情况：

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- EXPLAIN ANALYZE (actually runs query)
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at > '2024-01-01';

-- Detailed output
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

关键指标：

• 规划时间：生成执行计划所需的时间
• 执行时间：查询的实际运行时间
• Shared Hit 与 Read：缓冲区缓存命中次数与磁盘读取次数
• 行数：预估行数与实际行数
• Filter 与 Index Cond：扫描后的过滤与索引使用情况

常见查询反模式

1. N+1 查询

**问题：**循环中每行执行一次查询 **解决方案：**使用 JOIN 或批量查询

2. SELECT *

**问题：**获取不必要的列 **解决方案：**仅选择所需列

3. 隐式类型转换

**问题：**类型不匹配导致索引无法使用 **解决方案：**确保查询中的类型与列类型匹配

4. 对索引列使用函数

问题：WHERE UPPER(email) = '[email protected]' **解决方案：**使用表达式索引或进行正确的比较

5. OR 条件

问题：WHERE status = 'A' OR status = 'B' **解决方案：**使用 IN：WHERE status IN ('A', 'B')

联接优化

联接类型：

1. 嵌套循环

   • 最适合：较小的外表、带索引的内表
   • 原理：针对每一行外表数据扫描内表
   • 适用场景：结果集较小、索引良好

2. 哈希联接

   • 最适合：大型表、缺少合适的索引
   • 原理：为较小的表构建哈希表
   • 适用场景：等值联接、内存充足

3. 归并联接

   • 最适合：已排序的数据、等值联接
   • 原理：对两个输入进行排序，然后扫描归并
   • 适用场景：两个输入均已排序，或可以低成本排序

联接顺序很重要：

• 规划器会为优化而重新排列联接
• 统计信息会影响联接顺序的决策
• 可以使用 SET join_collapse_limit 强制指定顺序

聚合优化

技术：

• 部分聚合：按分区聚合
• 哈希聚合：在内存中分组
• 排序聚合：使用预先排序的输入
• 并行聚合：使用多个工作进程

物化视图：

• 预先计算开销较大的聚合
• 按计划或触发器刷新
• 以数据新鲜度换取查询速度

查询缓存

级别：

1. 共享缓冲区：PostgreSQL 页面缓存
2. 操作系统页面缓存：操作系统缓存
3. 应用程序缓存：Redis、Memcached
4. 预备语句：重用查询计划

分区

实现范围分区

时间序列示例：

-- Create partitioned table
CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    data JSONB,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Default partition for data outside ranges
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Indexes on partitions
CREATE INDEX idx_events_2024_01_user ON events_2024_01(user_id);
CREATE INDEX idx_events_2024_02_user ON events_2024_02(user_id);

分区自动化

自动分区管理：

-- Function to create monthly partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(
    base_table TEXT,
    partition_date DATE
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := base_table || '_' || TO_CHAR(partition_date, 'YYYY_MM');
    start_date := DATE_TRUNC('month', partition_date);
    end_date := start_date + INTERVAL '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, base_table, start_date, end_date
    );

    -- Create indexes
    EXECUTE format(
        'CREATE INDEX IF NOT EXISTS %I ON %I(user_id)',
        'idx_' || partition_name || '_user', partition_name
    );
END;
$$ LANGUAGE plpgsql;

分区维护

删除旧分区：

-- Detach partition (fast, non-blocking)
ALTER TABLE events DETACH PARTITION events_2023_01;

-- Drop detached partition
DROP TABLE events_2023_01;

-- Or archive before dropping
CREATE TABLE archive.events_2023_01 AS SELECT * FROM events_2023_01;
DROP TABLE events_2023_01;

高可用性和复制

设置流复制

主服务器配置（postgresql.conf）：

# Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
synchronous_commit = on  # or off for async
synchronous_standby_names = 'standby1,standby2'  # for sync replication

创建复制用户：

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secure_password';

主服务器上的 pg_hba.conf：

# Allow replication connections
host replication replicator standby_ip/32 md5

备用服务器设置：

# Stop standby PostgreSQL
systemctl stop postgresql

# Remove old data directory
rm -rf /var/lib/postgresql/14/main

# Base backup from primary
pg_basebackup -h primary_host -D /var/lib/postgresql/14/main \
              -U replicator -P -v -R -X stream -C -S standby1

# Start standby
systemctl start postgresql

备用服务器配置（由 -R 标志创建）：

# standby.signal file created automatically
# postgresql.auto.conf contains:
primary_conninfo = 'host=primary_host port=5432 user=replicator password=secure_password'
primary_slot_name = 'standby1'

监控复制

在主服务器上：

-- Check replication status
SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;

-- Check replication slots
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

在备用服务器上：

-- Check replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- Check recovery status
SELECT pg_is_in_recovery();

故障转移和切换

将备用服务器提升为主服务器：

# Trigger failover
pg_ctl promote -D /var/lib/postgresql/14/main

# Or using SQL
SELECT pg_promote();

受控切换：

# 1. Stop writes on primary
# 2. Wait for standby to catch up
# 3. Promote standby
# 4. Reconfigure old primary as new standby

逻辑复制设置

在发布端（源）：

-- Create publication
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Or all tables
CREATE PUBLICATION all_tables FOR ALL TABLES;

在订阅端（目标）：

-- Create subscription
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher_host dbname=mydb user=replicator password=pass'
    PUBLICATION my_publication;

-- Monitor subscription
SELECT * FROM pg_stat_subscription;

备份和恢复

物理备份

pg_basebackup：

# Full physical backup
pg_basebackup -h localhost -U postgres -D /backup/base \
              -F tar -z -P -v

# With WAL files for point-in-time recovery
pg_basebackup -h localhost -U postgres -D /backup/base \
              -X stream -F tar -z -P

持续归档（WAL 归档）：

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'

逻辑备份

pg_dump：

# Single database
pg_dump -h localhost -U postgres -F c -b -v -f mydb.dump mydb

# All databases
pg_dumpall -h localhost -U postgres -f all_databases.sql

# Specific tables
pg_dump -h localhost -U postgres -t users -t orders -F c -f tables.dump mydb

# Schema only
pg_dump -h localhost -U postgres --schema-only -F c -f schema.dump mydb

pg_restore：

# Restore database
pg_restore -h localhost -U postgres -d mydb -v mydb.dump

# Parallel restore
pg_restore -h localhost -U postgres -d mydb -j 4 -v mydb.dump

# Restore specific tables
pg_restore -h localhost -U postgres -d mydb -t users -v mydb.dump

时间点恢复（PITR）

设置：

1. 执行基础备份
2. 配置 WAL 归档
3. 安全存储 WAL 文件

恢复：

# 1. Restore base backup
tar -xzf base.tar.gz -C /var/lib/postgresql/14/main

# 2. Create recovery.signal file
touch /var/lib/postgresql/14/main/recovery.signal

# 3. Configure recovery target (postgresql.conf or postgresql.auto.conf)
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
# Or: recovery_target_name = 'before_disaster'
# Or: recovery_target_lsn = '0/3000000'

# 4. Start PostgreSQL
systemctl start postgresql

备份策略

3-2-1 规则：

• 3 份数据副本
• 2 种不同的介质类型
• 1 份异地备份

备份计划：

• 每日：增量 WAL 归档
• 每周：完整 pg_basebackup
• 每月：长期保留

测试备份：

• 定期恢复到测试环境
• 验证数据完整性
• 测量恢复时间

性能监控

需要监控的关键指标

数据库健康状况：

• 活动连接数
• 事务速率
• 缓存命中率
• 死锁
• 检查点频率
• autovacuum 运行次数

查询性能：

• 慢查询日志
• 查询执行时间
• 锁等待
• 顺序扫描

系统资源：

• CPU 利用率
• 内存使用量
• 磁盘 I/O
• 网络带宽

必备监控查询

连接统计信息：

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
FROM pg_stat_activity;

缓存命中率：

SELECT sum(heap_blks_read) as heap_read,
       sum(heap_blks_hit) as heap_hit,
       sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

表膨胀：

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

长时间运行的查询：

SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;

锁监控：

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
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

pg_stat_statements

安装：

CREATE EXTENSION pg_stat_statements;

配置（postgresql.conf）：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

按总耗时排序的热门查询：

SELECT query,
       calls,
       total_exec_time,
       mean_exec_time,
       max_exec_time,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

按平均耗时排序的热门查询：

SELECT query,
       calls,
       mean_exec_time,
       total_exec_time
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

最佳实践

架构设计

规范化：

• 事务型系统应规范化至 3NF
• 对读取密集型工作负载有选择地进行反规范化
• 使用外键确保数据完整性
• 对超大型表考虑分区

数据类型：

• 使用符合需求的最小数据类型
• 大型 ID 使用 BIGINT，较小范围使用 INTEGER
• 精确小数值使用 NUMERIC
• 时间戳使用 TIMESTAMP WITH TIME ZONE
• 除非需要长度限制，否则优先使用 TEXT 而非 VARCHAR
• 使用 UUID 生成分布式 ID
• 使用 JSONB 存储半结构化数据

约束：

• 所有表都设置主键
• 使用外键确保引用完整性
• 使用 CHECK 约束定义业务规则
• 在适当的字段上设置 NOT NULL
• 使用 UNIQUE 约束确保唯一性
• 为约束命名，以便维护

迁移策略

零停机迁移：

1. 添加新列

   ALTER TABLE users ADD COLUMN email_verified BOOLEAN;
   

2. 回填数据（分批进行）

   UPDATE users SET email_verified = false
   WHERE email_verified IS NULL
   LIMIT 10000;
   

3. 添加 NOT NULL 约束

   ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
   

创建索引：

• 在生产环境中使用 CREATE INDEX CONCURRENTLY
• 不会锁定表，读写操作均可进行
• 耗时更长，但不会造成阻塞
• 使用 pg_stat_progress_create_index 监控进度

大型表修改：

• 使用 pg_repack 重写表
• 在修改前对大型表进行分区
• 安排在维护时段进行
• 在类似生产环境的数据集上进行测试

安全最佳实践

身份验证：

• 使用强密码或证书身份验证
• 使用 SCRAM-SHA-256 进行密码加密
• 为不同应用使用单独的用户
• 避免应用连接使用超级用户

授权：

• 仅授予所需的最低权限
• 使用基于角色的访问控制
• 撤销 PUBLIC 访问权限
• 在多租户场景中使用行级安全性

网络安全：

• 严格配置 pg_hba.conf
• 使用 SSL/TLS 建立连接
• 对数据库端口设置防火墙
• 复制使用 VPN 或专用网络

审计日志：

• 启用连接日志记录
• 记录 DDL 语句
• 使用 pgAudit 扩展进行详细审计
• 监控可疑活动

维护计划

每日：

• 监控慢查询
• 检查复制延迟
• 查看 autovacuum 活动
• 监控磁盘空间

每周：

• 分析排名靠前的查询
• 检查索引使用情况
• 检查膨胀情况
• 验证备份

每月：

• 对关键表执行完整 VACUUM
• 对膨胀的索引执行 REINDEX
• 查看配置参数
• 规划容量

每季度：

• 检查并优化索引
• 寻找架构优化机会
• 规划升级
• 更新性能基线

高级主题

并行查询执行

配置：

max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
min_parallel_table_scan_size = 8MB

强制并行执行：

SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

并行处理适用场景：

• 大型顺序扫描
• 大型聚合操作
• 大型表上的哈希连接
• 位图堆扫描

自定义函数和过程

存储过程：

CREATE OR REPLACE PROCEDURE update_user_statistics()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET
        order_count = (SELECT COUNT(*) FROM orders WHERE user_id = users.id),
        last_order_date = (SELECT MAX(created_at) FROM orders WHERE user_id = users.id);

    COMMIT;
END;
$$;

带有适当错误处理的函数：

CREATE OR REPLACE FUNCTION create_user(
    p_email TEXT,
    p_name TEXT
) RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id INTEGER;
BEGIN
    INSERT INTO users (email, name)
    VALUES (p_email, p_name)
    RETURNING id INTO v_user_id;

    RETURN v_user_id;
EXCEPTION
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Email already exists: %', p_email;
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error creating user: %', SQLERRM;
END;
$$;

外部数据封装器

访问外部数据源：

-- Install postgres_fdw
CREATE EXTENSION postgres_fdw;

-- Create server
CREATE SERVER remote_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', dbname 'remote_database', port '5432');

-- Create user mapping
CREATE USER MAPPING FOR current_user
SERVER remote_db
OPTIONS (user 'remote_user', password 'remote_password');

-- Import foreign schema
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_db
INTO local_schema;

-- Query foreign table
SELECT * FROM local_schema.remote_table;

JSON 和 JSONB 操作

为 JSONB 创建索引：

-- GIN index for containment queries
CREATE INDEX idx_data_gin ON documents USING GIN (data);

-- Expression index for specific field
CREATE INDEX idx_data_status ON documents ((data->>'status'));

-- GIN index with jsonb_path_ops (smaller, faster for @> queries)
CREATE INDEX idx_data_path_ops ON documents USING GIN (data jsonb_path_ops);

高效的 JSONB 查询：

-- Containment query (uses GIN index)
SELECT * FROM documents WHERE data @> '{"status": "active"}';

-- Existence query
SELECT * FROM documents WHERE data ? 'email';

-- Path query
SELECT * FROM documents WHERE data->'user'->>'email' = '[email protected]';

-- Array operations
SELECT * FROM documents WHERE data->'tags' @> '["sql", "postgres"]';

全文搜索

基本设置：

-- Add tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Generate search vector
UPDATE articles SET search_vector =
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));

-- Create GIN index
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Trigger for automatic updates
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

搜索查询：

-- Basic search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Phrase search
SELECT title FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'database engineering');

-- Search with highlighting
SELECT title,
       ts_headline('english', content, query) AS snippet
FROM articles, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query;

故障排除

常见问题

问题：查询速度慢

• 检查 EXPLAIN ANALYZE 输出
• 确认索引存在且正在使用
• 更新表统计信息：ANALYZE table_name
• 检查外键是否缺少索引
• 查找索引列上的函数调用

问题：CPU 使用率过高

• 使用 pg_stat_statements 找出开销较大的查询
• 检查是否因缺少索引而导致顺序扫描
• 检查并行查询设置
• 查找低效的联接或聚合

问题：连接耗尽

• 增加 max_connections（需要重启）
• 实施连接池（pgBouncer）
• 找出应用中的连接泄漏
• 使用 pg_stat_activity 监控

问题：Autovacuum 跟不上

• 增加 autovacuum_max_workers
• 调整 autovacuum 阈值
• 减少 autovacuum_naptime
• 增加 autovacuum_work_mem
• 检查是否有长时间运行的事务阻塞 VACUUM

问题：复制延迟

• 检查主服务器与备用服务器之间的网络带宽
• 确认备用服务器的硬件资源
• 检查备用服务器上是否有长时间运行的查询
• 监控 WAL 生成速率
• 考虑增加 wal_sender_timeout

问题：事务 ID 回卷

• 监控最早事务的年龄
• 对旧表运行 VACUUM FREEZE
• 检查 autovacuum_freeze_max_age
• 提高 autovacuum 的积极程度
• 必要时手动运行 VACUUM FREEZE

诊断查询

查找外键上缺失的索引：

SELECT c.conrelid::regclass AS table,
       c.confrelid::regclass AS referenced_table,
       string_agg(a.attname, ', ') AS foreign_key_columns
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND c.conkey[1:array_length(c.conkey, 1)]
          OPERATOR(pg_catalog.@>) i.indkey[0:array_length(c.conkey, 1) - 1]
  )
GROUP BY c.conrelid, c.confrelid, c.conname;

识别阻塞查询：

SELECT activity.pid,
       activity.usename,
       activity.query,
       blocking.pid AS blocking_id,
       blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

---

技能版本：1.0.0 最后更新：2025 年 10 月 技能类别：数据库工程、性能优化、数据架构 兼容版本：PostgreSQL 12+、13、14、15、16 先决条件：SQL 知识、数据库基础概念、Linux 命令行