Postgresql建分区表步骤sql

小城南

已于 2022-11-16 13:51:41 修改

阅读量3.6k

点赞数

分类专栏：数据库文章标签： sql 数据库 pgsql 分区表 pgsql默认分区

于 2022-09-29 20:08:26 首次发布

本文链接：https://blog.youkuaiyun.com/cainiaobulan/article/details/127113099

版权

数据库专栏收录该内容

32 篇文章

订阅专栏

该博客介绍了在业务扩展中如何利用PostgreSQL进行表分区以提升查询效率。首先，通过备份、删除和创建带分区的新表来实现表的转换。接着，创建了针对分区表的索引和主键约束，并定义了一个函数用于动态创建分区。最后，将历史数据导入到默认分区，并执行函数创建指定日期范围的分区。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

由于业务发展，需要对系统表进行分区处理，以提高查询速度，下面代码是示例，以partition_table_name表为例。

--备份表
SELECT * INTO partition_table_name_2022_bak FROM partition_table_name;
--删除原表
DROP TABLE IF EXISTS "public"."partition_table_name";
--创建带分区的原表
CREATE TABLE "public"."partition_table_name" (
  "id" int8 NOT NULL DEFAULT '-1'::integer,
  "tenant_id" int8 NOT NULL DEFAULT '-1'::integer,
  "app_id" int8 NOT NULL DEFAULT '-1'::integer,
  "data_id" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "data_type" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "data_version" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "data_format" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "data_source" varchar(32) COLLATE "pg_catalog"."default",
  "data" text COLLATE "pg_catalog"."default",
  "status" char(1) COLLATE "pg_catalog"."default" NOT NULL,
  "status_time" timestamp(6),
  "create_time" timestamp(6) NOT NULL
)
--分区字段 可支持多个
PARTITION BY RANGE (
  "create_time"
)
;
//创建默认分区
CREATE TABLE IF NOT EXISTS SWAP_DATA_INFO_DEFAULT PARTITION OF SWAP_DATA_INFO DEFAULT;
--索引
CREATE INDEX "idx_partition_table_name_1" ON "public"."partition_table_name" USING btree (
  "data_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "idx_partition_table_name_2" ON "public"."partition_table_name" USING btree (
  "tenant_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_partition_table_name_3" ON "public"."partition_table_name" USING btree (
  "create_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);

ALTER TABLE "public"."partition_table_name" ADD CONSTRAINT "pk_partition_table_name" PRIMARY KEY ("id","create_time");

--创建分区表函数
CREATE OR REPLACE FUNCTION public.create_daypartition_by_day(v_tablename character varying, v_start_day character varying, v_end_day character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE vt_i integer;  vd_i integer; vn_day date; vn_next_day date; vn_num_day varchar;  vn_day_num integer;
BEGIN
        select count(tablename) into vt_i from  pg_tables where tablename=v_tablename||'_'||to_char(v_end_day :: DATE, 'yyyyMMdd');
        if vt_i = 0 then

select (v_end_day::date - v_start_day::date) into vn_day_num;
for vd_i in 0..vn_day_num loop
vn_day := v_start_day :: DATE + (vd_i || ' day') :: INTERVAL ;

vn_next_day := vn_day + INTERVAL '1 day' ;

 vn_num_day := to_char(vn_day, 'yyyyMMdd') ; 

 EXECUTE 'create table ' || v_tablename || '_' || vn_num_day || ' partition of ' || v_tablename || ' for values from (''' || vn_day || ' 00:00:00'') to (''' || vn_next_day || ' 00:00:00'');' ;
end loop;

  else  
      RAISE EXCEPTION 'partition table % exist!',v_tablename ||'_' ||v_start_day; 
  end if;
END ; $function$;

set lock_timeout = 9999999;

--执行创建分区表函数
select create_daypartition_by_day('partition_table_name','2022-07-01','2022-09-30');
select create_daypartition_by_day('partition_table_name','2022-10-01','2022-12-01');

--历史数据放进默认分区
INSERT INTO partition_table_name SELECT * FROM partition_table_name_bak;