PostgreSQL建立分区表

  • lihm01

    2012-12-20 17:36:37
  • 数据库开发技术
  • 原创
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE MicrosoftInternetExplorer4

PostgreSQL建立分区表

PostgreSQL里没有类似MySQL或Oracle里类似的分区表，数据的插入或删除需要用触发器或规则来完成，可以说是半自动的，但也给我们认为控制带来了方便，下面是建立分区表的实例，总的来说分三步：

1） 建立主表

2） 建立分表

3） 为主表创建触发器函数

4） 为主表创建触发器

5） 导入数据并测试

1） 建立主表

CREATE TABLE rpt_traffic_src (

site_id numeric,

data_date numeric,

country character varying(64),

medium character varying(64),

source character varying(64),

real_source character varying(1024),

campaign character varying(512),

search_keyword character varying(512),

bid_keyword character varying(512),

is_new numeric(1,0),

visits numeric,

pv numeric,

time_on_site numeric,

bounces numeric,

register_conv numeric,

register_order_conv numeric,

order_conv numeric,

paid_conv numeric,

orders numeric,

paid_orders numeric,

revenue numeric,

express_orders numeric,

insert_time date,

update_time date

);

CREATE INDEX idx_rpt_traffic_src_sd ON rpt_traffic_src USING btree (site_id, data_date);

2） 建立分表

create table rpt_traffic_src_201201(check (data_date>=1325347200+86400*30*0 and data_date<1325347200+86400*30*1)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201202(check (data_date>=1325347200+86400*30*1 and data_date<1325347200+86400*30*2)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201203(check (data_date>=1325347200+86400*30*2 and data_date<1325347200+86400*30*3)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201204(check (data_date>=1325347200+86400*30*3 and data_date<1325347200+86400*30*4)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201205(check (data_date>=1325347200+86400*30*4 and data_date<1325347200+86400*30*5)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201206(check (data_date>=1325347200+86400*30*5 and data_date<1325347200+86400*30*6)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201207(check (data_date>=1325347200+86400*30*6 and data_date<1325347200+86400*30*7)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201208(check (data_date>=1325347200+86400*30*7 and data_date<1325347200+86400*30*8)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201209(check (data_date>=1325347200+86400*30*8 and data_date<1325347200+86400*30*9)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201210(check (data_date>=1325347200+86400*30*9 and data_date<1325347200+86400*30*10)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201211(check (data_date>=1325347200+86400*30*10 and data_date<1325347200+86400*30*11)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201212(check (data_date>=1325347200+86400*30*11 and data_date<1325347200+86400*30*12)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201301(check (data_date>=1325347200+86400*30*12 and data_date<1325347200+86400*30*13)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201302(check (data_date>=1325347200+86400*30*13 and data_date<1325347200+86400*30*14)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201303(check (data_date>=1325347200+86400*30*14 and data_date<1325347200+86400*30*15)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201304(check (data_date>=1325347200+86400*30*15 and data_date<1325347200+86400*30*16)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201305(check (data_date>=1325347200+86400*30*16 and data_date<1325347200+86400*30*17)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201306(check (data_date>=1325347200+86400*30*17 and data_date<1325347200+86400*30*18)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201307(check (data_date>=1325347200+86400*30*18 and data_date<1325347200+86400*30*19)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201308(check (data_date>=1325347200+86400*30*19 and data_date<1325347200+86400*30*20)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201309(check (data_date>=1325347200+86400*30*20 and data_date<1325347200+86400*30*21)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201310(check (data_date>=1325347200+86400*30*21 and data_date<1325347200+86400*30*22)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201311(check (data_date>=1325347200+86400*30*22 and data_date<1325347200+86400*30*23)) inherits(rpt_traffic_src);

create table rpt_traffic_src_201312(check (data_date>=1325347200+86400*30*23 and data_date<1325347200+86400*30*24)) inherits(rpt_traffic_src);

3） 为主表创建触发器函数

CREATE OR REPLACE FUNCTION fun_for_trigger_insert_rpt_traffic_src()

RETURNS "trigger" AS

$BODY$

BEGIN

IF( NEW.data_date>=1325347200+86400*30*0 and NEW.data_date<1325347200+86400*30*1 ) THEN

INSERT INTO rpt_traffic_src_201201 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*1 and NEW.data_date<1325347200+86400*30*2 ) THEN

INSERT INTO rpt_traffic_src_201202 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*2 and NEW.data_date<1325347200+86400*30*3 ) THEN

INSERT INTO rpt_traffic_src_201203 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*3 and NEW.data_date<1325347200+86400*30*4 ) THEN

INSERT INTO rpt_traffic_src_201204 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*4 and NEW.data_date<1325347200+86400*30*5 ) THEN

INSERT INTO rpt_traffic_src_201205 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*5 and NEW.data_date<1325347200+86400*30*6 ) THEN

INSERT INTO rpt_traffic_src_201206 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*6 and NEW.data_date<1325347200+86400*30*7 ) THEN

INSERT INTO rpt_traffic_src_201207 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*7 and NEW.data_date<1325347200+86400*30*8 ) THEN

INSERT INTO rpt_traffic_src_201208 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*8 and NEW.data_date<1325347200+86400*30*9 ) THEN

INSERT INTO rpt_traffic_src_201209 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*9 and NEW.data_date<1325347200+86400*30*10 ) THEN

INSERT INTO rpt_traffic_src_201210 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*10 and NEW.data_date<1325347200+86400*30*11 ) THEN

INSERT INTO rpt_traffic_src_201211 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*11 and NEW.data_date<1325347200+86400*30*12 ) THEN

INSERT INTO rpt_traffic_src_201212 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*12 and NEW.data_date<1325347200+86400*30*13 ) THEN

INSERT INTO rpt_traffic_src_201301 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*13 and NEW.data_date<1325347200+86400*30*14 ) THEN

INSERT INTO rpt_traffic_src_201302 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*14 and NEW.data_date<1325347200+86400*30*15 ) THEN

INSERT INTO rpt_traffic_src_201303 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*15 and NEW.data_date<1325347200+86400*30*16 ) THEN

INSERT INTO rpt_traffic_src_201304 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*16 and NEW.data_date<1325347200+86400*30*17 ) THEN

INSERT INTO rpt_traffic_src_201305 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*17 and NEW.data_date<1325347200+86400*30*18 ) THEN

INSERT INTO rpt_traffic_src_201306 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*18 and NEW.data_date<1325347200+86400*30*19 ) THEN

INSERT INTO rpt_traffic_src_201307 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*19 and NEW.data_date<1325347200+86400*30*20 ) THEN

INSERT INTO rpt_traffic_src_201308 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*20 and NEW.data_date<1325347200+86400*30*21 ) THEN

INSERT INTO rpt_traffic_src_201309 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*21 and NEW.data_date<1325347200+86400*30*22 ) THEN

INSERT INTO rpt_traffic_src_201310 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*22 and NEW.data_date<1325347200+86400*30*23 ) THEN

INSERT INTO rpt_traffic_src_201311 VALUES (NEW.*);

ELSIF ( NEW.data_date>=1325347200+86400*30*23 and NEW.data_date<1325347200+86400*30*24 ) THEN

INSERT INTO rpt_traffic_src_201312 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'range out of data_date,fix the trigger function fun_for_trigger_insert_rpt_traffic_src';

END IF;

RETURN NULL;

END;

$BODY$ LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION fun_for_trigger_delete_rpt_traffic_src()

RETURNS "trigger" AS

$BODY$

BEGIN

IF( OLD.data_date>=1325347200+86400*30*0 and OLD.data_date<1325347200+86400*30*1 ) THEN

DELETE FROM rpt_traffic_src_201201 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*1 and OLD.data_date<1325347200+86400*30*2 ) THEN

DELETE FROM rpt_traffic_src_201202 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*2 and OLD.data_date<1325347200+86400*30*3 ) THEN

DELETE FROM rpt_traffic_src_201203 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*3 and OLD.data_date<1325347200+86400*30*4 ) THEN

DELETE FROM rpt_traffic_src_201204 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*4 and OLD.data_date<1325347200+86400*30*5 ) THEN

DELETE FROM rpt_traffic_src_201205 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*5 and OLD.data_date<1325347200+86400*30*6 ) THEN

DELETE FROM rpt_traffic_src_201206 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*6 and OLD.data_date<1325347200+86400*30*7 ) THEN

DELETE FROM rpt_traffic_src_201207 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*7 and OLD.data_date<1325347200+86400*30*8 ) THEN

DELETE FROM rpt_traffic_src_201208 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*8 and OLD.data_date<1325347200+86400*30*9 ) THEN

DELETE FROM rpt_traffic_src_201209 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*9 and OLD.data_date<1325347200+86400*30*10 ) THEN

DELETE FROM rpt_traffic_src_201210 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*10 and OLD.data_date<1325347200+86400*30*11 ) THEN

DELETE FROM rpt_traffic_src_201211 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*11 and OLD.data_date<1325347200+86400*30*12 ) THEN

DELETE FROM rpt_traffic_src_201212 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*12 and OLD.data_date<1325347200+86400*30*13 ) THEN

DELETE FROM rpt_traffic_src_201301 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*13 and OLD.data_date<1325347200+86400*30*14 ) THEN

DELETE FROM rpt_traffic_src_201302 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*14 and OLD.data_date<1325347200+86400*30*15 ) THEN

DELETE FROM rpt_traffic_src_201303 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*15 and OLD.data_date<1325347200+86400*30*16 ) THEN

DELETE FROM rpt_traffic_src_201304 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*16 and OLD.data_date<1325347200+86400*30*17 ) THEN

DELETE FROM rpt_traffic_src_201305 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*17 and OLD.data_date<1325347200+86400*30*18 ) THEN

DELETE FROM rpt_traffic_src_201306 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*18 and OLD.data_date<1325347200+86400*30*19 ) THEN

DELETE FROM rpt_traffic_src_201307 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*19 and OLD.data_date<1325347200+86400*30*20 ) THEN

DELETE FROM rpt_traffic_src_201308 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*20 and OLD.data_date<1325347200+86400*30*21 ) THEN

DELETE FROM rpt_traffic_src_201309 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*21 and OLD.data_date<1325347200+86400*30*22 ) THEN

DELETE FROM rpt_traffic_src_201310 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*22 and OLD.data_date<1325347200+86400*30*23 ) THEN

DELETE FROM rpt_traffic_src_201311 WHERE data_date=old.data_date;

ELSIF ( OLD.data_date>=1325347200+86400*30*23 and OLD.data_date<1325347200+86400*30*24 ) THEN

DELETE FROM rpt_traffic_src_201312 WHERE data_date=old.data_date;

ELSE

RAISE EXCEPTION 'range out of data_date,fix the trigger function fun_for_trigger_insert_rpt_traffic_src';

END IF;

RETURN NULL;

END;

$BODY$ LANGUAGE plpgsql VOLATILE;

4） 为主表创建触发器

DROP TRIGGER trigger_b_insert_rpt_traffic_src ON rpt_traffic_src;

DROP TRIGGER trigger_b_delete_rpt_traffic_src ON rpt_traffic_src;

CREATE TRIGGER trigger_b_insert_rpt_traffic_src

BEFORE INSERT ON rpt_traffic_src

FOR EACH ROW EXECUTE PROCEDURE fun_for_trigger_insert_rpt_traffic_src();

CREATE TRIGGER trigger_b_delete_rpt_traffic_src

BEFORE DELETE ON rpt_traffic_src

FOR EACH ROW EXECUTE PROCEDURE fun_for_trigger_delete_rpt_traffic_src();

5）导入数据并测试

test=# explain analyze select count(1) from rpt_traffic_src where data_date=1329667200;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------

-------------------------------

Aggregate (cost=4016.10..4016.11 rows=1 width=0) (actual time=69.881..69.881 rows=1 loops=1)

-> Append (cost=0.00..3984.68 rows=12570 width=0) (actual time=0.028..66.951 rows=12589 loops=1)

-> Seq Scan on rpt_traffic_src (cost=0.00..0.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)

Filter: (data_date = 1329667200::numeric)

-> Seq Scan on rpt_traffic_src_201202 rpt_traffic_src (cost=0.00..3984.68 rows=12569 width=0) (actual time=0.0

17..63.763 rows=12589 loops=1)

Filter: (data_date = 1329667200::numeric)

Rows Removed by Filter: 107385

Total runtime: 69.953 ms

[@more@]
请使用浏览器的分享功能分享到微信等