PostgreSQL 9.6 水平分库,还差一点点啦

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS SQL Server,基础系列 2核4GB
简介: PostgreSQL 持续在基于fdw的sharding技术上深耕,9.6开始,在符合条件的前提下,支持JOIN和SORT下推到数据节点执行。下面是一个测试创建几个shard库 for subfix in 0 1 2 3 do psql -c "create database db$su

PostgreSQL 持续在基于fdw的sharding技术上深耕,9.6开始,在符合条件的前提下,支持JOIN和SORT下推到数据节点执行。
下面是一个测试
创建几个shard库

for subfix in 0 1 2 3 
do
psql -c "create database db$subfix"
done

创建master库

psql -c "create database master;"
psql master -c "create extension postgres_fdw;"

在master库创建foreign server和user mapping

for subfix in 0 1 2 3 
do
psql master -c "create server db$subfix foreign data wrapper postgres_fdw options (hostaddr 'xxx.xxx.xxx.xxx', dbname 'db$subfix', port '1923');"
psql master -c "create user mapping for postgres server db$subfix options (user 'postgres', password 'postgres');"
done

在shard库创建分片表

for subfix in 0 1 2 3 
do
psql db$subfix -c "drop table if exists tbl; create table tbl(id int primary key, info text)"
psql db$subfix -c "drop table if exists tab; create table tab(id int primary key, info text)"
done

在master库创建foreign 表,并设置约束

for subfix in 0 1 2 3 
do
psql master -c "drop foreign table if exists tbl$subfix ; create foreign table tbl$subfix (id int not null, info text) server db$subfix options (schema_name 'public', table_name 'tbl');"
psql master -c "alter foreign table tbl$subfix add constraint ck1 check (mod(id,4) = $subfix );"

psql master -c "drop foreign table if exists tab$subfix ; create foreign table tab$subfix (id int not null, info text) server db$subfix options (schema_name 'public', table_name 'tab');"
psql master -c "alter foreign table tab$subfix add constraint ck1 check (mod(id,4) = $subfix );"
done

查看

psql master <<EOF
\det
EOF

结果

 List of foreign tables
 Schema | Table | Server 
--------+-------+--------
 public | tab0  | db0
 public | tab1  | db1
 public | tab2  | db2
 public | tab3  | db3
 public | tbl0  | db0
 public | tbl1  | db1
 public | tbl2  | db2
 public | tbl3  | db3
(8 rows)

在master库创建父表

psql master -c "create table tbl(id int primary key, info text);"
psql master -c "create table tab(id int primary key, info text);"

在master库创建foreign表和父表的继承关系

for subfix in 0 1 2 3 
do
psql master -c "alter foreign table tbl$subfix inherit tbl;"
psql master -c "alter foreign table tab$subfix inherit tab;"
done

测试JOIN的下推

master=# explain verbose select * from tbl1,tab1 where tab1.id=tbl1.id and mod(tbl1.id,4)=1;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.00..226.75 rows=48 width=72)
   Output: tbl1.id, tbl1.info, tab1.id, tab1.info
   Relations: (public.tbl1) INNER JOIN (public.tab1)
   Remote SQL: SELECT r1.id, r1.info, r2.id, r2.info FROM (public.tbl r1 INNER JOIN public.tab r2 ON (((r1.id = r2.id)) AND ((mod(r1.id, 4) = 1))))
(4 rows)

目前sort下推需要关闭优化器enable_sort开关才会下推,也是值得改进的地方

master=# set enable_sort=off;
SET
master=# explain verbose select * from tbl1 where mod(id,4)=mod(100,4) order by id;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Foreign Scan on public.tbl1  (cost=100.00..136.71 rows=7 width=36)
   Output: id, info
   Remote SQL: SELECT id, info FROM public.tbl WHERE ((mod(id, 4) = 0)) ORDER BY id ASC NULLS LAST
(3 rows)



还需要改进的地方
这样的查询优化器能优化什么?

  1. 如果要更纯粹的sharding,父表不应该参与计算,只是一个别名而已。因此JOIN 可以根据前提条件下推。
  2. tab.id=tbl.id and mod(tbl.id,4)=1 可以推演出 and mod(tab.id,4)=1 。 因此tab表只需要扫描tab1。
master=# explain verbose select * from tbl,tab where tab.id=tbl.id and mod(tbl.id,4)=1;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..0.00 rows=0 width=0)
   Output: tbl.id, tbl.info, tab.id, tab.info
   Workers Planned: 1
   Single Copy: true
   ->  Hash Join  (cost=130.71..757.17 rows=218 width=72)
         Output: tbl.id, tbl.info, tab.id, tab.info
         Hash Cond: (tab.id = tbl.id)
         ->  Append  (cost=0.00..603.80 rows=5461 width=36)
               ->  Seq Scan on public.tab  (cost=0.00..0.00 rows=1 width=36)
                     Output: tab.id, tab.info
               ->  Foreign Scan on public.tab0  (cost=100.00..150.95 rows=1365 width=36)
                     Output: tab0.id, tab0.info
                     Remote SQL: SELECT id, info FROM public.tab
               ->  Foreign Scan on public.tab1  (cost=100.00..150.95 rows=1365 width=36)
                     Output: tab1.id, tab1.info
                     Remote SQL: SELECT id, info FROM public.tab
               ->  Foreign Scan on public.tab2  (cost=100.00..150.95 rows=1365 width=36)
                     Output: tab2.id, tab2.info
                     Remote SQL: SELECT id, info FROM public.tab
               ->  Foreign Scan on public.tab3  (cost=100.00..150.95 rows=1365 width=36)
                     Output: tab3.id, tab3.info
                     Remote SQL: SELECT id, info FROM public.tab
         ->  Hash  (cost=130.61..130.61 rows=8 width=36)
               Output: tbl.id, tbl.info
               ->  Append  (cost=0.00..130.61 rows=8 width=36)
                     ->  Seq Scan on public.tbl  (cost=0.00..0.00 rows=1 width=36)
                           Output: tbl.id, tbl.info
                           Filter: (mod(tbl.id, 4) = 1)
                     ->  Foreign Scan on public.tbl1  (cost=100.00..130.61 rows=7 width=36)
                           Output: tbl1.id, tbl1.info
                           Remote SQL: SELECT id, info FROM public.tbl WHERE ((mod(id, 4) = 1))
(31 rows)

修改源码,允许删除继承的约束
vi src/backend/commands/tablecmds.c

                /* Don't drop inherited constraints */
//              if (con->coninhcount > 0 && !recursing)
//                      ereport(ERROR,
//                                      (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
//                                       errmsg("cannot drop inherited constraint \"%s\" of relation \"%s\"",
//                                                      constrName, RelationGetRelationName(rel))));

重新编译,重启

make && make install
pg_ctl restart -m fast

添加主表的约束,删除子表的约束,造成主表不被访问的假象。

alter table tab add constraint tab_ck check (mod(id, 4) is null);
alter table tbl add constraint tbl_ck check (mod(id, 4) is null);

alter table only tab0 drop constraint tab_ck;
alter table only tab1 drop constraint tab_ck;
alter table only tab2 drop constraint tab_ck;
alter table only tab3 drop constraint tab_ck;

alter table only tbl0 drop constraint tbl_ck;
alter table only tbl1 drop constraint tbl_ck;
alter table only tbl2 drop constraint tbl_ck;
alter table only tbl3 drop constraint tbl_ck;

我的目标是这样能做到下推JOIN,但实际上没有下推,这个是非常痛苦的。
一个真正的分库中间件应该解决这样的问题。

master=# explain verbose select * from tbl ,tab where tab.id=tbl.id and mod(tbl.id,4)=1 and mod(tab.id,4)=1;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Nested Loop  (cost=200.00..261.98 rows=7 width=72)
   Output: tbl1.id, tbl1.info, tab1.id, tab1.info
   Join Filter: (tbl1.id = tab1.id)
   ->  Append  (cost=100.00..130.61 rows=7 width=36)
         ->  Foreign Scan on public.tbl1  (cost=100.00..130.61 rows=7 width=36)
               Output: tbl1.id, tbl1.info
               Remote SQL: SELECT id, info FROM public.tbl WHERE ((mod(id, 4) = 1))
   ->  Materialize  (cost=100.00..130.65 rows=7 width=36)
         Output: tab1.id, tab1.info
         ->  Append  (cost=100.00..130.61 rows=7 width=36)
               ->  Foreign Scan on public.tab1  (cost=100.00..130.61 rows=7 width=36)
                     Output: tab1.id, tab1.info
                     Remote SQL: SELECT id, info FROM public.tab WHERE ((mod(id, 4) = 1))
(13 rows)

这才是我要的结果

                                                                                  QUERY PLAN                                                                                  
-------------------------
 Foreign Scan  (cost=100.00..161.58 rows=1 width=72)
   Output: tbl.id, tbl.info, tab.id, tab.info
   Relations: (public.tbl1 tbl) INNER JOIN (public.tab1 tab)
   Remote SQL: SELECT r1.id, r1.info, r2.id, r2.info FROM (public.tbl r1 INNER JOIN public.tab r2 ON (((r1.id = r2.id)) AND ((mod(r2.id, 4) = 1)) AND ((mod(r1.id, 4) = 1))))
(4 rows)

小结

  1. 通常应用会以父表作为常用的表,而不是直接访问子表,但是可以要求用户带上分区约束的条件,从而满足下推的排他性。
  2. PG应该允许父表和子表有不一样的约束,从而可以利用排他约束把父表的访问过滤掉。
  3. 父表访问过滤掉之后,子表的JOIN应该可以下推使用。
  4. 复制表的实现和下推?
  5. 在PostgreSQL的shard完美之前,用户应该尽量避免JOIN。
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
5月前
|
关系型数据库 MySQL 中间件
|
2月前
|
SQL 算法 Java
(二十六)MySQL分库篇:Sharding-Sphere分库分表框架的保姆级教学!
前面《MySQL主从原理篇》、《MySQL主从实践篇》两章中聊明白了MySQL主备读写分离、多主多写热备等方案,但如果这些高可用架构依旧无法满足业务规模,或业务增长的需要,此时就需要考虑选用分库分表架构。
1624 2
|
5月前
|
存储 关系型数据库 MySQL
Mysql 分库分区分表
Mysql 分库分区分表
|
关系型数据库 MySQL Java
Mysql集群部署实现主从复制读写分离分表分库 2
Mysql集群部署实现主从复制读写分离分表分库
61 0
|
存储 关系型数据库 MySQL
Mysql集群部署实现主从复制读写分离分表分库 1
Mysql集群部署实现主从复制读写分离分表分库
102 0
|
存储 关系型数据库 MySQL
Mysql分表分库背景知识(2)
Mysql分表分库背景知识(2)
78 0
|
存储 架构师 关系型数据库
Mysql分表分库背景知识(1)
Mysql分表分库背景知识(1)
39 0
|
SQL 运维 关系型数据库
Mysql将现有表进行分表分库
Mysql将现有表进行分表分库
78 0
|
存储 关系型数据库 MySQL
MySQL的水平分库分表
MySQL的水平分库分表
159 0
|
SQL 关系型数据库 MySQL

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版
  • 下一篇
    无影云桌面