首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

利用PostgreSQL外部表实现数据库sharding

作者：书友35194403 | 来源：互联网 | 2023-07-12 12:46
2019独角兽企业重金招聘Python工程师标准前言PostgreSQL9.3开始推出postgres_fdw功能，9.5又实现了外部表可以继承的特性。那么我们

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

前言

PostgreSQL9.3开始推出postgres_fdw功能&＃xff0c;9.5又实现了外部表可以继承的特性。那么我们就可以像实现分区表一样&＃xff0c;利用"外部表继承&＃43;约束&＃43;规则"来实现数据库的sharding。

 

原理

传统的普通表之间的继承特性可以实现数据库级别的表分区&＃xff0c;而外部表可以继承普通表的特性&＃xff0c;则可以实现跨数据库&＃xff0c;甚至跨物理节点的表分区。下图是实现数据库sharding的一个逻辑图。

 

应用

测试场景有4台机器&＃xff0c;一台作为本地机用于管理访问外部节点分区&＃xff0c;另外3台作为存储表分区的机器。

1. 先在3台外部节点机器上&＃xff0c;创建3个表结构相同的普通表&＃xff0c;并添加相关约束。

postgres&＃61;# create table test_partition_127(id int check (id >&＃61; 1 AND id <100), tt text, tm timestamp);
CREATE TABLE

postgres&＃61;# create table test_partition_136(id int check (id >&＃61; 100 AND id <200), tt text, tm timestamp);
CREATE TABLE

postgres&＃61;# create table test_partition_144(id int check (id >&＃61; 200 AND id <300), tt text, tm timestamp);
CREATE TABLE

表名后面的数字127,136,144表示节点IP的后三位。

2. 为了让本地机器可以访问外部节点&＃xff0c;还需要对这3个外部节点的配置文件postgresql.conf&＃xff0c;pg_hba.conf配置一下。

 

下面操作都是在本地机上进行。

3. 先创建postgres_fdw扩展。

postgres&＃61;# create extension postgres_fdw;
CREATE EXTENSION

4. 创建可以映射到外部节点普通表的外部表。

postgres&＃61;# CREATE SERVER server_remote_127 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host &＃39;192.168.100.127&＃39;, port &＃39;5432&＃39;, dbname &＃39;postgres&＃39;);
CREATE SERVER
postgres&＃61;# CREATE USER MAPPING FOR postgres SERVER server_remote_127 OPTIONS(user &＃39;highgo&＃39;);
CREATE USER MAPPING
postgres&＃61;# CREATE FOREIGN TABLE test_partition_foreign_127(id int, tt text, tm timestamp) SERVER server_remote_127 OPTIONS(schema_name &＃39;public&＃39;, table_name &＃39;test_partition_127&＃39;);
CREATE FOREIGN TABLE
postgres&＃61;#
postgres&＃61;# CREATE SERVER server_remote_136 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host &＃39;192.168.100.136&＃39;, port &＃39;5432&＃39;, dbname &＃39;postgres&＃39;);
CREATE SERVER
postgres&＃61;# CREATE USER MAPPING FOR postgres SERVER server_remote_136 OPTIONS(user &＃39;highgo&＃39;);
CREATE USER MAPPING
postgres&＃61;# CREATE FOREIGN TABLE test_partition_foreign_136(id int, tt text, tm timestamp) SERVER server_remote_136 OPTIONS(schema_name &＃39;public&＃39;, table_name &＃39;test_partition_136&＃39;);
CREATE FOREIGN TABLE
postgres&＃61;#
postgres&＃61;# CREATE SERVER server_remote_144 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host &＃39;192.168.100.144&＃39;, port &＃39;5432&＃39;, dbname &＃39;postgres&＃39;);
CREATE SERVER
postgres&＃61;# CREATE USER MAPPING FOR postgres SERVER server_remote_144 OPTIONS(user &＃39;highgo&＃39;);
CREATE USER MAPPING
postgres&＃61;# CREATE FOREIGN TABLE test_partition_foreign_144(id int, tt text, tm timestamp) SERVER server_remote_144 OPTIONS(schema_name &＃39;public&＃39;, table_name &＃39;test_partition_144&＃39;);
CREATE FOREIGN TABLE

5. 创建本地父表&＃xff0c;并让上一步创建的外部表继承于父表。

postgres&＃61;# create table test_master(id int, tt text, tm timestamp);
CREATE TABLE
postgres&＃61;# alter table test_partition_foreign_127 inherit test_master;
ALTER TABLE
postgres&＃61;# alter table test_partition_foreign_136 inherit test_master;
ALTER TABLE
postgres&＃61;# alter table test_partition_foreign_144 inherit test_master;
ALTER TABLE

6. 查看外部表的信息。

postgres&＃61;# \d test_partition_foreign_127引用的外部表 "public.test_partition_foreign_127"栏位 | 类型 | 修饰词 | FDW选项
------&＃43;-----------------------------&＃43;--------&＃43;---------id | integer | | tt | text | | tm | timestamp without time zone | |
Server: server_remote_127
FDW Options: (schema_name &＃39;public&＃39;, table_name &＃39;test_partition_127&＃39;)
继承: test_masterpostgres&＃61;# \d test_partition_foreign_136引用的外部表 "public.test_partition_foreign_136"栏位 | 类型 | 修饰词 | FDW选项
------&＃43;-----------------------------&＃43;--------&＃43;---------id | integer | | tt | text | | tm | timestamp without time zone | |
Server: server_remote_136
FDW Options: (schema_name &＃39;public&＃39;, table_name &＃39;test_partition_136&＃39;)
继承: test_masterpostgres&＃61;# \d test_partition_foreign_144引用的外部表 "public.test_partition_foreign_144"栏位 | 类型 | 修饰词 | FDW选项
------&＃43;-----------------------------&＃43;--------&＃43;---------id | integer | | tt | text | | tm | timestamp without time zone | |
Server: server_remote_144
FDW Options: (schema_name &＃39;public&＃39;, table_name &＃39;test_partition_144&＃39;)
继承: test_master

外部表已经继承了父表test_master。

7. 对父表创建规则&＃xff0c;以便向父表中插入数据时&＃xff0c;数据能分发到外部表中。

postgres&＃61;# CREATE OR REPLACE RULE insert_rule_test_partition_foreign_127 AS ON INSERT TO test_master WHERE id >&＃61; 1 AND id <100 DO INSTEAD INSERT INTO test_partition_foreign_127 VALUES(NEW.*);
CREATE RULE
postgres&＃61;#
postgres&＃61;# CREATE OR REPLACE RULE insert_rule_test_partition_foreign_136 AS ON INSERT TO test_master WHERE id >&＃61; 100 AND id <200 DO INSTEAD INSERT INTO test_partition_foreign_136 VALUES(NEW.*);
CREATE RULE
postgres&＃61;#
postgres&＃61;# CREATE OR REPLACE RULE insert_rule_test_partition_foreign_144 AS ON INSERT TO test_master WHERE id >&＃61; 200 AND id <300 DO INSTEAD INSERT INTO test_partition_foreign_144 VALUES(NEW.*);
CREATE RULE

8. 向父表中插入数据测试。

postgres&＃61;# insert into test_master select generate_series(1, 299), &＃39;highgo&＃39;, now();
INSERT 0 0

9. 查看select父表的执行计划。

postgres&＃61;# explain analyze select * from test_master;QUERY PLAN ---------------------------------------------------------------------------------------------------------
---------------------------------Append (cost&＃61;0.00..438.36 rows&＃61;3613 width&＃61;44) (actual time&＃61;6.364..34.279 rows&＃61;299 loops&＃61;1)-> Seq Scan on test_master (cost&＃61;0.00..0.00 rows&＃61;1 width&＃61;44) (actual time&＃61;0.016..0.016 rows&＃61;0 loops&＃61;
1)-> Foreign Scan on test_partition_foreign_127 (cost&＃61;100.00..146.12 rows&＃61;1204 width&＃61;44) (actual time&＃61;
6.345..6.378 rows&＃61;99 loops&＃61;1)-> Foreign Scan on test_partition_foreign_136 (cost&＃61;100.00..146.12 rows&＃61;1204 width&＃61;44) (actual time&＃61;
21.258..21.707 rows&＃61;100 loops&＃61;1)-> Foreign Scan on test_partition_foreign_144 (cost&＃61;100.00..146.12 rows&＃61;1204 width&＃61;44) (actual time&＃61;
5.327..6.054 rows&＃61;100 loops&＃61;1)Planning time: 0.617 msExecution time: 40.256 ms

10. 查看3个节点的表的数据存储。

postgres&＃61;# select * from test_partition_127 limit 5;id | tt | tm
----&＃43;--------&＃43;----------------------------1 | highgo | 2017-06-26 11:07:11.8169572 | highgo | 2017-06-26 11:07:11.8169573 | highgo | 2017-06-26 11:07:11.8169574 | highgo | 2017-06-26 11:07:11.8169575 | highgo | 2017-06-26 11:07:11.816957
(5 行记录)postgres&＃61;# explain analyze select * from test_partition_127 ;QUERY PLAN
---------------------------------------------------------------------------------------------------------------Seq Scan on test_partition_127 (cost&＃61;0.00..1.99 rows&＃61;99 width&＃61;19) (actual time&＃61;0.026..0.107 rows&＃61;99 loops&＃61;1)Planning time: 0.094 msExecution time: 0.238 ms
(3 行记录)

postgres&＃61;# select * from test_partition_136 limit 5;id | tt | tm
-----&＃43;--------&＃43;----------------------------100 | highgo | 2017-06-26 11:07:11.816957101 | highgo | 2017-06-26 11:07:11.816957102 | highgo | 2017-06-26 11:07:11.816957103 | highgo | 2017-06-26 11:07:11.816957104 | highgo | 2017-06-26 11:07:11.816957
(5 行记录)postgres&＃61;# explain analyze select * from test_partition_136;QUERY PLAN
-----------------------------------------------------------------------------------------------------------------Seq Scan on test_partition_136 (cost&＃61;0.00..2.00 rows&＃61;100 width&＃61;19) (actual time&＃61;0.022..0.067 rows&＃61;100 loops&＃61;1)Planning time: 0.088 msExecution time: 0.152 ms
(3 行记录)

postgres&＃61;# select * from test_partition_144 limit 5;id | tt | tm
-----&＃43;--------&＃43;----------------------------200 | highgo | 2017-06-26 11:07:11.816957201 | highgo | 2017-06-26 11:07:11.816957202 | highgo | 2017-06-26 11:07:11.816957203 | highgo | 2017-06-26 11:07:11.816957204 | highgo | 2017-06-26 11:07:11.816957
(5 行记录)postgres&＃61;# explain analyze select * from test_partition_144;QUERY PLAN
-----------------------------------------------------------------------------------------------------------------Seq Scan on test_partition_144 (cost&＃61;0.00..2.00 rows&＃61;100 width&＃61;19) (actual time&＃61;0.039..0.144 rows&＃61;100 loops&＃61;1)Planning time: 0.083 msExecution time: 0.403 ms
(3 行记录)

插入的数据已经通过规则和外部表&＃xff0c;分发到下面三个节点中去。

 

性能方面

写入方面&＃xff0c;由于采用了规则&＃xff0c;性能自然不会很好。这里重点说一下读的性能&＃xff0c;对父表做一次条件检索。

postgres&＃61;# explain analyze select * from test_master where id &＃61; 123;QUERY PLAN ---------------------------------------------------------------------------------------------------------
--------------------------Append (cost&＃61;0.00..375.51 rows&＃61;19 width&＃61;44) (actual time&＃61;7.614..11.620 rows&＃61;1 loops&＃61;1)-> Seq Scan on test_master (cost&＃61;0.00..0.00 rows&＃61;1 width&＃61;44) (actual time&＃61;0.002..0.002 rows&＃61;0 loops&＃61;
1)Filter: (id &＃61; 123)-> Foreign Scan on test_partition_foreign_127 (cost&＃61;100.00..125.17 rows&＃61;6 width&＃61;44) (actual time&＃61;3.3
59..3.359 rows&＃61;0 loops&＃61;1)-> Foreign Scan on test_partition_foreign_136 (cost&＃61;100.00..125.17 rows&＃61;6 width&＃61;44) (actual time&＃61;4.2
48..4.251 rows&＃61;1 loops&＃61;1)-> Foreign Scan on test_partition_foreign_144 (cost&＃61;100.00..125.17 rows&＃61;6 width&＃61;44) (actual time&＃61;4.0
00..4.000 rows&＃61;0 loops&＃61;1)Planning time: 0.464 msExecution time: 19.298 ms

可以看出&＃xff0c;执行计划是对所有的外部表做的扫描&＃xff0c;性能上肯定会大打折扣。传统的普通表实现的分区可以利用继承表的查询优化&＃xff0c;对主表进行条件检索时&＃xff0c;只对目标子分区进行顺序扫描。所以目前利用外部表实现的数据库的sharding&＃xff0c;查询优化还需要做下优化工作。


转:https://my.oschina.net/tianbing/blog/1031105



推荐阅读
  • string

    Python SQLAlchemy库的使用方法详解

    Python SQLAlchemy库的使用方法详解
    本文详细介绍了Python中使用SQLAlchemy库的方法。首先对SQLAlchemy进行了简介，包括其定义、适用的数据库类型等。然后讨论了SQLAlchemy提供的两种主要使用模式，即SQL表达式语言和ORM。针对不同的需求，给出了选择哪种模式的建议。最后，介绍了连接数据库的方法，包括创建SQLAlchemy引擎和执行SQL语句的接口。 ... [详细]
  • timestamp

    Android 新闻App的本地服务器搭建教程

    本文介绍了在开发Android新闻App时，搭建本地服务器的步骤。通过使用XAMPP软件，可以一键式搭建起开发环境，包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表，并设置相应的属性。最后，给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]
  • timestamp

    logistic回归（线性和非线性）的开发笔记

    本文由编程笔记#小编为大家整理，主要介绍了logistic回归（线性和非线性）相关的知识，包括线性logistic回归的代码和数据集的分布情况。希望对你有一定的参考价值。 ... [详细]
  • js

    VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

    本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]
  • string

    Android开发笔记：使用Picasso加载网络图片等比例缩放

    在Android开发中，使用Picasso库可以实现对网络图片的等比例缩放。本文介绍了使用Picasso库进行图片缩放的方法，并提供了具体的代码实现。通过获取图片的宽高，计算目标宽度和高度，并创建新图实现等比例缩放。 ... [详细]
  • default

    VScode格式化文档换行或不换行的设置方法

    VScode格式化文档换行或不换行的设置方法
    本文介绍了在VScode中设置格式化文档换行或不换行的方法，包括使用插件和修改settings.json文件的内容。详细步骤为：找到settings.json文件，将其中的代码替换为指定的代码。 ... [详细]
  • string

    向QTextEdit拖放文件的方法及实现步骤

    向QTextEdit拖放文件的方法及实现步骤
    本文介绍了在使用QTextEdit时如何实现拖放文件的功能，包括相关的方法和实现步骤。通过重写dragEnterEvent和dropEvent函数，并结合QMimeData和QUrl等类，可以轻松实现向QTextEdit拖放文件的功能。详细的代码实现和说明可以参考本文提供的示例代码。 ... [详细]
  • string

    C#学习教程：在Console中工作但在Windows窗体中不工作的异步代码分享

    本文分享了一个关于在C#中使用异步代码的问题，作者在控制台中运行时代码正常工作，但在Windows窗体中却无法正常工作。作者尝试搜索局域网上的主机，但在窗体中计数器没有减少。文章提供了相关的代码和解决思路。 ... [详细]
  • md5

    开发笔记:加密&json&StringIO模块&BytesIO模块

    开发笔记:加密&json&StringIO模块&BytesIO模块
    篇首语：本文由编程笔记#小编为大家整理，主要介绍了加密&json&StringIO模块&BytesIO模块相关的知识，希望对你有一定的参考价值。一、加密加密 ... [详细]
  • md5

    CSS3选择器的使用方法详解，提高Web开发效率和精准度

    CSS3选择器的使用方法详解，提高Web开发效率和精准度
    本文详细介绍了CSS3新增的选择器方法，包括属性选择器的使用。通过CSS3选择器，可以提高Web开发的效率和精准度，使得查找元素更加方便和快捷。同时，本文还对属性选择器的各种用法进行了详细解释，并给出了相应的代码示例。通过学习本文，读者可以更好地掌握CSS3选择器的使用方法，提升自己的Web开发能力。 ... [详细]
  • match

    android listview OnItemClickListener失效原因

    android listview OnItemClickListener失效原因
    最近在做listview时发现OnItemClickListener失效的问题，经过查找发现是因为button的原因。不仅listitem中存在button会影响OnItemClickListener事件的失效，还会导致单击后listview每个item的背景改变，使得item中的所有有关焦点的事件都失效。本文给出了一个范例来说明这种情况，并提供了解决方法。 ... [详细]
  • search

    用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性

    ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ... [详细]
  • string

    iOS数据库Sqlite的SQL语句分类和常见约束关键字

    本文介绍了iOS数据库Sqlite的SQL语句分类和常见约束关键字。SQL语句分为DDL、DML和DQL三种类型，其中DDL语句用于定义、删除和修改数据表，关键字包括create、drop和alter。常见约束关键字包括if not exists、if exists、primary key、autoincrement、not null和default。此外，还介绍了常见的数据库数据类型，包括integer、text和real。 ... [详细]
  • join

    MyBatis多表查询与动态SQL使用

    MyBatis多表查询与动态SQL使用
    本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]
  • join

    web.py开发web 第八章 Formalchemy 服务端验证方法

    web.py开发web 第八章 Formalchemy 服务端验证方法
    本文介绍了在web.py开发中使用Formalchemy进行服务端表单数据验证的方法。以User表单为例，详细说明了对各字段的验证要求，包括必填、长度限制、唯一性等。同时介绍了如何自定义验证方法来实现验证唯一性和两个密码是否相等的功能。该文提供了相关代码示例。 ... [详细]
author-avatar
书友35194403
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有