PostgreSQL 最佳实践本文全网唯一源地址
PostgreSQL 自动创建分区最佳实践
引言
分区表是 PostgreSQL 在 10 版本才具有的特性，实际使用中，用户往往需要做到提前创建分区或者按写入的数据实时创建分区。 本文探讨常见的几种自动分区创建方案。
场景
分区表在实际使用中，一般以时间字段作为分区键。这里为了简化问题，我们假设分区字段类型为timestamp，分区方式为List of values.
表结构如下：
代码语言：sql复制CREATE TABLE tab
(
id bigint GENERATED ALWAYS AS IDENTITY,
ts timestamp NOT NULL,
data text
) PARTITION BY LIST ((ts::date));
CREATE TABLE tab_def PARTITION OF tab DEFAULT;分区的创建一般分以下两种场景：
- 定时提前创建分区
- 按需实时创建分区
方案
定时提前创建分区
定时提前创建分区一般只需要一个定时任务调度工具即可实现，常见的有以下几种：
- 使用系统调度器，如 Crontab (Linux, Unix, etc.) 和 Task Scheduler (Windows) ;
- 使用数据库内置调度器，如 pg_cron, pg_timetable ;
- 使用专门的分区管理插件，如 pg_partman ;
使用系统调度器
以 Linux 操作系统为例，每天下午 14 点创建下一天的分区表；
代码语言：shell复制cat > /tmp/create_part.sh <<EOF
dateStr=$(date -d ' 1 days' %Y%m%d);
psql -c "CREATE TABLE tab_$dateStr (LIKE tab INCLUDING INDEXES); ALTER TABLE tab ATTACH PARTITION tab_$dateStr FOR VALUES IN ('$dateStr')";
EOF
(crontab -l 2>/dev/null; echo "0 14 * * * bash /tmp/create_part.sh ") | crontab -使用数据库内置调度器
以pg_cron为例，每天下午 14 点创建下一天的分区表；
CREATE OR REPLACE FUNCTION create_tab_part() RETURNS integer
LANGUAGE plpgsql AS
$$
DECLARE
dateStr varchar;
BEGIN
SELECT to_char(DATE 'tomorrow', 'YYYYMMDD') INTO dateStr;
EXECUTE
format('CREATE TABLE tab_%s (LIKE tab INCLUDING INDEXES)', dateStr);
EXECUTE
format('ALTER TABLE tab ATTACH PARTITION tab_%s FOR VALUES IN (%L)', dateStr, dateStr);
RETURN 1;
END;
$$;
CREATE EXTENSION pg_cron;
SELECT cron.schedule('0 14 * * *', $$SELECT create_tab_part();$$);使用专门的分区管理插件
以pg_partman为例，每天提前创建下一天的分区表；
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(p_parent_table => 'public.tab',
p_control => 'ts',
p_type => 'native',
p_interval=> 'daily',
p_premake => 1);按需实时创建分区
有时候，我们需要按数据插入的需要来创建分区，而不是提前预创建。 根据分区是否存在，可以方便地判断该时间区间内有没有数据存在。此时一般采用触发器来实现。
但仍然有两个问题要解决：
- 13及以上版本才提供了针对分区表的
BEFORE/FOR EACH ROW触发器。
ERROR: "tab" is a partitioned table
DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.- 插入数据时，因为锁表的原因，无法修改分区表定义，即无法ATTACH子表。 因此必须有另一个连接来做 ATTACH 的操作，此处我们想到了用
LISTEN/NOTIFY机制来通知另一个连接进行分区定义的修改。
ERROR: cannot CREATE TABLE .. PARTITION OF "tab"
because it is being used by active queries in this session
或
ERROR: cannot ALTER TABLE "tab"
because it is being used by active queries in this session触发器(实施子表创建和NOTIFY)
代码语言：sql复制CREATE FUNCTION part_trig() RETURNS trigger
LANGUAGE plpgsql AS
$$
BEGIN
BEGIN
/* try to create a table for the new partition */
EXECUTE
format('CREATE TABLE %I (LIKE tab INCLUDING INDEXES)', 'tab_' || to_char(NEW.ts, 'YYYYMMDD'));
/*
* tell listener to attach the partition
* (only if a new table was created)
*/
EXECUTE
format('NOTIFY tab, %L', to_char(NEW.ts, 'YYYYMMDD'));
EXCEPTION
WHEN duplicate_table THEN
NULL; -- ignore
END;
/* insert into the new partition */
EXECUTE
format('INSERT INTO %I VALUES ($1.*)', 'tab_' || to_char(NEW.ts, 'YYYYMMDD'))
USING NEW;
/* skip insert into the partitioned table */
RETURN NULL;
END;
$$;
CREATE TRIGGER part_trig
BEFORE INSERT
ON TAB
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE FUNCTION part_trig();代码(实施LISTEN和子表ATTACH)
代码语言：python代码运行次数：0复制#! /usr/bin/env python3.9
# encoding:utf8
import asyncio
import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
conn = psycopg2.connect('application_name=listener')
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
cursor.execute(f'LISTEN tab;')
def attach_partition(table, date):
with conn.cursor() as cs:
cs.execute('ALTER TABLE "%s" ATTACH PARTITION "%s_%s" FOR VALUES IN ('%s')' % (table, table, date, date))
def handle_notify():
conn.poll()
for notify in conn.notifies:
print(notify.payload)
attach_partition(notify.channel, notify.payload)
conn.notifies.clear()
loop = asyncio.get_event_loop()
loop.add_reader(conn, handle_notify)
loop.run_forever()总结
本文介绍了两种场景下自动创建分区的几种解决方案，各有优劣。
定时提前创建分区场景下的几种解决方案比较简单易懂，但是会依赖系统或插件的定时管理机制，在运维、迁移时具有额外管理成本。
按需实时创建分区场景下，能按实际数据规律减少不必要的分区数量，但是也需要较高版本(>=13)及额外连接来完成，复杂度比较高。
我们可视自身业务情况，来选择合适的自动创建分区的方式。
