在PostgreSQL分区表是很容易做到的,它涉及到PostgreSQL继承和触发的概念。在这里,提供了一个示例来演示如何在PostgreSQL上分区表。
之前进行,请了解一些基本的概念一样,呃......好我提供分区的概念,“time”在表中。
“Mother” — (Child1, Child2, Child3,Child4, Child5)
MasterTable — (Child_01_2008, Child_02_2008, Child_03_2008, Child_04_2008,Child_05_2008)
我们只有插入,选择,更新和删除MasterTable,所有子表是对用户透明。
例如,当我插入记录MasterTable,是在2008年1月。 记录将自动重定向(触发)到子表(Child_01_2008)。当用户选择主表中的记录,PostgreSQL将自动检索来自MasterTable继承所有子表中的数据。
1)创建一个简单的表叫“hashvalue_PT”,它仅包含2列“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) 完成示例
下一议题,将创建一个函数来插入数据万元分区表来测试分区和非分区表之间的性能。
易百教程移动端:请扫描本页面底部(右侧)二维码并关注微信公众号,回复:"教程" 选择相关教程阅读或直接访问:http://m.yiibai.com 。
加QQ群啦,易百教程官方技术学习群
注意:建议每个人选自己的技术方向加群,同一个QQ最多限加 3 个群。
- Java技术群: 227270512 (人数:2000,免费:否)
- MySQL/SQL群: 418407075 (人数:2000,免费:否)
- 大数据开发群: 655154550 (人数:2000,免费:否)
- Python技术群: 287904175 (人数:2000,免费:否)
- 人工智能深度学习: 456236082 (人数:2000,免费:否)
- 测试工程师(新群): 415553199 (人数:1000,免费:否)
- 前端技术群(新群): 410430016 (人数:1000,免费:是)
- C/C++技术(新群): 629264796 (人数:1000,免费:是)
- Node.js技术(新群): 621549808 (人数:1000,免费:是)
- PostgreSQL数据库(新群): 539504187 (人数:1000,免费:是)
- Linux技术: 479429477 (人数:2000,免费:否)
- PHP开发者: 460153241 (人数:2000,免费:否)
- Oracle数据库: 175248146 (人数:2000,免费:否)
- C#/ASP.Net开发者: 579821706 (人数:2000,免费:是)
- 数据分析师: 397883996 (人数:2000,免费:是)R语言,Matlab语言等技术