SQL优化（三） PostgreSQL Table Partitioning

原创文章，转载请务必在文章开头处注明出自Jason’s Blog，并给出原文链接http://www.jasongj.com/2015/12/13/SQL3_partition/

摘要

　　本文介绍了数据库分区表的使用场景，优势，原理，及在PostgreSQL中的实现和注意事项。

典型使用场景

　　随着使用时间的增加，数据库中的数据量也不断增加，因此数据库查询越来越慢。
　　加速数据库的方法很多，如添加特定的索引，将日志目录换到单独的磁盘分区，调整数据库引擎的参数等。这些方法都能将数据库的查询性能提高到一定程度。

　　对于许多应用数据库来说，许多数据是历史数据并且随着时间的推移它们的重要性逐渐降低。如果能找到一个办法将这些可能不太重要的数据隐藏，数据库查询速度将会大幅提高。可以通过DELETE来达到此目的，但同时这些数据就永远不可用了。
　　因此，需要一个高效的把历史数据从当前查询中隐藏起来并且不造成数据丢失的方法。本文即将介绍的数据库表分区即能达到此效果。

数据库表分区介绍

　　数据库表分区把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。

数据库表分区术语介绍

• 主表 / 父表 / Master Table　该表是创建子表的模板。它是一个正常的普通表，但正常情况下它并不储存任何数据。
• 子表 / 分区表 / Child Table / Partition Table　这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表

数据库表分区的优势

• 在特定场景下，查询性能极大提高，尤其是当大部分经常访问的数据记录在一个或少数几个分区表上时。表分区减小了索引的大小，并使得常访问的分区表的索引更容易保存于内存中。
• 当查询或者更新访问一个或少数几个分区表中的大部分数据时，可以通过顺序扫描该分区表而非使用大表索引来提高性能。
• 可通过添加或移除分区表来高效的批量增删数据。如可使用ALTER TABLE NO INHERIT可将特定分区从主逻辑表中移除（该表依然存在，并可单独使用，只是与主表不再有继承关系并无法再通过主表访问该分区表），或使用DROP TABLE直接将该分区表删除。这两种方式完全避免了使用DELETE时所需的VACUUM额外代价。

• 很少使用的数据可被迁移到便宜些的慢些的存储介质中

  　　以上优势只有当表非常大的时候才能体现出来。一般来说，当表的大小超过数据库服务器的物理内存时以上优势才能体现出来

PostgreSQL表分区

　　现在PostgreSQL支持通过表继承来实现表的分区。父表是普通表并且正常情况下并不存储任何数据，它的存在只是为了代表整个数据集。PostgreSQL可实现如下两种表分区
- 范围分区　每个分区表包含一个或多个字段组合的一部分，并且每个分区表的范围互不重叠。比如可近日期范围分区
- 列表分区　分区表显示列出其所包含的key值

表分区在PostgreSQL上的实现

在PostgreSQL中实现表分区的步骤

1. 创建主表。不用为该表定义任何检查限制，除非需要将该限制应用到所有的分区表中。同样也无需为该表创建任何索引和唯一限制。

CREATE TABLE almart
(
    date_key date,
    hour_key smallint,
    client_key integer,
    item_key integer,
    account integer,
    expense numeric
);

1. 创建多个分区表。每个分区表必须继承自主表，并且正常情况下都不要为这些分区表添加任何新的列。

CREATE TABLE almart_2015_12_10 () inherits (almart);

CREATE TABLE almart_2015_12_11 () inherits (almart);

CREATE TABLE almart_2015_12_12 () inherits (almart);

CREATE TABLE almart_2015_12_13 () inherits (almart);

1. 为分区表添加限制。这些限制决定了该表所能允许保存的数据集范围。这里必须保证各个分区表之间的限制不能有重叠。

ALTER TABLE almart_2015_12_10
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-10'::date);

ALTER TABLE almart_2015_12_11
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-11'::date);

ALTER TABLE almart_2015_12_12
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-12'::date);

ALTER TABLE almart_2015_12_13
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-13'::date);

1. 为每一个分区表，在主要的列上创建索引。该索引并不是严格必须创建的，但在大部分场景下，它都非常有用。

CREATE INDEX almart_date_key_2015_12_10
ON almart_2015_12_10 (date_key);

CREATE INDEX almart_date_key_2015_12_11
ON almart_2015_12_11 (date_key);

CREATE INDEX almart_date_key_2015_12_12
ON almart_2015_12_12 (date_key);

CREATE INDEX almart_date_key_2015_12_13
ON almart_2015_12_13 (date_key);

1. 定义一个trigger或者rule把对主表的数据插入操作重定向到对应的分区表。

--创建分区函数
CREATE OR REPLACE FUNCTION almart_partition_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.date_key =