postgresql 创建分表

划分指的是将逻辑上的一个大表分成一些小的物理上的片。划分有很多益处：

　　1、在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。

　　2、当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。

　　3、如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。ALTER TABLE NO INHERIT和DROP TABLE都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。

　　4、很少使用的数据可以被迁移到便宜且较慢的存储介质上。

当一个表非常大时，划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决于应用，一个经验法则是当表的尺寸超过了数据库服务器物理内存时，划分会为表带来好处。

目前，PostgreSQL支持通过表继承来进行划分。每一个分区被创建为父表的一个子表。父表本身通常是空的，它的存在仅仅为了表示整个数据集。

例如: select  * from only tb_log，可以查看到主表并未存储数据。

 

范围划分

　　表被根据一个关键列或一组列划分为"范围"，不同的分区的范围之间没有重叠。例如，我们可以根据日期范围划分，或者根据特定业务对象的标识符划分。

列表划分

　　通过显式地列出每一个分区中出现的键值来划分表。

 

 

实现划分

 

 

1、创建分表并关联主表(可以根据年份，季度和或者月份来创建分表)

　　例：表名为tb_log，createtime 表示tb_log的一个字段，记录时间

　　

CREATE TABLE tb_log_2017 (
    CHECK (  createtime >= DATE '2017-01-01' AND  createtime < DATE '2018-01-01' )
) INHERITS (tb_log);
CREATE TABLE tb_log_2018 (
    CHECK (  createtime >= DATE '2018-01-01' AND  createtime < DATE '2019-01-01' )
) INHERITS (tb_log);

 

 

　　-- 这里创建了tb_log_2017 和 tb_log_2018 两张分表.

　　** 这个语句创建的分表没有主键，手动为每张分表加上主键

 

2、创建触发器函数

　　

CREATE OR REPLACE FUNCTION function_insert_tb_log()
RETURNS TRIGGER AS $$
BEGIN
	IF ( NEW.createtime >= DATE '2017-01-01' AND  NEW.createtime < DATE '2018-01-01' ) THEN
        INSERT INTO tb_log_2017 VALUES (NEW.*);
    ELSIF ( NEW.createtime >= DATE '2018-01-01' AND  NEW.createtime < DATE '2019-01-01' ) THEN
        INSERT INTO tb_log_2018 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the function_insert_tb_log() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

　　

 

 

3、创建触发器

　　

CREATE TRIGGER trigger_insert_tb_log
    BEFORE INSERT ON tb_log
    FOR EACH ROW 
    EXECUTE PROCEDURE function_insert_tb_log();

 

 

 

备注：维护表的时候，只要执行第1步和第2步就可以了，比如到2019年的时候，再用第1步的格式去创建2019年的分表(tb_log_2019)，然后修改一下第2步的逻辑，执行一下就可以了。

** 使用Hibernate的时候注意,hibernate的save方法有期待返回的保存结果，分表之后保存并不会返回保存结果，所以要在实体里加入下面这行代码:

@SQLInsert(sql = "insert into tb_log (id,column1,column2) values (?,?,?)")