Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog ApsaraDB RDS for PostgreSQL: postgres_fdw Sharding for Database and Table Sharding (Not DRDS)

ApsaraDB RDS for PostgreSQL: postgres_fdw Sharding for Database and Table Sharding (Not DRDS)

In this article, the author explains database and table sharding using PostgreSQL postgres_fdw module.

By digoal

Background

All the ApsaraDB RDS for PostgreSQL instances in the same VPC can communicate with each other. They can also communicate with the ApsaraDB RDS for MySQL instances.

You can perform database and table sharding using PostgreSQL postgres_fdw and the partition table interfaces. However, do not use them thoughtlessly. Here are the reasons:

  • Complex to use
  • A single PostgreSQL database is enough for simple addition, deletion, modification, and query operations.
  • For a business to use database and table sharding, specify whether it is an analysis business or an online transaction processing (OLTP) business. For OLTP business, we recommended using Citus or decision-making database sharding at the application layer, such as scenarios like Platform as a Service (PaaS) or enterprise (ERP). Each enterprise has one set of databases, and cross-enterprise data operations are not supported. Therefore, you can isolate enterprise data through schema or database. It is highly efficient to implement simple database and table sharding in the business layer. We recommend Greenplum for an analysis business.

However, you should understand how to use postgres_fdw with a partitioned table to split a database and table. In the future, the PostgreSQL community will work on this to achieve better performance and functions. Already, Bruce has publicly shared the community's idea of using postgres_fdw for sharding many times.

postgres_fdw and Partitioned Table Sharding for Database and Table Sharding

Environment:

Master
pgm-bp15yx5p63i8y52f117910.pg.rds.aliyuncs.com
shard0, shard1
pgm-bp121kb7628ub4p9118050.pg.rds.aliyuncs.com
shard2, shard3
pgm-bp11lllmi55hyq68118070.pg.rds.aliyuncs.com

Procedures:

1) Create postgres_fdw and a primary table or primary partition on the master node and enable access to master through the application.

2) Create a partitioned table in a shard node.

3) Create an external table for these partitioned tables on the master node.

4) Attach an external table to the primary table as a partition.

5) Access the primary table of the master, and data is routed automatically to a shard node.

Operations such as select, insert, update, delete, join, group, and sort are supported. All operations are not affected, except that you can push down some queries and some cannot be.

Specific Procedures

export PGPASSWORD=xxx  
export PGHOST=xxxxpg.rds.aliyuncs.com  
export PGPORT=3433  
export PGUSER=dtstest

1) master: create a master database.

create database master with owner dtstest;

2) Remotely create a sharded database.

Remote instance 1:

create database shard0 with owner dtstest;  
create database shard1 with owner dtstest;

Remote instance 2:

create database shard2 with owner dtstest;  
create database shard3 with owner dtstest;

3) Run the master command to create the fdw plug-in.

create extension postgres_fdw;

3) Run the master command to create a remote server instance.

CREATE SERVER s0   
FOREIGN DATA WRAPPER postgres_fdw  
OPTIONS (host 'pgm-bp121kb7628ub4p9118050.pg.rds.aliyuncs.com', port '3433', dbname 'shard0');  
CREATE SERVER s1   
FOREIGN DATA WRAPPER postgres_fdw  
OPTIONS (host 'pgm-bp121kb7628ub4p9118050.pg.rds.aliyuncs.com', port '3433', dbname 'shard1');  
CREATE SERVER s2   
FOREIGN DATA WRAPPER postgres_fdw  
OPTIONS (host 'pgm-bp11lllmi55hyq68118070.pg.rds.aliyuncs.com', port '3433', dbname 'shard2');  
CREATE SERVER s3   
FOREIGN DATA WRAPPER postgres_fdw  
OPTIONS (host 'pgm-bp11lllmi55hyq68118070.pg.rds.aliyuncs.com', port '3433', dbname 'shard3');

4) Run the master command to configure the local account to connect it to the remote instance server after connecting the database account of the master.

CREATE USER MAPPING FOR 本地账号   
SERVER s0  
OPTIONS (user '远程实例账号', password '远程实例账号密码');  
CREATE USER MAPPING FOR dtstest   
SERVER s1  
OPTIONS (user 'dtstest', password 'xxx');  
CREATE USER MAPPING FOR dtstest   
SERVER s2  
OPTIONS (user 'dtstest', password 'xxx');  
CREATE USER MAPPING FOR dtstest   
SERVER s3  
OPTIONS (user 'dtstest', password 'xxx');

5) Run the master command to create a primary table or primary partition. For a multi-level partition table, create a hash partition.

create table tbl_user (id int, info text, crt_time timestamp) PARTITION BY hash (id);  
create table tbl_log  (id int, orderid int8, info text, crt_time timestamp) PARTITION BY hash (id);

Tbl_log is a two-level partition table. The first level is the id hash partition, and the second level is the orderid partition.

create table tbl_log0 PARTITION OF tbl_log FOR VALUES WITH ( MODULUS 4, REMAINDER 0) PARTITION BY hash (orderid);  
create table tbl_log1 PARTITION OF tbl_log FOR VALUES WITH ( MODULUS 4, REMAINDER 1) PARTITION BY hash (orderid);  
create table tbl_log2 PARTITION OF tbl_log FOR VALUES WITH ( MODULUS 4, REMAINDER 2) PARTITION BY hash (orderid);  
create table tbl_log3 PARTITION OF tbl_log FOR VALUES WITH ( MODULUS 4, REMAINDER 3) PARTITION BY hash (orderid);

You can attach the next-level partitions of these tables using the fdw external table.

You can define the multi-level partitions in the master node or a remote database. In this example, the master node is used to define list partitions.

6) Create a data table in the remote database.

s0, s1, s2, s3

create table tbl_user (id int primary key, info text, crt_time timestamp);  
  
create table tbl_log_0  (id int not null, orderid int8 not null, info text, crt_time timestamp);  
create table tbl_log_1  (id int not null, orderid int8 not null, info text, crt_time timestamp);  
create table tbl_log_2  (id int not null, orderid int8 not null, info text, crt_time timestamp);  
create table tbl_log_3  (id int not null, orderid int8 not null, info text, crt_time timestamp);

7) Run the master command to create a foreign table for the remote instance.

CREATE FOREIGN TABLE tbl_user_0 (id int not null, info text, crt_time timestamp)  
SERVER s0  
OPTIONS (schema_name 'public', table_name 'tbl_user');  
CREATE FOREIGN TABLE tbl_user_1 (id int not null, info text, crt_time timestamp)  
SERVER s1  
OPTIONS (schema_name 'public', table_name 'tbl_user');  
CREATE FOREIGN TABLE tbl_user_2 (id int not null, info text, crt_time timestamp)  
SERVER s2  
OPTIONS (schema_name 'public', table_name 'tbl_user');  
CREATE FOREIGN TABLE tbl_user_3 (id int not null, info text, crt_time timestamp)  
SERVER s3  
OPTIONS (schema_name 'public', table_name 'tbl_user');  
CREATE FOREIGN TABLE tbl_log0_0 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s0  
OPTIONS (schema_name 'public', table_name 'tbl_log_0');  
CREATE FOREIGN TABLE tbl_log0_1 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s0  
OPTIONS (schema_name 'public', table_name 'tbl_log_1');  
CREATE FOREIGN TABLE tbl_log0_2 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s0  
OPTIONS (schema_name 'public', table_name 'tbl_log_2');  
CREATE FOREIGN TABLE tbl_log0_3 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s0  
OPTIONS (schema_name 'public', table_name 'tbl_log_3');  
CREATE FOREIGN TABLE tbl_log1_0 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s1  
OPTIONS (schema_name 'public', table_name 'tbl_log_0');  
CREATE FOREIGN TABLE tbl_log1_1 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s1  
OPTIONS (schema_name 'public', table_name 'tbl_log_1');  
CREATE FOREIGN TABLE tbl_log1_2 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s1  
OPTIONS (schema_name 'public', table_name 'tbl_log_2');  
CREATE FOREIGN TABLE tbl_log1_3 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s1  
OPTIONS (schema_name 'public', table_name 'tbl_log_3');  
CREATE FOREIGN TABLE tbl_log2_0 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s2  
OPTIONS (schema_name 'public', table_name 'tbl_log_0');  
CREATE FOREIGN TABLE tbl_log2_1 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s2  
OPTIONS (schema_name 'public', table_name 'tbl_log_1');  
CREATE FOREIGN TABLE tbl_log2_2 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s2  
OPTIONS (schema_name 'public', table_name 'tbl_log_2');  
CREATE FOREIGN TABLE tbl_log2_3 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s2  
OPTIONS (schema_name 'public', table_name 'tbl_log_3');  
CREATE FOREIGN TABLE tbl_log3_0 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s3  
OPTIONS (schema_name 'public', table_name 'tbl_log_0');  
CREATE FOREIGN TABLE tbl_log3_1 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s3  
OPTIONS (schema_name 'public', table_name 'tbl_log_1');  
CREATE FOREIGN TABLE tbl_log3_2 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s3  
OPTIONS (schema_name 'public', table_name 'tbl_log_2');  
CREATE FOREIGN TABLE tbl_log3_3 (id int not null, orderid int8 not null, info text, crt_time timestamp)  
SERVER s3  
OPTIONS (schema_name 'public', table_name 'tbl_log_3');

Attach partition:

alter table tbl_user attach partition tbl_user_0 FOR VALUES WITH ( MODULUS 4, REMAINDER 0);  
alter table tbl_user attach partition tbl_user_1 FOR VALUES WITH ( MODULUS 4, REMAINDER 1);  
alter table tbl_user attach partition tbl_user_2 FOR VALUES WITH ( MODULUS 4, REMAINDER 2);  
alter table tbl_user attach partition tbl_user_3 FOR VALUES WITH ( MODULUS 4, REMAINDER 3);  
alter table tbl_log0 attach partition tbl_log0_0 FOR VALUES WITH ( MODULUS 4, REMAINDER 0);  
alter table tbl_log0 attach partition tbl_log0_1 FOR VALUES WITH ( MODULUS 4, REMAINDER 1);  
alter table tbl_log0 attach partition tbl_log0_2 FOR VALUES WITH ( MODULUS 4, REMAINDER 2);  
alter table tbl_log0 attach partition tbl_log0_3 FOR VALUES WITH ( MODULUS 4, REMAINDER 3);  
alter table tbl_log1 attach partition tbl_log1_0 FOR VALUES WITH ( MODULUS 4, REMAINDER 0);  
alter table tbl_log1 attach partition tbl_log1_1 FOR VALUES WITH ( MODULUS 4, REMAINDER 1);  
alter table tbl_log1 attach partition tbl_log1_2 FOR VALUES WITH ( MODULUS 4, REMAINDER 2);  
alter table tbl_log1 attach partition tbl_log1_3 FOR VALUES WITH ( MODULUS 4, REMAINDER 3); 
alter table tbl_log2 attach partition tbl_log2_0 FOR VALUES WITH ( MODULUS 4, REMAINDER 0);  
alter table tbl_log2 attach partition tbl_log2_1 FOR VALUES WITH ( MODULUS 4, REMAINDER 1);  
alter table tbl_log2 attach partition tbl_log2_2 FOR VALUES WITH ( MODULUS 4, REMAINDER 2);  
alter table tbl_log2 attach partition tbl_log2_3 FOR VALUES WITH ( MODULUS 4, REMAINDER 3);  
alter table tbl_log3 attach partition tbl_log3_0 FOR VALUES WITH ( MODULUS 4, REMAINDER 0);  
alter table tbl_log3 attach partition tbl_log3_1 FOR VALUES WITH ( MODULUS 4, REMAINDER 1);  
alter table tbl_log3 attach partition tbl_log3_2 FOR VALUES WITH ( MODULUS 4, REMAINDER 2);  
alter table tbl_log3 attach partition tbl_log3_3 FOR VALUES WITH ( MODULUS 4, REMAINDER 3);

In the preceding example, two table sharding projects are deployed, where one is a hash partition, and the other is an orderid partition.

Application Methods

  • Read
  • Write
  • Update
  • JOIN
  • Sorting
  • Aggregation
master=> insert into tbl_user select generate_series(1,10000), md5(random()::text), clock_timestamp();  
INSERT 0 10000  
Time: 2068.634 ms (00:02.069)  

master=> select * from tbl_user where id=1;  
 id |               info               |          crt_time            
----+----------------------------------+----------------------------  
  1 | ce4195228b81c7324e74c7581e2f9b6b | 2019-11-22 14:32:46.963279  
(1 row)  
  
Time: 2.868 ms  
master=> explain verbose select * from tbl_user where id=1;  
                                     QUERY PLAN                                        
-------------------------------------------------------------------------------------  
 Append  (cost=100.00..125.20 rows=6 width=44)  
   ->  Foreign Scan on public.tbl_user_0  (cost=100.00..125.17 rows=6 width=44)  
         Output: tbl_user_0.id, tbl_user_0.info, tbl_user_0.crt_time  
         Remote SQL: SELECT id, info, crt_time FROM public.tbl_user WHERE ((id = 1))  
(4 rows)  
  
Time: 0.740 ms  
  
master=> insert into tbl_log select random()*10000, generate_series(1,100000), md5(random()::text), clock_timestamp();  
INSERT 0 100000  
  
  
master=> select * from tbl_log where orderid =1;  
  id  | orderid |               info               |         crt_time            
------+---------+----------------------------------+---------------------------  
 3913 |       1 | ea78241381ffc6f18e97d575b8173cb7 | 2019-11-22 14:54:29.76664  
(1 row)  
  
Time: 10.395 ms  
master=> explain verbose select * from tbl_log where orderid =1;  
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
 Append  (cost=100.00..494.35 rows=20 width=52)  
   ->  Foreign Scan on public.tbl_log0_0  (cost=100.00..123.56 rows=5 width=52)  
         Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
         Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((orderid = 1))  
   ->  Foreign Scan on public.tbl_log1_0  (cost=100.00..123.56 rows=5 width=52)  
         Output: tbl_log1_0.id, tbl_log1_0.orderid, tbl_log1_0.info, tbl_log1_0.crt_time  
         Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((orderid = 1))  
   ->  Foreign Scan on public.tbl_log2_0  (cost=100.00..123.56 rows=5 width=52)  
         Output: tbl_log2_0.id, tbl_log2_0.orderid, tbl_log2_0.info, tbl_log2_0.crt_time  
         Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((orderid = 1))  
   ->  Foreign Scan on public.tbl_log3_0  (cost=100.00..123.56 rows=5 width=52)  
         Output: tbl_log3_0.id, tbl_log3_0.orderid, tbl_log3_0.info, tbl_log3_0.crt_time  
         Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((orderid = 1))  
(13 rows)  
  
Time: 1.186 ms  
master=> explain verbose select * from tbl_log where id=1;  
                                          QUERY PLAN                                             
-----------------------------------------------------------------------------------------------  
 Append  (cost=100.00..494.35 rows=20 width=52)  
   ->  Foreign Scan on public.tbl_log0_0  (cost=100.00..123.56 rows=5 width=52)  
         Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
         Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((id = 1))  
   ->  Foreign Scan on public.tbl_log0_1  (cost=100.00..123.56 rows=5 width=52)  
         Output: tbl_log0_1.id, tbl_log0_1.orderid, tbl_log0_1.info, tbl_log0_1.crt_time  
         Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1 WHERE ((id = 1))  
   ->  Foreign Scan on public.tbl_log0_2  (cost=100.00..123.56 rows=5 width=52)  
         Output: tbl_log0_2.id, tbl_log0_2.orderid, tbl_log0_2.info, tbl_log0_2.crt_time  
         Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2 WHERE ((id = 1))  
   ->  Foreign Scan on public.tbl_log0_3  (cost=100.00..123.56 rows=5 width=52)  
         Output: tbl_log0_3.id, tbl_log0_3.orderid, tbl_log0_3.info, tbl_log0_3.crt_time  
         Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3 WHERE ((id = 1))  
(13 rows)  
  
Time: 0.928 ms  
master=> explain verbose select * from tbl_log join tbl_user using (id) where tbl_log.orderid=1;  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 Append  (cost=223.65..1098.94 rows=120 width=92)  
   ->  Hash Join  (cost=223.65..274.58 rows=30 width=92)  
         Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time, tbl_user_0.info, tbl_user_0.crt_time  
         Hash Cond: (tbl_user_0.id = tbl_log0_0.id)  
         ->  Foreign Scan on public.tbl_user_0  (cost=100.00..146.12 rows=1204 width=44)  
               Output: tbl_user_0.info, tbl_user_0.crt_time, tbl_user_0.id  
               Remote SQL: SELECT id, info, crt_time FROM public.tbl_user  
         ->  Hash  (cost=123.59..123.59 rows=5 width=52)  
               Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
               ->  Append  (cost=100.00..123.59 rows=5 width=52)  
                     ->  Foreign Scan on public.tbl_log0_0  (cost=100.00..123.56 rows=5 width=52)  
                           Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((orderid = 1))  
   ->  Hash Join  (cost=223.65..274.58 rows=30 width=92)  
         Output: tbl_log1_0.id, tbl_log1_0.orderid, tbl_log1_0.info, tbl_log1_0.crt_time, tbl_user_1.info, tbl_user_1.crt_time  
         Hash Cond: (tbl_user_1.id = tbl_log1_0.id)  
         ->  Foreign Scan on public.tbl_user_1  (cost=100.00..146.12 rows=1204 width=44)  
               Output: tbl_user_1.info, tbl_user_1.crt_time, tbl_user_1.id  
               Remote SQL: SELECT id, info, crt_time FROM public.tbl_user  
         ->  Hash  (cost=123.59..123.59 rows=5 width=52)  
               Output: tbl_log1_0.id, tbl_log1_0.orderid, tbl_log1_0.info, tbl_log1_0.crt_time  
               ->  Append  (cost=100.00..123.59 rows=5 width=52)  
                     ->  Foreign Scan on public.tbl_log1_0  (cost=100.00..123.56 rows=5 width=52)  
                           Output: tbl_log1_0.id, tbl_log1_0.orderid, tbl_log1_0.info, tbl_log1_0.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((orderid = 1))  
   ->  Hash Join  (cost=223.65..274.58 rows=30 width=92)  
         Output: tbl_log2_0.id, tbl_log2_0.orderid, tbl_log2_0.info, tbl_log2_0.crt_time, tbl_user_2.info, tbl_user_2.crt_time  
         Hash Cond: (tbl_user_2.id = tbl_log2_0.id)  
         ->  Foreign Scan on public.tbl_user_2  (cost=100.00..146.12 rows=1204 width=44)  
               Output: tbl_user_2.info, tbl_user_2.crt_time, tbl_user_2.id  
               Remote SQL: SELECT id, info, crt_time FROM public.tbl_user  
         ->  Hash  (cost=123.59..123.59 rows=5 width=52)  
               Output: tbl_log2_0.id, tbl_log2_0.orderid, tbl_log2_0.info, tbl_log2_0.crt_time  
               ->  Append  (cost=100.00..123.59 rows=5 width=52)  
                     ->  Foreign Scan on public.tbl_log2_0  (cost=100.00..123.56 rows=5 width=52)  
                           Output: tbl_log2_0.id, tbl_log2_0.orderid, tbl_log2_0.info, tbl_log2_0.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((orderid = 1))  
   ->  Hash Join  (cost=223.65..274.58 rows=30 width=92)  
         Output: tbl_log3_0.id, tbl_log3_0.orderid, tbl_log3_0.info, tbl_log3_0.crt_time, tbl_user_3.info, tbl_user_3.crt_time  
         Hash Cond: (tbl_user_3.id = tbl_log3_0.id)  
         ->  Foreign Scan on public.tbl_user_3  (cost=100.00..146.12 rows=1204 width=44)  
               Output: tbl_user_3.info, tbl_user_3.crt_time, tbl_user_3.id  
               Remote SQL: SELECT id, info, crt_time FROM public.tbl_user  
         ->  Hash  (cost=123.59..123.59 rows=5 width=52)  
               Output: tbl_log3_0.id, tbl_log3_0.orderid, tbl_log3_0.info, tbl_log3_0.crt_time  
               ->  Append  (cost=100.00..123.59 rows=5 width=52)  
                     ->  Foreign Scan on public.tbl_log3_0  (cost=100.00..123.56 rows=5 width=52)  
                           Output: tbl_log3_0.id, tbl_log3_0.orderid, tbl_log3_0.info, tbl_log3_0.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((orderid = 1))  
(49 rows)  
  
Time: 1.575 ms  
master=> explain verbose select * from tbl_log join tbl_user using (id) ;  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 Append  (cost=1058.55..6333.02 rows=103736 width=92)  
   ->  Merge Join  (cost=1058.55..1453.58 rows=25934 width=92)  
         Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time, tbl_user_0.info, tbl_user_0.crt_time  
         Merge Cond: (tbl_user_0.id = tbl_log0_0.id)  
         ->  Sort  (cost=207.73..210.74 rows=1204 width=44)  
               Output: tbl_user_0.info, tbl_user_0.crt_time, tbl_user_0.id  
               Sort Key: tbl_user_0.id  
               ->  Foreign Scan on public.tbl_user_0  (cost=100.00..146.12 rows=1204 width=44)  
                     Output: tbl_user_0.info, tbl_user_0.crt_time, tbl_user_0.id  
                     Remote SQL: SELECT id, info, crt_time FROM public.tbl_user  
         ->  Sort  (cost=850.83..861.60 rows=4308 width=52)  
               Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
               Sort Key: tbl_log0_0.id  
               ->  Append  (cost=100.00..590.78 rows=4308 width=52)  
                     ->  Foreign Scan on public.tbl_log0_0  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0  
                     ->  Foreign Scan on public.tbl_log0_1  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log0_1.id, tbl_log0_1.orderid, tbl_log0_1.info, tbl_log0_1.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1  
                     ->  Foreign Scan on public.tbl_log0_2  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log0_2.id, tbl_log0_2.orderid, tbl_log0_2.info, tbl_log0_2.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2  
                     ->  Foreign Scan on public.tbl_log0_3  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log0_3.id, tbl_log0_3.orderid, tbl_log0_3.info, tbl_log0_3.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3  
   ->  Merge Join  (cost=1058.55..1453.58 rows=25934 width=92)  
         Output: tbl_log1_0.id, tbl_log1_0.orderid, tbl_log1_0.info, tbl_log1_0.crt_time, tbl_user_1.info, tbl_user_1.crt_time  
         Merge Cond: (tbl_user_1.id = tbl_log1_0.id)  
         ->  Sort  (cost=207.73..210.74 rows=1204 width=44)  
               Output: tbl_user_1.info, tbl_user_1.crt_time, tbl_user_1.id  
               Sort Key: tbl_user_1.id  
               ->  Foreign Scan on public.tbl_user_1  (cost=100.00..146.12 rows=1204 width=44)  
                     Output: tbl_user_1.info, tbl_user_1.crt_time, tbl_user_1.id  
                     Remote SQL: SELECT id, info, crt_time FROM public.tbl_user  
         ->  Sort  (cost=850.83..861.60 rows=4308 width=52)  
               Output: tbl_log1_0.id, tbl_log1_0.orderid, tbl_log1_0.info, tbl_log1_0.crt_time  
               Sort Key: tbl_log1_0.id  
               ->  Append  (cost=100.00..590.78 rows=4308 width=52)  
                     ->  Foreign Scan on public.tbl_log1_0  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log1_0.id, tbl_log1_0.orderid, tbl_log1_0.info, tbl_log1_0.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0  
                     ->  Foreign Scan on public.tbl_log1_1  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log1_1.id, tbl_log1_1.orderid, tbl_log1_1.info, tbl_log1_1.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1  
                     ->  Foreign Scan on public.tbl_log1_2  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log1_2.id, tbl_log1_2.orderid, tbl_log1_2.info, tbl_log1_2.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2  
                     ->  Foreign Scan on public.tbl_log1_3  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log1_3.id, tbl_log1_3.orderid, tbl_log1_3.info, tbl_log1_3.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3  
   ->  Merge Join  (cost=1058.55..1453.58 rows=25934 width=92)  
         Output: tbl_log2_0.id, tbl_log2_0.orderid, tbl_log2_0.info, tbl_log2_0.crt_time, tbl_user_2.info, tbl_user_2.crt_time  
         Merge Cond: (tbl_user_2.id = tbl_log2_0.id)  
         ->  Sort  (cost=207.73..210.74 rows=1204 width=44)  
               Output: tbl_user_2.info, tbl_user_2.crt_time, tbl_user_2.id  
               Sort Key: tbl_user_2.id  
               ->  Foreign Scan on public.tbl_user_2  (cost=100.00..146.12 rows=1204 width=44)  
                     Output: tbl_user_2.info, tbl_user_2.crt_time, tbl_user_2.id  
                     Remote SQL: SELECT id, info, crt_time FROM public.tbl_user  
         ->  Sort  (cost=850.83..861.60 rows=4308 width=52)  
               Output: tbl_log2_0.id, tbl_log2_0.orderid, tbl_log2_0.info, tbl_log2_0.crt_time  
               Sort Key: tbl_log2_0.id  
               ->  Append  (cost=100.00..590.78 rows=4308 width=52)  
                     ->  Foreign Scan on public.tbl_log2_0  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log2_0.id, tbl_log2_0.orderid, tbl_log2_0.info, tbl_log2_0.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0  
                     ->  Foreign Scan on public.tbl_log2_1  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log2_1.id, tbl_log2_1.orderid, tbl_log2_1.info, tbl_log2_1.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1  
                     ->  Foreign Scan on public.tbl_log2_2  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log2_2.id, tbl_log2_2.orderid, tbl_log2_2.info, tbl_log2_2.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2  
                     ->  Foreign Scan on public.tbl_log2_3  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log2_3.id, tbl_log2_3.orderid, tbl_log2_3.info, tbl_log2_3.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3  
   ->  Merge Join  (cost=1058.55..1453.58 rows=25934 width=92)  
         Output: tbl_log3_0.id, tbl_log3_0.orderid, tbl_log3_0.info, tbl_log3_0.crt_time, tbl_user_3.info, tbl_user_3.crt_time  
         Merge Cond: (tbl_user_3.id = tbl_log3_0.id)  
         ->  Sort  (cost=207.73..210.74 rows=1204 width=44)  
               Output: tbl_user_3.info, tbl_user_3.crt_time, tbl_user_3.id  
               Sort Key: tbl_user_3.id  
               ->  Foreign Scan on public.tbl_user_3  (cost=100.00..146.12 rows=1204 width=44)  
                     Output: tbl_user_3.info, tbl_user_3.crt_time, tbl_user_3.id  
                     Remote SQL: SELECT id, info, crt_time FROM public.tbl_user  
         ->  Sort  (cost=850.83..861.60 rows=4308 width=52)  
               Output: tbl_log3_0.id, tbl_log3_0.orderid, tbl_log3_0.info, tbl_log3_0.crt_time  
               Sort Key: tbl_log3_0.id  
               ->  Append  (cost=100.00..590.78 rows=4308 width=52)  
                     ->  Foreign Scan on public.tbl_log3_0  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log3_0.id, tbl_log3_0.orderid, tbl_log3_0.info, tbl_log3_0.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0  
                     ->  Foreign Scan on public.tbl_log3_1  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log3_1.id, tbl_log3_1.orderid, tbl_log3_1.info, tbl_log3_1.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1  
                     ->  Foreign Scan on public.tbl_log3_2  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log3_2.id, tbl_log3_2.orderid, tbl_log3_2.info, tbl_log3_2.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2  
                     ->  Foreign Scan on public.tbl_log3_3  (cost=100.00..142.31 rows=1077 width=52)  
                           Output: tbl_log3_3.id, tbl_log3_3.orderid, tbl_log3_3.info, tbl_log3_3.crt_time  
                           Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3  
(101 rows)  
  
Time: 1.692 ms  
master=> explain verbose select * from tbl_log order by id limit 1;  
                                                  QUERY PLAN                                                     
---------------------------------------------------------------------------------------------------------------  
 Limit  (cost=1600.32..1600.39 rows=1 width=52)  
   Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
   ->  Merge Append  (cost=1600.32..2774.54 rows=17232 width=52)  
         Sort Key: tbl_log0_0.id  
         ->  Foreign Scan on public.tbl_log0_0  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log0_1  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log0_1.id, tbl_log0_1.orderid, tbl_log0_1.info, tbl_log0_1.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log0_2  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log0_2.id, tbl_log0_2.orderid, tbl_log0_2.info, tbl_log0_2.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log0_3  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log0_3.id, tbl_log0_3.orderid, tbl_log0_3.info, tbl_log0_3.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log1_0  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log1_0.id, tbl_log1_0.orderid, tbl_log1_0.info, tbl_log1_0.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log1_1  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log1_1.id, tbl_log1_1.orderid, tbl_log1_1.info, tbl_log1_1.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log1_2  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log1_2.id, tbl_log1_2.orderid, tbl_log1_2.info, tbl_log1_2.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log1_3  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log1_3.id, tbl_log1_3.orderid, tbl_log1_3.info, tbl_log1_3.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log2_0  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log2_0.id, tbl_log2_0.orderid, tbl_log2_0.info, tbl_log2_0.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log2_1  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log2_1.id, tbl_log2_1.orderid, tbl_log2_1.info, tbl_log2_1.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log2_2  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log2_2.id, tbl_log2_2.orderid, tbl_log2_2.info, tbl_log2_2.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log2_3  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log2_3.id, tbl_log2_3.orderid, tbl_log2_3.info, tbl_log2_3.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log3_0  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log3_0.id, tbl_log3_0.orderid, tbl_log3_0.info, tbl_log3_0.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log3_1  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log3_1.id, tbl_log3_1.orderid, tbl_log3_1.info, tbl_log3_1.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log3_2  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log3_2.id, tbl_log3_2.orderid, tbl_log3_2.info, tbl_log3_2.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2 ORDER BY id ASC NULLS LAST  
         ->  Foreign Scan on public.tbl_log3_3  (cost=100.00..146.46 rows=1077 width=52)  
               Output: tbl_log3_3.id, tbl_log3_3.orderid, tbl_log3_3.info, tbl_log3_3.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3 ORDER BY id ASC NULLS LAST  
(52 rows)  
  
Time: 1.264 ms  
  
master=> explain verbose select * from tbl_log join tbl_user using (id) where id=1;  
                                                       QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------  
 Nested Loop  (cost=200.00..621.07 rows=120 width=92)  
   Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time, tbl_user_0.info, tbl_user_0.crt_time  
   ->  Append  (cost=100.00..494.35 rows=20 width=52)  
         ->  Foreign Scan on public.tbl_log0_0  (cost=100.00..123.56 rows=5 width=52)  
               Output: tbl_log0_0.id, tbl_log0_0.orderid, tbl_log0_0.info, tbl_log0_0.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_0 WHERE ((id = 1))  
         ->  Foreign Scan on public.tbl_log0_1  (cost=100.00..123.56 rows=5 width=52)  
               Output: tbl_log0_1.id, tbl_log0_1.orderid, tbl_log0_1.info, tbl_log0_1.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_1 WHERE ((id = 1))  
         ->  Foreign Scan on public.tbl_log0_2  (cost=100.00..123.56 rows=5 width=52)  
               Output: tbl_log0_2.id, tbl_log0_2.orderid, tbl_log0_2.info, tbl_log0_2.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_2 WHERE ((id = 1))  
         ->  Foreign Scan on public.tbl_log0_3  (cost=100.00..123.56 rows=5 width=52)  
               Output: tbl_log0_3.id, tbl_log0_3.orderid, tbl_log0_3.info, tbl_log0_3.crt_time  
               Remote SQL: SELECT id, orderid, info, crt_time FROM public.tbl_log_3 WHERE ((id = 1))  
   ->  Materialize  (cost=100.00..125.23 rows=6 width=44)  
         Output: tbl_user_0.info, tbl_user_0.crt_time, tbl_user_0.id  
         ->  Append  (cost=100.00..125.20 rows=6 width=44)  
               ->  Foreign Scan on public.tbl_user_0  (cost=100.00..125.17 rows=6 width=44)  
                     Output: tbl_user_0.info, tbl_user_0.crt_time, tbl_user_0.id  
                     Remote SQL: SELECT id, info, crt_time FROM public.tbl_user WHERE ((id = 1))  
(21 rows)  
  
Time: 1.018 ms

Summary

All the ApsaraDB RDS for PostgreSQL instances in the same VPC can communicate with each other. They can also communicate with the ApsaraDB RDS for MySQL instances.

You can perform database and table sharding using PostgreSQL postgres_fdw and the partition table interfaces. However, these operations are not perfect yet. In the future, the PostgreSQL community will work on this to achieve better performance and functions. Already, Bruce has publicly shared the community's idea of using postgres_fdw for sharding many times.

  • At present, this plan is more complicated to use.
  • A single PostgreSQL database is enough for simple addition, deletion, modification, and query operations.
  • For a business to use database and table sharding, specify whether it is an analysis business or an online transaction processing (OLTP) business. For OLTP business, we recommended using Citus or decision-making database sharding at the application layer, such as scenarios like Platform as a Service (PaaS) or enterprise (ERP). Each enterprise has one set of databases, and cross-enterprise data operations are not supported. Therefore, you can isolate enterprise data through schema or database. It is highly efficient to implement simple database and table sharding in the business layer. We recommend Greenplum for an analysis business.

References

PostgreSQL RDS Database DRDS postgres_fdw Database and Table Sharding
0 0 0
Share on

Read previous post:

How to Build a Remote Secondary Database on PostgreSQL Using pg_basebackup

Read next post:

PostgreSQL Chemical Analysis Module: RDKit Cartridge 1 for Environment Deployment

digoal

277 posts | 24 followers

You may also like

Comments

digoal

277 posts | 24 followers

Related Products

More Posts by digoal

See All