数据库分区例子

--创建表
CREATE TABLE "public"."ds_product_imagery" (
"id" float8 DEFAULT 0 NOT NULL,
"productname" varchar(100) COLLATE "default" NOT NULL,
"fgeometry" "public"."geometry" NOT NULL,
"browserimg" varchar(200) COLLATE "default",
"thumbimg" varchar(200) COLLATE "default",
"centertime" timestamp(6) NOT NULL,
"centerlatitude" float8,
"centerlongitude" float8,
"satellite" varchar(50) COLLATE "default" NOT NULL,
"sensor" varchar(50) COLLATE "default" NOT NULL,
"cloudcover" float8 DEFAULT 0,
"rollangle" float8 DEFAULT 0,
"topleftlatitude" float8,
"topleftlongitude" float8,
"toprightlatitude" float8,
"toprightlongitude" float8,
"bottomrightlatitude" float8,
"bottomrightlongitude" float8,
"bottomleftlatitude" float8,
"bottomleftlongitude" float8,
"fbounds" "public"."geometry" NOT NULL,
"cloudgeometry" "public"."geometry",
"addtime" timestamp(6),
"resolution" varchar(50) COLLATE "default",
"productlevels" varchar(50) COLLATE "default",
"transformimg" varchar(200) COLLATE "default",
"ditaskid" varchar(100) COLLATE "default",
"diversion" int4 DEFAULT 1 NOT NULL,
"nocloudgeometry" "public"."geometry",
"diindexsource" varchar(100) COLLATE "default",
CONSTRAINT "ds_product_imagery_pkey" PRIMARY KEY ("id")
)


--创建索引
CREATE INDEX "ds_product_imagery_cen_fboun_idx" ON "public"."ds_product_imagery" USING btree (centertime, satellite, cloudcover, rollangle, fbounds);


CREATE INDEX "ds_product_imagery_centertime_idx" ON "public"."ds_product_imagery" USING btree (centertime DESC);


CREATE INDEX "ds_product_imagery_fbounds_idx" ON "public"."ds_product_imagery" USING gist (fbounds);


CREATE INDEX "ds_product_imagery_fgeometry_idx" ON "public"."ds_product_imagery" USING btree (fgeometry);


CREATE INDEX "ds_product_imagery_id_idx" ON "public"."ds_product_imagery" USING btree (id);


CREATE INDEX "ds_product_imagery_satellite_sensor_idx" ON "public"."ds_product_imagery" USING btree (satellite DESC, sensor DESC);






--分区语句


select  create_range_partitions('ds_product_imagery' ::regclass,            -- 主表OID
                        'centertime',                        -- 分区列名
                        '2010-01-01 00:00:00' ::timestamp,  -- 开始值
                        interval '1 month',                -- 间隔；interval 类型，用于时间分区表
                        24,                                -- 分多少个区
                        false) ;                           -- 不迁移数据




--数据迁移完成后，建议禁用主表，这样执行计划就不会出现主表了
select set_enable_parent('ds_product_imagery'::regclass, false);


--分裂分区
select split_range_partition('ds_product_imagery_1'::regclass,              -- 分区oid
                      '2016-11-10 00:00:00'::timestamp,     -- 分裂值
                      'ds_product_imagery_1_2');                     -- 分区表名
        


--增加分区1
 select prepend_range_partition('ds_product_imagery'::regclass);    
--增加分区2
select add_range_partition('ds_product_imagery'::regclass,    -- 主表OID
                    '2020-01-01 00:00:00'::timestamp,  -- 起始值
                    '2020-02-01 00:00:00'::timestamp); -- 结束值      


--删除分区， 数据迁移到主表  
select drop_range_partition('ds_product_imagery_3',false);
--删除分区，分区数据也删除，不迁移到主表  
select drop_range_partition('ds_product_imagery_4',true);
--删除所有分区，并且指定是否要将数据迁移到主表
elect drop_partitions('ds_product_imagery'::regclass, false);  -- 删除所有分区表，并将数据迁移到主表
explain select * from ds_product_imagery where centertime = '2016-10-25 00:00:00'::timestamp;