PostgreSQL大数据表的分区存储

为什么80%的码农都做不了架构师？>>>   

1.创建主表和B+tree索引：

CREATE TABLE public.student
(
  studentid integer NOT NULL,
  gradeid integer NOT NULL,
  classid integer NOT NULL,
  datetime timestamp(3) without time zone NOT NULL,
  data real NOT NULL
)
CREATE INDEX datetimeindex
  ON public.student
  USING btree
  (datetime)
  WITH (FILLFACTOR=95);

2.以一个季度为限，每个月分一个表存储

CREATE TABLE public.student_y2017m01
(
-- 継承 from table student:  studentid integer NOT NULL,
-- 継承 from table student:  gradeid integer NOT NULL,
-- 継承 from table student:  classid integer NOT NULL,
-- 継承 from table student:  datetime timestamp(3) without time zone NOT NULL,
-- 継承 from table student:  data real NOT NULL,
  CONSTRAINT student_y2017m01_datetime_check CHECK (datetime >= '2017-01-01T00:00:00' AND datetime < '2017-02-01T00:00:00')
)
INHERITS (public.student)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.student_y2017m01
  OWNER TO postgres;

CREATE TABLE public.student_y2017m02
(
-- 継承 from table student:  studentid integer NOT NULL,
-- 継承 from table student:  gradeid integer NOT NULL,
-- 継承 from table student:  classid integer NOT NULL,
-- 継承 from table student:  datetime timestamp(3) without time zone NOT NULL,
-- 継承 from table student:  data real NOT NULL,
  CONSTRAINT student_y2017m02_datetime_check CHECK (datetime >= '2017-02-01T00:00:00' AND datetime < '2017-03-01T00:00:00')
)
INHERITS (public.student)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.student_y2017m02
  OWNER TO postgres;

CREATE TABLE public.student_y2017m03
(
-- 継承 from table student:  studentid integer NOT NULL,
-- 継承 from table student:  gradeid integer NOT NULL,
-- 継承 from table student:  classid integer NOT NULL,
-- 継承 from table student:  datetime timestamp(3) without time zone NOT NULL,
-- 継承 from table student:  data real NOT NULL,
  CONSTRAINT student_y2017m03_datetime_check CHECK (datetime >= '2017-02-03T00:00:00' AND datetime < '2017-04-01T00:00:00')
)
INHERITS (public.student)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.student_y2017m03
  OWNER TO postgres;

3. 为每一张分区表创建索引

-- Index: public.student_y2017m01_datetime

-- DROP INDEX public.student_y2017m01_datetime;

CREATE INDEX student_y2017m01_datetime
  ON public.student_y2017m01
  USING btree
  (datetime);

-- Index: public.student_y2017m02_datetime

-- DROP INDEX public.student_y2017m02_datetime;

CREATE INDEX student_y2017m02_datetime
  ON public.student_y2017m02
  USING btree
  (datetime);

-- Index: public.student_y2017m03_datetime

-- DROP INDEX public.student_y2017m03_datetime;

CREATE INDEX student_y2017m03_datetime
  ON public.student_y2017m03
  USING btree
  (datetime);

4.创建触发器函数

CREATE OR REPLACE FUNCTION public.student_insert_trigger()
  RETURNS trigger AS
$BODY$
BEGIN
    IF ( NEW.datetime >= '2017-01-01T00:00:00' AND
         NEW.datetime <  '2017-02-01T00:00:00' ) THEN
        INSERT INTO student_y2017m01 VALUES (NEW.*);
    ELSIF ( NEW.datetime >=  '2017-02-01T00:00:00' AND
         NEW.datetime <  '2017-03-01T00:00:00' ) THEN
        INSERT INTO student_y2017m02 VALUES (NEW.*);
    ELSIF ( NEW.datetime >=  '2017-03-01T00:00:00' AND
         NEW.datetime <  '2017-04-01T00:00:00' ) THEN
        INSERT INTO student_y2017m03 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the student_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.student_insert_trigger()
  OWNER TO postgres;

5.创建调用4.的函数的触发器

CREATE TRIGGER insert_student_trigger
    BEFORE INSERT ON student
    FOR EACH ROW EXECUTE PROCEDURE student_insert_trigger();

OK了，这样student这张表就会按月份来分表存储。

维护的话，随着时间的推移，程序中做一个定时器，定期去创建（例：月末一周前）、drop（例：月初一周后）分区表和索引就可以了。

参考文档：

https://www.postgresql.jp/document/9.4/html/ddl-partitioning.html

SQL Server分区表参考（中文文档参考性太差）

https://techinfoofmicrosofttech.osscons.jp/index.php?SQL%20Server%20%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E5%88%86%E5%89%B2

转载于:https://my.oschina.net/mj23/blog/888588