GCP上Postgres 12基于Epoch的自动范围分区方案咨询

一、预处理：将numeric时间戳转为可分区的时间类型 #

由于原列是numeric(19)存储的毫秒级Epoch时间戳（如1668368711000），Postgres范围分区需要基于可排序的时间类型，建议在主表中添加生成列转换为timestamptz（带时区，适配GCP多区域场景），无需修改原有数据：

ALTER TABLE your_table
ADD COLUMN ts timestamptz GENERATED ALWAYS AS (
  to_timestamp(CAST(epoch_col / 1000 AS bigint))
) STORED;

注：epoch_col是你原有的numeric类型时间戳列名，ts是新生成的带时区时间列，STORED表示值会物理存储，提升分区查询性能。

二、创建范围分区主表 #

1. 按天分区的主表 #

CREATE TABLE your_table (
  -- 保留原有所有列，加上上面的ts列
  id INT,
  epoch_col NUMERIC(19),
  ts timestamptz GENERATED ALWAYS AS (to_timestamp(CAST(epoch_col / 1000 AS bigint))) STORED,
  -- 其他列...
) PARTITION BY RANGE (ts);

2. 按小时分区的主表 #

仅需基于相同列定义调整分区粒度，主表结构与上述一致：

CREATE TABLE your_table (
  -- 同上面的列定义
) PARTITION BY RANGE (ts);

后续压测时，只需切换主表的分区粒度和对应的触发器逻辑即可。

三、实现插入时自动创建分区 #

Postgres 12无原生自动分区功能，通过触发器函数实现插入时动态创建分区：

1. 创建自动分区函数 #

按天分区的函数

CREATE OR REPLACE FUNCTION create_daily_partition()
RETURNS TRIGGER AS $$
DECLARE
  partition_name TEXT;
  partition_start timestamptz;
  partition_end timestamptz;
BEGIN
  -- 生成分区名：your_table_20240520
  partition_name := 'your_table_' || to_char(NEW.ts, 'YYYYMMDD');
  -- 计算分区的时间范围（当天0点到次日0点）
  partition_start := date_trunc('day', NEW.ts);
  partition_end := partition_start + INTERVAL '1 day';

  -- 检查分区是否存在，不存在则创建
  IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = partition_name) THEN
    EXECUTE format(
      'CREATE TABLE %I PARTITION OF your_table FOR VALUES FROM (%L) TO (%L)',
      partition_name, partition_start, partition_end
    );
    -- 可选：给分区添加索引（和主表索引一致）
    EXECUTE format('CREATE INDEX %I_idx ON %I (ts)', partition_name, partition_name);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

按小时分区的函数

仅调整分区名和时间范围：

CREATE OR REPLACE FUNCTION create_hourly_partition()
RETURNS TRIGGER AS $$
DECLARE
  partition_name TEXT;
  partition_start timestamptz;
  partition_end timestamptz;
BEGIN
  -- 生成分区名：your_table_2024052014
  partition_name := 'your_table_' || to_char(NEW.ts, 'YYYYMMDDHH24');
  -- 计算分区的时间范围（当前小时整点到下一小时整点）
  partition_start := date_trunc('hour', NEW.ts);
  partition_end := partition_start + INTERVAL '1 hour';

  IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = partition_name) THEN
    EXECUTE format(
      'CREATE TABLE %I PARTITION OF your_table FOR VALUES FROM (%L) TO (%L)',
      partition_name, partition_start, partition_end
    );
    EXECUTE format('CREATE INDEX %I_idx ON %I (ts)', partition_name, partition_name);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到主表 #

按天分区的触发器

CREATE TRIGGER trigger_create_daily_partition
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE FUNCTION create_daily_partition();

按小时分区的触发器

CREATE TRIGGER trigger_create_hourly_partition
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE FUNCTION create_hourly_partition();

四、通过GCP Cloud Scheduler定时删除旧分区 #

1. 编写删除旧分区的SQL脚本 #

按天删除（保留最近N天）

DO $$
DECLARE
  cutoff_date timestamptz := now() - INTERVAL '30 days'; -- 保留最近30天，可调整
  partition_rec RECORD;
BEGIN
  -- 遍历所有符合条件的分区
  FOR partition_rec IN
    SELECT tablename
    FROM pg_tables
    WHERE tablename LIKE 'your_table_%' -- 匹配分区命名规则
      AND to_timestamp(substring(tablename from 12 for 8), 'YYYYMMDD') < cutoff_date
  LOOP
    EXECUTE format('DROP TABLE %I', partition_rec.tablename);
  END LOOP;
END $$;

按小时删除（保留最近N小时）

DO $$
DECLARE
  cutoff_date timestamptz := now() - INTERVAL '72 hours'; -- 保留最近72小时，可调整
  partition_rec RECORD;
BEGIN
  FOR partition_rec IN
    SELECT tablename
    FROM pg_tables
    WHERE tablename LIKE 'your_table_%'
      AND to_timestamp(substring(tablename from 12 for 10), 'YYYYMMDDHH24') < cutoff_date
  LOOP
    EXECUTE format('DROP TABLE %I', partition_rec.tablename);
  END LOOP;
END $$;

2. 在GCP Cloud Scheduler中配置定时任务 #

• 推荐使用Cloud Functions封装SQL执行（更安全），或选择HTTP/S类型任务调用自定义脚本
• 调度频率：按天删除设为0 0 * * *（每天凌晨），按小时删除设为0 * * * *（每小时整点）
• 确保执行任务的服务账号拥有Postgres的DROP TABLE权限

注意事项 #

• 权限：触发器函数的执行用户需要CREATE TABLE权限；调度任务的服务账号需要对应Postgres操作权限
• 性能：生成列STORED会占用额外存储，但能避免查询时实时转换的开销；分区索引建议与主表索引保持一致
• 压测对比：分别搭建按天、按小时分区的测试环境，模拟业务流量对比查询、插入、删除性能，选择适配业务的粒度

内容的提问来源于stack exchange，提问作者user3706888