创建按月 range 分区的表（在循环中生成分区）

命令

CREATE TABLE sales (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  created_at      date   NOT NULL,
  customer_id bigint NOT NULL,
  amount      numeric(12,2),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- An index defined on the parent propagates to all partitions automatically
CREATE INDEX sales_customer_id_idx ON sales (customer_id);

-- 12 monthly partitions for 2026
DO $$
DECLARE
  m date;
BEGIN
  FOR m IN SELECT generate_series(date '2026-01-01', date '2026-12-01', interval '1 month')::date LOOP
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF sales FOR VALUES FROM (%L) TO (%L)',
      'sales_' || to_char(m, 'YYYY_MM'), m, (m + interval '1 month')::date);
  END LOOP;
END $$;

-- Hierarchy, bounds and sizes
SELECT t.relid::regclass AS partition_name,
       t.parentrelid::regclass AS parent,
       t.isleaf,
       t.level,
       pg_get_expr(c.relpartbound, c.oid) AS bounds,
       pg_size_pretty(pg_total_relation_size(t.relid)) AS total_size
FROM pg_partition_tree('sales') t
JOIN pg_class c ON c.oid = t.relid
ORDER BY t.level, bounds;

作用

在日期列上创建 RANGE 分区的父表、一个会传播到每个分区的索引，并在循环中创建 12 个按月分区；然后列出层次结构及其边界和大小。

适用场景

用于不断增长、定期删除旧数据（DETACH/DROP）或查询大多按日期范围过滤的大表（订单、日志、时间序列）。

注意

在分区表上，PRIMARY KEY/UNIQUE 约束必须包含分区键（不存在全局唯一索引）。下界包含，上界不包含（FROM 包含，TO 不包含）。如果没有预先创建未来分区的作业（pg_cron、pg_partman），超出范围的 INSERT 会失败。数千个分区会增加规划时间和锁开销。父表不支持 CREATE INDEX CONCURRENTLY：在父表上使用 CREATE INDEX ... ON ONLY，在每个分区上使用 CREATE INDEX CONCURRENTLY，然后执行 ALTER INDEX ... ATTACH PARTITION。