PostgreSQL 9.6 sharding + 单元化 (based on postgres_fdw) 最佳实践 - 通用水平分库场景设计与实践

digoal

2016-10-05

postgresql , 9.6 , 水平分库 , sharding , 单元化

本文将以实践为主，定一个小目标，讲解一下如何使用postgres_fdw实现sharding。

单元化则可以结合bdr插件来实现。

我以一个最简单易懂的场景来举例，例如所有的业务表都包含了用户id这个字段，并且业务访问数据时，一定会带上用户id进行访问。

同时用户没有跨用户id的访问需求。

以上设计是大多数公司使用数据库sharding的最佳实践设计。

以典型的关系系统为例，构建一个测试场景，每个表都带有用户id，以用户id哈希取模为分片规则。

1. 用户信息表

2. 日志表

3. 用户正向关系表（user like who）

聚合物化视图

4. 用户反向关系表（who like user）

ddl-1.sql如下

业务需求

初设256个分片，4个datanode，每个datanode存放64个分片。

测试时使用本地环境模拟，真实环境修改为对应的ip地址和端口即可。

segment 1 : 127.0.0.1:5281:db0:role0:pwd , schema_name role0

segment 2 : 127.0.0.1:5281:db1:role1:pwd , schema_name role1

segment 3 : 127.0.0.1:5281:db2:role2:pwd , schema_name role2

segment 4 : 127.0.0.1:5281:db3:role3:pwd , schema_name role3

master 1 : 127.0.0.1:5281:mas1:digoal:pwd , schema_name digoal

master 2 : 127.0.0.1:5281:mas2:digoal:pwd , schema_name digoal

修改pg_hba.conf，确保127.0.0.1使用md5认证

0. 初始化测试环境

1. 初始化segments (datanodes)

初始化segment1

初始化segment2

初始化segment3

初始化segment4

2. 初始化masters (coordinators)

master节点只存储元数据，无状态，完全对称，可以横向或者。

初始化 master_1

导入外部表结构

构建约束

构建继承关系

构建插入触发器

对于业务需要插入数据的表，构建插入触发器，例子使用动态sql，未优化效率。

初始化 master_n

如果元数据发生变更，注意同步master的meta信息。

可以使用逻辑复制的方式，复制出多个一样的master。

1. 所有sql都需要带上分区条件，例如

2. userinfo 增删改查

新增用户、销毁用户、修改用户资料、查询用户资料

3. user_log 增查

新增用户登陆日志、查询用户日志

4. user_membership 增删查

新增用户关系，删除用户关系，查询用户关系

5. user_membership_rev 增删查

新增反向用户关系，删除反向用户关系，查询反向用户关系

6. 物化视图

mv_user_membership 刷新、查询

mv_user_membership_rev 刷新、查询

聚合物化视图主要是提高关系查询效率用的，刷新物化视图，查询物化视图

1. 查看执行计划，sql是否下推到对应节点执行，是否只访问目标数据节点。

以上sql满足业务需求。

对于sql中带的用户新增的函数或其他条件，如果要下推，则必须使用extension来管理这些函数，master与所有的segment都需要创建这些extension，并且在创建server时包含这些extension。

语法见我上一篇讲postgres_fdw的文档。

1. 全局唯一序列

步调一致，起始值不一致。

由于master只存储了meta数据，因此master 都是对等的，如果master成为瓶颈的话，我们可以新增master节点，上层使用负载均衡的方式连接多个master即可。

通常master不会成为瓶颈，因为master不参与计算，对于9.6来说，单节点的master已经可以处理百万级别的qps(基于pk的查询)。

1. 外部表暂时不支持truncate

2. 外部表暂时不支持透传upsert

简单场景未包含需要在多个segment之间复制的维度表，小表。

下一篇将为大家介绍

二、复杂场景设计

三、单元化

<a href="http://info.flagcounter.com/h9v1">count</a>