PostgreSQL数据库优化实战手册

PostgreSQL概述

PostgreSQL是功能强大的开源关系型数据库，以稳定性、扩展性和SQL标准合规性著称。

核心特性

• ACID合规：完整的事务支持
• 复杂查询：JOIN、子查询、CTE、窗口函数
• 扩展性：自定义类型、函数、索引
• JSON支持：JSON/JSONB类型和操作符
• 全文搜索：内置全文搜索功能

索引优化

索引类型

-- B-tree（默认，适合等值和范围查询）
CREATE INDEX idx_users_email ON users(email);

-- 多列索引
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 覆盖索引
CREATE INDEX idx_users_covering ON users(email) INCLUDE (name, created_at);

-- 部分索引
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

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

-- GIN索引（数组、JSON、全文搜索）
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
CREATE INDEX idx_posts_content ON posts USING GIN(to_tsvector('english', content));

-- GiST索引（地理空间）
CREATE INDEX idx_locations_geo ON locations USING GIST(coordinates);

索引分析

-- 查看执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 分析索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查找未使用的索引
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT i.indisunique
AND idx_scan = 0
ORDER BY pg_relation_size(i.indexrelid) DESC;

查询优化

查询分析

-- 启用查询计时
	iming on

-- 分析查询
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10;

-- 慢查询日志
-- postgresql.conf
log_min_duration_statement = 500  -- 记录超过500ms的查询

优化技巧

-- 1. 避免SELECT *
-- ❌ 不好
SELECT * FROM users;

-- ✅ 好
SELECT id, name, email FROM users;

-- 2. 使用LIMIT
SELECT * FROM large_table LIMIT 100;

-- 3. 分页优化
-- ❌ 偏移量大时性能差
SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10;

-- ✅ 使用游标
SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10;

-- 4. 批量插入
-- ❌ 单条插入
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

-- ✅ 批量插入
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- 5. 使用CTE提高可读性
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
),
recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT au.name, COUNT(ro.id) as order_count
FROM active_users au
LEFT JOIN recent_orders ro ON au.id = ro.user_id
GROUP BY au.id;

配置优化

内存配置

# postgresql.conf

# 共享缓冲区（建议设为系统内存的25%）
shared_buffers = 4GB

# 工作内存（每个操作可用内存）
work_mem = 64MB

# 维护工作内存（VACUUM、CREATE INDEX）
maintenance_work_mem = 512MB

# 有效缓存大小（操作系统缓存+共享缓冲区）
effective_cache_size = 12GB

# WAL缓冲区
wal_buffers = 64MB

连接池配置

# 最大连接数
max_connections = 200

# 使用连接池（PgBouncer）
# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

并行查询

# 启用并行查询
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# 并行查询最小代价
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000

表设计优化

分区表

-- 创建分区表
CREATE TABLE orders (
    id BIGSERIAL,
    user_id INTEGER,
    created_at TIMESTAMP,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 自动创建分区（pg_partman扩展）
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table := 'public.orders',
    p_control := 'created_at',
    p_interval := '1 month',
    p_premake := 3
);

约束优化

-- 外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

-- 检查约束
ALTER TABLE products
ADD CONSTRAINT chk_price_positive
CHECK (price > 0);

-- 唯一约束
ALTER TABLE users
ADD CONSTRAINT uq_users_email
UNIQUE (email);

-- 排他约束
ALTER TABLE reservations
ADD CONSTRAINT ex_reservation_overlap
EXCLUDE USING GIST (
    room_id WITH =,
    during WITH &&
);

监控与维护

-- 查看表统计信息
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 手动VACUUM
VACUUM ANALYZE users;

-- 重建表（消除碎片）
VACUUM FULL users;  -- 锁表，生产环境慎用

-- 查看锁等待
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
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;

最佳实践

1. 合理设计索引：不是越多越好
2. 定期维护：VACUUM、ANALYZE
3. 监控慢查询：持续优化
4. 使用连接池：减少连接开销
5. 备份策略：定期备份和测试恢复

PostgreSQL是功能丰富的企业级数据库，合理的优化能够支撑大规模应用。

本文链接：https://www.kkkliao.cn/?id=760 转载需授权！