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
** 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文档中单独说明.
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文档中单独说明.