PostgreSQL 数据库分库分表

核心概念

分库分表是将大型数据库拆分为多个较小的数据库或表，以提高系统的扩展性和性能。PostgreSQL 分库分表主要涉及以下核心概念：

水平分片：将同一表中的数据按行拆分到不同的节点
垂直分片：将同一表中的数据按列拆分到不同的节点
分区表：PostgreSQL 内置的表分区功能
分布式扩展：如 Citus、pg_shard 等扩展实现的分布式数据库
分片键：用于决定数据分布的列
分片策略：数据拆分的规则，如范围分片、哈希分片、列表分片

实现方案

1. PostgreSQL 内置分区表

PostgreSQL 10+ 支持原生分区表，主要分为以下几种类型：

范围分区（Range Partitioning）

sql

-- 创建范围分区表
CREATE TABLE orders (
    order_id SERIAL,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);

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

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

CREATE TABLE orders_2023_q3 PARTITION OF orders
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE orders_2023_q4 PARTITION OF orders
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

-- 自动创建分区（PostgreSQL 11+）
CREATE TABLE orders (
    order_id SERIAL,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date)
PARTITION BY RANGE (order_date)
(
    PARTITION p2023q1 VALUES FROM ('2023-01-01') TO ('2023-04-01'),
    PARTITION p2023q2 VALUES FROM ('2023-04-01') TO ('2023-07-01')
);

哈希分区（Hash Partitioning）

sql

-- 创建哈希分区表
CREATE TABLE users (
    user_id SERIAL,
    username VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (user_id)
) PARTITION BY HASH (user_id);

-- 创建分区
CREATE TABLE users_0 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_1 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE users_2 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE users_3 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

列表分区（List Partitioning）

sql

-- 创建列表分区表
CREATE TABLE products (
    product_id SERIAL,
    product_name VARCHAR(100),
    category VARCHAR(50),
    PRIMARY KEY (product_id, category)
) PARTITION BY LIST (category);

-- 创建分区
CREATE TABLE products_electronics PARTITION OF products
    FOR VALUES IN ('electronics', 'computers', 'phones');

CREATE TABLE products_clothing PARTITION OF products
    FOR VALUES IN ('clothing', 'shoes', 'accessories');

CREATE TABLE products_home PARTITION OF products
    FOR VALUES IN ('home', 'furniture', 'kitchen');

2. Citus 分布式扩展

Citus 是 PostgreSQL 的分布式扩展，支持水平分片和分布式查询。

安装 Citus

bash

# Ubuntu/Debian
apt-get install postgresql-14-citus

# CentOS/RHEL
yum install citus10_14

配置 Citus

sql

-- 在 coordinator 节点上启用 Citus 扩展
CREATE EXTENSION citus;

-- 添加 worker 节点
SELECT * FROM citus_add_node('worker1', 5432);
SELECT * FROM citus_add_node('worker2', 5432);
SELECT * FROM citus_add_node('worker3', 5432);

-- 创建分布式表
CREATE TABLE events (
    event_id SERIAL,
    event_type VARCHAR(50),
    event_time TIMESTAMP,
    payload JSONB,
    PRIMARY KEY (event_id, event_time)
);

-- 分布式化表（按 event_time 范围分片）
SELECT create_distributed_table('events', 'event_time', 'range');

-- 或按 event_id 哈希分片
SELECT create_distributed_table('events', 'event_id', 'hash');

验证 Citus 配置

sql

-- 查看集群信息
SELECT * FROM citus_nodes;

-- 查看分布式表信息
SELECT * FROM citus_tables;

-- 查看分片信息
SELECT * FROM citus_shards WHERE table_name = 'events';

3. 应用层分库分表

应用层分库分表是通过应用代码或中间件实现数据的拆分和路由。

分库分表策略设计

选择分片键：
- 选择访问频率高的列
- 避免热点数据
- 考虑数据分布均匀性
分片策略：
- 范围分片：适合按时间、ID 等连续值拆分
- 哈希分片：适合随机访问模式
- 列表分片：适合按业务类型拆分
分片数量：
- 考虑数据增长趋势
- 考虑节点资源
- 建议初始分片数量为节点数的 2-4 倍

示例：应用层分库分表实现

java

// 示例：基于 MyBatis-Plus 实现的分库分表
@Configuration
public class ShardingSphereConfig {
    
    @Bean
    public ShardingRuleConfiguration shardingRuleConfiguration() {
        // 配置分片规则
        ShardingRuleConfiguration ruleConfig = new ShardingRuleConfiguration();
        
        // 配置数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", createDataSource("ds0"));
        dataSourceMap.put("ds1", createDataSource("ds1"));
        
        // 配置表规则
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration("orders", "ds${0..1}.orders_${0..3}");
        
        // 配置数据库分片策略
        tableRuleConfig.setDatabaseShardingStrategyConfig(
            new InlineShardingStrategyConfiguration("customer_id", "ds${customer_id % 2}"));
        
        // 配置表分片策略
        tableRuleConfig.setTableShardingStrategyConfig(
            new InlineShardingStrategyConfiguration("order_id", "orders_${order_id % 4}"));
        
        ruleConfig.getTableRuleConfigs().add(tableRuleConfig);
        return ruleConfig;
    }
}

最佳实践

生产环境配置建议

选择合适的分库分表方案：
- 数据量 < 1000 万：使用内置分区表
- 数据量 1000 万 - 1 亿：考虑 Citus 扩展
- 数据量 > 1 亿：考虑分布式数据库或应用层分库分表
分区表最佳实践：
- 合理设计分区键，避免跨分区查询
- 定期清理旧分区，使用 DROP TABLE 或 TRUNCATE TABLE
- 对分区表创建合适的索引
- 考虑使用 PARTITION BY DEFAULT 处理异常数据
Citus 最佳实践：
- 选择合适的分片键，避免数据倾斜
- 合理设置分片数量，建议每个分片大小为 10-50GB
- 对分布式表的查询添加分片键过滤条件
- 定期运行 VACUUM ANALYZE 维护统计信息
应用层分库分表最佳实践：
- 封装分库分表逻辑，避免业务代码耦合
- 实现数据迁移和扩容机制
- 考虑分布式事务处理
- 实现全局唯一 ID 生成机制

性能优化建议

查询优化：
- 避免跨分片查询，尽量在单个分片内完成
- 对频繁查询的列创建索引
- 使用 EXPLAIN ANALYZE 分析查询计划
写入优化：
- 批量写入数据，减少网络开销
- 考虑使用 COPY 命令导入大量数据
- 合理设置 max_wal_size 和 checkpoint_completion_target 参数
存储优化：
- 对旧分区使用压缩表：ALTER TABLE orders_2023_q1 SET (autovacuum_enabled = true, toast.autovacuum_enabled = true, autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.05);
- 考虑使用表空间将不同分区存储在不同磁盘

常见问题处理

问题1：分区表查询性能差 解决方法：
- 检查查询是否包含分区键
- 确保分区表统计信息准确，运行 ANALYZE
- 考虑调整分区策略，避免过多分区
问题2：Citus 集群数据倾斜 解决方法：
- 重新评估分片键选择
- 使用 citus_rebalance_table_shards() 重新平衡分片
- 考虑使用哈希分片替代范围分片
问题3：应用层分库分表扩容困难 解决方法：
- 设计时考虑未来扩容，预留足够的分片数量
- 实现在线数据迁移工具
- 考虑使用一致性哈希算法，减少扩容时的数据迁移量
问题4：跨分片事务一致性 解决方法：
- 尽量避免跨分片事务
- 使用分布式事务中间件（如 Seata）
- 采用最终一致性方案

常见问题（FAQ）

Q1：何时需要考虑分库分表？

A1：当出现以下情况时，考虑分库分表：

单表数据量超过 1000 万行
数据库查询响应时间明显增加
写入吞吐量达到瓶颈
单个数据库实例资源（CPU、内存、磁盘）使用率过高

Q2：分区表和分布式表的区别是什么？

A2：主要区别：

分区表：数据存储在同一数据库实例的不同表中，逻辑上是一个表
分布式表：数据存储在不同的数据库实例中，逻辑上是一个表
分区表适合单实例内的数据拆分，分布式表适合跨实例的数据拆分

Q3：如何选择合适的分片键？

A3：分片键选择原则：

高基数：有足够多的不同值
高访问频率：作为查询条件频繁出现
分布均匀：避免数据倾斜
业务相关：与业务查询模式匹配

Q4：分库分表后如何处理全局唯一 ID？

A4：全局唯一 ID 生成方案：

使用 UUID：简单但存储占用大，查询性能差
使用雪花算法（Snowflake）：适合分布式环境
使用数据库自增 ID：通过设置不同的起始值和步长
使用 Redis 生成 ID：性能高但依赖外部系统

Q5：如何监控分库分表的性能？

A5：关键监控指标：

分区表：查询计划、分区访问频率、统计信息准确性
Citus：分片分布、查询延迟、数据倾斜度
应用层：分片访问分布、跨分片查询比例、事务成功率

Q6：分库分表后如何处理数据迁移？

A6：数据迁移策略：

离线迁移：停止服务，一次性迁移所有数据
在线迁移：
1. 双写：同时写入旧库和新库
2. 数据同步：使用工具同步历史数据
3. 验证：对比新旧库数据一致性
4. 切换：将流量切换到新库
渐进式迁移：逐步将业务迁移到新的分库分表架构

PostgreSQL 数据库分库分表 ​

核心概念 ​

实现方案 ​

1. PostgreSQL 内置分区表 ​

范围分区（Range Partitioning） ​

哈希分区（Hash Partitioning） ​

列表分区（List Partitioning） ​

2. Citus 分布式扩展 ​

安装 Citus ​

配置 Citus ​

验证 Citus 配置 ​

3. 应用层分库分表 ​

分库分表策略设计 ​

示例：应用层分库分表实现 ​

最佳实践 ​

生产环境配置建议 ​

性能优化建议 ​

常见问题处理 ​

常见问题（FAQ） ​

Q1：何时需要考虑分库分表？ ​

Q2：分区表和分布式表的区别是什么？ ​

Q3：如何选择合适的分片键？ ​

Q4：分库分表后如何处理全局唯一 ID？ ​

Q5：如何监控分库分表的性能？ ​

Q6：分库分表后如何处理数据迁移？ ​