如何将PostgreSQL跨多个Docker容器分区并实现数据查询

前提说明 #

两个PostgreSQL Docker容器预设配置如下，后续示例统一用该配置：

• 节点1（存储[a-h]首字母数据）：IP 172.17.0.2，端口5432，库名user_db，账号postgres，密码123456
• 节点2（存储[i-z]首字母数据）：IP 172.17.0.3，端口5432，库名user_db，账号postgres，密码123456

注：你提到的区间存在h值重复的问题，建议调整为[a-h]和[i-z]避免写入冲突，以下示例按调整后的规则实现

实现方案 #

方案1：应用层分片（无额外数据库依赖，性能最优） #

不需要修改数据库配置，直接在业务代码层面做路由判断：

1. 两个节点分别创建业务表，执行以下SQL：

CREATE TABLE user_info (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

2. 写入数据时，先提取name的首字母做路由判断：

• 用lower(left(name, 1))获取小写首字母
• 首字母在a~h区间：调用节点1的连接执行INSERT
• 首字母在i~z区间：调用节点2的连接执行INSERT

方案2：PostgreSQL原生跨实例分区（对业务透明，无需修改代码） #

选任意一个节点作为协调节点，也可以单独搭第三个协调节点，通过FDW（外部数据包装器）+ 声明式分区实现自动路由：

1. 先在两个存储节点执行方案1的建表语句，创建user_info表
2. 在协调节点执行以下配置：

-- 安装FDW扩展
CREATE EXTENSION postgres_fdw;

-- 创建连接两个存储节点的外部服务器
CREATE SERVER pg_node1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '172.17.0.2', port '5432', dbname 'user_db');
CREATE SERVER pg_node2 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '172.17.0.3', port '5432', dbname 'user_db');

-- 创建用户映射，配置访问存储节点的账号密码
CREATE USER MAPPING FOR postgres SERVER pg_node1
OPTIONS (user 'postgres', password '123456');
CREATE USER MAPPING FOR postgres SERVER pg_node2
OPTIONS (user 'postgres', password '123456');

-- 创建分区父表
CREATE TABLE user_info (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
) PARTITION BY RANGE (lower(left(name, 1)));

-- 绑定两个存储节点的外部表作为分区
CREATE FOREIGN TABLE user_info_part_a_h PARTITION OF user_info
FOR VALUES FROM ('a') TO ('i')
SERVER pg_node1;
CREATE FOREIGN TABLE user_info_part_i_z PARTITION OF user_info
FOR VALUES FROM ('i') TO ('{') -- '{'是z的ASCII码后一位，覆盖所有小写字母到z的区间
SERVER pg_node2;

3. 开启分区裁剪参数（默认已开启，确认即可）：

SET enable_partition_pruning = on;

配置完成后，业务直接往协调节点的user_info表写入数据，PostgreSQL会自动按规则路由到对应存储节点。

数据检索方法 #

应用层分片方案检索逻辑 #

• 带name查询条件：先提取查询值的首字母，路由到对应存储节点执行查询，返回结果即可
• 不带name查询条件/模糊查询无法确定首字母：同时向两个存储节点发起查询，将结果合并后返回给业务

原生跨实例分区方案检索逻辑 #

业务直接查询协调节点的user_info表即可：

• 带首字母明确的name条件时，PostgreSQL自动做分区裁剪，只请求对应存储节点的数据
• 无明确首字母条件时，PostgreSQL自动请求两个节点的数据并合并返回，业务无需做额外处理

内容的提问来源于stack exchange，提问作者Mekatoo