10. 模式 Sche..
12. 表的继承和分区..

postgresql教程

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(二)

完结。

书籍推荐