PostgreSQL学习（三）—— 数据库表简介篇

PostgreSQL Table简单操作

PostgreSQL使用CTAS的方式, PG的CTAS方式的功能比较丰富, 具体使用:
create table t2 (like t1);

mydb=# create table t2 (like t1);
CREATE TABLE
mydb=# \dt
         List of relations
  Schema  | Name | Type  |  Owner   
----------+------+-------+----------
 postgres | t    | table | postgres
 postgres | t1   | table | postgres
 postgres | t2   | table | postgres
(3 rows)

但是可以包含以下子句:
INCLUDING DEFAULTS
INCLUDING CONSTRAINTS
INCLUDING INDEXES
INCLUDING STORAGE
INCLUDING COMMENTS
INCLUDING ALL  ==> 复制所有信息.

这里使用Create Table As Select * From T1;是一样的.


**  表的存储属性 ** 

属性 1. TOAST策略:

TOAST技术: "The Oversized-Attribute Storage Technique"

PG的Page大小是固定的8KB, 并且不允许横跨多个Page, 所以无法存储Large Objects, 所以大的字段通常被压缩、切片成地平个物理行存储到另一张系统表中(TOAST表).支持TOAST必须是变长的类型.

字段的TOAST策略:
PLAIN: 避免行外存储和压缩.
EXTENDED: 使用行外存储和压缩.
EXTERNAL: 使用行外存储, 避免压缩(对Text和Bytea字符操作更快)
MAIN: 允许压缩, 不允许行外存储. (如果可能尽量压缩来存储, 实在不行, 还是会行外存储)

T1.note(Varchar(20)) Row n -->  指针  --> TOAST表(<2K Chunk)
------------------------------------------------------------------------------
|Toast | chunk_id(Indexed)  | chunk_seq(Indexed)              chunk_data
|------ -------------------- ----------------------------- -------------------
|      |     id_1      |        Loc1                   Real Data 1 ...
|      |     id_2     |      Loc2     Real Data 2 ...
|      |     id_3     |      ....     ......
|      |     ...     |
|-----------------------------------------------------------------------------

1 Row  ---> N Chunks(N>=0)

指针的结构:
32bit长度字(2bit Flag(1 bit compressed, 1 bit 行外存储), 30bit Length(实际尺寸)) + Real Data或者指针.数据没有超过Page的最大限制, 则存储Real Data, 超出了就存储指针.


属性 2. Fillfactor  Toast.Fillfactor

有点像是Oracle的Pct_used. 若设置为60, 则Page使用 8 * 0.6 = 4.8K的时候,就不会再插入新的数据, 剩余空间被用来Update.

PostgreSQL的Update比较有意思, PG不会删除掉Block中的Old Row, 而是在Free Space中添加New Row,然后根据Heap-Only Tuple技术, 将Old Row的数据Relink到New Row,这样PG就不需要更新维护索引了. 看起来像是这样:

Index Leaf Node -----> Old Row -----> New Row

优点: 

    1. 索引维护代价极低

缺点: 

    1. 存储开销过大, 也就是说, 旧的Row会一直存在, 相当于一份数据存了两遍（只增数据）   

    2. Heap-Only Tuple不能跨越Block, 也就是说, 剩余空间不足的时候, 新数据插入新的Block,  并且更新索引的Leaf Node中的信息.

** Temporary Table **

PG的临时表, 相当于为每个会话创建一个临时Schema存放的, 其他的会话默认是无法看到这个表的, 除非\d pg_temp_xx.temp01,  但是无法访问该表(当然也无法插入)

mydb=# create temporary table temp_01
mydb-# (id int primary key , note text);
CREATE TABLE
mydb=# 
mydb=# 
mydb=# \d
           List of relations
  Schema   |  Name   | Type  |  Owner   
-----------+---------+-------+----------
 pg_temp_2 | temp_01 | table | postgres
 postgres  | t       | table | postgres
 postgres  | t1      | table | postgres
 postgres  | t2      | table | postgres
(4 rows)

mydb=# insert into temp_01 values(1,'AAA');
INSERT 0 1
mydb=# commit;
WARNING:  there is no transaction in progress
COMMIT
mydb=# select * from temp_01;
 id | note 
----+------
  1 | AAA
(1 row)


mydb=# \q
[postgres@orastb pg_error_log]$ psql mydb
psql (9.6.9)
Type "help" for help.


mydb=# \d
         List of relations
  Schema  | Name | Type  |  Owner   
----------+------+-------+----------
 postgres | t    | table | postgres
 postgres | t1   | table | postgres
 postgres | t2   | table | postgres
(3 rows)

我们可以看到, 退出会话后, 进入另一个会话周期, 该临时表就不见了.

临时表语句子句:
ON COMMIT DELETE ROWS: 事务提交的时候, 删除所有的行数据.
ON COMMIT PRESERVE ROWS: 事务提交的时候, 保留数据, 但是会话周期结束后, drop该表(默认)
ON COMMIT DROP: 视图提交后drop该临时表.


** 表继承 & 表分区 **

表继承, 在我看来是个还不错的功能, 对汇聚功能的实现比较好用.

mydb=# create table t (id int , name varchar(10) ,constraint p_t primary key(id));
CREATE TABLE
mydb=# create table t_class_1 (id int , name varchar(10) not null ,constraint p_t_class_1 primary key(id)) inherits (t);
NOTICE:  merging column "id" with inherited definition
NOTICE:  merging column "name" with inherited definition
CREATE TABLE
mydb=# create table t_class_2 (id int , name varchar(10) not null ,constraint p_t_class_2 primary key(id)) inherits (t);
NOTICE:  merging column "id" with inherited definition
NOTICE:  merging column "name" with inherited definition
CREATE TABLE

mydb=# insert into t_class_1 values (1,'NiuJinlin');
INSERT 0 1
mydb=# insert into t_class_2 values (1,'LiMengXun');
INSERT 0 1
mydb=# select * from t;
 id |   name    
----+-----------
  1 | NiuJinlin
  1 | LiMengXun
(2 rows)

mydb=# select * from t_class_1;
 id |   name    
----+-----------
  1 | NiuJinlin
(1 row)

mydb=# select * from t_class_2;
 id |   name    
----+-----------
  1 | LiMengXun
(1 row)

这里逻辑上应该设计Class#, 并且把id设置为学号之类的信息,避免冲突.


mydb=# insert into t(id) values(2);
INSERT 0 1
mydb=# select * from t;
 id |   name    
----+-----------
  2 | 
  1 | NiuJinlin
  1 | LiMengXun
(3 rows)

mydb=# select * from t_class_1;
 id |   name    
----+-----------
  1 | NiuJinlin
(1 row)

mydb=# select * from t_class_2;
 id |   name    
----+-----------
  1 | LiMengXun
(1 row)

这里对子表的插入时不影响父表的.在一些教程里说Sub Table继承Not Null约束, 但是在我的测试里面是没有的, 可能是因为版本问题, 这里我的PG是9.6.9
分区表将会在PG_Partitions.txt文档中单独说明.