ApsaraDB RDS:Use the PL/Proxy plug-in for horizontal sharding

Precautions

• You can directly manage tables across RDS instances that reside in the same virtual private cloud (VPC).
• An Elastic Compute Service (ECS) instance that resides in the same VPC as your RDS instance can serve as a proxy to redirect access requests for your RDS instance. This allows you to manage tables across RDS instances that reside in different VPCs.
• The number of data nodes that are served by the proxy node must be 2 to the power of n.

Test environment

Select an RDS instance as the proxy node and another two RDS instances as the data nodes. The following table provides details about the three RDS instances.

Create a PL/Proxy plug-in

Execute the following statement to create a PL/Proxy plug-in:

create extension plproxy

Create a PL/Proxy cluster

1. Create a PL/Proxy cluster and specify the names, IP addresses, and ports of the RDS instances that you want to connect as data nodes in the cluster. Example:

   postgres=# CREATE SERVER cluster_srv1 FOREIGN DATA WRAPPER plproxy
   postgres-# OPTIONS (
   postgres(#         connection_lifetime '1800',
   postgres(#         disable_binary '1',
   postgres(#         p0 'dbname=pl_db0 host=100.xxx.xxx.72 port=5678',
   postgres(#         p1 'dbname=pl_db1 host=11.xxx.xxx.9 port=5678'
   postgres(#         );
   CREATE SERVER

2. Grant the permissions on the created PL/Proxy cluster to the postgres user. Example:

   postgres=# grant usage on FOREIGN server cluster_srv1 to postgres;
   GRANT 

3. Create a user mapping. Example:

   postgres=> create user mapping for postgres server cluster_srv1 options (user 'postgres');
   CREATE USER MAPPING

Create a test table

Create a test table named users on each data node. Example:

create table users(userid int, name text);

Test the CLUSTER mode

To test horizontal sharding, perform the following steps:

1. Create a function that is used to insert data on each data node. Example:

   pl_db0=> CREATE OR REPLACE FUNCTION insert_user(i_id int, i_name text)
   pl_db0-> RETURNS integer AS $$
   pl_db0$>        INSERT INTO users (userid, name) VALUES ($1,$2);
   pl_db0$>        SELECT 1;
   pl_db0$> $$ LANGUAGE SQL;
   CREATE FUNCTION
   
   pl_db1=> CREATE OR REPLACE FUNCTION insert_user(i_id int, i_name text)
   pl_db1-> RETURNS integer AS $$
   pl_db1$>        INSERT INTO users (userid, name) VALUES ($1,$2);
   pl_db1$>        SELECT 1;
   pl_db1$> $$ LANGUAGE SQL;
   CREATE FUNCTION

2. Create a function that is used to insert data on the proxy node. This function has the same name as the function that is used to insert data on each data node. Example:

   postgres=> CREATE OR REPLACE FUNCTION insert_user(i_id int, i_name text)
   postgres-> RETURNS integer AS $$
   postgres$>     CLUSTER 'cluster_srv1';
   postgres$>     RUN ON ANY;
   postgres$> $$ LANGUAGE plproxy;
   CREATE FUNCTION

3. Create a function that is used to read data on the proxy node. Example:

   postgres=> CREATE OR REPLACE FUNCTION get_user_name()
   postgres-> RETURNS TABLE(userid int, name text) AS $$
   postgres$>     CLUSTER 'cluster_srv1';
   postgres$>     RUN ON ALL ;
   postgres$> SELECT userid,name FROM users;
   postgres$> $$ LANGUAGE plproxy;
   CREATE FUNCTION

4. Insert 10 test records on the proxy node. Example:

   SELECT insert_user(1001, 'Sven');
   SELECT insert_user(1002, 'Marko');
   SELECT insert_user(1003, 'Steve');
   SELECT insert_user(1004, 'lottu');
   SELECT insert_user(1005, 'rax');
   SELECT insert_user(1006, 'ak');
   SELECT insert_user(1007, 'jack');
   SELECT insert_user(1008, 'molica');
   SELECT insert_user(1009, 'pg');
   SELECT insert_user(1010, 'oracle');

5. View the data on each data node. The function that is used to insert data contains the RUN ON ANY statement. This statement randomly inserts data into either data node. Therefore, you may find the following data on the two data nodes:

   pl_db0=> select * from users;
    userid |  name
   --------+--------
      1001 | Sven
      1003 | Steve
      1004 | lottu
      1005 | rax
      1006 | ak
      1007 | jack
      1008 | molica
      1009 | pg
   (8 rows)
   
   pl_db1=> select * from users;
    userid |  name
   --------+--------
      1002 | Marko
      1010 | oracle
   (2 rows)

   Note The query results indicate that the 10 data records are unevenly distributed between the two data nodes.
6. Invoke the function that is used to read data on the proxy node. This function contains the RUN ON ALL statement that reads data from both data nodes. Example:

   postgres=> SELECT USERID,NAME FROM GET_USER_NAME();
    userid |  name
   --------+--------
      1001 | Sven
      1003 | Steve
      1004 | lottu
      1005 | rax
      1006 | ak
      1007 | jack
      1008 | molica
      1009 | pg
      1002 | Marko
      1010 | oracle
   (10 rows)

To test SQL replication, perform the following steps:

1. Create a function that is used to truncate the users table on each node. Example:

   pl_db0=> CREATE OR REPLACE FUNCTION trunc_user()
   pl_db0-> RETURNS integer AS $$
   pl_db0$>        truncate table users;
   pl_db0$>        SELECT 1;
   pl_db0$> $$ LANGUAGE SQL;
   CREATE FUNCTION
   
   pl_db1=> CREATE OR REPLACE FUNCTION trunc_user()
   pl_db1-> RETURNS integer AS $$
   pl_db1$>        truncate table users;
   pl_db1$>        SELECT 1;
   pl_db1$> $$ LANGUAGE SQL;
   CREATE FUNCTION
   
   postgres=> CREATE OR REPLACE FUNCTION trunc_user()
   postgres-> RETURNS SETOF integer AS $$
   postgres$>     CLUSTER 'cluster_srv1';
   postgres$>     RUN ON ALL;
   postgres$> $$ LANGUAGE plproxy;
   CREATE FUNCTION

2. Invoke the function that is used to truncate data on the proxy node. Example:

   postgres=> SELECT TRUNC_USER();
    trunc_user
   ------------
             1
             1
   (2 rows)

3. Create a function that is used to insert data on the proxy node. Example:

   postgres=> CREATE OR REPLACE FUNCTION insert_user_2(i_id int, i_name text)
   postgres-> RETURNS SETOF integer AS $$
   postgres$>     CLUSTER 'cluster_srv1';
   postgres$>     RUN ON ALL;
   postgres$> TARGET insert_user;
   postgres$> $$ LANGUAGE plproxy;
   CREATE FUNCTION

4. Insert four test records into the proxy node. Example:

   SELECT insert_user_2(1004, 'lottu');
   SELECT insert_user_2(1005, 'rax');
   SELECT insert_user_2(1006, 'ak');
   SELECT insert_user_2(1007, 'jack');

5. View the data on each data node. Example:

   pl_db0=> select * from users;
    userid | name
   --------+-------
      1004 | lottu
      1005 | rax
      1006 | ak
      1007 | jack
   (4 rows)
   
   pl_db1=> select * from users;
    userid | name
   --------+-------
      1004 | lottu
      1005 | rax
      1006 | ak
      1007 | jack
   (4 rows)

   Note The data is the same on each data node. This indicates that SQL replication is successful.
6. Query data on the proxy node. You need only to execute the RUN ON ANY statement that randomly reads data from either data node. Example:

   postgres=> CREATE OR REPLACE FUNCTION get_user_name_2()
   postgres-> RETURNS TABLE(userid int, name text) AS $$
   postgres$>     CLUSTER 'cluster_srv1';
   postgres$>     RUN ON ANY ;
   postgres$> SELECT userid,name FROM users;
   postgres$> $$ LANGUAGE plproxy;
   CREATE FUNCTION
   
   postgres=> SELECT USERID,NAME FROM GET_USER_NAME_2();
    userid | name
   --------+-------
      1004 | lottu
      1005 | rax
      1006 | ak
      1007 | jack
   (4 rows)

Test the CONNECT mode

When you use the CONNECT mode, you can access other RDS instances from the proxy node. Examples:

postgres=> CREATE OR REPLACE FUNCTION get_user_name_3()
postgres-> RETURNS TABLE(userid int, name text) AS $$
postgres$>     CONNECT 'dbname=pl_db0 host=100.81.137.72 port=56789';
postgres$> SELECT userid,name FROM users;
postgres$> $$ LANGUAGE plproxy;
CREATE FUNCTION

postgres=> SELECT USERID,NAME FROM GET_USER_NAME_3();
 userid | name
--------+-------
   1004 | lottu
   1005 | rax
   1006 | ak
   1007 | jack
(4 rows)