PostgreSQL 分表

2019独角兽企业重金招聘Python工程师标准>>>

背景了解

最近在研究postgresql, 里面分割表的场景特别适合一张很大的表的场景, 通常有两种分割办法:

• 范围分割
• 列表分割 创建一个分割表的步骤:

1. 创建master表
2. 创建多个字表,且没有重复的约束
3. 创建索引
4. 创建触发器, 插入数据到子表中去
5. 启用约束执行

根据步骤创建一个范围分割的表操作如下:

//主表
create table sales ( id numeric primary key, amount numeric, ctime date not null default current_date );

//子表一
create table sales_p_first( primary key (id, ctime), check(ctime>= date '2017-1-1' and ctime< date '2017-6-30') ) inherits (sales);

//字表二
create table sales_p_second( primary key (id, ctime), check(ctime>= date '2017-7-1' and ctime< date '2017-12-31') ) inherits (sales);

//子表一索引
create index sales_first_idx on sales_p_first(ctime);

//子表二索引
create index sales_second_idx on sales_p_second(ctime);

//查看分割表的链接情况
testdb=# \d+ sales
                                      Table "public.sales"
 Column | Type | Modifiers | Storage | Stats target | Description 
--------+---------+--------------------------------------+---------+--------------+-------------
 id | numeric | not null | main | | 
 amount | numeric | | main | | 
 ctime | date | not null default ('now'::text)::date | plain | | 
Indexes:
    "sales_pkey" PRIMARY KEY, btree (id)
Child tables: sales_p_first,
              sales_p_second
testdb=#

//创建触发器方法
create or replace function tg_sales() returns trigger as $$ 
begin if (new.ctime >= date '2017-1-1' and ctime < date '2017-6-30') then insert into sales_p_first values(new.*); elseif (new.ctime >= date '2017-7-1' and ctime < date '2017-12-31') then insert into sales_p_second values(new.*); else raise exception 'out of the range 2017-1-1~2017-12-31'; 
end if; 
return null; 
end; $$ language plpgsql;

//创建触发器
testdb=# create trigger tg_sales 
testdb-# before insert on sales
testdb-# for each row
testdb-# execute procedure tg_sales();
CREATE TRIGGER
testdb=# 

//开启一致性索引
set constraint_exclusion = on;

验证逻辑如下:

insert into sales values(1, 100, to_date('2017-1-1', 'YYYY-MM-DD'));
insert into sales values(2, 700, to_date('2017-7-1', 'YYYY-MM-DD')); 
testdb=# select * from sales_p_first;
 id | amount | ctime    
----+--------+------------
  1 | 100 | 2017-01-01
(1 row)
testdb=# select * from sales_p_second;
 id | amount | ctime    
----+--------+------------
  2 | 700 | 2017-07-01
(1 row)
testdb=# select * from sales;
 id | amount | ctime    
----+--------+------------
  1 | 100 | 2017-01-01
  2 | 700 | 2017-07-01
(2 rows)
testdb=# update sales set amount = 999 where id = 2;
UPDATE 1
testdb=# select * from sales;
 id | amount | ctime    
----+--------+------------
  1 | 100 | 2017-01-01
  2 | 999 | 2017-07-01
(2 rows)

转载于:https://my.oschina.net/jimmywa/blog/861650