postgresql分区表 | Qliang的小站

技术实践数据库 PostgreSQL

数据库

发布日期: 2017-12-01

文章字数: 964

阅读时长: 5 分

阅读次数:

分区表介绍

主表/父表/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'."$
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'."$
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 $$
BEGIN
    DELETE FROM sales_view WHERE id = NEW.id;
    INSERT INTO sales_view VALUES (NEW.*);
    RETURN NEW;
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'."$
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'."$
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操作，视图分区不能。