By digoal
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:
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.
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.
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.
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
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.
How to Build a Remote Secondary Database on PostgreSQL Using pg_basebackup
How to Switch a Secondary Physical Database to a Secondary Logical Database on PostgreSQL
Alibaba Clouder - January 17, 2018
Alibaba Clouder - July 16, 2020
ApsaraDB - June 5, 2024
ApsaraDB - April 20, 2023
ApsaraDB - November 22, 2022
digoal - April 22, 2021
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAn easy transformation for heterogeneous database.
Learn MoreAn on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreLeverage cloud-native database solutions dedicated for FinTech.
Learn MoreMore Posts by digoal