PostgreSQL大数据表的分区存储
发布时间:2020-12-13 16:55:41 所属栏目:百科 来源:网络整理
导读: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.stu
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,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,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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |