postgresql-optimization

PostgreSQL 优化

概述

专注于 PostgreSQL 性能调优、查询优化和数据库管理。擅长 EXPLAIN 分析、索引策略、连接池、分区，以及生产级 PostgreSQL 运维。

适用场景

• 使用 EXPLAIN ANALYZE 诊断慢查询
• 根据查询模式创建最优索引
• 设计高性能数据库架构
• 为生产工作负载配置 PostgreSQL
• 实施连接池（PgBouncer、Supavisor）
• 为大型表设置分区
• 分析并减少锁竞争
• 迁移或升级 PostgreSQL 版本

能力

查询优化

• 解读 EXPLAIN / EXPLAIN ANALYZE
• 分析并优化查询计划
• 识别顺序扫描与索引扫描
• 优化连接并重写查询
• 权衡 CTE 与子查询的性能
• 优化窗口函数

索引策略

• 选择 B-tree、GIN、GiST、BRIN 索引
• 为筛选查询创建部分索引
• 为计算值创建表达式索引
• 覆盖索引（INCLUDE 子句）
• 优化仅索引扫描
• 并发创建索引

架构设计

• 权衡规范化与反规范化
• 设计并为 JSONB 列建立索引
• 数组列及其操作
• 枚举类型与查找表的选择
• 外键级联策略
• 表继承与分区

配置调优

• 内存设置（shared_buffers、work_mem、effective_cache_size）
• 连接限制与连接池
• WAL 和检查点调优
• 自动清理配置
• 统计信息收集设置

高级功能

• 分区（范围、列表、哈希）
• 物化视图及其刷新策略
• 使用 tsvector/tsquery 进行全文搜索
• PostGIS 地理空间查询
• 设置逻辑复制
• 分析 pg_stat_statements

依赖项

配合以下工具效果良好：

• database-modeler - 架构设计和 ERD 创建
• data-pipeline-engineer - ETL 和数据处理
• site-reliability-engineer - 数据库监控和告警
• nextjs-app-router-expert - 全栈数据获取

示例

阅读 EXPLAIN ANALYZE 输出

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.*, 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;

-- Key metrics to look for:
-- - "Seq Scan" on large tables → needs index
-- - "Rows Removed by Filter" high → filter before join
-- - "Sort Method: external merge" → increase work_mem
-- - "Buffers: shared hit" vs "shared read" → cache efficiency

创建高效索引

-- Basic B-tree for equality and range queries
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);

-- Partial index for common filter
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

-- GIN index for JSONB containment queries
CREATE INDEX CONCURRENTLY idx_products_metadata
ON products USING GIN (metadata jsonb_path_ops);

-- Covering index to enable index-only scans
CREATE INDEX CONCURRENTLY idx_users_email_covering
ON users (email) INCLUDE (name, created_at);

-- Expression index for case-insensitive search
CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users (LOWER(email));

优化 N+1 查询

-- BAD: N+1 pattern (1 + N queries)
SELECT * FROM posts WHERE user_id = $1;
-- Then for each post: SELECT * FROM comments WHERE post_id = $1;

-- GOOD: Single query with lateral join
SELECT p.*, c.comments
FROM posts p
LEFT JOIN LATERAL (
  SELECT json_agg(c.*) as comments
  FROM comments c
  WHERE c.post_id = p.id
) c ON true
WHERE p.user_id = $1;

-- GOOD: Window function for aggregates
SELECT
  p.*,
  COUNT(*) OVER (PARTITION BY p.user_id) as user_post_count
FROM posts p
WHERE p.user_id = $1;

表分区

-- Create partitioned table by date range
CREATE TABLE events (
  id BIGSERIAL,
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly 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');

-- Automate partition creation with pg_partman
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');

连接池配置（PgBouncer）

; pgbouncer.ini

[databases]
myapp = host=localhost dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Pool settings
pool_mode = transaction        ; Recommended for most apps
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5

; Timeouts
server_idle_timeout = 600
client_idle_timeout = 0

性能配置

-- Check current settings
SHOW shared_buffers;        -- ~25% of RAM
SHOW effective_cache_size;  -- ~75% of RAM
SHOW work_mem;              -- Per-operation, start small (64MB)
SHOW maintenance_work_mem;  -- For VACUUM, CREATE INDEX (512MB-1GB)

-- Recommended production settings (for 32GB RAM server)
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET random_page_cost = 1.1;  -- For SSD storage
ALTER SYSTEM SET effective_io_concurrency = 200;  -- For SSD

-- Reload configuration
SELECT pg_reload_conf();

查找慢查询

-- Enable pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) as total_ms,
  calls,
  round(mean_exec_time::numeric, 2) as avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) as pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with most I/O
SELECT
  round(shared_blks_read::numeric, 2) as disk_reads,
  round(shared_blks_hit::numeric, 2) as cache_hits,
  round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) as cache_hit_ratio,
  query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

分析表膨胀

-- Check table bloat
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,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Manual VACUUM for critical tables
VACUUM (VERBOSE, ANALYZE) orders;

-- Reclaim space (requires exclusive lock)
VACUUM FULL orders;  -- Use during maintenance window

最佳实践

1. 始终使用 EXPLAIN ANALYZE - 不要猜测，要实际测量查询性能
2. 使用 CONCURRENTLY 创建索引 - 避免在创建索引期间阻塞写入
3. 为热点路径创建部分索引 - 只为频繁查询的行建立索引
4. 使用连接池 - 在生产环境中使用 PgBouncer 或 Supavisor
5. 监控 pg_stat_statements - 持续跟踪查询性能
6. 定期运行 ANALYZE - 保持统计信息最新，供查询规划器使用
7. **避免使用 SELECT *** - 只获取所需的列
8. 批量处理大型更新 - 分批处理，避免锁竞争
9. 使用预备语句 - 减少重复查询的解析开销

常见问题

• 缺少索引 - 检查大型表上是否存在顺序扫描
• 索引过多 - 索引过多会拖慢写入
• work_mem 过低 - 会导致基于磁盘的排序和哈希连接
• 连接耗尽 - 未使用连接池
• 统计信息过时 - 自动清理运行频率不足
• 表膨胀 - 大量删除或更新后未进行清理
• N+1 查询 - 在循环中获取关联数据，而非使用连接
• **到处使用 SELECT *** - 获取不必要的列