GP db模板、dblink、tablespace、交换分区和数据倾斜

database 模板

模板数据库其实就是用于创建新库时候的copy 来源，默认使用template1 ，template1 和template0的差异

1、template1 可以连接，template0 不可以连接。
2、使用 template1 模板库建库时不可指定新的 encoding 和 locale，而 template0 可以。

实际可以将任意一个database作为template 用于创建新database，但作为模板的时候需要没有连接，否则会有如下错误

test=# create database test_3 template test;
ERROR: source database “test” is being accessed by other users
DETAIL: There are 2 other sessions using the database.

template database的所有对象会复制到新database下

dblink

GP 默认是不允许跨database操作（scheme是可以跨的），如果有需要可以使用dblink来进行

test_3=# create extension dblink;
CREATE EXTENSION

test_3=# SELECT dblink_connect('test','host=localhost port=5432 dbname=test user=postgres password=postgres options=-csearch_path=');
 dblink_connect 
----------------
 OK
(1 row)

test_3=# SELECT * FROM dblink('test', 'select fid, ftype, fdate from public.foo')
test_3-# AS t(fid INTEGER,ftype text, fdate DATE)
test_3-# union all 
test_3-# SELECT * FROM  foo;
 fid | ftype  |   fdate    
-----+--------+------------
   2 | stuff2 | 2019-01-01
   2 | stuff2 | 2019-01-01
   1 | stuff1 | 2018-12-01
   1 | stuff1 | 2018-12-01
   2 | stuff2 | 2019-01-01
   2 | stuff2 | 2019-01-01
   1 | stuff1 | 2018-12-01
   1 | stuff1 | 2018-12-01
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

可以参考：https://stackoverflow.com/questions/51784903/cross-database-references-are-not-implemented

tablespace

GP6创建 tablespace和之前版本不同，需要先在所有主机上确保有对应的目录， 可以通过函数gp_tablespace_location(oid)查看已经创建的（默认除外）的路径。

先创建文件

mkdir /srv/gpmaster/gpsne-1/data01

test_3=# CREATE TABLESPACE data01tbs LOCATION '/srv/gpmaster/gpsne-1/data01';
ERROR:  directory "/srv/gpmaster/gpsne-1/data01" does not exist
test_3=# CREATE TABLESPACE data01tbs LOCATION '/srv/gpmaster/gpsne-1/data01';
CREATE TABLESPACE
test_3=# CREATE TABLESPACE data01tbs LOCATION '/srv/gpmaster/gpsne-1/data01';
ERROR:  directory "/srv/gpmaster/gpsne-1/data01" does not exist
test_3=# CREATE TABLESPACE data01tbs LOCATION '/srv/gpmaster/gpsne-1/data01';
CREATE TABLESPACE
test_3=# SELECT oid,* FROM pg_tablespace ;

  oid  |  spcname   | spcowner | spcacl | spcoptions 
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 19214 | data01tbs  |       10 |        | 
(3 rows)

test_3=# 
test_3=# SELECT * FROM gp_tablespace_location(19214)
test_3-# ;
 gp_segment_id |          tblspc_loc          
---------------+------------------------------
             0 | /srv/gpmaster/gpsne-1/data01
            -1 | /srv/gpmaster/gpsne-1/data01
(2 rows)

test_3=# SELECT * FROM gp_tablespace_location(1664);
 gp_segment_id | tblspc_loc 
---------------+------------
             0 | 
            -1 | 
(2 rows)

test_3=# drop  TABLESPACE data01tbs;
DROP TABLESPACE
test_3=# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

交换分区

gp支持oracle 一样的交换分区，类似的概念还有物化视图。主要GP支持与外部表进行交换，以达到将低频访问数据放到远端存储上（如使用商业版本，可以放到hdfs上）。

test=# CREATE TABLE sales (trans_id int, date char(8), amount decimal(9,2), region text)
test-# DISTRIBUTED BY (trans_id)
test-# PARTITION  BY LIST (region)
test-#    ( 
test(#     PARTITION usa VALUES ('usa'),
test(#     PARTITION asia VALUES ('asia'),
test(#     PARTITION europe VALUES ('europe'),
test(#     DEFAULT PARTITION other_regions
test(#   );
NOTICE:  CREATE TABLE will create partition "sales_1_prt_usa" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_asia" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_europe" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_regions" for table "sales"
CREATE TABLE
test=# CREATE TABLE usa(LIKE sales) WITH (appendonly=true);
NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
CREATE TABLE
test=# insert into usa values(1,'20220303',12.3,'usa');
INSERT 0 1
test=# select * from usa;
 trans_id |   date   | amount | region 
----------+----------+--------+--------
        1 | 20220303 |  12.30 | usa
(1 row)


test=# ALTER TABLE sales EXCHANGE PARTITION FOR ( 'usa' ) WITH TABLE usa;       
NOTICE:  exchanged partition "usa" of relation "sales" with relation "usa"
ALTER TABLE
test=# select * from usa;
 trans_id | date | amount | region 
----------+------+--------+--------
(0 rows)

test=# select * from sales_1_prt_usa;
 trans_id |   date   | amount | region 
----------+----------+--------+--------
        1 | 20220303 |  12.30 | usa
(1 row)

test=# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

数据倾斜

查看所有segments上的表倾斜（数据文件大小）：

select *,pg_relation_size('foo') from gp_dist_random('gp_id');      
1

pg_relation_size有时候不生效，可以改用pg_table_size

同时如果关注partition，同样可以使用pg_table_size，原因是GP分区在pg上也是独立的表.

test=# select pg_table_size('sales_1_prt_usa');
 pg_table_size 
---------------
        196672
(1 row)

test=# select pg_table_size('sales');          
 pg_table_size 
---------------
         65536
(1 row)

test=# select pg_table_size('sales_1_prt_asia');   
 pg_table_size 
---------------
         65536
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17