Postgresql 从小白到高手 五 ：postgresql的继承特性以及分区表创建

一 ：继承特性
Postgredsql 的空间结构为 库 database—页 schemas–表 table。
表有父表和子表 。
创建：


子表继承父表的所有字段，并且有自己独有的state字段 。
查询：
在PostgreSQL中，一个表可以从0个或者多个其他表继承，而对一个表的查询则可以引用一个表的所有行或者该表的所有行加上它所有的后代表。 默认情况是后一种行为。
SELECT name, elevation FROM cities ；
结果展示父表和子表的数据。
SELECT name, elevation FROM ONLY cities；
结果只展示该表的数据 。
插入数据：
继承不会自动地将来自INSERT或COPY命令的数据传播到继承层次中的其他表中。
INSERT总是向指定的表中插入。

继承表一般在创建时候建立，但后生的表满足子表有父表所有字段等约束时候，可以使用ALTER TABLE的INHERIT命令。
例如：ALTER TABLE travel_cities inherit cities ;
新的子表必须已经包括和父表相同名称和数据类型的列。子表还必须包括和父表相同的检查约束和检查表达式。
ALTER TABLE的 NO INHERIT命令从一个子表集中移除子表。
例如：ALTER TABLE travel_cities no inherit cities ;
一种创建一个未来将被用做子女的新表的方法是在CREATE TABLE中使用LIKE子句。这将创建一个和源表具有相同列的新表。如果源表上定义有任何CHECK约束，LIKE的INCLUDING CONSTRAINTS选项可以用来让新的子表也包含和父表相同的约束。
CASCADE选项删除父表及其子表 。
ALTER TABLE将会把列的数据定义或检查约束上的任何变化沿着继承层次向下传播。
权限的继承：
子表继承父表。子表的安全策略会把查询和更新权限从父表中继承。

并非所有的SQL命令都能工作在继承层次上。用于数据查询、数据修改或模式修改（例如SELECT、UPDATE、DELETE、大部分ALTER TABLE的变体，但INSERT或ALTER TABLE … RENAME不在此列）的命令会默认将子表包含在内并且支持ONLY记号来排除子表。负责数据库维护和调整的命令（如REINDEX、VACUUM）只工作在独立的、物理的表上并且不支持在继承层次上的递归。

二： 表分区

1. 分区
   划分指的是将逻辑上的一个大表分成一些小的物理上的片。划分有很多益处：
   在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。分区有效地替代了索引的上层树级别，使得索引中被大量使用的部分更有可能适合内存。
   当查询或更新访问单个分区的很大一部分时，可以通过使用该分区的顺序扫描来提高性能，而不是使用索引，这将需要分散在整个表中的随机访问读取。
   如果分区设计中考虑了使用模式，则可以通过添加或删除分区来完成批量加载和删除。使用DROP TABLE删除单个分区，或执行ALTER TABLE DETACH PARTITION操作，比批量操作快得多。这些命令还完全避免了批量DELETE所导致的VACUUM开销。
   很少使用的数据可以被迁移到便宜且较慢的存储介质上。

2. 分区的类型
   范围划分：
   表被根据一个关键列或一组列划分为“范围”，不同的分区的范围之间没有重叠。例如，我们可以根据日期范围划分，或者根据特定业务对象的标识符划分。 例如 Oracle的行分区，每月一个分区数据表。
   列表划分：
   通过显式地列出每一个分区中出现的键值来划分表。例如Oracle的列分区，前缀加业务值来创建分区表。
   哈希分区:
   通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余数。

3. 分区表的创建
   分区表本身是一个“虚拟”表，没有自己的存储空间。相反，存储属于分区，这些分区是与分区表关联的普通表。
   CREATE TABLE tname(
   )PARTITION BY { RANGE | LIST | HASH } //三种分区
   ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, … ] )
   a.申明表为分区
   CREATE TABLE measurement (
   city_id int not null,
   logdate date not null,
   peaktemp int,
   unitsales int
   ) PARTITION BY RANGE (logdate);
   b.创建分区表
   CREATE TABLE measurement_y2006m02 PARTITION OF measurement
   FOR VALUES FROM (‘2006-02-01’) TO (‘2006-03-01’);
   CREATE TABLE measurement_y2006m03 PARTITION OF measurement
   FOR VALUES FROM (‘2006-03-01’) TO (‘2006-04-01’);

c.创建子分区
CREATE TABLE measurement_y2006m04 PARTITION OF measurement
FOR VALUES FROM (‘2006-04-01’) TO (‘2006-05-01’)
PARTITION BY RANGE (peaktemp);
时间范围为左闭右开，数学符号[ );

4. 分区表索引
   在分区表的键列以及任何其它索引上创建索引。会自动在每个分区上创建一个匹配的索引，你稍后创建或附加的任何分区也会有这样的索引。
   CREATE INDEX ON measurement (logdate);

5. 列分区表
   –分区
   CREATE TABLE sell (
   bus_id int not null,
   logdate varchar not null,
   peaktemp int,
   unitsales int
   ) PARTITION BY LIST (logdate);
   – 创建分区
   CREATE TABLE shall_1 PARTITION OF sell FOR VALUES in (‘2021-08-05’);
   CREATE TABLE shall_2 PARTITION OF sell FOR VALUES in (‘2021-08-06’);
   – 插入数据 数据会根据city_id分配到对应的分区
   insert into sell values(1,‘2021-08-05’);
   insert into sell values(2,‘2021-08-05’);
   insert into sell values(3,‘2021-08-05’);
   insert into sell values(4,‘2021-08-05’);
   insert into sell values(5,‘2021-08-06’);
   insert into sell values(6,‘2021-08-06’);
   insert into sell values(7,‘2021-08-06’);
   insert into sell values(8,‘2021-08-06’);
   

6. 哈希分区表
   CREATE TABLE parent_table3 (
   bid SERIAL PRIMARY KEY,
   proname varchar(100) ,
   script int
   ) PARTITION BY HASH (bid);

CREATE TABLE parent_table_part1 PARTITION OF parent_table3 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE parent_table_part2 PARTITION OF parent_table3 FOR VALUES WITH (MODULUS 4, REMAINDER 1);

hash_key列的哈希值如果对4余数0，将会落在parent_table_part1分区；对4余数1的值将会落在parent_table_part2分区，依此类推。
这样，通过在id列上进行哈希分区，数据将会根据id的哈希值分布在不同的分区中。

10. 分区维护
    删除 分区
    DROP TABLE measurement_y2006m02;
    ALTER TABLE DETACH PARTITION操作，比批量操作快。
    移除分区成为普通表
    ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
    ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;