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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

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

作者

digoal

日期

2016-10-05

标签

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


背景

20161004_01.md这篇文档讲解了PostgreSQL postgres_fdw的用法以及9.6的增强。

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

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

一、通用场景设计

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

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

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

目标场景

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

1. 用户信息表

create table userinfo(uid int8 primary key, info text, crt_time timestamp);

2. 日志表

create table user_log(uid int8, logintime timestamp, logouttime timestamp, client_addr inet, client_port int);

3. 用户正向关系表(user like who)

create table user_membership(uid1 int8, uid2 int8, unique (uid1,uid2) );

聚合物化视图

create materialized view mv_user_membership as select uid1,array_agg(uid2) uid2s from user_membership group by uid1;
create unique index idx_mv_user_membership_1 on mv_user_membership (uid1);
refresh materialized view CONCURRENTLY mv_user_membership with data;

4. 用户反向关系表(who like user)

create table user_membership_rev(uid2 int8, uid1 int8, unique (uid2,uid1) );

聚合物化视图

create materialized view mv_user_membership_rev as select uid2,array_agg(uid1) uid1s from user_membership_rev group by uid2;
create unique index idx_mv_user_membership_rev_1 on mv_user_membership_rev (uid2);
refresh materialized view CONCURRENTLY mv_user_membership_rev with data;

ddl-1.sql如下

create table userinfo(uid int8 primary key, info text, crt_time timestamp);

create table user_log(uid int8, logintime timestamp, logouttime timestamp, client_addr inet, client_port int);

create table user_membership(uid1 int8, uid2 int8, unique (uid1,uid2) );

create materialized view mv_user_membership as select uid1,array_agg(uid2) uid2s from user_membership group by uid1;
create unique index idx_mv_user_membership_1 on mv_user_membership (uid1);
refresh materialized view CONCURRENTLY mv_user_membership with data;

create table user_membership_rev(uid2 int8, uid1 int8, unique (uid2,uid1) );

create materialized view mv_user_membership_rev as select uid2,array_agg(uid1) uid1s from user_membership_rev group by uid2;
create unique index idx_mv_user_membership_rev_1 on mv_user_membership_rev (uid2);
refresh materialized view CONCURRENTLY mv_user_membership_rev with data;

业务需求

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

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

user_membership 增删查
新增用户关系,删除用户关系,查询用户关系

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

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

构建测试环境

初设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. 初始化测试环境

create role role0 nosuperuser login encrypted password 'pwd';
create role role1 nosuperuser login encrypted password 'pwd';
create role role2 nosuperuser login encrypted password 'pwd';
create role role3 nosuperuser login encrypted password 'pwd';
create role digoal nosuperuser login encrypted password 'pwd';

create database db0 with template template0 encoding 'UTF8';
create database db1 with template template0 encoding 'UTF8';
create database db2 with template template0 encoding 'UTF8';
create database db3 with template template0 encoding 'UTF8';
create database mas1 with template template0 encoding 'UTF8';
create database mas2 with template template0 encoding 'UTF8';

grant all on database db0 to role0;
grant all on database db1 to role1;
grant all on database db2 to role2;
grant all on database db3 to role3;
grant all on database mas1 to digoal;
grant all on database mas2 to digoal;

\c db0 role0
create schema role0;
\c db1 role1
create schema role1;
\c db2 role2
create schema role2;
\c db3 role3
create schema role3;
\c mas1 digoal
create schema digoal;
\c mas2 digoal
create schema digoal;

\c mas1 postgres
create extension postgres_fdw;
grant usage on foreign data wrapper postgres_fdw to digoal;
\c mas2 postgres
create extension postgres_fdw;
grant usage on foreign data wrapper postgres_fdw to digoal;

1. 初始化segments (datanodes)

初始化segment1

\c db0 role0

执行 ddl-1.sql  

然后执行 :   
do language plpgsql $$
declare
  i int;
  mod int := 64;
  nod int := 0;
  sql text;
begin
  for i in (mod*nod)..(mod*(nod+1)-1) loop
      sql := 'create table userinfo_'||i||'(like userinfo including all, check(mod(abs(uid), 256) = '||i||' ))';
      execute sql;
      sql := 'create table user_log_'||i||'(like user_log including all, check(mod(abs(uid), 256) = '||i||' ))';
      execute sql;
      sql := 'create table user_membership_'||i||'(like user_membership including all, check(mod(abs(uid1), 256) = '||i||' ))';
      execute sql;
      sql := 'create table user_membership_rev_'||i||'(like user_membership_rev including all, check(mod(abs(uid2), 256) = '||i||' ))';
      execute sql;
      sql := 'create materialized view mv_user_membership_'||i||' as select uid1,array_agg(uid2) uid2s from user_membership_'||i||' group by uid1';
      execute sql;
      sql := 'create unique index idx_mv_user_membership_'||i||'_1 on mv_user_membership_'||i||' (uid1)';
      execute sql;
      sql := 'create materialized view mv_user_membership_rev_'||i||' as select uid2,array_agg(uid1) uid1s from user_membership_rev_'||i||' group by uid2';
      execute sql;
      sql := 'create unique index idx_mv_user_membership_rev_'||i||'_1 on mv_user_membership_rev_'||i||' (uid2)';
      execute sql;
  end loop;
end;
$$;

create or replace function refresh_mv(nsp name, mvname name) returns void as $$
declare
begin
  execute 'refresh materialized view CONCURRENTLY '||quote_ident($1)||'.'||quote_ident($2)||' with data';
end;
$$ language plpgsql strict;

create view v_refresh_mv0 as 
select refresh_mv('role0'::name, mvname::name)::text from 
( 
select id, 'mv_user_membership_'||id mvname from generate_series(0,255) t(id) 
union all 
select id, 'mv_user_membership_rev_'||id mvname from generate_series(0,255) t(id) 
) t 
where mod(abs(id), 256) >= 0 and mod(abs(id), 256) <= 63;

初始化segment2

\c db1 role1

执行 ddl-1.sql  

修改为
  nod int := 1;

修改为
create view v_refresh_mv1 as 
select refresh_mv('role1'::name, mvname::name)::text from 
( 
select id, 'mv_user_membership_'||id mvname from generate_series(0,255) t(id) 
union all 
select id, 'mv_user_membership_rev_'||id mvname from generate_series(0,255) t(id) 
) t 
where mod(abs(id), 256) >= 64 and mod(abs(id), 256) <= 127;

初始化segment3

\c db2 role2

执行 ddl-1.sql  

修改为
  nod int := 2;

修改为
create view v_refresh_mv2 as 
select refresh_mv('role2'::name, mvname::name)::text from 
( 
select id, 'mv_user_membership_'||id mvname from generate_series(0,255) t(id) 
union all 
select id, 'mv_user_membership_rev_'||id mvname from generate_series(0,255) t(id) 
) t 
where mod(abs(id), 256) >= 128 and mod(abs(id), 256) <= 191;

初始化segment4

\c db3 role3

执行 ddl-1.sql  

修改为
  nod int := 3;

修改为
create view v_refresh_mv3 as 
select refresh_mv('role3'::name, mvname::name)::text from 
( 
select id, 'mv_user_membership_'||id mvname from generate_series(0,255) t(id) 
union all 
select id, 'mv_user_membership_rev_'||id mvname from generate_series(0,255) t(id) 
) t 
where mod(abs(id), 256) >= 192 and mod(abs(id), 256) <= 255;

2. 初始化masters (coordinators)
master节点只存储元数据,无状态,完全对称,可以横向或者。

初始化 master_1

导入外部表结构

\c mas1 digoal

create server db0 foreign data wrapper postgres_fdw OPTIONS (host '127.0.0.1', port '5281', dbname 'db0', use_remote_estimate 'true');
create server db1 foreign data wrapper postgres_fdw OPTIONS (host '127.0.0.1', port '5281', dbname 'db1', use_remote_estimate 'true');
create server db2 foreign data wrapper postgres_fdw OPTIONS (host '127.0.0.1', port '5281', dbname 'db2', use_remote_estimate 'true');
create server db3 foreign data wrapper postgres_fdw OPTIONS (host '127.0.0.1', port '5281', dbname 'db3', use_remote_estimate 'true');

create user mapping FOR digoal SERVER db0 options (user 'role0', password 'pwd');
create user mapping FOR digoal SERVER db1 options (user 'role1', password 'pwd');
create user mapping FOR digoal SERVER db2 options (user 'role2', password 'pwd');
create user mapping FOR digoal SERVER db3 options (user 'role3', password 'pwd');

import foreign schema role0 EXCEPT (userinfo,user_log,user_membership,mv_user_membership,user_membership_rev,mv_user_membership_rev) from server db0 into digoal;
import foreign schema role1 EXCEPT (userinfo,user_log,user_membership,mv_user_membership,user_membership_rev,mv_user_membership_rev) from server db1 into digoal;
import foreign schema role2 EXCEPT (userinfo,user_log,user_membership,mv_user_membership,user_membership_rev,mv_user_membership_rev) from server db2 into digoal;
import foreign schema role3 EXCEPT (userinfo,user_log,user_membership,mv_user_membership,user_membership_rev,mv_user_membership_rev) from server db3 into digoal;

构建约束

do language plpgsql $$
declare
  i int;
  x int := 0;
  y int := 255;
  sql text;
begin
  for i in x..y loop
      sql := 'alter foreign table userinfo_'||i||' add constraint ck_userinfo_'||i||' check(mod(abs(uid), 256) = '||i||' )';
      execute sql;
      sql := 'alter foreign table user_log_'||i||' add constraint ck_user_log_'||i||' check(mod(abs(uid), 256) = '||i||' )';
      execute sql;
      sql := 'alter foreign table user_membership_'||i||' add constraint ck_user_membership_'||i||' check(mod(abs(uid1), 256) = '||i||' )';
      execute sql;
      sql := 'alter foreign table user_membership_rev_'||i||' add constraint ck_user_membership_rev_'||i||' check(mod(abs(uid2), 256) = '||i||' )';
      execute sql;
      sql := 'alter foreign table mv_user_membership_'||i||' add constraint ck_mv_user_membership_'||i||' check(mod(abs(uid1), 256) = '||i||' )';
      execute sql;
      sql := 'alter foreign table mv_user_membership_rev_'||i||' add constraint ck_mv_user_membership_rev_'||i||' check(mod(abs(uid2), 256) = '||i||' )';
      execute sql;
  end loop;
end;
$$;

构建继承关系

create table userinfo(uid int8 primary key, info text, crt_time timestamp);

create table user_log(uid int8, logintime timestamp, logouttime timestamp, client_addr inet, client_port int);

create table user_membership(uid1 int8, uid2 int8, unique (uid1,uid2) );

create table mv_user_membership (uid1 int8, uid2s int8[]);

create table user_membership_rev(uid2 int8, uid1 int8, unique (uid2,uid1) );

create table mv_user_membership_rev (uid2 int8, uid1s int8[]);
do language plpgsql $$
declare
  i int;
  x int := 0;
  y int := 255;
  sql text;
begin
  for i in x..y loop
      sql := 'alter foreign table userinfo_'||i||' inherit userinfo';
      execute sql;
      sql := 'alter foreign table user_log_'||i||' inherit user_log';
      execute sql;
      sql := 'alter foreign table user_membership_'||i||' inherit user_membership';
      execute sql;
      sql := 'alter foreign table user_membership_rev_'||i||' inherit user_membership_rev';
      execute sql;
      sql := 'alter foreign table mv_user_membership_'||i||' inherit mv_user_membership';
      execute sql;
      sql := 'alter foreign table mv_user_membership_rev_'||i||' inherit mv_user_membership_rev';
      execute sql;
  end loop;
end;
$$;

构建插入触发器

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

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

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

user_membership 增删查
新增用户关系,删除用户关系,查询用户关系

user_membership_rev 增删查
新增反向用户关系,删除反向用户关系,查询反向用户关系
create or replace function tg_userinfo_ins() returns trigger as $$
declare
  i int;
begin
  i := mod(abs(NEW.uid), 256);
  execute 'insert into userinfo_'||i||'(uid,info,crt_time) values ('||quote_nullable(NEW.uid)||','||quote_nullable(NEW.info)||','||quote_nullable(NEW.crt_time)||')';
  return null;
end;
$$ language plpgsql strict;

create or replace function tg_user_log_ins() returns trigger as $$
declare
  i int;
begin
  i := mod(abs(NEW.uid), 256);
  execute 'insert into user_log_'||i||'(uid,logintime,logouttime,client_addr,client_port) values ('||quote_nullable(NEW.uid)||','||quote_nullable(NEW.logintime)||','||quote_nullable(NEW.logouttime)||','||quote_nullable(NEW.client_addr)||','||quote_nullable(NEW.client_port)||')';
  return null;
end;
$$ language plpgsql strict;

create or replace function tg_user_membership_ins() returns trigger as $$
declare
  i int;
begin
  i := mod(abs(NEW.uid1), 256);
  execute 'insert into user_membership_'||i||'(uid1,uid2) values ('||quote_nullable(NEW.uid1)||','||quote_nullable(NEW.uid2)||')';
  return null;
end;
$$ language plpgsql strict;

create or replace function tg_user_membership_rev_ins() returns trigger as $$
declare
  i int;
begin
  i := mod(abs(NEW.uid2), 256);
  execute 'insert into user_membership_rev_'||i||'(uid2,uid1) values ('||quote_nullable(NEW.uid2)||','||quote_nullable(NEW.uid1)||')';
  return null;
end;
$$ language plpgsql strict;
create trigger tg_ins before insert on userinfo for each row execute procedure tg_userinfo_ins();
create trigger tg_ins before insert on user_log for each row execute procedure tg_user_log_ins();
create trigger tg_ins before insert on user_membership for each row execute procedure tg_user_membership_ins();
create trigger tg_ins before insert on user_membership_rev for each row execute procedure tg_user_membership_rev_ins();

初始化 master_n

从mas1克隆一个结构即可  

pg_dump -s -d mas1 -U digoal | psql -U postgres -d mas2 -f -  

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

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

初始化测试数据

\c mas1 digoal

-- 生成用户信息
insert into userinfo select generate_series(1,100000), md5(random()::text), now();

-- 生成用户日志信息
insert into user_log select uid,now(),null,null,null from userinfo;

-- 生成uid1喜欢uid2信息
insert into user_membership select uid1, uid2 from (select trunc(100000*random()) uid1,trunc(100000*random()) uid2 from generate_series(1,100000)) t where uid1<>uid2 group by 1,2; 

-- 生成uid2被uid1喜欢信息
insert into user_membership_rev select uid2, uid1 from user_membership; 

-- 刷新物化视图
select count(*) from v_refresh_mv0 union all select count(*) from v_refresh_mv1 union all select count(*) from v_refresh_mv2 union all select count(*) from v_refresh_mv3 ;

业务SQL 设计

1. 所有SQL都需要带上分区条件,例如

where uid=? and mod(abs(uid), 256) = mod(abs(?), 256);

where uid1=? and mod(abs(uid1), 256) = mod(abs(?), 256);

where uid2=? and mod(abs(uid2), 256) = mod(abs(?), 256);

2. userinfo 增删改查
新增用户、销毁用户、修改用户资料、查询用户资料

insert into userinfo(uid,info,text) values (?,?,?);

delete from userinfo where uid=? and mod(abs(uid), 256) = mod(abs(?), 256);

update userinfo set xxx=xxx where uid=? and mod(abs(uid), 256) = mod(abs(?), 256);

select x,x,x from userinfo where uid=? and mod(abs(uid), 256) = mod(abs(?), 256);

3. user_log 增查
新增用户登陆日志、查询用户日志

insert into user_log(uid,logintime,logouttime,client_addr,client_port) values (?,?,?,?,?);

select x,x,x,x,x from user_log where uid=? and mod(abs(uid), 256) = mod(abs(?), 256);

4. user_membership 增删查
新增用户关系,删除用户关系,查询用户关系

insert into user_membership(uid1, uid2) values (?,?);

delete from user_membership where uid1=? and mod(abs(uid1), 256) = mod(abs(?), 256);

select x,x from user_membership where uid1=? and mod(abs(uid1), 256) = mod(abs(?), 256);

5. user_membership_rev 增删查
新增反向用户关系,删除反向用户关系,查询反向用户关系

insert into user_membership_rev(uid2, uid1) values (?,?);

delete from user_membership_rev where uid2=? and mod(abs(uid2), 256) = mod(abs(?), 256);

select x,x from user_membership_rev where uid2=? and mod(abs(uid2), 256) = mod(abs(?), 256);

6. 物化视图
mv_user_membership 刷新、查询
mv_user_membership_rev 刷新、查询
聚合物化视图主要是提高关系查询效率用的,刷新物化视图,查询物化视图

select count(*) from v_refresh_mv0 union all select count(*) from v_refresh_mv1 union all select count(*) from v_refresh_mv2 union all select count(*) from v_refresh_mv3 ;

select x,x from mv_user_membership where uid1=? and mod(abs(uid1), 256) = mod(abs(?), 256);

select x,x from mv_user_membership_rev where uid2=? and mod(abs(uid2), 256) = mod(abs(?), 256);

执行计划查看

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

查询
mas1=> explain verbose select * from userinfo where uid=100 and mod(abs(uid), 256) = mod(abs(100), 256);
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..102.19 rows=2 width=48)
   ->  Seq Scan on digoal.userinfo  (cost=0.00..0.00 rows=1 width=48)
         Output: userinfo.uid, userinfo.info, userinfo.crt_time
         Filter: ((userinfo.uid = 100) AND (mod(abs(userinfo.uid), '256'::bigint) = 100))
   ->  Foreign Scan on digoal.userinfo_100  (cost=100.15..102.19 rows=1 width=49)
         Output: userinfo_100.uid, userinfo_100.info, userinfo_100.crt_time
         Remote SQL: SELECT uid, info, crt_time FROM role1.userinfo_100 WHERE ((uid = 100)) AND ((mod(abs(uid), 256::bigint) = 100))
(7 rows)

排序
mas1=> explain verbose select * from user_membership where uid1=100 and mod(abs(uid1), 256) = mod(abs(100), 256) order by uid2;
                                                                               QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=102.33..102.34 rows=2 width=16)
   Output: user_membership.uid1, user_membership.uid2
   Sort Key: user_membership.uid2
   ->  Append  (cost=0.00..102.32 rows=2 width=16)
         ->  Seq Scan on digoal.user_membership  (cost=0.00..0.00 rows=1 width=16)
               Output: user_membership.uid1, user_membership.uid2
               Filter: ((user_membership.uid1 = 100) AND (mod(abs(user_membership.uid1), '256'::bigint) = 100))
         ->  Foreign Scan on digoal.user_membership_100  (cost=100.27..102.32 rows=1 width=16)
               Output: user_membership_100.uid1, user_membership_100.uid2
               Remote SQL: SELECT uid1, uid2 FROM role1.user_membership_100 WHERE ((uid1 = 100)) AND ((mod(abs(uid1), 256::bigint) = 100)) ORDER BY uid2 ASC NULLS LAST
(10 rows)

更新
mas1=> explain verbose update userinfo set info='new' where uid=100 and mod(abs(uid), 256) = mod(abs(100), 256);
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Update on digoal.userinfo  (cost=0.00..102.20 rows=2 width=54)
   Update on digoal.userinfo
   Foreign Update on digoal.userinfo_100
   ->  Seq Scan on digoal.userinfo  (cost=0.00..0.00 rows=1 width=54)
         Output: userinfo.uid, 'new'::text, userinfo.crt_time, userinfo.ctid
         Filter: ((userinfo.uid = 100) AND (mod(abs(userinfo.uid), '256'::bigint) = 100))
   ->  Foreign Update on digoal.userinfo_100  (cost=100.15..102.20 rows=1 width=54)
         Remote SQL: UPDATE role1.userinfo_100 SET info = 'new'::text WHERE ((uid = 100)) AND ((mod(abs(uid), 256::bigint) = 100))
(8 rows)

删除
mas1=> explain verbose delete from user_membership where uid1=1001 and mod(abs(uid1), 256) = mod(abs(1001), 256);
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Delete on digoal.user_membership  (cost=0.00..102.33 rows=2 width=6)
   Delete on digoal.user_membership
   Foreign Delete on digoal.user_membership_233
   ->  Seq Scan on digoal.user_membership  (cost=0.00..0.00 rows=1 width=6)
         Output: user_membership.ctid
         Filter: ((user_membership.uid1 = 1001) AND (mod(abs(user_membership.uid1), '256'::bigint) = 233))
   ->  Foreign Delete on digoal.user_membership_233  (cost=100.27..102.33 rows=1 width=6)
         Remote SQL: DELETE FROM role3.user_membership_233 WHERE ((uid1 = 1001)) AND ((mod(abs(uid1), 256::bigint) = 233))
(8 rows)

JOIN
mas1=> explain verbose select * from userinfo t1 join user_membership t2 on (t1.uid=t2.uid1) 
where 
t2.uid1=1001 and mod(abs(t2.uid1), 256) = mod(abs(1001), 256) 
and 
t1.uid=1001 and mod(abs(t1.uid), 256) = mod(abs(1001), 256);
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..204.57 rows=4 width=64)
   Output: t1.uid, t1.info, t1.crt_time, t2.uid1, t2.uid2
   ->  Append  (cost=0.00..102.19 rows=2 width=48)
         ->  Seq Scan on digoal.userinfo t1  (cost=0.00..0.00 rows=1 width=48)
               Output: t1.uid, t1.info, t1.crt_time
               Filter: ((t1.uid = 1001) AND (mod(abs(t1.uid), '256'::bigint) = 233))
         ->  Foreign Scan on digoal.userinfo_233 t1_1  (cost=100.15..102.19 rows=1 width=49)
               Output: t1_1.uid, t1_1.info, t1_1.crt_time
               Remote SQL: SELECT uid, info, crt_time FROM role3.userinfo_233 WHERE ((uid = 1001)) AND ((mod(abs(uid), 256::bigint) = 233))
   ->  Materialize  (cost=0.00..102.33 rows=2 width=16)
         Output: t2.uid1, t2.uid2
         ->  Append  (cost=0.00..102.32 rows=2 width=16)
               ->  Seq Scan on digoal.user_membership t2  (cost=0.00..0.00 rows=1 width=16)
                     Output: t2.uid1, t2.uid2
                     Filter: ((t2.uid1 = 1001) AND (mod(abs(t2.uid1), '256'::bigint) = 233))
               ->  Foreign Scan on digoal.user_membership_233 t2_1  (cost=100.27..102.32 rows=1 width=16)
                     Output: t2_1.uid1, t2_1.uid2
                     Remote SQL: SELECT uid1, uid2 FROM role3.user_membership_233 WHERE ((uid1 = 1001)) AND ((mod(abs(uid1), 256::bigint) = 233))
(18 rows)

以上SQL满足业务需求。

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

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

全局约束设计

1. 全局唯一序列

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

create sequence seq increment by 10000 start with 0;
create sequence seq increment by 10000 start with 1;
create sequence seq increment by 10000 start with 2;
......
create sequence seq increment by 10000 start with 255;

master横向扩展

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

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

pic1

fdw的一些使用限制

1. 外部表暂时不支持truncate

mas1=> truncate userinfo ;
ERROR:  42809: "userinfo_0" is not a table
LOCATION:  truncate_check_rel, tablecmds.c:1300
Time: 5.950 ms

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

mas1=> insert into userinfo values (1,'test',now()) on conflict (uid) do update set info=excluded.info;
ERROR:  23505: duplicate key value violates unique constraint "userinfo_1_pkey"
DETAIL:  Key (uid)=(1) already exists.
CONTEXT:  Remote SQL command: INSERT INTO role0.userinfo_1(uid, info, crt_time) VALUES ($1, $2, $3)
SQL statement "insert into userinfo_1(uid,info,crt_time) values ('1','test','2016-10-05 16:50:15.794812')"
PL/pgSQL function tg_userinfo_ins() line 6 at EXECUTE
LOCATION:  pgfdw_report_error, connection.c:574
Time: 0.931 ms

3. 目前没有办法过滤主表的扫描(除非使用 https://github.com/postgrespro/pg_pathman/ 插件)

mas1=> explain verbose select * from user_membership where uid1=100 and mod(abs(uid1), 256) = mod(abs(100), 256) order by uid2;
                                                                               QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=102.33..102.34 rows=2 width=16)
   Output: user_membership.uid1, user_membership.uid2
   Sort Key: user_membership.uid2
   ->  Append  (cost=0.00..102.32 rows=2 width=16)
         ->  Seq Scan on digoal.user_membership  (cost=0.00..0.00 rows=1 width=16)
               Output: user_membership.uid1, user_membership.uid2
               Filter: ((user_membership.uid1 = 100) AND (mod(abs(user_membership.uid1), '256'::bigint) = 100))
         ->  Foreign Scan on digoal.user_membership_100  (cost=100.27..102.32 rows=1 width=16)
               Output: user_membership_100.uid1, user_membership_100.uid2
               Remote SQL: SELECT uid1, uid2 FROM role1.user_membership_100 WHERE ((uid1 = 100)) AND ((mod(abs(uid1), 256::bigint) = 100)) ORDER BY uid2 ASC NULLS LAST
(10 rows)

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

下一篇将为大家介绍

二、复杂场景设计

三、单元化

负载均衡  

对等  

单元化  

大表,水平拆分  

小表,但是需要与其他表JOIN操作,逻辑复制   

维度表,复制  

一些单元化相关的表,可以垂直拆分或隔离,按单元拆分  

同时需要考虑全局约束,序列步调等  

还将包括,复制怎么做?  

如何避免环路,如何解决冲突。    

Count

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
6月前
|
自然语言处理 关系型数据库 数据库
技术经验解读:【转】PostgreSQL的FTI(TSearch)与中文全文索引的实践
技术经验解读:【转】PostgreSQL的FTI(TSearch)与中文全文索引的实践
71 0
|
7月前
|
弹性计算 关系型数据库 数据库
开源PostgreSQL在倚天ECS上的最佳优化实践
本文基于倚天ECS硬件平台,以自顶向下的方式从上层应用、到基础软件,再到底层芯片硬件,通过应用与芯片的硬件特性的亲和性分析,实现PostgreSQL与倚天芯片软硬协同的深度优化,充分使能倚天硬件性能,帮助开源PostgreSQL应用实现性能提升。
|
7月前
|
SQL 运维 关系型数据库
基于AnalyticDB PostgreSQL的实时物化视图研发实践
AnalyticDB PostgreSQL版提供了实时物化视图功能,相较于普通(非实时)物化视图,实时物化视图无需手动调用刷新命令,即可实现数据更新时自动同步刷新物化视图。当基表发生变化时,构建在基表上的实时物化视图将会自动更新。AnalyticDB PostgreSQL企业数据智能平台是构建数据智能的全流程平台,提供可视化实时任务开发 + 实时数据洞察,让您轻松平移离线任务,使用SQL和简单配置即可完成整个实时数仓的搭建。
144002 8
|
7月前
|
SQL 关系型数据库 MySQL
MySQL【实践 02】MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享(通过bat命令修改mapper文件内的SQL语法)
MySQL【实践 02】MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享(通过bat命令修改mapper文件内的SQL语法)
269 0
|
关系型数据库 测试技术 分布式数据库
PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
在电商业务中可能涉及这样的场景, 由于有上下游关系的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理. 如果是高并发的处理, 因为大家都按一个顺序获取, 容易产生热点, 可能遇到取出队列遇到锁冲突瓶颈、IO扫描浪费、CPU计算浪费的瓶颈. 以及在清除已处理订单后, 索引版本未及时清理导致的回表版本判断带来的IO浪费和CPU运算浪费瓶颈等. 本文将给出“队列处理业务的数据库性能优化”优化方法和demo演示. 性能提升10到20倍.
840 4
|
存储 关系型数据库 MySQL
MySQL的水平分库分表
MySQL的水平分库分表
214 0
|
存储 对象存储 块存储
|
SQL 存储 DataWorks
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(上)——一、产品概述
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(上)——一、产品概述
|
SQL 存储 Cloud Native
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(上)——二、产品架构及原理
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(上)——二、产品架构及原理
|
存储 算法 Cloud Native
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(上)——三、产品相关概念(上)
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(上)——三、产品相关概念(上)

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版