PostgreSQL中的分区表很容易做到,它涉及PostgreSQL的继承概念和触发器。 在这里,我提供了一个示例来演示如何在PostgreSQL中对表进行分区。
在继续之前,请了解一些基本概念,例如,更好……我在表中提供分区“时间”的概念。
“母亲” —(Child1,Child2,Child3,Child4,Child5)
主表-(Child_01_2008,Child_02_2008,Child_03_2008,Child_04_2008,Child_05_2008)
我们仅在MasterTable上插入,选择,更新和删除,所有子表对用户都是透明的。
例如,当我在2008年1月向MasterTable中插入一条记录时,记录将自动重定向(触发)到子表(Child_01_2008)。 当用户从Master表中选择一条记录时,postgreSQL将自动从所有继承于MasterTable的子表中检索数据。
1)创建一个简单的表调用“ hashvalue_PT”,它仅包含两列“ hash”和“ hashtime”
CREATE TABLE hashvalue_PT
(
hash bytea NOT NULL,
hashtime timestamp without time zone NOT NULL
);
2)在不同月份创建10个表,并从主hashvalue_PT表创建继承。
--Create Partition with check rule for validation
CREATE TABLE hashvalue_PT_y2008m01 (
CHECK ( hashtime >= DATE '2008-01-01' AND hashtime < DATE '2008-01-31' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m02 (
CHECK ( hashtime >= DATE '2008-02-01' AND hashtime < DATE '2008-02-29' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m03 (
CHECK ( hashtime >= DATE '2008-03-01' AND hashtime < DATE '2008-03-31' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m04 (
CHECK ( hashtime >= DATE '2008-04-01' AND hashtime < DATE '2008-04-30' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m05 (
CHECK ( hashtime >= DATE '2008-05-01' AND hashtime < DATE '2008-05-31' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m06 (
CHECK ( hashtime >= DATE '2008-06-01' AND hashtime < DATE '2008-06-30' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m07 (
CHECK ( hashtime >= DATE '2008-07-01' AND hashtime < DATE '2008-07-31' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m08 (
CHECK ( hashtime >= DATE '2008-08-01' AND hashtime < DATE '2008-08-31' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m09 (
CHECK ( hashtime >= DATE '2008-09-01' AND hashtime < DATE '2008-09-30' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m010 (
CHECK ( hashtime >= DATE '2008-10-01' AND hashtime < DATE '2008-10-31' )
) INHERITS (hashvalue_PT);
3)为每个子表创建主键
ALTER TABLE hashvalue_PT_y2008m01 ADD CONSTRAINT hashvalue_PT_y2008m01_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m02 ADD CONSTRAINT hashvalue_PT_y2008m02_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m03 ADD CONSTRAINT hashvalue_PT_y2008m03_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m04 ADD CONSTRAINT hashvalue_PT_y2008m04_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m05 ADD CONSTRAINT hashvalue_PT_y2008m05_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m06 ADD CONSTRAINT hashvalue_PT_y2008m06_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m07 ADD CONSTRAINT hashvalue_PT_y2008m07_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m08 ADD CONSTRAINT hashvalue_PT_y2008m08_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m09 ADD CONSTRAINT hashvalue_PT_y2008m09_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m010 ADD CONSTRAINT hashvalue_PT_y2008m010_pkey PRIMARY KEY (hashtime, hash);
4)为每个子表创建一个索引
CREATE INDEX idx_hashvalue_PT_y2008m01_hashtime ON hashvalue_PT_y2008m01 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m02_hashtime ON hashvalue_PT_y2008m02 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m03_hashtime ON hashvalue_PT_y2008m03 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m04_hashtime ON hashvalue_PT_y2008m04 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m05_hashtime ON hashvalue_PT_y2008m05 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m06_hashtime ON hashvalue_PT_y2008m06 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m07_hashtime ON hashvalue_PT_y2008m07 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m08_hashtime ON hashvalue_PT_y2008m08 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m09_hashtime ON hashvalue_PT_y2008m09 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m010_hashtime ON hashvalue_PT_y2008m010 (hashtime);
5)在母表上创建触发器以将记录重定向到子表中。
--create a trigger to redirect records to child table
CREATE OR REPLACE FUNCTION hashvalue_PT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.hashtime >= DATE '2008-01-01' AND NEW.hashtime < DATE '2008-01-31' ) THEN
INSERT INTO hashvalue_PT_y2008m01 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-02-01' AND NEW.hashtime < DATE '2008-02-29' ) THEN
INSERT INTO hashvalue_PT_y2008m02 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-03-01' AND NEW.hashtime < DATE '2008-03-31' ) THEN
INSERT INTO hashvalue_PT_y2008m03 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-04-01' AND NEW.hashtime < DATE '2008-04-30' ) THEN
INSERT INTO hashvalue_PT_y2008m04 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-05-01' AND NEW.hashtime < DATE '2008-05-31' ) THEN
INSERT INTO hashvalue_PT_y2008m05 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-06-01' AND NEW.hashtime < DATE '2008-06-30' ) THEN
INSERT INTO hashvalue_PT_y2008m06 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-07-01' AND NEW.hashtime < DATE '2008-07-31' ) THEN
INSERT INTO hashvalue_PT_y2008m07 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-08-01' AND NEW.hashtime < DATE '2008-08-31' ) THEN
INSERT INTO hashvalue_PT_y2008m08 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-09-01' AND NEW.hashtime < DATE '2008-09-30' ) THEN
INSERT INTO hashvalue_PT_y2008m09 VALUES (NEW.*);
ELSIF ( NEW.hashtime >= DATE '2008-10-01' AND NEW.hashtime < DATE '2008-10-31' ) THEN
INSERT INTO hashvalue_PT_y2008m010 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER trigger_hashvalue_PT_insert
BEFORE INSERT ON hashvalue_PT
FOR EACH ROW EXECUTE PROCEDURE hashvalue_PT_func_insert_trigger();
6)完成,简单
下次会话i将创建一个函数,以从分区表中插入数百万个数据,以测试分区表与非分区表之间的性能。 请访问PostgreSQL中的分区表(模拟百万数据)–第2部分
翻译自: https://mkyong.com/database/partition-table-in-postgresql-create-partition-part-1/