obs_data 月度分区表落地实施手册（PostgreSQL）

适用场景：每月千万级写入；按 obs_time 月度 RANGE 分区；覆盖高频查询
1）st_id + 时间范围 拉取设备时间段内所有要素数据
2）st_id + el_code + 时间范围 拉取设备指定要素时间段内数据
3）时间范围 扫描该时间段所有设备所有观测数据
同时实现：自动创建下个月分区 + 自动为分区创建索引，避免月初写入失败与性能抖动。

---

1. 会话准备（进入数据库）

1.1 进入 psql（示例）

psql -h <host> -p <port> -U <user> -d <db>

1.2 建议会话设置（可选）

\timing on
\set ON_ERROR_STOP on

---

2. 创建父表（分区表）与约束

关键点：

• 分区表的 PRIMARY KEY / UNIQUE 约束必须包含分区键 obs_time（PostgreSQL 限制）。
• el_mode_code 允许 NULL 时，UNIQUE 会把多个 NULL 当作“互不相等”，可能导致“看似防重但仍重复”。
• 通过生成列 el_mode_code_nn = COALESCE(el_mode_code, '_') 将 NULL 归一化，使唯一约束真正生效。

CREATE TABLE IF NOT EXISTS public.obs_data (
  id                int8         NOT NULL,
  st_id             varchar(10)  NOT NULL,
  obs_time          timestamp(6) NOT NULL,
  el_code           varchar(10)  NOT NULL,
  el_mode_code      varchar(10),
  value             double precision,
  value_t           text,
  qc_flag           varchar(4),
  ingest_time       timestamp(6) NOT NULL DEFAULT now(),

  -- 生成列：将 NULL 模式统一为 '_'，用于真正防重复
  el_mode_code_nn   varchar(10) GENERATED ALWAYS AS (COALESCE(el_mode_code, '_')) STORED,

  -- 主键（必须包含分区键）
  CONSTRAINT obs_data_pk PRIMARY KEY (obs_time, id),

  -- 防重复：同设备同一时刻同一要素同一模式仅允许一条
  CONSTRAINT obs_data_uq_st_time_el_mode UNIQUE (st_id, obs_time, el_code, el_mode_code_nn),

  -- 约束（按需启用）：value 与 value_t 避免同时有值
  CONSTRAINT obs_data_ck_value_xor_text CHECK (value IS NULL OR value_t IS NULL),

  -- 约束（按需启用）：非 MISS 至少有一个结果
  CONSTRAINT obs_data_ck_missing_value CHECK (
    qc_flag = 'MISS' OR value IS NOT NULL OR value_t IS NOT NULL
  )
)
PARTITION BY RANGE (obs_time);

COMMENT ON TABLE  public.obs_data IS '基础数据表（按 obs_time 月度分区）';
COMMENT ON COLUMN public.obs_data.id IS '主键ID（snow id）；约束层面主键为(obs_time,id)';
COMMENT ON COLUMN public.obs_data.st_id IS '站点ID（区站号或网格码）';
COMMENT ON COLUMN public.obs_data.obs_time IS '观测时间点（用于按月分区）';
COMMENT ON COLUMN public.obs_data.el_code IS '要素代码（如 TEMP、RAIN）';
COMMENT ON COLUMN public.obs_data.el_mode_code IS '观测模式代码（例如 instant/period/event）';
COMMENT ON COLUMN public.obs_data.value IS '数值型结果';
COMMENT ON COLUMN public.obs_data.value_t IS '文本描述（非数值型结果）';
COMMENT ON COLUMN public.obs_data.qc_flag IS '质量标志（OK / OUT / MISS）';
COMMENT ON COLUMN public.obs_data.ingest_time IS '入库时间（默认当前时间）';

2.1 验证父表存在

SELECT relname
FROM pg_class
WHERE relname = 'obs_data';

---

3. 创建 DEFAULT 兜底分区（推荐）

目的：避免遗漏建分区导致写入失败；也可承接少量历史补录/异常时间数据（建议监控其写入量）。

CREATE TABLE IF NOT EXISTS public.obs_data_default
PARTITION OF public.obs_data DEFAULT;

3.1 验证当前分区列表

SELECT c.relname AS partition_name
FROM pg_inherits i
JOIN pg_class  c ON c.oid = i.inhrelid
JOIN pg_class  p ON p.oid = i.inhparent
WHERE p.relname = 'obs_data'
ORDER BY 1;

---

4. 创建“按月建分区 + 自动建索引”的函数（核心）

索引覆盖查询场景：

• (st_id, obs_time)：设备 + 时间范围（拉全要素）
• (st_id, el_code, obs_time)：设备 + 要素 + 时间范围
• BRIN(obs_time)：纯时间范围扫描（时间追加写入形态下更有效、占用空间更小）

CREATE OR REPLACE FUNCTION public.obs_data_ensure_month_partition(p_month date)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  v_start    timestamp(6);
  v_end      timestamp(6);
  v_part     text;
BEGIN
  v_start := date_trunc('month', p_month)::timestamp(6);
  v_end   := (v_start + interval '1 month')::timestamp(6);
  v_part  := format('obs_data_%s', to_char(v_start, 'YYYYMM'));

  -- 1) 创建分区
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS public.%I PARTITION OF public.obs_data
     FOR VALUES FROM (%L) TO (%L);',
    v_part, v_start, v_end
  );

  -- 2) 设备 + 时间范围（全要素）
  EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (st_id, obs_time)
     INCLUDE (el_code, el_mode_code, value, value_t, qc_flag, ingest_time);',
    v_part || '_st_time_idx', v_part
  );

  -- 3) 设备 + 要素 + 时间范围
  EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (st_id, el_code, obs_time)
     INCLUDE (el_mode_code, value, value_t, qc_flag, ingest_time);',
    v_part || '_st_el_time_idx', v_part
  );

  -- 4) 时间范围扫描（追加写入更适配）
  EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I USING BRIN (obs_time) WITH (pages_per_range = 64);',
    v_part || '_time_brin_idx', v_part
  );

END;
$$;

4.1 验证函数存在

SELECT proname
FROM pg_proc
WHERE proname = 'obs_data_ensure_month_partition';

---

5. 初始化分区（至少当月 + 下月）

-- 当月分区
SELECT public.obs_data_ensure_month_partition(current_date);

-- 下月分区（提前建，避免月初写入失败）
SELECT public.obs_data_ensure_month_partition((current_date + interval '1 month')::date);

5.1 验证分区已创建

SELECT c.relname AS partition_name
FROM pg_inherits i
JOIN pg_class  c ON c.oid = i.inhrelid
JOIN pg_class  p ON p.oid = i.inhparent
WHERE p.relname = 'obs_data'
ORDER BY 1;

5.2 验证某分区索引已创建（替换为实际分区名）

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname='public' AND tablename='obs_data_YYYYMM'
ORDER BY indexname;

---

6. 自动创建下月分区（调度）

方案 A：数据库内 pg_cron（推荐，需安装扩展）

6A.1 安装扩展（需要权限）

CREATE EXTENSION IF NOT EXISTS pg_cron;

6A.2 创建定时任务（每月 25 号 02:10 建下月分区）

SELECT cron.schedule(
  'obs_data_create_next_month_partition',
  '10 2 25 * *',
  $$SELECT public.obs_data_ensure_month_partition((current_date + interval '1 month')::date);$$
);

6A.3 验证任务存在

SELECT jobid, jobname, schedule, command
FROM cron.job
ORDER BY jobid;

方案 B：操作系统 cron（最通用）

编辑 crontab：

crontab -e

新增（每月 25 号 02:10 执行；按实际连接信息替换）：

10 2 25 * * psql -h <host> -p <port> -U <user> -d <db> -c "SELECT public.obs_data_ensure_month_partition((current_date + interval '1 month')::date);" >> /var/log/obs_data_partition.log 2>&1

---

7. 查询规范（保证分区裁剪 + 索引命中）

必须使用半开区间：obs_time >= :start AND obs_time < :end
避免对 obs_time 做函数包裹（如 date_trunc(obs_time)）以免破坏分区裁剪与索引使用。

7.1 场景 1：st_id + 时间范围（全要素）

SELECT *
FROM public.obs_data
WHERE st_id = ANY(:st_ids)
  AND obs_time >= :start_time
  AND obs_time <  :end_time;

7.2 场景 2：st_id + el_code + 时间范围

SELECT *
FROM public.obs_data
WHERE st_id = ANY(:st_ids)
  AND el_code = ANY(:el_codes)
  AND obs_time >= :start_time
  AND obs_time <  :end_time;

7.3 场景 3：时间范围扫全设备全量数据

SELECT *
FROM public.obs_data
WHERE obs_time >= :start_time
  AND obs_time <  :end_time;

---

8. 写入与性能验收（上线前建议做一次）

8.1 插入少量测试数据（示例）

INSERT INTO public.obs_data (id, st_id, obs_time, el_code, el_mode_code, value, qc_flag)
VALUES
  (1001, 'S0001', date_trunc('minute', now()), 'TEMP', 'instant', 25.3, 'OK'),
  (1002, 'S0001', date_trunc('minute', now()), 'RAIN', 'period',  0.0, 'OK'),
  (1003, 'S0002', date_trunc('minute', now()), 'TEMP', 'instant', 26.1, 'OK');

8.2 验证数据路由到了具体月份分区（而非 default）

SELECT tableoid::regclass AS real_partition, count(*)
FROM public.obs_data
GROUP BY 1
ORDER BY 2 DESC;

8.3 用 EXPLAIN 验证分区裁剪与索引命中

场景 1

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM public.obs_data
WHERE st_id IN ('S0001','S0002')
  AND obs_time >= date_trunc('month', now())
  AND obs_time <  date_trunc('month', now()) + interval '1 month';

场景 2

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM public.obs_data
WHERE st_id IN ('S0001')
  AND el_code IN ('TEMP','RAIN')
  AND obs_time >= now() - interval '1 day'
  AND obs_time <  now();

场景 3

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM public.obs_data
WHERE obs_time >= now() - interval '1 hour'
  AND obs_time <  now();

验收要点：

• 计划中仅扫描命中的月份分区（Partition pruning 生效）
• 场景 1/2 通常命中 _st_time_idx / _st_el_time_idx
• 场景 3 可能命中 BRIN 或顺序扫分区（取决于时间范围与统计信息）

---

9. 日常运维检查与应急

9.1 每月检查下月分区是否已创建

SELECT c.relname
FROM pg_inherits i
JOIN pg_class c ON c.oid=i.inhrelid
JOIN pg_class p ON p.oid=i.inhparent
WHERE p.relname='obs_data'
  AND c.relname = format(
    'obs_data_%s',
    to_char(date_trunc('month', current_date + interval '1 month'), 'YYYYMM')
  );

9.2 如发现漏建，手动补建

SELECT public.obs_data_ensure_month_partition((current_date + interval '1 month')::date);

9.3 写入量巨大时，定期更新统计信息（按需）

ANALYZE public.obs_data;
-- 或只分析新分区
ANALYZE public.obs_data_YYYYMM;

---

10. 可选扩展（按业务查询再加）

• 若存在“按 el_code 跨设备统计/检索”高频场景，可补充索引：(el_code, obs_time, st_id)
• 若 value 需要固定精度（如保留 2 位小数且避免浮点误差），可将 value 改为 numeric(9,2) 并同步调整上层代码与性能预期