数据库分区例子

地理空间数据表设计与管理

最新推荐文章于 2024-01-28 12:21:33 发布

原创最新推荐文章于 2024-01-28 12:21:33 发布 · 501 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #数据分区例子 #pg分区

本文详细介绍了地理空间数据表“ds_product_imagery”的创建过程，包括字段定义、索引建立及分区管理等关键步骤，旨在高效管理和查询地理空间数据。

--创建表
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;