create database test; -- see created db in storage shards, introduce schema -- see processes in CN, show processlist in storage node and see the mapping -- see meta data tables in metadata shard and computing node ./psql -hlocalhost -p4003 -Uabc test set mysql_write_timeout=200; set mysql_read_timeout=200; set statement_timeout=200000; create table t1(a int primary key, b int, c int, d int); select now(); insert into t1 select i, i%15, i%35, i%75 from generate_series(1, 1000000) i; select now(); -- check time elapsed; see table t1's meta&data in storage shard -- see XA binlog events create table t2(a int primary key, b int, c int, d int) partition by hash(a); create table t2_0 partition of t2 for values with (modulus 4, remainder 0); create table t2_1 partition of t2 for values with (modulus 4, remainder 1); create table t2_2 partition of t2 for values with (modulus 4, remainder 2); create table t2_3 partition of t2 for values with (modulus 4, remainder 3); -- see ddl log in meta cluster, and pg_ddl_log_progress, start computing node with port 4004 and see its pg_ddl_log_progress and tables replicated -- see table t2&t2_x metadata, and t2_x in storage shard select now(); insert into t2 select i, i%15, i%35, i%75 from generate_series(1, 1000000) i; select now(); -- check time elapsed, compare with t1; see XA binlog events and commit log entry -- more types of partitioning, when to use each create table orders(id bigserial , when_buy timestamptz, address varchar(512), total_pay float, primary key(id, when_buy)) partition by range(when_buy); create table orders_2021_09 partition of orders for values from('2021-09-01 00:00:00') to ('2021-09-30 23:59:59'); insert into orders(when_buy, address, total_pay) values('2021-09-15 12:35:24', 'Shenzhen Baoan Library', 326.23); create table orders_2021_10 partition of orders for values from('2021-10-01 00:00:00') to ('2021-10-31 23:59:59'); insert into orders(when_buy, address, total_pay) values('2021-10-18 17:45:18', 'Shenzhen Baoan Wanjun', 123.45); create table orders_2021_11 partition of orders for values from('2021-11-01 00:00:00') to ('2021-11-30 23:59:59'); insert into orders(when_buy, address, total_pay) values('2021-11-15 12:35:24', 'Shenzhen Baoan Wanjun', 326.23); create table orders_2021_12 partition of orders for values from('2021-12-01 00:00:00') to ('2021-12-31 23:59:59'); insert into orders(when_buy, address, total_pay) values('2021-12-18 17:45:18', 'Shenzhen Baoan Haina', 123.45); -- 2 level partitioning create table stores(id serial , province varchar(128), city varchar(64), name varchar(128), primary key(id, province, city)) partition by list(province); create table stores_bj partition of stores for values in ('Beijing'); create table stores_sh partition of stores for values in ('Shanghai'); create table stores_gd partition of stores for values in ('Guangdong') partition by list(city); create table stores_gd_gz partition of stores_gd for values in ('Guangzhou'); create table stores_gd_sz partition of stores_gd for values in ('Shenzhen'); create table stores_gd_other partition of stores_gd for values in ('Zhuhai','Shantou', 'Dongguan', 'Huizhou','Shaoguan','Meizhou'); insert into stores(province,city,name) values('Beijing', 'Beijing', 'WangFuJing'); insert into stores(province,city,name) values('Shanghai', 'Shanghai', 'Nanjing Road 1st Store'); insert into stores(province,city,name) values('Guangdong', 'Guangzhou', 'Beijing Road'); insert into stores(province,city,name) values('Guangdong', 'Shenzhen', 'Dongmen'); insert into stores(province,city,name) values('Guangdong', 'Zhuhai', 'Gongbei'); insert into stores(province,city,name) values('Guangdong', 'Shantou', 'Nanao'); -- 3 levels partitioning, any more levels allowed. create table stores3(id serial , province varchar(128), city varchar(64), district varchar(64), name varchar(128), primary key(id, province, city, district)) partition by list(province); create table stores_gd3 partition of stores3 for values in ('Guangdong') partition by list(city); create table stores_gd_sz3 partition of stores_gd3 for values in ('Shenzhen') partition by list(district);; create table stores_gd_sz_nanshan3 partition of stores_gd_sz3 for values in ('Nanshan'); insert into stores3(province, city, district, name) values('Guangdong', 'Shenzhen', 'Nanshan', 'Dongmen'); -- perf of partitioned tables, compare time between t1&t2, and between pushdown&in-CN of same table -- QUAL, PROJ, ORDER BY, LIMIT PUSH DOWN set enable_remote_orderby_pushdown=true; select now(); select * from t1 where b = 0 and c = 0 order by a limit 20;select now(); explain select * from t1 where b = 0 and c = 0 order by a limit 20; select now(); select * from t2 where b = 0 and c = 0 order by a limit 20;select now(); explain select * from t2 where b = 0 and c = 0 order by a limit 20; set enable_remote_orderby_pushdown=false; select now(); select * from t1 where b = 0 and c = 0 order by a limit 20;select now(); explain select * from t1 where b = 0 and c = 0 order by a limit 20; select now(); select * from t2 where b = 0 and c = 0 order by a limit 20;select now(); explain select * from t2 where b = 0 and c = 0 order by a limit 20; set enable_remote_orderby_pushdown=true; -- JOIN PUSH DOWN create table t11(like t1 including all); -- make sure t11 in same shard as t1, if not, retry select relshardid from pg_class where relname='t11'; insert into t11 select*from t1; explain insert into t11 select*from t1; select*from t1 join t11 on t1.c=t11.d and t1.a < 100 limit 20; explain select*from t1 join t11 on t1.c=t11.d and t1.a < 100 limit 20; select count(*) from t1 join t11 on t1.c=t11.d and t1.a < 100 limit 20; explain select count(*) from t1 join t11 on t1.c=t11.d and t1.a < 100 limit 20; set enable_remote_join_pushdown=false; select*from t1 join t11 on t1.c=t11.d and t1.a < 100 limit 20; explain select*from t1 join t11 on t1.c=t11.d and t1.a < 100 limit 20; select count(*) from t1 join t11 on t1.c=t11.d and t1.a < 100 limit 20; explain select count(*) from t1 join t11 on t1.c=t11.d and t1.a < 100 limit 20; set enable_remote_join_pushdown=true; -- AGG PUSH DOWN set enable_remote_agg_pushdown=true; select now(); select count(*) from t1; select now(); explain select count(*) from t1; select now(); select count(*) from t2; select now(); explain select count(*) from t2; set enable_remote_agg_pushdown=false; select now(); select count(*) from t1; select now(); explain select count(*) from t1; select now(); select count(*) from t2; select now(); explain select count(*) from t2; set enable_remote_agg_pushdown=true; select now(); select b, count(*), sum(c), avg(d) from t1 group by b; select now(); explain select b, count(*), sum(c), avg(d) from t1 group by b; select now(); select b, count(*), sum(c), avg(d) from t2 group by b; select now(); explain select b, count(*), sum(c), avg(d) from t2 group by b; set enable_remote_agg_pushdown=false; select now(); select b, count(*), sum(c), avg(d) from t1 group by b; select now(); explain select b, count(*), sum(c), avg(d) from t1 group by b; select now(); select b, count(*), sum(c), avg(d) from t2 group by b; select now(); explain select b, count(*), sum(c), avg(d) from t2 group by b;