1. 介绍

PostgreSQL的分区是建立在继承的基础上的,所以先来讲讲继承。

2. 继承

继承指的是表的继承,就是一个表继承自另一个表,字段也继承自父表,跟面向对象的概念差不多。因为有时候几张表就是具有差不多的属性或字段,唯一有区别的就是其中一两个字段,这个时候可以用继承来简化操作和管理。

比如,如果不用继承,会像下面这样处理的。

CREATE TABLE capitals (
  name       text,
  population real,
  altitude   int,    -- (in ft)
  state      char(2)
);

CREATE TABLE non_capitals (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE VIEW cities AS
  SELECT name, population, altitude FROM capitals
    UNION
  SELECT name, population, altitude FROM non_capitals;

要查找那两张表就得使用union语句。

而使用继承就是这样处理的。

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

这样就创建了两张表,插入(insert)数据之后就可以用select来查询的。

3. 分区

PostgreSQL-partitioning对分区作了完整的描述。

分区是数据库的一种设计实现方法。我们知道,当一张表的数据越来越多时,假如到了上亿条或几十亿条记录,对这张表的操作都会比较慢,比如,查询,更改等。而分区技术就是把这一张大表分成几个逻辑分片。分区之后有很多好处:

  • 单个分区表的索引和表都变小了,可以保持在内存里面,适合把热数据从大表拆分出来的场景;
  • 对于大范围的查询,大表可以通过索引来避免全表扫描,但是如果分区的话,可以使用分区的全表扫描;
  • 大批量的数据导入或删除,删除大量的数据使用DELETE会很慢,可是如果使用分区表,直接drop或truncate整个分区表即可;

而分区技术就是基于上面所提的继承技术来实现的。

PostgreSQL实现了两种分区。

  • Range Partitioning:比如数值范围,时间范围等。
  • List Partitioning: 按照固定的值。

4. 实战分区

其中一种实现分区的方法是基于继承并配合触发器来实现。

先创建母表,它其实是一张只有数据结构的表。

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

创建分区表,用时间范围来分区。

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
    CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
    CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
    CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);

check指定的是约束条件,按照时间来规定范围。

按照需要可以添加索引。

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

当执行INSERT INTO measurement ...时,为了让数据插入到正确的分区表上,我们需要创建触发器来实现这个逻辑。

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

这样就OK了。

另外来实现同样插入逻辑的方式是用rule(规则)。

CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);

具体的详细可以阅读官方文档

下一篇: PostgreSQL的表的继承和分区之pg_partman(二)

完结。


书籍推荐