postgresql分区表 - 详解

分区表的作用是提升查询效率，对于插入也有一定的优化，就是一张表，划分成多个物理表。

分区表主要以范围、列表、哈希这三种，目前最多的就是范围，典型的就刷时间，所有这里也以时间范围为例。

创建分区表

分区创建有2种方式，分别是继承和声明。pg10以前只是继承，pg10以后支持声明。需要说明的刷pg数据库还支持多久符合分区。

1.声明

CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);
-- 创建季度分区
CREATE TABLE sales_q1 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE sales_q2 PARTITION OF sales
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

2.继承

1.创建父表
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

2.创建继承表，指定约束范围
CREATE TABLE measurement_202409 (
    CHECK ( logdate >= DATE '2024-09-01' AND logdate < DATE '2024-10-01' )
) INHERITS (measurement);
CREATE TABLE measurement_202410 (
    CHECK ( logdate >= DATE '2024-10-01' AND logdate < DATE '2024-11-01' )
) INHERITS (measurement);

继承的方式比较灵活，当然也就比较麻烦，还可以重定向，目前很少这样，有需求还可以了解。

这里不讲。

维护分区

创建分区SQL

CREATE TABLE sales_q1 PARTITION OF
 sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01'
);

管理创建分区

先说创建分区，可以使用创建分区的sql语句操作分区，也可以使用触发器或者存储过程管理，pg也可以使用插件。

目前的资料显示推荐触发器，只是我的理解触发器每次插入数据都会检查，实际操作是提前将分区表一次创建很久，1年或者5年。但是推荐使用触发器我没有明白 不会降低效率吗？

触发器创建

这里还是说说触发器。样例：

CREATE OR REPLACE FUNCTION create_monthly_partitions()
RETURNS VOID AS $$
BEGIN
    FOR i IN 0..11 LOOP
        EXECUTE format('CREATE TABLE sales_%s PARTITION OF sales FOR VALUES FROM (%L) TO (%L)',
            to_char(CURRENT_DATE + (i || ' month')::interval, 'YYYY_MM'),
            CURRENT_DATE + (i || ' month')::interval,
            CURRENT_DATE + ((i+1) || ' month')::interval);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

这样就会按照月份创建一个触发器来自动创建。

pg_partman 扩展插件

下载插件编译安装，稳定刷linux环境下的编译安装。

安装完成后需要做以下工作：

1.postgresql.conf 文件中添加以下配置

shared_preload_libraries = 'pg_partman_bgw'

2.数据库中执行命令

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

插件地址：

pgpartman/pg_partman: Partition management extension for PostgreSQL

删除分区

直接删除分区和数据

DROP TABLE 分区表名;

分离删除

-- 假设你有一个名为 partitioned_table 的父表
-- 首先，从父表中分离分区
DETACH PARTITION partitioned_table FOR VALUES IN (1);

-- 然后，删除这个分区
DROP TABLE partitioned_table_part1;

删除分区

ALTER TABLE partitioned_table DROP PARTITION FOR VALUES IN (1);

移除分区变成普通表

 alter table t_range detach partition t_range_2 ;

查询分区

1.查询所有

-- 查询所有分区表
SELECT nmsp_parent.nspname AS parent_schema,
       parent.relname      AS parent_table,
       nmsp_child.nspname  AS child_schema,
       child.relname       AS child_table
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child  ON nmsp_child.oid  = child.relnamespace;

2.查询特地分区

SELECT child.relname AS partition_name
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
WHERE parent.relname = 'sales'

3.查询声明式分区

-- 查询声明式分区表
SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       c.relkind,
       CASE WHEN c.relispartition THEN 'YES' ELSE 'NO' END AS is_partition,
       CASE WHEN c.relispartition THEN pg_get_expr(c.relpartbound, c.oid) ELSE NULL END AS partition_bound,
       CASE WHEN c.relispartition THEN pg_get_partkeydef(c.oid) ELSE NULL END AS partition_key
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind IN ('r', 'p')  -- 普通表或分区表
AND n.nspname NOT IN ('pg_catalog', 'information_schema');

4.整合查询需要的信息

SELECT parent.relname,
        child.relname,
                CASE WHEN child.relispartition THEN pg_get_expr(child.relpartbound, child.oid) ELSE NULL END AS
 partition_bound
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid