1.背景及分析

业务数据可能存在数据时间异常的情况。例如，推送数据的时间戳是2090年12月12日，或1998年11月1日。

业务侧明确要求，由于数据只是时间戳有问题，其他字段信息仍然有统计价值。因此不能在上游清洗丢失，要保证异常数据也正常入库。后续业务会单独处理异常。

另外，为减少运维成本，客户希望在同时考虑到不影响客户实时业务的前提下，DWS分区表可自动创建分区。

当前版本（9.1.0）自建分区和分区自增功能上有冲突，自动分区超范围存在maxvalue时无法自动分区的，客户无法接受手动大量建分区。

2.  解决方案【过度方案】

因过多分区影响性能，下面过度方案为手动执行，历史数据调整为按月分区，未来按天分区，200天内即（6个月）为按天分区，分区到期后手动调整。

3.  原表定义示例（表a_temp1）

目前系统内按天分区的表定义一般如下：

-- drop table a_temp1;

CREATE  TABLE a_temp1(

id int,gisid int,tag1 int,tag2 int,tg_data_time  timestamp

)

WITH (orientation=column,secondary_part_column='gisid',bitmap_columns='tag1,tag2', enable_hstore=on, enable_turbo_store=on, enable_hstore_opt=on, compression=middle, colversion=2.0, max_batchrow=60000, enable_delta=false)

---- 二级分区建议选择有聚集数据的等值，where查询列，如gisid，

---- bitmap_cloumns列根据需要选择，可选择除二级分区外的多列

DISTRIBUTE BY HASH(id)    ---- 唯一键

PARTITION BY RANGE (tg_data_time)   ---- where条件会使用的时间字段

(

         PARTITION p11 VALUES LESS THAN ('2024-01-01 00:00:00'::timestamp(6) without time zone) TABLESPACE pg_default,

         PARTITION p12 VALUES LESS THAN ('2024-01-02 00:00:00'::timestamp(6) without time zone) TABLESPACE pg_default,

         PARTITION pmax VALUES LESS THAN ('10001-03-01 00:00:00'::timestamp(6) without time zone) TABLESPACE pg_default

);

4.  分区调整示例（按天分区）

注：可根据需求自行调整

1)、2024年按月分区

2)、2025年上半年按天分区

3)、dws单表支持最大支持分区数32768个，但基于性能建议分区数个数不宜太多，需及时合并分区

• 调整分区

alter table a_temp1 split partition pmax into(

partition p2024m1 start('2024-01-02') end ('2024-02-01') ,            

partition p2024 start('2024-02-01') end ('2025-01-01') every(interval '1 month'),

partition p2025m1 start('2025-01-01') end ('2025-02-01') every(interval '1 day'),

partition p2025m2 start('2025-02-01') end ('2025-03-01') every(interval '1 day'),

partition p2025m3 start('2025-03-01') end ('2025-04-01') every(interval '1 day'),

partition p2025m4 start('2025-04-01') end ('2025-05-01') every(interval '1 day'),

partition p2025m5 start('2025-05-01') end ('2025-06-01') every(interval '1 day'),

partition p2025m6 start('2025-06-01') end ('2025-07-01') every(interval '1 day'),

partition pmax1 end ('10001-03-01') );

• 后续修改分区，增加2025年7月分区

alter table a_temp1 split partition pmax1 into(

partition p2025m7 start('2025-07-01') end ('2025-08-01') every(interval '1 day'),

partition pmax2 end ('10001-03-01') );

• 合并分区，2025年1月合并分区

alter table a_temp1 merge partitions

p2025m1_1,p2025m1_2,p2025m1_3,p2025m1_4,p2025m1_5,p2025m1_6,p2025m1_7,p2025m1_8,p2025m1_9,p2025m1_10,p2025m1_11,p2025m1_12,p2025m1_13,p2025m1_14,p2025m1_15,p2025m1_16,p2025m1_17,p2025m1_18,p2025m1_19,p2025m1_20,p2025m1_21,p2025m1_22,p2025m1_23,p2025m1_24,p2025m1_25,p2025m1_26,p2025m1_27,p2025m1_28,p2025m1_29,p2025m1_30,p2025m1_31

into partition p2025m1;

• 清理分区数据，建议使用truncate，不支持多分区

alter table a_temp1 truncate partition p2024_1;

• 删除分区及数据，建议使用drop，支持多分区

alter table a_temp1 drop partition p2024_1,p2024_2;

5.     数据倾斜判断方法及修改分布键方法

注：tablexxx 为schemaname.tablename

• 通过系统函数判断

select * from table_skewness(' tablexxx')

• 通过count计算简单判断

select 分布键,count(1) from tablexxx group by 1 order by 2 desc

• 修改分布键

alter table tablexxx distribute by hash(新分布键)

注：分布键选择参考6建表建议

6.     通过新建表方式修改表定义和迁移数据

注：不考虑数据变化，可以不使用事务，省略1、2、7步

• 开启事务

start transaction; 

• 锁表（确保不会有数据写入）

lock table a_temp1;

• 通过like语法或自定义表结构创建表

create table a_temp1_copy(like a_temp1);

• 插入数据

insert into a_temp1_copy select * from a_temp1;

• 删除原表

drop table a_temp1 ;

• 重命名新表

alter table a_temp1_copy rename to a_temp1;

• 提交事务

commit;