PG分区-传统分区

作者: 古飞_数据 | 来源:发表于2023-05-02 10:08 被阅读0次

PostgreSQL 源码解读（100）- 分区表#6（数据查询
mysql表分区
PostgreSQL 源码解读（101）- 分区表#7（数据查询
hive分区表
fdisk
linux 分区说明
Ubuntu系统安装分区攻略
MBR与分区表
硬盘分区
mysql 段、区、页以及分区知识点

支持范围分区和列表分区


create table tbl_log(id int4,create_date date,log_type text);


create table tbl_log_sql(sql text) inherits(tbl_log);
\d tbl_log_sql

insert into tbl_log values(1,'2017-08-26',null);
insert into tbl_log_sql values(2,'2017-08-27',null,'select 2');
select * from tbl_log;
select tableoid,* from tbl_log;

select p.relname,c.* from tbl_log c, pg_class p where c.tableoid = p.oid;

--只显示父表数据
select * from only tbl_log;

delete from tbl_log;
select count(*) from tbl_log;

===

创建分区

create table log_ins(id serial,user_id int4,create_time timestamp(0) without timezone);
create table log_ins_history(check(create_time < '2017-01-01')) inherits(log_ins);
create table log_ins_history_201701(check(create_time >= '2017-01-01' and create_time < '2017-02-01')) inherits(log_ins);
create table log_ins_history_201702(check(create_time >= '2017-02-01' and create_time < '2017-03-01')) inherits(log_ins);
create table log_ins_history_201703(check(create_time >= '2017-03-01' and create_time < '2017-04-01')) inherits(log_ins);
create table log_ins_history_201704(check(create_time >= '2017-04-01' and create_time < '2017-05-01')) inherits(log_ins);
create table log_ins_history_201705(check(create_time >= '2017-05-01' and create_time < '2017-06-01')) inherits(log_ins);
create table log_ins_history_201706(check(create_time >= '2017-06-01' and create_time < '2017-07-01')) inherits(log_ins);
create table log_ins_history_201707(check(create_time >= '2017-07-01' and create_time < '2017-08-01')) inherits(log_ins);
create table log_ins_history_201708(check(create_time >= '2017-08-01' and create_time < '2017-09-01')) inherits(log_ins);
create table log_ins_history_201709(check(create_time >= '2017-09-01' and create_time < '2017-10-01')) inherits(log_ins);
create table log_ins_history_201710(check(create_time >= '2017-10-01' and create_time < '2017-11-01')) inherits(log_ins);
create table log_ins_history_201711(check(create_time >= '2017-11-01' and create_time < '2017-12-01')) inherits(log_ins);
create table log_ins_history_201712(check(create_time >= '2017-12-01' and create_time < '2018-01-01')) inherits(log_ins);

创建索引

create index idx_his_ctime on log_ins_history using btree(create_time);
create index idx_log_ins_201701_ctime on log_ins_history_201701 using btree(create_time);
create index idx_log_ins_201702_ctime on log_ins_history_201702 using btree(create_time);
create index idx_log_ins_201703_ctime on log_ins_history_201703 using btree(create_time);
create index idx_log_ins_201704_ctime on log_ins_history_201704 using btree(create_time);
create index idx_log_ins_201705_ctime on log_ins_history_201705 using btree(create_time);
create index idx_log_ins_201706_ctime on log_ins_history_201706 using btree(create_time);
create index idx_log_ins_201707_ctime on log_ins_history_201707 using btree(create_time);
create index idx_log_ins_201708_ctime on log_ins_history_201708 using btree(create_time);
create index idx_log_ins_201709_ctime on log_ins_history_201709 using btree(create_time);
create index idx_log_ins_201710_ctime on log_ins_history_201710 using btree(create_time);
create index idx_log_ins_201711_ctime on log_ins_history_201711 using btree(create_time);
create index idx_log_ins_201712_ctime on log_ins_history_201712 using btree(create_time);

创建函数

CREATE OR REPLACE FUNCTION log_ins_insert_trigger()
  RETURNS trigger
  LANGUAGE plpgsql
AS $function$
BEGIN
  IF  (NEW.create_time < '2017-01-01') THEN
    INSERT INTO log_ins_history VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-01-01' and NEW.create_time < '2017-02-01') THEN
    INSERT INTO log_ins_201701 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-02-01' and NEW.create_time < '2017-03-01') THEN
    INSERT INTO log_ins_201702 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-03-01' and NEW.create_time < '2017-04-01') THEN
    INSERT INTO log_ins_201703 VALUES (NEW.*);  
  ELSIF( NEW.create_time >= '2017-04-01' and NEW.create_time < '2017-05-01') THEN
    INSERT INTO log_ins_201704 VALUES (NEW.*);  
  ELSIF( NEW.create_time >= '2017-05-01' and NEW.create_time < '2017-06-01') THEN
    INSERT INTO log_ins_201705 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-06-01' and NEW.create_time < '2017-07-01') THEN
    INSERT INTO log_ins_201706 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-07-01' and NEW.create_time < '2017-08-01') THEN
    INSERT INTO log_ins_201707 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-08-01' and NEW.create_time < '2017-09-01') THEN
    INSERT INTO log_ins_201708 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-09-01' and NEW.create_time < '2017-10-01') THEN
    INSERT INTO log_ins_201709 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-10-01' and NEW.create_time < '2017-11-01') THEN
    INSERT INTO log_ins_201710 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-11-01' and NEW.create_time < '2017-12-01') THEN
    INSERT INTO log_ins_201711 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-12-01' and NEW.create_time < '2018-01-01') THEN
    INSERT INTO log_ins_201712 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'create_time out of range. Fix the log_ins_insert_trigger() function! ' ;
  END IF;
  RETURN NULL;
END;
$function$;

创建触发器

CREATE TRIGGER insert_log_ins_trigger BEFORE INSERT ON log_ins FOR EACH ROW
  EXECUTE PROCEDURE log_ins_insert_trigger();

测试-插入数据

INSERT INTO log_ins (user_id, create_time)
SELECT round (100000000*random ()) ,generate_series ('2016-12-01'::date,'2017-12-01'::date, '1 minute');
select * from log_ins limit 2;

--查看父表,没数据
select count(*) from only log_ins;
--子表里面有数据
select count(*) from log_ins;
select min(create_time),max(create_time) from log_ins_201701;

\dt* log_ins*

性能测试

explain analyze select * from log_ins where create_time > '2017-01-01' and create_time < '2017-01-02';
explain analyze select * from log_ins_201701 where create_time > '2017-01-01' and create_time < '2017-01-02';

constraint_exclusing off on partition(*)

set constraint_exclusing = off;
explain analyze select * from log_ins where create_time > '2017-01-01' and create_time < '2017-01-02';


--添加分区
create table log_ins_201801(check(create_time >= '2018-01-01' and create time < '2018-02-01'))
create index idx_log_ins_201801_ctime on log_ins_201801 using btree(create_time);


CREATE OR REPLACE FUNCTION log_ins_insert_trigger()
  RETURNS trigger
  LANGUAGE plpgsql
AS $function$
BEGIN
  IF  (NEW.create_time < '2017-01-01') THEN
    INSERT INTO log_ins_history VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-01-01' and NEW.create_time < '2017-02-01') THEN
    INSERT INTO log_ins_201701 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-02-01' and NEW.create_time < '2017-03-01') THEN
    INSERT INTO log_ins_201702 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-03-01' and NEW.create_time < '2017-04-01') THEN
    INSERT INTO log_ins_201703 VALUES (NEW.*);  
  ELSIF( NEW.create_time >= '2017-04-01' and NEW.create_time < '2017-05-01') THEN
    INSERT INTO log_ins_201704 VALUES (NEW.*);  
  ELSIF( NEW.create_time >= '2017-05-01' and NEW.create_time < '2017-06-01') THEN
    INSERT INTO log_ins_201705 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-06-01' and NEW.create_time < '2017-07-01') THEN
    INSERT INTO log_ins_201706 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-07-01' and NEW.create_time < '2017-08-01') THEN
    INSERT INTO log_ins_201707 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-08-01' and NEW.create_time < '2017-09-01') THEN
    INSERT INTO log_ins_201708 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-09-01' and NEW.create_time < '2017-10-01') THEN
    INSERT INTO log_ins_201709 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-10-01' and NEW.create_time < '2017-11-01') THEN
    INSERT INTO log_ins_201710 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-11-01' and NEW.create_time < '2017-12-01') THEN
    INSERT INTO log_ins_201711 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2017-12-01' and NEW.create_time < '2018-01-01') THEN
    INSERT INTO log_ins_201712 VALUES (NEW.*);
  ELSIF( NEW.create_time >= '2018-01-01' and NEW.create_time < '2018-02-01') THEN
    INSERT INTO log_ins_201801 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'create_time out of range. Fix the log_ins_insert_trigger() function! ' ;
  END IF;
  RETURN NULL;
END;
$function$;




\d+ log_ins



方法二：
--创建分区
create table log_ins_201802(like log_ins including all);
--添加约束
alter table log_ins_201802 add constraint log_ins_201802_create_time_check check(create_time >= '2018-02-01' and create time < '2018-03-01');
--刷新触发器函数(刷新前先备份)
--继承
alter table log_ins_201802 inherit log_ins;


--删除分区
方法一：
drop table log_ins_201802
方法二：
alter table log_ins_201802 on inherit log_ins;


--分区表的查询
\d log_ins
\d+ log_ins
SQL 语句


--性能测试
create table log(id serial,user_id int4,create_time timestamp(0) without time zone);
INSERT INTO log (user_id, create_time)
SELECT round (100000000*random ()) ,generate_series ('2016-12-01'::date,'2017-12-01'::date, '1 minute');
select count(*) from log_ins;
select count(*) from log;

--创建索引
create index idx_log_userid on log using btree(user_id);
create index idx_log_create_time on log using btree(create_time);

create index idx_log_ins_userid on log_ins using btree(user_id);
create index idx_his_userid on log_ins_history using btree(user_id);

create index idx_log_ins_201701_userid on log_ins_201701 using btree(user_id);
create index idx_log_ins_201702_userid on log_ins_201702 using btree(user_id);
create index idx_log_ins_201703_userid on log_ins_201703 using btree(user_id);
create index idx_log_ins_201704_userid on log_ins_201704 using btree(user_id);
create index idx_log_ins_201705_userid on log_ins_201705 using btree(user_id);
create index idx_log_ins_201706_userid on log_ins_201706 using btree(user_id);
create index idx_log_ins_201707_userid on log_ins_201707 using btree(user_id);
create index idx_log_ins_201708_userid on log_ins_201708 using btree(user_id);
create index idx_log_ins_201709_userid on log_ins_201709 using btree(user_id);
create index idx_log_ins_201710_userid on log_ins_201710 using btree(user_id);
create index idx_log_ins_201711_userid on log_ins_201711 using btree(user_id);
create index idx_log_ins_201712_userid on log_ins_201712 using btree(user_id);
create index idx_log_ins_201801_userid on log_ins_201801 using btree(user_id);

PostgreSQL 源码解读（100）- 分区表#6（数据查询
本节介绍了PG在查询分区表的时候如何确定查询的是哪个分区。在规划阶段,函数set_rel_size中，如RTE为分...
mysql表分区
mysql表分区主要分为RANGE分区，LIST分区，HASH分区，LINEAR HASH分区，KEY分区目前，...
PostgreSQL 源码解读（101）- 分区表#7（数据查询
本节是PG在查询分区表的时候如何确定查询的是哪个分区逻辑介绍的第二部分。在规划阶段,函数set_rel_size中...
hive分区表
分区就是分目录：增加分区：显示所有分区：同时创建多个分区: 删除单个分区：删除多个分区：创建二级分区： ...
fdisk
一、磁盘分区原理与规则磁盘分区类型：主分区，扩展分区，逻辑分区分区规则： 1、主分区+扩展分区的数量不能超过4...
linux 分区说明
一、磁盘管理 1、分区类型：主分区、扩展分区、逻辑分区 2、分区要求：分区不是必须的操作主分区最多创建4个主...
Ubuntu系统安装分区攻略
主分区和逻辑分区主分区英语是Primary Partition。也称为主磁盘分区，和扩展分区、逻辑分区...
MBR与分区表
MBR分区表：在传统硬盘分区模式中，引导扇区是每个分区（Partition）的第一扇区，而主引导扇区是硬盘的第一...
硬盘分区
开始分区 n p 删除分区 d 添加扩展分区 n e (逻辑分区) 有了扩展分区才能添加逻辑分区 l和...
mysql 段、区、页以及分区知识点
目录一.InnoDB逻辑存储结构段区页二.分区概述分区三.分区类型RANGE分区LIST分区HASH分区KE...