人大金仓按时间字段创建分区表

javazrj

555人浏览 · 2026-01-30 15:48:09

javazrj · 2026-01-30 15:48:09 发布

最近有点忙，人大金仓适配ShardingSphere的整理过程还没写完，先发一个简单的分区表逻辑出来，对于数据库的维护要从实际需求出发从分区开始再到分表分库，关于分区分表分库的适用场景要区分好，具体可自行百度，能用最简单的方式来解决当前的问题就好，下面附关于人大金仓分区按时间范围分区的完整示例、自定义函数与实用语句

以一张record记录表为例未分区的建表语句：

CREATE TABLE "public"."record" (
  "record_id" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "check_date" "pg_catalog"."datetime" NOT NULL,
  "check_type" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "status" int2 NOT NULL DEFAULT 1,
  "create_time" "pg_catalog"."datetime" NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" "pg_catalog"."datetime" NOT NULL,
  CONSTRAINT "record_pkey" PRIMARY KEY ("record_id")
);
ALTER TABLE "public"."record" 
  OWNER TO "system";
COMMENT ON COLUMN "public"."record"."record_id" IS '记录唯一ID';

COMMENT ON COLUMN "public"."record"."check_date" IS '检查日期';

COMMENT ON COLUMN "public"."record"."check_type" IS '检查类型 字典项';

COMMENT ON COLUMN "public"."record"."status" IS '状态：0表示异常，1表示正常';

COMMENT ON COLUMN "public"."record"."create_time" IS '记录创建时间，默认值为当前系统时间';

COMMENT ON COLUMN "public"."record"."update_time" IS '记录最后更新时间，自动更新为当前系统时间';

COMMENT ON TABLE "public"."record" IS '记录表';

备份数据备份到record_back表

CREATE TABLE public.record_backup AS SELECT * FROM public.record;

2.创建按照时间字段分区的分区主表先删除原表

-- 删未分区的原表
DROP TABLE IF EXISTS public.record CASCADE;
-- 创建分区主表
CREATE TABLE "public"."record" (
  "record_id" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "check_date" "pg_catalog"."datetime" NOT NULL,
  "check_type" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "status" int2 NOT NULL DEFAULT 1,
  "create_time" "pg_catalog"."datetime" NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" "pg_catalog"."datetime" NOT NULL,
  CONSTRAINT "record_pkey" PRIMARY KEY ("record_id")
) PARTITION BY RANGE ( check_date );
ALTER TABLE "public"."record" 
  OWNER TO "system";
COMMENT ON COLUMN "public"."record"."record_id" IS '记录唯一ID';

COMMENT ON COLUMN "public"."record"."check_date" IS '检查日期';

COMMENT ON COLUMN "public"."record"."check_type" IS '检查类型 字典项';

COMMENT ON COLUMN "public"."record"."status" IS '状态：0表示异常，1表示正常';

COMMENT ON COLUMN "public"."record"."create_time" IS '记录创建时间，默认值为当前系统时间';

COMMENT ON COLUMN "public"."record"."update_time" IS '记录最后更新时间，自动更新为当前系统时间';

COMMENT ON TABLE "public"."record" IS '记录表';

-- 创建分区（2026年开始）
-- 2026年分区
CREATE TABLE public.record_y2026m01 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-01-01 00:00:00' ) TO ( '2026-02-01 00:00:00' );
CREATE TABLE public.record_y2026m02 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-02-01 00:00:00' ) TO ( '2026-03-01 00:00:00' );
CREATE TABLE public.record_y2026m03 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-03-01 00:00:00' ) TO ( '2026-04-01 00:00:00' );
CREATE TABLE public.record_y2026m04 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-04-01 00:00:00' ) TO ( '2026-05-01 00:00:00' );
CREATE TABLE public.record_y2026m05 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-05-01 00:00:00' ) TO ( '2026-06-01 00:00:00' );
CREATE TABLE public.record_y2026m06 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-06-01 00:00:00' ) TO ( '2026-07-01 00:00:00' );
CREATE TABLE public.record_y2026m07 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-07-01 00:00:00' ) TO ( '2026-08-01 00:00:00' );
CREATE TABLE public.record_y2026m08 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-08-01 00:00:00' ) TO ( '2026-09-01 00:00:00' );
CREATE TABLE public.record_y2026m09 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-09-01 00:00:00' ) TO ( '2026-10-01 00:00:00' );
CREATE TABLE public.record_y2026m10 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-10-01 00:00:00' ) TO ( '2026-11-01 00:00:00' );
CREATE TABLE public.record_y2026m11 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-11-01 00:00:00' ) TO ( '2026-12-01 00:00:00' );
CREATE TABLE public.record_y2026m12 PARTITION OF public.record FOR
    VALUES
    FROM
        ( '2026-12-01 00:00:00' ) TO ( '2027-01-01 00:00:00' );

-- 创建常用索引（基于查询分析）
CREATE INDEX "idx_check_type_record" ON "public"."record" USING btree (
  "check_type" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

3.查看分区

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'record%'
ORDER BY tablename;

在这里插入图片描述可以看到分区的名称以及大小写法还是与pgsql一致的因为人大金仓底层是基于pgsql来的，所以下面的自定义函数也是同理，关于pgsql这一点在我适配ShardingSphere的时候也很关键，后续会在适配的文章里详细解读几种适配思路，包含SPI机制适配人大金仓驱动OR高版本ShardingSphere中在配置文件中直接使用pgsql的驱动。

4.迁移备份数据到新分区表

INSERT INTO public.record SELECT * FROM public.record_backup;

5.比对数据再次查看分区信息

在这里插入图片描述测试数据13条分布26年的12个月其中1月份两条
再次查看分区信息
分区都变大了，这里一月份数据虽然是两条，但大小与其他分区一致，分区具体大小也还是引擎决定的，感兴趣的话可以研究一下b+tree引擎的数据结构，pgsql在这一点上与mysql类似

6.手动新增分区 OR 删除分区

CREATE TABLE public.record_y2027m01 PARTITION OF public.record
	FOR VALUES FROM ('2027-01-01 00:00:00') TO ('2027-02-01 00:00:00');

DROP TABLE IF EXISTS public.record_y2027m01;

7.执行测试SQL查看分区命中情况
分别测试insert和select

-- 插入一条未建分区的数据
INSERT INTO "public"."record" ("record_id", "check_date", "check_type", "status", "create_time", "update_time")
VALUES
(SYS_GUID_NAME(), '2027-02-01 10:49:21', '1', 1, NOW(), NOW());

在这里插入图片描述 SQL报错提示 ‘2027-02-01’ 找不到分区所以表分区后一定要确保分区字段涵盖的分区全部建好下文中会附批量创建分区的自定义函数

插入一条正常的数据分区字段check_date的时间为 11月分区的结束时间也是12月分区的开始时间

INSERT INTO "public"."record" ("record_id", "check_date", "check_type", "status", "create_time", "update_time")
VALUES
(SYS_GUID_NAME(), '2026-12-01 00:00:00', '1', 1, NOW(), NOW());

查询一定时间范围内的数据包含刚才新建的’2026-12-01 00:00:00’的数据

  SELECT * from public.record where check_date >='2026-06-01 00:00:00' AND check_date <= '2026-12-01 00:00:00' ;

查看执行计划看命中哪些分区
在这里插入图片描述

可以看到命中了6月到12月的分区
那么按照范围命中分区没问题了

这里看下刚才 '2026-12-01 00:00:00’的数据回头看下11月和12月分区范围是：在这里插入图片描述那么这条数据其实是放在12月分区内的的所以这个区间是前开后闭的

再单独查看一下这条数据在不在12月的分区确认这个前开后闭区间
在这里插入图片描述

8.创建用于管理分区的自定义函数
8.1 按照日期创建月分区

-- 识别日期创建月分区
CREATE OR REPLACE FUNCTION public.auto_create_partition_record(
    p_date DATE
) RETURNS VOID AS
$$
DECLARE
    v_year           TEXT;
    v_month          TEXT;
    v_partition_name TEXT;
    v_start_date     TEXT;
    v_end_date       TEXT;
    v_exists         INTEGER;
BEGIN
    -- 提取年份和月份
    v_year := TO_CHAR(p_date, 'YYYY');
    v_month := TO_CHAR(p_date, 'MM');
    v_partition_name := 'record_y' || v_year || 'm' || v_month;

    -- 检查分区是否已存在
    SELECT COUNT(*)
    INTO v_exists
    FROM pg_tables
    WHERE schemaname = 'public'
      AND tablename = v_partition_name;
	  
    -- 如果分区不存在，则创建
    IF v_exists = 0 THEN
        -- 计算分区范围
        v_start_date := TO_CHAR(DATE_TRUNC('month', p_date), 'YYYY-MM-DD HH24:MI:SS');
        v_end_date := TO_CHAR(DATE_TRUNC('month', p_date + INTERVAL '1 month'), 'YYYY-MM-DD HH24:MI:SS');

        -- 动态创建分区
        EXECUTE format(
                'CREATE TABLE public.%I PARTITION OF public.record FOR VALUES FROM (%L) TO (%L)',
                v_partition_name,
                v_start_date,
                v_end_date
                );

        -- 记录日志
        RAISE NOTICE '自动创建分区: % (范围: % 到 %)', v_partition_name, v_start_date, v_end_date;
    END IF;
END;
$$ LANGUAGE plpgsql;

使用auto_create_partition_record函数创建指定分区

SELECT public.auto_create_partition_record('2027-02-22'::DATE);

8.2 批量创建分区

CREATE OR REPLACE FUNCTION public.batch_create_future_partitions(
    p_months INTEGER DEFAULT 3
) RETURNS VOID AS
$$
DECLARE
    v_current_date DATE;
    v_counter      INTEGER;
BEGIN
    v_current_date := CURRENT_DATE;

    FOR v_counter IN 0..p_months
        LOOP
            -- 为 record 创建分区
            PERFORM public.auto_create_partition_record(v_current_date + (v_counter || ' months')::INTERVAL);
        END LOOP;

    RAISE NOTICE '批量创建分区完成，已创建未来 % 个月的分区', p_months;
END;
$$ LANGUAGE plpgsql;

使用batch_create_future_partitions函数批量创建未来6个月的分区

SELECT public.batch_create_future_partitions(6);

9.附实用函数工具
创建查看分区函数

-- 1. 查看所有分区信息
CREATE OR REPLACE FUNCTION public.list_partitions(p_table_name TEXT)
    RETURNS TABLE
            (
                partition_name TEXT,
                partition_size TEXT,
                row_count      BIGINT
            )
AS
$$
BEGIN
    RETURN QUERY
        SELECT t.tablename::TEXT,
               pg_size_pretty(pg_total_relation_size('public.' || t.tablename))::TEXT,
               (SELECT COUNT(*)
                FROM pg_catalog.pg_class c
                WHERE c.relname = t.tablename
                  AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public'))::BIGINT
        FROM pg_tables t
        WHERE t.schemaname = 'public'
          AND t.tablename LIKE p_table_name || '_y%'
        ORDER BY t.tablename;
END;
$$ LANGUAGE plpgsql;

– list_partitions 使用示例

SELECT * FROM public.list_partitions('record');

创建删除过去N月的分区函数

CREATE OR REPLACE FUNCTION public.drop_old_partitions(
    p_table_name TEXT,
    p_keep_months INTEGER DEFAULT 36
) RETURNS VOID AS
$$
DECLARE
    v_cutoff_date      DATE;
    v_partition_record RECORD;
    v_drop_count       INTEGER := 0;
BEGIN
    v_cutoff_date := CURRENT_DATE - (p_keep_months || ' months')::INTERVAL;

    FOR v_partition_record IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'public'
          AND tablename LIKE p_table_name || '_y%'
        LOOP
            -- 从分区名称提取日期并判断是否需要删除
            DECLARE
                v_year           TEXT;
                v_month          TEXT;
                v_partition_date DATE;
            BEGIN
                v_year := SUBSTRING(v_partition_record.tablename FROM '_y(\d{4})m');
                v_month := SUBSTRING(v_partition_record.tablename FROM 'm(\d{2})$');
                v_partition_date := (v_year || '-' || v_month || '-01')::DATE;

                IF v_partition_date < v_cutoff_date THEN
                    EXECUTE format('DROP TABLE IF EXISTS public.%I', v_partition_record.tablename);
                    v_drop_count := v_drop_count + 1;
                    RAISE NOTICE '删除旧分区: %', v_partition_record.tablename;
                END IF;
            END;
        END LOOP;

    RAISE NOTICE '共删除 % 个旧分区', v_drop_count;
END;
$$ LANGUAGE plpgsql;

drop_old_partitions 使用示例：

-- 使用示例（删除3年前的分区）
SELECT public.drop_old_partitions('record', 36);

写在最后：
1.本文的分区是Rang范围分区，那么基于pgsql研发的人大金仓理论上也支持List、Hash分区等，甚至可以支持组合分区，比如我表里的一个check_type字段这是一个字典项，值是给定范围内不变的，那么完全可以先对check_type进行List分区，在分区内再进行时间的Rang范围分区，当然分区的主要目的是优化查询效率，对于频繁的数据插入和删除的场景下还是要谨慎考虑的。
2.分区创建后的管理工作也很重要，在设计分区的时候，需要同步考虑历史数据问题，分区表会越来越多，涉及到一些复杂查询匹配不上分区引发全表扫描时性能开销会很大，需要在业务上给定范围归档历史数据，控制分区数量。