![[SQL系列] PostgreSQL分库分表实战：从原理到落地](http://pic.xiahunao.cn/yaotu/[SQL系列] PostgreSQL分库分表实战：从原理到落地)
1. 分库分表为什么你的PostgreSQL需要它我第一次遇到单表数据爆炸是在一个电商项目中。某天凌晨3点报警短信把我吵醒——订单查询接口响应时间从200ms飙升到8秒。打开监控一看单表数据量已经突破3000万行索引都快撑不住了。这就是典型的单表瓶颈也是分库分表要解决的核心问题。PostgreSQL的分库分表本质上是一种数据分片技术。想象你有一个超大的图书馆数据库当藏书量暴增时管理员会把不同类型的书放到不同楼层垂直分库把同类型书籍按字母顺序分散到多个书架水平分表给每个区域配备专门的图书管理员数据库实例垂直分库就像把电商系统的用户数据、订单数据、商品数据分别存在不同的物理数据库。我最近给一个SaaS平台做架构优化把核心业务表拆分到三个数据库后QPS直接从1500提升到4200。水平分表则更精细比如把2023年的订单放在order_2023表2024年的放在order_2024表。去年帮一个物联网项目处理设备日志按设备ID哈希分表后查询延迟降低了76%。PostgreSQL从11版本开始原生支持三种分区方式范围分区最适合时间序列数据比如CREATE TABLE logs PARTITION BY RANGE (created_at)列表分区适合离散值分类比如按地区PARTITION BY LIST (region)哈希分区能均匀分布热点数据像用户ID这种PARTITION BY HASH (user_id)提示在PG12之前分区表需要手动创建继承关系触发器现在直接用PARTITION BY语法更高效2. PostgreSQL分区实战从建表到优化2.1 创建分区表的正确姿势先看一个时间范围分区的完整案例。假设我们要处理共享单车的骑行记录-- 主表定义不实际存储数据 CREATE TABLE bike_trips ( trip_id BIGSERIAL, bike_number VARCHAR(32) NOT NULL, user_id INT NOT NULL, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ, distance DECIMAL(10,2) ) PARTITION BY RANGE (start_time); -- 按月创建分区 CREATE TABLE bike_trips_202301 PARTITION OF bike_trips FOR VALUES FROM (2023-01-01) TO (2023-02-01); CREATE TABLE bike_trips_202302 PARTITION OF bike_trips FOR VALUES FROM (2023-02-01) TO (2023-03-01);这里有个坑我踩过如果插入2023-03-01的数据会报错因为右边界是开区间。正确的做法是-- 自动创建分区的函数 CREATE OR REPLACE FUNCTION create_bike_trip_partition() RETURNS TRIGGER AS $$ BEGIN EXECUTE format( CREATE TABLE IF NOT EXISTS bike_trips_%s PARTITION OF bike_trips FOR VALUES FROM (%L) TO (%L), to_char(NEW.start_time, YYYYMM), date_trunc(month, NEW.start_time), date_trunc(month, NEW.start_time) interval 1 month ); RETURN NEW; END; $$ LANGUAGE plpgsql; -- 绑定到主表的插入触发器 CREATE TRIGGER bike_trips_partition_trigger BEFORE INSERT ON bike_trips FOR EACH ROW EXECUTE FUNCTION create_bike_trip_partition();2.2 查询优化的关键技巧分区表查询有门道。有次我优化一个慢查询原来是漏了分区键条件-- 慢查询扫描所有分区 SELECT * FROM bike_trips WHERE user_id 1001; -- 优化后只扫描相关分区 SELECT * FROM bike_trips WHERE user_id 1001 AND start_time BETWEEN 2023-01-01 AND 2023-01-31;三个提升性能的必杀技分区裁剪确保WHERE条件包含分区键本地索引每个分区单独建索引CREATE INDEX idx_bike_trips_202301_user ON bike_trips_202301(user_id);约束排除设置CHECK约束帮助优化器ALTER TABLE bike_trips_202301 ADD CONSTRAINT check_202301 CHECK (start_time 2023-01-01 AND start_time 2023-02-01);3. 分库方案设计与数据路由3.1 何时该考虑分库当出现以下情况时单机PostgreSQL可能撑不住了数据量超过服务器物理内存的3倍备份/恢复时间超过业务允许窗口主从复制延迟经常超过30秒去年设计的一个社交平台架构中我们按用户ID范围分库用户库1user_1UID 1-1000万用户库2user_2UID 1000万-2000万每个库部署在独立的EC2实例上3.2 分库路由的三种实现方式应用层路由最灵活我在Spring Boot项目中常用Repository public class UserRepository { // 根据用户ID选择数据源 public DataSource determineDataSource(Long userId) { int dbIndex (int) (userId % 3); return dataSources.get(dbIndex); } }中间件方案如PG的FDW外部数据包装器-- 在主库创建外部表映射 CREATE SERVER user_1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 10.0.0.1, dbname user_1); CREATE SERVER user_2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 10.0.0.2, dbname user_2); -- 创建统一视图 CREATE VIEW all_users AS SELECT * FROM user_1.users UNION ALL SELECT * FROM user_2.users;专业分片工具如Citus-- 安装扩展 CREATE EXTENSION citus; -- 将表定义为分布式表 SELECT create_distributed_table(users, user_id);4. 避坑指南分库分表常见问题4.1 跨分区查询的解决方案遇到需要聚合多分区数据的场景比如统计年度骑行总距离-- 低效做法 SELECT SUM(distance) FROM bike_trips WHERE start_time BETWEEN 2023-01-01 AND 2023-12-31; -- 高效方案并行查询各分区 SELECT SUM(sub.total) FROM ( SELECT SUM(distance) AS total FROM bike_trips_202301 WHERE start_time 2023-01-01 UNION ALL SELECT SUM(distance) FROM bike_trips_202302 UNION ALL ... SELECT SUM(distance) FROM bike_trips_202312 ) sub;4.2 全局唯一ID生成自增序列在分库环境下会冲突我的常用方案-- 使用UUID CREATE TABLE orders ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, ... ); -- 或Snowflake算法实现 CREATE SEQUENCE global_id_seq; CREATE OR REPLACE FUNCTION next_global_id() RETURNS BIGINT AS $$ DECLARE our_epoch BIGINT : 1609459200000; -- 2021-01-01 seq_id BIGINT; now_millis BIGINT; shard_id INT : 1; -- 分片ID BEGIN SELECT nextval(global_id_seq) % 1024 INTO seq_id; SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis; RETURN (now_millis - our_epoch) 23 | shard_id 10 | seq_id; END; $$ LANGUAGE plpgsql;4.3 分布式事务处理对于跨库事务我通常采用最终一致性方案。比如转账场景在事务中插入转账记录状态为处理中通过消息队列异步处理分别更新两个账户的余额标记转账记录为已完成-- 创建事务日志表 CREATE TABLE transaction_log ( id BIGSERIAL PRIMARY KEY, from_account BIGINT, to_account BIGINT, amount DECIMAL(18,2), status VARCHAR(20), created_at TIMESTAMPTZ DEFAULT NOW() ); -- 异步处理任务 CREATE OR REPLACE PROCEDURE process_transfer() LANGUAGE plpgsql AS $$ BEGIN FOR tx IN SELECT * FROM transaction_log WHERE status PENDING LIMIT 100 LOOP BEGIN -- 扣减转出账户 UPDATE account_1 SET balance balance - tx.amount WHERE account_id tx.from_account; -- 增加转入账户 UPDATE account_2 SET balance balance tx.amount WHERE account_id tx.to_account; UPDATE transaction_log SET status COMPLETED WHERE id tx.id; EXCEPTION WHEN OTHERS THEN UPDATE transaction_log SET status FAILED WHERE id tx.id; END; END LOOP; END; $$;5. 性能监控与维护5.1 关键监控指标在我的Grafana看板里这些指标最重要-- 分区表空间使用情况 SELECT schemaname || . || relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_catalog.pg_statio_user_tables WHERE relname LIKE bike_trips%; -- 查询是否命中分区 EXPLAIN ANALYZE SELECT * FROM bike_trips WHERE start_time BETWEEN 2023-01-01 AND 2023-01-02;5.2 分区维护自动化设置定期任务清理旧数据# 每月1号凌晨清理6个月前数据 0 0 1 * * psql -c DROP TABLE IF EXISTS bike_trips_$(date -d 6 months ago %Y%m)对于时间序列数据我常用这个自动创建分区的函数CREATE OR REPLACE FUNCTION create_next_partition() RETURNS VOID AS $$ DECLARE next_month TEXT : to_char(CURRENT_DATE interval 1 month, YYYYMM); next_start TEXT : to_char(date_trunc(month, CURRENT_DATE interval 1 month), YYYY-MM-DD); next_end TEXT : to_char(date_trunc(month, CURRENT_DATE interval 2 month), YYYY-MM-DD); BEGIN EXECUTE format(CREATE TABLE IF NOT EXISTS bike_trips_%s PARTITION OF bike_trips FOR VALUES FROM (%L) TO (%L), next_month, next_start, next_end); END; $$ LANGUAGE plpgsql;把这段代码放进PostgreSQL的定时任务pg_cron里就能实现全自动分区管理。最近给一个智慧园区项目部署这套方案后他们的DBA团队每月节省了至少20小时的手动维护时间。
[SQL系列] PostgreSQL分库分表实战：从原理到落地
发布时间：2026/5/19 2:12:02
1. 分库分表为什么你的PostgreSQL需要它我第一次遇到单表数据爆炸是在一个电商项目中。某天凌晨3点报警短信把我吵醒——订单查询接口响应时间从200ms飙升到8秒。打开监控一看单表数据量已经突破3000万行索引都快撑不住了。这就是典型的单表瓶颈也是分库分表要解决的核心问题。PostgreSQL的分库分表本质上是一种数据分片技术。想象你有一个超大的图书馆数据库当藏书量暴增时管理员会把不同类型的书放到不同楼层垂直分库把同类型书籍按字母顺序分散到多个书架水平分表给每个区域配备专门的图书管理员数据库实例垂直分库就像把电商系统的用户数据、订单数据、商品数据分别存在不同的物理数据库。我最近给一个SaaS平台做架构优化把核心业务表拆分到三个数据库后QPS直接从1500提升到4200。水平分表则更精细比如把2023年的订单放在order_2023表2024年的放在order_2024表。去年帮一个物联网项目处理设备日志按设备ID哈希分表后查询延迟降低了76%。PostgreSQL从11版本开始原生支持三种分区方式范围分区最适合时间序列数据比如CREATE TABLE logs PARTITION BY RANGE (created_at)列表分区适合离散值分类比如按地区PARTITION BY LIST (region)哈希分区能均匀分布热点数据像用户ID这种PARTITION BY HASH (user_id)提示在PG12之前分区表需要手动创建继承关系触发器现在直接用PARTITION BY语法更高效2. PostgreSQL分区实战从建表到优化2.1 创建分区表的正确姿势先看一个时间范围分区的完整案例。假设我们要处理共享单车的骑行记录-- 主表定义不实际存储数据 CREATE TABLE bike_trips ( trip_id BIGSERIAL, bike_number VARCHAR(32) NOT NULL, user_id INT NOT NULL, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ, distance DECIMAL(10,2) ) PARTITION BY RANGE (start_time); -- 按月创建分区 CREATE TABLE bike_trips_202301 PARTITION OF bike_trips FOR VALUES FROM (2023-01-01) TO (2023-02-01); CREATE TABLE bike_trips_202302 PARTITION OF bike_trips FOR VALUES FROM (2023-02-01) TO (2023-03-01);这里有个坑我踩过如果插入2023-03-01的数据会报错因为右边界是开区间。正确的做法是-- 自动创建分区的函数 CREATE OR REPLACE FUNCTION create_bike_trip_partition() RETURNS TRIGGER AS $$ BEGIN EXECUTE format( CREATE TABLE IF NOT EXISTS bike_trips_%s PARTITION OF bike_trips FOR VALUES FROM (%L) TO (%L), to_char(NEW.start_time, YYYYMM), date_trunc(month, NEW.start_time), date_trunc(month, NEW.start_time) interval 1 month ); RETURN NEW; END; $$ LANGUAGE plpgsql; -- 绑定到主表的插入触发器 CREATE TRIGGER bike_trips_partition_trigger BEFORE INSERT ON bike_trips FOR EACH ROW EXECUTE FUNCTION create_bike_trip_partition();2.2 查询优化的关键技巧分区表查询有门道。有次我优化一个慢查询原来是漏了分区键条件-- 慢查询扫描所有分区 SELECT * FROM bike_trips WHERE user_id 1001; -- 优化后只扫描相关分区 SELECT * FROM bike_trips WHERE user_id 1001 AND start_time BETWEEN 2023-01-01 AND 2023-01-31;三个提升性能的必杀技分区裁剪确保WHERE条件包含分区键本地索引每个分区单独建索引CREATE INDEX idx_bike_trips_202301_user ON bike_trips_202301(user_id);约束排除设置CHECK约束帮助优化器ALTER TABLE bike_trips_202301 ADD CONSTRAINT check_202301 CHECK (start_time 2023-01-01 AND start_time 2023-02-01);3. 分库方案设计与数据路由3.1 何时该考虑分库当出现以下情况时单机PostgreSQL可能撑不住了数据量超过服务器物理内存的3倍备份/恢复时间超过业务允许窗口主从复制延迟经常超过30秒去年设计的一个社交平台架构中我们按用户ID范围分库用户库1user_1UID 1-1000万用户库2user_2UID 1000万-2000万每个库部署在独立的EC2实例上3.2 分库路由的三种实现方式应用层路由最灵活我在Spring Boot项目中常用Repository public class UserRepository { // 根据用户ID选择数据源 public DataSource determineDataSource(Long userId) { int dbIndex (int) (userId % 3); return dataSources.get(dbIndex); } }中间件方案如PG的FDW外部数据包装器-- 在主库创建外部表映射 CREATE SERVER user_1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 10.0.0.1, dbname user_1); CREATE SERVER user_2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 10.0.0.2, dbname user_2); -- 创建统一视图 CREATE VIEW all_users AS SELECT * FROM user_1.users UNION ALL SELECT * FROM user_2.users;专业分片工具如Citus-- 安装扩展 CREATE EXTENSION citus; -- 将表定义为分布式表 SELECT create_distributed_table(users, user_id);4. 避坑指南分库分表常见问题4.1 跨分区查询的解决方案遇到需要聚合多分区数据的场景比如统计年度骑行总距离-- 低效做法 SELECT SUM(distance) FROM bike_trips WHERE start_time BETWEEN 2023-01-01 AND 2023-12-31; -- 高效方案并行查询各分区 SELECT SUM(sub.total) FROM ( SELECT SUM(distance) AS total FROM bike_trips_202301 WHERE start_time 2023-01-01 UNION ALL SELECT SUM(distance) FROM bike_trips_202302 UNION ALL ... SELECT SUM(distance) FROM bike_trips_202312 ) sub;4.2 全局唯一ID生成自增序列在分库环境下会冲突我的常用方案-- 使用UUID CREATE TABLE orders ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, ... ); -- 或Snowflake算法实现 CREATE SEQUENCE global_id_seq; CREATE OR REPLACE FUNCTION next_global_id() RETURNS BIGINT AS $$ DECLARE our_epoch BIGINT : 1609459200000; -- 2021-01-01 seq_id BIGINT; now_millis BIGINT; shard_id INT : 1; -- 分片ID BEGIN SELECT nextval(global_id_seq) % 1024 INTO seq_id; SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis; RETURN (now_millis - our_epoch) 23 | shard_id 10 | seq_id; END; $$ LANGUAGE plpgsql;4.3 分布式事务处理对于跨库事务我通常采用最终一致性方案。比如转账场景在事务中插入转账记录状态为处理中通过消息队列异步处理分别更新两个账户的余额标记转账记录为已完成-- 创建事务日志表 CREATE TABLE transaction_log ( id BIGSERIAL PRIMARY KEY, from_account BIGINT, to_account BIGINT, amount DECIMAL(18,2), status VARCHAR(20), created_at TIMESTAMPTZ DEFAULT NOW() ); -- 异步处理任务 CREATE OR REPLACE PROCEDURE process_transfer() LANGUAGE plpgsql AS $$ BEGIN FOR tx IN SELECT * FROM transaction_log WHERE status PENDING LIMIT 100 LOOP BEGIN -- 扣减转出账户 UPDATE account_1 SET balance balance - tx.amount WHERE account_id tx.from_account; -- 增加转入账户 UPDATE account_2 SET balance balance tx.amount WHERE account_id tx.to_account; UPDATE transaction_log SET status COMPLETED WHERE id tx.id; EXCEPTION WHEN OTHERS THEN UPDATE transaction_log SET status FAILED WHERE id tx.id; END; END LOOP; END; $$;5. 性能监控与维护5.1 关键监控指标在我的Grafana看板里这些指标最重要-- 分区表空间使用情况 SELECT schemaname || . || relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_catalog.pg_statio_user_tables WHERE relname LIKE bike_trips%; -- 查询是否命中分区 EXPLAIN ANALYZE SELECT * FROM bike_trips WHERE start_time BETWEEN 2023-01-01 AND 2023-01-02;5.2 分区维护自动化设置定期任务清理旧数据# 每月1号凌晨清理6个月前数据 0 0 1 * * psql -c DROP TABLE IF EXISTS bike_trips_$(date -d 6 months ago %Y%m)对于时间序列数据我常用这个自动创建分区的函数CREATE OR REPLACE FUNCTION create_next_partition() RETURNS VOID AS $$ DECLARE next_month TEXT : to_char(CURRENT_DATE interval 1 month, YYYYMM); next_start TEXT : to_char(date_trunc(month, CURRENT_DATE interval 1 month), YYYY-MM-DD); next_end TEXT : to_char(date_trunc(month, CURRENT_DATE interval 2 month), YYYY-MM-DD); BEGIN EXECUTE format(CREATE TABLE IF NOT EXISTS bike_trips_%s PARTITION OF bike_trips FOR VALUES FROM (%L) TO (%L), next_month, next_start, next_end); END; $$ LANGUAGE plpgsql;把这段代码放进PostgreSQL的定时任务pg_cron里就能实现全自动分区管理。最近给一个智慧园区项目部署这套方案后他们的DBA团队每月节省了至少20小时的手动维护时间。
