PostgreSQL之数据定义(二)表分区
PostgreSQL支持基本的表划分。
- 范围划分：表被根据一个关键列或一组列划分为“范围”，不同的分区的范围之间没有重叠。例如，我们可以根据日期范围划分，或者根据特定业务对象的标识符划分。
- 列表划分：通过显式地列出每一个分区中出现的键值来划分表。
- 哈希分区：通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余数。
语法格式
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_option ... ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ]
TEMPORARY or TEMP：如果指定，该表被创建为一个临时表。临时表会被在会话结束时自动被删除，或者也可以选择在当前事务结束时删除。当临时表存在时，已有的同名持久表将对于当前会话不可见，不过可以使用模式限定的名称进行引用。在一个临时表上创建的任何索引也自动地变为临时的。
UNLOGGED：如果指定，该表被创建为一个不受日志记录的表。被写入到不做日志的表中的数据不会被写到预写式日志中（见第 29 章），这让它们比普通表快非常多。不过，它们在崩溃时是不安全的：一个不做日志的表在一次崩溃或非干净关闭之后会被自动地截断。一个不做日志的表中的内容也不会被复制到后备服务器中。在一个不做日志的表上创建的任何索引也会自动地不被日志记录。
INHERITS ：可选的INHERITS子句指定一个表的列表， 新表将从其中自动地继承所有列。 父表可以是普通表或者外部表。INHERITS的使用在新的子表和它的父表之间创建一种持久的关系。 对于父表的模式修改通常也会传播到子表， 并且默认情况下子表的数据会被包括在对父表的扫描中。
PARTITION BY：可选的PARTITION BY子句指定了对表进行分区的策略。 这样创建的表称为分区表。 带括号的列或表达式的列表构成表的分区键。 使用范围或哈希分区时，分区键可以包含多个列或表达式（最多32个，但在构建 PostgreSQL时可以更改此限制）， 但对于列表分区，分区键必须由单个列或表达式组成。分区表被分成多个子表（称为分区），它们是使用单独的CREATE TABLE命令创建的。 分区表本身是空的。插入到表中的数据行将根据分区键中的列或表达式的值路由到分区。 如果没有现有的分区与新行中的值匹配，则会报告错误。
应用示例
1.通过指定PARTITION BY子句把measurement表创建为分区表
CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate);
2.创建分区。每个分区的定义必须指定对应于父表的分区方法和分区键的边界，分区以普通PostgreSQL表（或者可能是外部表）的方式创建。
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');
3.在分区表的键列上创建一个索引。这会自动在每个分区上创建一个索引，并且后来创建或者附着的任何分区也将会包含索引。
CREATE INDEX ON measurement (logdate);
4.确保enable_partition_pruning配置参数在postgresql.conf中没有被禁用。如果被禁用，查询将不会按照想要的方式被优化。允许或者禁止查询规划器从查询计划中消除一个分区表的分区。这也控制着规划器产生允许执行器在查询执行期间移除（忽略）分区的查询计划的能力。默认值是on。
分区维护
移除旧数据最简单的选择是删除掉不再需要的分区：
DROP TABLE measurement_y2006m02;
另一种通常更好的选项是把分区从分区表中移除，但是保留它作为一个独立的表：
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
分区剪枝
分区剪枝是一种提升声明式分区表性能的查询优化技术。
SET enable_partition_pruning = on; -− the default SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
如果没有分区剪枝，上面的查询将会扫描measurement表的每一个分区。如果启用了分区剪枝，规划器将会检查每个分区的定义并且检验该分区是否因为不包含符合查询WHERE子句的行而无需扫描。当规划器可以证实这一点时，它会把分区从查询计划中排除（剪枝）。
SET enable_partition_pruning = off; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; QUERY PLAN -−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−- Aggregate (cost=188.76..188.77 rows=1 width=8) -> Append (cost=0.00..181.05 rows=3085 width=0) -> Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2006m03 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date) ... -> Seq Scan on measurement_y2007m11 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2007m12 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date) -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date)
SET enable_partition_pruning = on; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; QUERY PLAN -−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−-−- Aggregate (cost=37.75..37.76 rows=1 width=8) -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date)
