PostgreSQL教程

性能优化的主要方面

• 硬件优化：CPU、内存、存储和网络的合理配置
• 操作系统优化：内核参数、文件系统和I/O调度器调优
• PostgreSQL配置优化：内存、连接、WAL和查询相关参数调整
• 数据库设计优化：表结构、索引、分区和数据类型选择
• 查询优化：SQL语句优化、执行计划分析和统计信息更新
• 应用层优化：连接池、缓存和批量操作优化

性能优化的基本原则：

存储系统优化

# 使用SSD存储
# SSD相比HDD有显著的性能提升，特别是随机I/O操作

# 分离数据和日志存储
# 将数据文件和WAL日志存储在不同的物理磁盘上
# 在postgresql.conf中配置
data_directory = '/data/postgresql'
log_directory = '/wal/postgresql'

# 使用合适的文件系统
# 推荐使用ext4或xfs文件系统
# 挂载选项优化
# /dev/sdb1 /data ext4 defaults,noatime,nobarrier 0 0

# 调整I/O调度器
# 对于数据库服务器，推荐使用deadline或noop调度器
echo deadline > /sys/block/sdb/queue/scheduler

# 禁用访问时间更新
# 在/etc/fstab中添加noatime选项
/dev/sdb1 /data ext4 defaults,noatime 0 0

内存配置优化

# 操作系统内核参数优化
# 在/etc/sysctl.conf中添加
kernel.shmmax = 2147483648        # 最大共享内存段大小 (2GB)
kernel.shmall = 524288            # 最大共享内存页数
kernel.shmmni = 4096              # 最大共享内存段数
vm.swappiness = 1                 # 降低交换倾向
vm.dirty_ratio = 15               # 脏页比例
vm.dirty_background_ratio = 3     # 后台脏页比例

# 应用内核参数
sysctl -p

# PostgreSQL内存相关配置
# 在postgresql.conf中
shared_buffers = 256MB            # 共享缓冲区 (建议为系统内存的25%)
effective_cache_size = 1GB        # 有效缓存大小 (建议为系统内存的50-75%)
work_mem = 4MB                    # 每个查询操作的内存
maintenance_work_mem = 64MB       # 维护操作内存
temp_buffers = 8MB                # 临时表缓冲区

# 大页配置 (Linux)
# 启用大页可以减少内存管理开销
# 在/etc/sysctl.conf中
vm.nr_hugepages = 128

# 在postgresql.conf中
huge_pages = try                  # 尝试使用大页

CPU和并发优化

# CPU相关配置
# 在postgresql.conf中
max_connections = 100             # 最大连接数
superuser_reserved_connections = 3  # 超级用户保留连接
max_worker_processes = 8          # 最大工作进程数
max_parallel_workers_per_gather = 2  # 并行查询工作进程数
max_parallel_workers = 8          # 最大并行工作进程数

# 操作系统限制调整
# 在/etc/security/limits.conf中
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 65536
postgres hard nproc 65536

# 检查当前限制
ulimit -n  # 文件描述符限制
ulimit -u  # 进程数限制

连接和会话优化

# 连接相关配置
# 在postgresql.conf中
max_connections = 100              # 根据应用需求调整
superuser_reserved_connections = 3  # 为超级用户保留连接
listen_addresses = '*'             # 监听地址
port = 5432                        # 端口

# 会话相关配置
tcp_keepalives_idle = 600          # TCP空闲时间
tcp_keepalives_interval = 30       # TCP探测间隔
tcp_keepalives_count = 3           # TCP探测次数
statement_timeout = 30000          # 语句超时时间 (毫秒)
idle_in_transaction_session_timeout = 300000  # 事务空闲超时 (毫秒)

# 预备事务配置
max_prepared_transactions = 0      # 预备事务数 (0表示禁用)

# 连接池配置 (推荐使用外部连接池如PgBouncer)
# pgbouncer.ini示例
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 100
reserve_pool_size = 5

WAL和检查点优化

# WAL相关配置
# 在postgresql.conf中
wal_level = replica                # WAL级别
fsync = on                         # 同步写入磁盘
synchronous_commit = on            # 同步提交
wal_sync_method = fsync            # WAL同步方法
full_page_writes = on              # 全页写入
wal_buffers = 16MB                 # WAL缓冲区
wal_writer_delay = 200ms           # WAL写入延迟

# 检查点相关配置
checkpoint_timeout = 15min         # 检查点间隔
max_wal_size = 1GB                 # 最大WAL大小
min_wal_size = 80MB                # 最小WAL大小
checkpoint_completion_target = 0.9  # 检查点完成目标
checkpoint_warning = 30s           # 检查点警告时间

# 归档配置
archive_mode = on                  # 归档模式
archive_command = 'cp %p /backup/postgresql/archive/%f'  # 归档命令

查询和规划器优化

# 查询优化器配置
# 在postgresql.conf中
random_page_cost = 1.1             # 随机页面访问成本 (SSD推荐1.1)
seq_page_cost = 1.0                # 顺序页面访问成本
cpu_tuple_cost = 0.01              # 处理每行的CPU成本
cpu_index_tuple_cost = 0.005       # 处理每个索引项的CPU成本
cpu_operator_cost = 0.0025         # 处理每个操作符的CPU成本
effective_io_concurrency = 200     # 并发I/O操作数 (SSD推荐200)

# 统计信息配置
default_statistics_target = 100    # 默认统计目标
track_activities = on              # 跟踪活动
track_counts = on                  # 跟踪计数
track_io_timing = on               # 跟踪I/O时间
track_functions = all              # 跟踪函数

# 自动清理配置
autovacuum = on                    # 启用自动清理
log_autovacuum_min_duration = 0    # 记录所有自动清理操作
autovacuum_max_workers = 3         # 最大自动清理工作进程
autovacuum_naptime = 1min          # 自动清理间隔
autovacuum_vacuum_threshold = 50   # 真空阈值
autovacuum_analyze_threshold = 50  # 分析阈值
autovacuum_vacuum_scale_factor = 0.2  # 真空比例因子
autovacuum_analyze_scale_factor = 0.1  # 分析比例因子

表结构优化

-- 选择合适的数据类型
-- 使用最小的满足需求的数据类型
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,     -- 避免使用TEXT如果长度已知
    email VARCHAR(255) UNIQUE,
    age SMALLINT,                      -- 使用SMALLINT而不是INTEGER
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT true     -- 使用BOOLEAN而不是INTEGER
);

-- 使用NOT NULL约束
-- NOT NULL列可以提高查询性能
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 垂直分区
-- 将大表拆分为多个相关表
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    bio TEXT,
    avatar_url VARCHAR(500),
    preferences JSONB
);

-- 水平分区
-- 使用表继承或分区表
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2025 PARTITION OF sales
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE sales_2026 PARTITION OF sales
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

索引优化

-- 创建合适的索引
-- B-tree索引（默认索引类型）
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 复合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 部分索引
CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- GiST索引（用于几何数据）
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);

-- GIN索引（用于数组和JSONB）
CREATE INDEX idx_products_tags ON products USING GIN(tags);
CREATE INDEX idx_user_preferences ON users USING GIN(preferences);

-- BRIN索引（用于大表的块范围索引）
CREATE INDEX idx_sales_date_brin ON sales USING BRIN(sale_date);

-- 索引维护
-- 定期重建碎片化的索引
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- 监控索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_tup_read = 0
ORDER BY schemaname, tablename;

-- 删除未使用的索引
DROP INDEX IF EXISTS unused_index;

规范化与反规范化

-- 规范化设计（减少数据冗余）
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category_id INTEGER REFERENCES categories(id),
    price DECIMAL(10,2)
);

-- 反规范化设计（提高查询性能）
-- 在订单表中包含产品名称，避免JOIN操作
CREATE TABLE order_items_denormalized (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER,
    product_name VARCHAR(200),  -- 冗余存储产品名称
    quantity INTEGER,
    price DECIMAL(10,2)
);

-- 使用物化视图
CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

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

执行计划分析

-- 使用EXPLAIN分析查询计划
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 使用EXPLAIN ANALYZE执行查询并显示实际执行信息
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.is_active = true
GROUP BY u.id, u.username
ORDER BY order_count DESC
LIMIT 10;

-- 详细执行计划分析
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT p.name, SUM(oi.quantity * oi.price) as total_sales
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= '2025-01-01'
GROUP BY p.id, p.name
ORDER BY total_sales DESC;

-- 监控慢查询
-- 在postgresql.conf中配置
log_min_duration_statement = 1000  -- 记录超过1秒的查询
log_statement = 'all'              -- 记录所有语句

-- 分析慢查询日志
-- 使用pgBadger生成报告

查询优化技巧

-- 1. 使用LIMIT减少数据传输
-- 不好的做法
SELECT * FROM users;

-- 好的做法
SELECT id, username, email FROM users LIMIT 100;

-- 2. 避免SELECT *
-- 不好的做法
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

-- 好的做法
SELECT u.username, o.total_amount, o.created_at
FROM users u 
JOIN orders o ON u.id = o.user_id;

-- 3. 使用 EXISTS 替代 IN
-- 不好的做法
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);

-- 好的做法
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total_amount > 1000);

-- 4. 使用UNION ALL替代UNION（如果不需要去重）
-- 不好的做法
SELECT email FROM users
UNION
SELECT email FROM customers;

-- 好的做法（如果允许重复）
SELECT email FROM users
UNION ALL
SELECT email FROM customers;

-- 5. 优化JOIN操作
-- 确保JOIN字段上有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 使用适当的JOIN类型
-- INNER JOIN比OUTER JOIN更高效
SELECT u.username, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 100;

-- 6. 使用CTE优化复杂查询
WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_spent
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.username,
    us.order_count,
    us.total_spent
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE us.total_spent > 1000;

统计信息更新

-- 更新表统计信息
ANALYZE users;
ANALYZE orders;

-- 更新所有表的统计信息
ANALYZE;

-- 查看统计信息
SELECT 
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE tablename = 'users';

-- 手动设置统计目标
ALTER TABLE users ALTER COLUMN email SET STATISTICS 200;

-- 增加默认统计目标
-- 在postgresql.conf中
default_statistics_target = 200;

-- 监控统计信息更新
SELECT 
    schemaname,
    tablename,
    last_autoanalyze,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY last_autoanalyze DESC;

性能监控

-- 监控活动会话
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    query
FROM pg_stat_activity
WHERE state = 'active';

-- 监控锁等待
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.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    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 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS indexes_size
FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 监控缓存命中率
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

-- 监控检查点统计
SELECT 
    checkpoints_req,
    checkpoints_timed,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint,
    buffers_clean,
    buffers_backend
FROM pg_stat_bgwriter;

基准测试

-- 使用pgbench进行基准测试
-- 初始化测试数据
pgbench -i -s 10 myapp

-- 运行简单测试
pgbench -c 10 -j 2 -t 1000 myapp

-- 自定义测试脚本
-- 创建测试脚本文件 test_script.sql
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM accounts WHERE aid = :aid;
INSERT INTO history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

-- 运行自定义测试
pgbench -c 20 -j 4 -t 5000 -f test_script.sql myapp

-- 监控基准测试结果
-- 记录TPS（每秒事务数）
-- 分析响应时间分布
-- 比较不同配置下的性能差异

-- 使用EXPLAIN进行查询性能测试
-- 创建测试函数
CREATE OR REPLACE FUNCTION test_query_performance()
RETURNS TABLE(query_name TEXT, execution_time INTERVAL) AS $$
DECLARE
    start_time TIMESTAMP;
BEGIN
    -- 测试查询1
    start_time := clock_timestamp();
    PERFORM COUNT(*) FROM users u JOIN orders o ON u.id = o.user_id;
    execution_time := clock_timestamp() - start_time;
    query_name := 'user_orders_join';
    RETURN NEXT;
    
    -- 测试查询2
    start_time := clock_timestamp();
    PERFORM COUNT(*) FROM orders WHERE created_at > '2025-01-01';
    execution_time := clock_timestamp() - start_time;
    query_name := 'orders_filter';
    RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

-- 运行性能测试
SELECT * FROM test_query_performance();