PostgreSQL + Citus 分布式数据库调优指南

本文介绍 PostgreSQL 配合 Citus 扩展构建分布式数据库集群的实践经验，涵盖表分布策略、性能调优、运维管理等核心内容。

Citus 简介

Citus 是 PostgreSQL 的开源扩展，可以将单机 PostgreSQL 转换为分布式数据库，支持：

• 水平扩展：数据分片到多个节点
• 并行查询：分布式 SQL 执行
• 高可用：主备复制、故障转移
• 实时分析：OLTP + OLAP 混合负载

架构组成

┌─────────────────────────────────────┐
│         Coordinator (协调器)         │
│   - SQL 解析、优化、路由              │
│   - 元数据管理                       │
└─────────────┬───────────────────────┘
              │
    ┌─────────┼─────────┐
    │         │         │
    ▼         ▼         ▼
┌───────┐ ┌───────┐ ┌───────┐
│Worker1│ │Worker2│ │Worker3│
│Shard1 │ │Shard2 │ │Shard3 │
│Shard4 │ │Shard5 │ │Shard6 │
└───────┘ └───────┘ └───────┘

核心概念

分布式表类型

创建分布式表

-- 创建普通表
CREATE TABLE events (
    event_id bigint,
    event_type text,
    user_id bigint,
    payload jsonb,
    created_at timestamp
);

-- 转换为分布式表（按 user_id 分片）
SELECT create_distributed_table('events', 'user_id');

创建参考表

-- 小表、配置表适合作为参考表
CREATE TABLE countries (
    code char(2) PRIMARY KEY,
    name text NOT NULL,
    timezone text
);

-- 分发到所有工作节点
SELECT create_reference_table('countries');

共置（Co-location）

将相关表放在同一节点，提升 JOIN 性能：

-- 父表
SELECT create_distributed_table('orders', 'store_id');

-- 子表指定共置组
SELECT create_distributed_table('order_items', 'store_id', 
    colocate_with => 'orders');

性能调优

写入性能优化

批量插入：

-- 使用 COPY 替代 INSERT
COPY events FROM '/data/events.csv' WITH CSV;

并发写入：

-- 使用多个并行连接
-- 考虑连接池（PgBouncer）

-- 两阶段提交（保证一致性）
SET citus.multi_shard_commit_protocol = '2pc';

最大化写入吞吐：

• 使用多连接并发写入
• 批量提交（每批 1000-10000 条）
• 调整 citus.max_adaptive_executor_pool_size

查询性能优化

分片裁剪：

-- 好的查询：包含分布列条件
SELECT * FROM events 
WHERE user_id = 12345 AND created_at > '2024-01-01';

-- 差的查询：缺少分布列条件（需要扫描所有分片）
SELECT * FROM events 
WHERE created_at > '2024-01-01';

本地 JOIN：

-- 共置表 JOIN 在本地执行
SELECT o.*, oi.* 
FROM orders o 
JOIN order_items oi ON o.id = oi.order_id 
WHERE o.store_id = 100;

并行查询：

-- 聚合查询自动并行
SELECT event_type, COUNT(*) 
FROM events 
GROUP BY event_type;

索引优化

-- 并发创建索引（不锁表）
CREATE INDEX CONCURRENTLY idx_events_user_time 
ON events (user_id, created_at);

-- BRIN 索引（时序数据）
CREATE INDEX CONCURRENTLY idx_events_time 
ON events USING BRIN (created_at);

集群管理

添加工作节点

-- 添加新节点
SELECT * FROM master_add_node('worker4', 5432);

-- 重新平衡数据
SELECT rebalance_table_shards('events');

查看集群状态

-- 查看节点列表
SELECT * FROM master_get_active_worker_nodes();

-- 查看分片分布
SELECT * FROM pg_dist_shard;

-- 查看分片放置
SELECT * FROM pg_dist_shard_placement;

-- 查看表大小分布
SELECT 
    nodename,
    pg_size_pretty(sum(pg_relation_size(shardid::regclass)))
FROM pg_dist_shard_placement
GROUP BY nodename;

维护操作

-- 移动分片到其他节点
SELECT master_move_shard_placement(
    102008,
    'worker1', 5432,
    'worker2', 5432
);

-- 清理失效的放置
SELECT master_drop_all_shards('events', 'public', 'events');

监控与告警

关键指标

-- 慢查询监控
SELECT * FROM pg_stat_activity 
WHERE state = 'active' 
AND query_start < now() - interval '1 minute';

-- 锁等待
SELECT * FROM pg_locks 
WHERE NOT granted;

-- 分片大小分布
SELECT 
    shardid,
    shardstate,
    nodename,
    pg_size_pretty(pg_relation_size(shardid::regclass))
FROM pg_dist_shard_placement
ORDER BY shardid;

系统视图

常见问题

问题1：跨分片查询慢

原因：查询缺少分布列条件

解决：

• 优化查询条件，包含分布列
• 考虑重新选择分布列
• 使用参考表减少跨节点 JOIN

问题2：数据倾斜

诊断：

SELECT 
    nodename,
    count(*) as shard_count,
    pg_size_pretty(sum(pg_relation_size(shardid::regclass)))
FROM pg_dist_shard_placement
WHERE logicalrelid = 'events'::regclass
GROUP BY nodename;

解决：

• 更换分布列（更均匀）
• 手动重新平衡分片
• 考虑使用范围分片

问题3：死锁

诊断：

SELECT * FROM pg_locks 
WHERE NOT granted 
AND mode = 'ExclusiveLock';

解决：

• 按相同顺序访问表
• 减少事务持有时间
• 使用 SELECT ... FOR UPDATE 谨慎

最佳实践

分布列选择

原则：

1. 高频查询条件列
2. 数据分布均匀
3. JOIN 条件列

示例：

• 电商系统：user_id 或 store_id
• 日志系统：tenant_id 或 service_id
• 时序数据：device_id 或 location_id

表设计建议

-- 1. 大表使用分布式
SELECT create_distributed_table('orders', 'user_id');

-- 2. 小表使用参考表
SELECT create_reference_table('products');

-- 3. 元数据使用本地表（不分布式）
CREATE TABLE admin_settings (...);

-- 4. 共置相关表
SELECT create_distributed_table('orders', 'user_id');
SELECT create_distributed_table('order_items', 'user_id', 
    colocate_with => 'orders');

升级与备份

备份：

# 逻辑备份（推荐）
pg_dump -h coordinator -Fc dbname > backup.dump

# 各节点物理备份
pg_basebackup -h worker1 -D /backup/worker1

升级：

1. 先升级协调器
2. 逐个升级工作节点
3. 验证数据一致性

小结

Citus + PostgreSQL 是构建分布式数据库的优秀方案：

1. 架构：协调器 + 工作节点，水平扩展
2. 表类型：分布式表、参考表、本地表
3. 性能：分布列选择是关键，善用共置
4. 运维：监控分片分布，及时处理倾斜

适用场景：

• 多租户 SaaS 应用
• 实时分析仪表盘
• 高并发 OLTP 系统
• 时序数据存储

参考资料：

• Citus 官方文档
• Citus GitHub