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

最新推荐文章于 2025-04-22 19:10:02 发布

weixin_34192816

最新推荐文章于 2025-04-22 19:10:02 发布

阅读量446

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/tianbing/blog/1031105

本文介绍了如何利用PostgreSQL的postgres_fdw功能和外部表继承特性实现跨数据库的分片（sharding）。通过创建外部表、定义继承关系和规则，将数据分散到多个物理节点，从而实现数据库的水平扩展。然而，这种方法在查询性能上存在不足，全表扫描可能导致性能下降，需要进一步的查询优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

前言

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

原理

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

应用

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

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

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

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

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

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

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

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

3. 先创建postgres_fdw扩展。

postgres=# create extension postgres_fdw;
CREATE EXTENSION

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

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

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

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

6. 查看外部表的信息。

postgres=# \d test_partition_foreign_127
   引用的外部表 "public.test_partition_foreign_127"
 栏位 |            类型             | 修饰词 | FDW选项 
------+-----------------------------+--------+---------
 id   | integer                     |        | 
 tt   | text                        |        | 
 tm   | timestamp without time zone |        | 
Server: server_remote_127
FDW Options: (schema_name 'public', table_name 'test_partition_127')
继承: test_master

postgres=# \d test_partition_foreign_136
   引用的外部表 "public.test_partition_foreign_136"
 栏位 |            类型             | 修饰词 | FDW选项 
------+-----------------------------+--------+---------
 id   | integer                     |        | 
 tt   | text                        |        | 
 tm   | timestamp without time zone |        | 
Server: server_remote_136
FDW Options: (schema_name 'public', table_name 'test_partition_136')
继承: test_master

postgres=# \d test_partition_foreign_144
   引用的外部表 "public.test_partition_foreign_144"
 栏位 |            类型             | 修饰词 | FDW选项 
------+-----------------------------+--------+---------
 id   | integer                     |        | 
 tt   | text                        |        | 
 tm   | timestamp without time zone |        | 
Server: server_remote_144
FDW Options: (schema_name 'public', table_name 'test_partition_144')
继承: test_master

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

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

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

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

postgres=# insert into test_master select generate_series(1, 299), 'highgo', now();
INSERT 0 0

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

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

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

postgres=# select * from test_partition_127 limit 5;
 id |   tt   |             tm             
----+--------+----------------------------
  1 | highgo | 2017-06-26 11:07:11.816957
  2 | highgo | 2017-06-26 11:07:11.816957
  3 | highgo | 2017-06-26 11:07:11.816957
  4 | highgo | 2017-06-26 11:07:11.816957
  5 | highgo | 2017-06-26 11:07:11.816957
(5 行记录)

postgres=# explain analyze select * from test_partition_127 ;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on test_partition_127  (cost=0.00..1.99 rows=99 width=19) (actual time=0.026..0.107 rows=99 loops=1)
 Planning time: 0.094 ms
 Execution time: 0.238 ms
(3 行记录)

postgres=# select * from test_partition_136 limit 5;
 id  |   tt   |             tm             
-----+--------+----------------------------
 100 | highgo | 2017-06-26 11:07:11.816957
 101 | highgo | 2017-06-26 11:07:11.816957
 102 | highgo | 2017-06-26 11:07:11.816957
 103 | highgo | 2017-06-26 11:07:11.816957
 104 | highgo | 2017-06-26 11:07:11.816957
(5 行记录)

postgres=# explain analyze select * from test_partition_136;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on test_partition_136  (cost=0.00..2.00 rows=100 width=19) (actual time=0.022..0.067 rows=100 loops=1)
 Planning time: 0.088 ms
 Execution time: 0.152 ms
(3 行记录)

postgres=# select * from test_partition_144 limit 5;
 id  |   tt   |             tm             
-----+--------+----------------------------
 200 | highgo | 2017-06-26 11:07:11.816957
 201 | highgo | 2017-06-26 11:07:11.816957
 202 | highgo | 2017-06-26 11:07:11.816957
 203 | highgo | 2017-06-26 11:07:11.816957
 204 | highgo | 2017-06-26 11:07:11.816957
(5 行记录)

postgres=# explain analyze select * from test_partition_144;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on test_partition_144  (cost=0.00..2.00 rows=100 width=19) (actual time=0.039..0.144 rows=100 loops=1)
 Planning time: 0.083 ms
 Execution time: 0.403 ms
(3 行记录)

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

性能方面

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

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

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

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