PostgreSQL 性能优化指南

为什么选择 PostgreSQL？

PostgreSQL 是一个功能强大的开源关系型数据库，以可靠性、丰富特性和性能表现著称。

真正使用 PostgreSQL 时，性能优化通常不靠单一技巧，而是索引、查询、配置、表结构和监控共同作用。

索引优化

索引是提升查询性能的关键。

1. B-Tree 索引（默认）

适用于大多数等值查询、范围查询和排序场景。

-- Create single-column index
CREATE INDEX idx_users_email ON users(email);

-- Create composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Create unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

2. 部分索引

只为满足特定条件的行建立索引，可以减小索引体积。

-- Index only active users
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Index only unpaid orders
CREATE INDEX idx_unpaid_orders ON orders(created_at)
WHERE status = 'pending';

3. 表达式索引

表达式索引适合经常按计算结果查询的字段。

-- Index on lowercase email
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Index on JSON field
CREATE INDEX idx_users_preferences ON users((preferences->>'theme'));

4. GIN 索引

GIN 索引适合全文搜索、数组和 JSONB 场景。

-- Full-text search index
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || content));

-- Array index
CREATE INDEX idx_tags ON articles USING GIN(tags);

5. GiST 索引

GiST 索引适合地理数据和范围查询。

CREATE INDEX idx_locations ON stores
USING GIST(location);

查询优化

使用 EXPLAIN ANALYZE

优化前先看执行计划，确认真正的瓶颈。

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND created_at > NOW() - INTERVAL '30 days';

避免 SELECT *

只查询需要的字段，可以减少 IO 和网络传输。

-- Bad
SELECT * FROM users WHERE id = 1;

-- Good
SELECT id, name, email FROM users WHERE id = 1;

用 JOIN 替代低效子查询

-- Bad (subquery)
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE country = 'US');

-- Good (JOIN)
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

批量操作

-- Bad (multiple single inserts)
INSERT INTO logs (message) VALUES ('Log 1');
INSERT INTO logs (message) VALUES ('Log 2');

-- Good (batch insert)
INSERT INTO logs (message) VALUES
  ('Log 1'),
  ('Log 2'),
  ('Log 3');

使用 LIMIT

对于列表页和后台工具，限制返回结果数量非常重要。

SELECT * FROM articles
ORDER BY created_at DESC
LIMIT 10;

配置优化

关键 postgresql.conf 参数

# Memory settings (assuming 16GB RAM)
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 512MB

# Concurrency settings
max_connections = 100
max_worker_processes = 8
max_parallel_workers_per_gather = 4

# Logging settings
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: '

# Checkpoint settings
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

# WAL settings
wal_buffers = 16MB

连接池

使用 PgBouncer 可以降低连接创建和维护成本。

# pgbouncer.ini
[databases]
mydb = host=localhost dbname=mydb

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

表设计优化

1. 选择合适的数据类型

-- Bad
CREATE TABLE users (
  age VARCHAR(3),
  is_active VARCHAR(5)
);

-- Good
CREATE TABLE users (
  age SMALLINT,
  is_active BOOLEAN
);

2. 使用约束

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  amount DECIMAL(10,2) CHECK (amount > 0),
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

3. 分区表

大表可以按时间范围或业务维度分区。

CREATE TABLE logs (
  id SERIAL,
  message TEXT,
  created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2025_01 PARTITION OF logs
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

维护任务

VACUUM

回收空间并更新统计信息。

VACUUM ANALYZE users;
VACUUM FULL users;

ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_analyze_scale_factor = 0.05
);

REINDEX

重建索引可以处理索引膨胀问题。

REINDEX INDEX idx_users_email;
REINDEX TABLE users;
REINDEX DATABASE mydb;

更新统计信息

ANALYZE users;
ANALYZE;

监控与调试

查看慢查询

SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE state != 'idle';

SELECT pg_terminate_backend(pid);

查看表大小

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

查看索引使用情况

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

缓存策略

应用层缓存

可以用 Redis 缓存热点数据。

async function getUser(id) {
  let user = await redis.get(`user:${id}`);

  if (!user) {
    user = await db.query("SELECT * FROM users WHERE id = $1", [id]);
    await redis.setex(`user:${id}`, 3600, JSON.stringify(user));
  }

  return user;
}

物化视图

CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  DATE_TRUNC('day', created_at) AS date,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_TRUNC('day', created_at);

CREATE INDEX idx_sales_summary_date ON sales_summary(date);
REFRESH MATERIALIZED VIEW sales_summary;

常见性能问题

N+1 查询

-- Bad: N+1 queries
SELECT * FROM orders;
SELECT * FROM users WHERE id = ?;

-- Good: Use JOIN
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id;

缺少索引

SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / seq_scan AS avg_seq_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_scan DESC;

锁竞争

SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

总结

PostgreSQL 性能优化是一个持续过程，需要：

1. 合理设计表结构和索引
2. 编写高效 SQL
3. 调整数据库配置
4. 定期维护和监控
5. 在合适的位置使用缓存

记住一个原则：先测量，再优化。使用 EXPLAIN ANALYZE 找到真实瓶颈，而不是凭感觉改 SQL。

参考资料

• PostgreSQL Official Documentation
• PgTune Configuration Generator
• pg_stat_statements Extension