注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

星期五

明天不上班

 
 
 

日志

 
 
关于我

一个特立独行的Java程序员,比较宅,上上网,写博客,听音乐,看电影。

网易考拉推荐

(转载)Partition Table In PostgreSQL (Create Partition) – Part 1  

2012-05-16 20:03:45|  分类: Postgresql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
原文地址:http://www.mkyong.com/database/partition-table-in-postgresql-create-partition-part-1/

Partition table in PostgreSQL is very easy to do, It involve inheritance concept and trigger of PostgreSQL. Here i provide a sample to demonstrate how to partition table in PostgreSQL.

Before proceed, please understand some basic concept like,er… better i provide a concept of partition “time” in a table.

“Mother” — (Child1, Child2, Child3,Child4, Child5)

MasterTable — (Child_01_2008, Child_02_2008, Child_03_2008, Child_04_2008,Child_05_2008)

We only insert, select, update and delete on MasterTable, all child tables are transparent to user.

For example when i insert a record into MasterTable which is at January 2008. Record will auto redirect (trigger) to child table (Child_01_2008). When user select a record from Master table, postgreSQL will automatically retrieve data from all child tables which inherited from MasterTable.

1)Create a simple table call “hashvalue_PT” , it only include 2 columns “hash” and “hashtime”

CREATE TABLE hashvalue_PT
(
hash bytea NOT NULL,
hashtime TIMESTAMP WITHOUT TIME zone NOT NULL
);

2) Create 10 tables in different months and inherantence from main hashvalue_PT table.

--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) Create primary key for each child tables

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 an index for each child tables

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 on mother table to redirect records into child tables.

--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) Done, Simple

Next Session i will create a function to insert million of data from partition table to test performance between partition and non partition table. Please visit Partition Table In PostgreSQL (Simulate Millions Data) – Part 2

  评论这张
 
阅读(632)| 评论(0)
推荐

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017