postgresql分区表和创建方式介绍
分区表介绍
主表/父表/master table 该表是创建子表的模板。它是一个正常的普通表，但正常情况下它并不储存任何数据。
子表/分区表/child table/partition table 这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表
创建自动分区表采用两种方式
采用视图分区表方式
采用直接分区表方式
创建表
create table if not exists public.sales
(
id bigserial primary key ,
store_id varchar(50) ,
business_date date,
start_time time,
end_time time,
dine_in_tc int,
delivery_tc int,
takeout_tc int,
dine_in_s decimal(20,4),
delivery_s decimal(20,4),
takeout_s decimal(20,4),
voucher_overcharge decimal(20,4),
freight decimal(20,4),
currency varchar(16),
created_at timestamp default now(),
updated_at timestamp default now()
);
创建索引
“`
create index sales_store_id on public.sales (store_id);
create index sales_business_date on public.sales (business_date);
alter table public.sales add constraint sales_storeid_businessdate_starttime_endtime unique(store_id,business_date,start_time,end_time);
### 1.采用视图分区方式
#### 建立视图
create view public.sales_view as select * from public.sales;
#### 定义分表function
create or replace function public.insert_sales()
returns trigger as
“.’body′."body′."
declare
_start_dt text;
_end_dt text;
_table_name text;
begin
if new.id is null then
new.id := nextval(‘sales_id_seq’); // 保证分区后的id可以自增
end if;
table_name := ‘sales_view’ || to_char(new.business_date, ‘yyyy_mm’); // 按照时间进行分区
perform 1
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where c.relkind = ‘r’
and c.relname = _table_name
and n.nspname = public;
if not found then
_start_dt := to_char(date_trunc(‘month’, new.business_date), ‘yyyy-mm-dd’);
_end_dt:=_start_dt::timestamp + interval ‘1 month’;
execute
‘create table public.’
|| quote_ident(_table_name)
|| ’ (check (business_date >= ’
|| quote_literal(_start_dt)
|| ‘and business_date < ’
|| quote_literal(_end_dt)
|| ‘)) inherits (public.sales)’;
execute ‘create index ’ || quote_ident(_table_name||’_business_date’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (business_date)’;
execute ‘create index ’ || quote_ident(_table_name||’_store_id’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (store_id)’;
execute ‘alter table public.’ || quote_ident(_table_name) || ’ add constraint ’ || quote_ident(_table_name||’_storeid_businessdate_starttime_endtime’||_start_dt) || ’ unique (store_id,business_date,start_time,end_time)’;
execute ‘alter table public.’ || quote_ident(_table_name) || ’ owner to ’ || quote_ident(current_user);
execute ‘grant all on table public.’ || quote_ident(_table_name) || ’ to ’ || quote_ident(current_user);
end if;
execute ‘insert into public.’ || quote_ident(_table_name) || ’ values ($1.) returning ’ using new;
return new;
end;
“.’body′."body′."
language plpgsql;
“;
#### 分表触发器
create trigger insert_sales_trigger instead of insert on public.sales_view for each row execute procedure insert_sales();
#### 定义更新function
create or replace function update_sales()
returns trigger as
begindeletefromsalesviewwhereid=new.id;insertintosalesviewvalues(new.∗);returnnew;end;
begindeletefromsalesviewwhereid=new.id;insertintosalesviewvalues(new.∗);returnnew;end;
language plpgsql;
“;
#### 更新触发器
create trigger update_sales_trigger instead of update on sales_view for each row execute procedure update_oc_sales();
### 2.直接分区方式
create or replace function insert_sales()
returns trigger as
“.’body′."body′."
declare
_start_dt text;
_end_dt text;
_table_name text;
begin
if new.id is null then
new.id := nextval(‘”.$this->tablename.”_id_seq’);
end if;
table_name := ‘sales’ || to_char(new.business_date, ‘yyyy_mm’);
perform 1
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where c.relkind = ‘r’
and c.relname = _table_name
and n.nspname = ‘public’;
if not found then
_start_dt := to_char(date_trunc(‘month’, new.business_date), ‘yyyy-mm-dd’);
_end_dt:=_start_dt::timestamp + interval ‘1 month’;
execute
‘create table if not exists public.’
|| quote_ident(_table_name)
|| ’ (check (business_date >= ’
|| quote_literal(_start_dt)
|| ‘and business_date < ’
|| quote_literal(_end_dt)
|| ‘)) inherits (public.sales)’;
execute ‘create index if not exists’ || quote_ident(_table_name||’_business_date’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (business_date)’;
execute ‘create index if not exists’ || quote_ident(_table_name||’_store_id’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (store_id)’;
execute ‘create unique index if not exists’ || quote_ident(_table_name||’_storeid_businessdate_starttime_endtime’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (store_id,business_date,start_time,end_time)’;
execute ‘alter table public.’ || quote_ident(_table_name) || ’ owner to ’ || quote_ident(current_user);
execute ‘grant all on table public.’ || quote_ident(_table_name) || ’ to ’ || quote_ident(current_user);
end if;
execute ‘insert into public.’ || quote_ident(_table_name) || ’ values ($1.) on conflict(store_id,business_date,start_time,end_time) do nothing returning ’ using new;
return null;
end;
“.’body′."body′."
language plpgsql;
#### 分表触发器
create trigger insert_sales_trigger before insert on public.salses for each row execute procedure insert_sales();
“`
两种方式比较
视图分区所有操作都是对视图的操作，直接分区是对主表进行操作；
视图分区触发器使用instead of,直接分区使用before,因为无法直接用触发器替代对主表的操作，只能操作视图；
视图分区用instead of，在function中可以return new，对操作后有明确的返回，直接分区用before方式，在function中采用return null,数据库操作没有返回；
直接分区可以用on conflict对主表insert进行ignore操作，视图分区不能。