PGSQL表分区的实现方案和实践操作

原创 已于 2022-05-25 09:36:14 修改 · 2.1k 阅读 · 10 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#postgresql #分区表

于 2022-05-18 16:51:23 首次发布
本文详细介绍了继承式分区（触发器与规则）和声明式分区（list, range, hash）的方法，包括创建表、子表、触发器/规则及维护操作。通过实例展示如何将大数据表拆分成更易管理的部分，提升查询效率。

表分区是解决一些因单表过大引用的性能问题的方式，比如某张表过大就会造成查询变慢，可能分区是一种解决方案。一般建议当单表大小超过内存就可以考虑表分区了。

目录

1，继承式分区，分为触发器（trigger）和规则（rule）两种方式

触发器的方式

规则的方式

2，声明式，分为list，range，hash三种方式

list方式

range方式

hash方式

3，分区表的维护

1，继承式分区，分为触发器（trigger）和规则（rule）两种方式

触发器的方式

1）创建表

CREATE TABLE "public"."track_info_rule_partition" (
    "id" serial,
    "object_type" int2 NOT NULL DEFAULT 0,
    "object_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
    "object_value" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL,
    "action_start_time" TIMESTAMP ( 0 ) NOT NULL,
    "action_end_time" TIMESTAMP ( 0 ) NOT NULL,
    "action_type" int2 NOT NULL,
    "action_geom" TEXT COLLATE "pg_catalog"."default" NOT NULL,
    "geom_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
    "geom_number" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
    "geom_address" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
    "ctime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
    "utime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
    "is_del" int2 NOT NULL DEFAULT 0,
    "geom_station_start" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
    "geom_station_end" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
CONSTRAINT "track_info_rule_partition_pkey" PRIMARY KEY ( "id" ) 
)

2）创建子表

DO $$ DECLARE
base TEXT;
sqlstring TEXT;
i INT;
BEGIN
        base = 'create table track_info_rule_partition_%s(check(action_start_time >= ''%s'' and action_start_time < ''%s'')) inherits (track_info_rule_partition)';
    FOR i IN 0..30
    loop
    sqlstring = format (
        base,
        to_char( '2022-05-15' :: DATE + ( i || ' day' ) :: INTERVAL, 'YYYYMMDD' ),
        '2022-05-15' :: DATE + ( i || ' day' ) :: INTERVAL,
        '2022-05-15' :: DATE + ( i + 1 || ' day' ) :: INTERVAL 
    );
    EXECUTE sqlstring;
    
END loop;

END $$ LANGUAGE plpgsql;

 3）创建触发器函数

CREATE 
   OR REPLACE FUNCTION ins_or_upd_on_track_info_trigger_part ( ) RETURNS TRIGGER AS $$ DECLARE
   sqlstring TEXT;
BEGIN
      sqlstring = 'INSERT INTO track_info_trigger_partition_%s values($1.*)';
   sqlstring := format ( sqlstring, to_char( NEW.action_start_time, 'YYYYMMDD' ) );
   EXECUTE sqlstring USING NEW;
   RETURN NULL;

END $$ LANGUAGE plpgsql;

4）创建触发器

CREATE TRIGGER tg_on_track_info_in_or_upd BEFORE INSERT ON track_info_trigger_partition FOR EACH ROW
EXECUTE FUNCTION ins_or_upd_on_track_info_trigger_part ( );

5）执行插入验证观察数据是否分布在子表和数据是否正常分布

规则的方式

1）创建大表

CREATE TABLE "public"."track_info_rule_partition" (
   "id" serial,
   "object_type" int2 NOT NULL DEFAULT 0,
   "object_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "object_value" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL,
   "action_start_time" TIMESTAMP ( 0 ) NOT NULL,
   "action_end_time" TIMESTAMP ( 0 ) NOT NULL,
   "action_type" int2 NOT NULL,
   "action_geom" TEXT COLLATE "pg_catalog"."default" NOT NULL,
   "geom_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_number" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_address" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "ctime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
   "utime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
   "is_del" int2 NOT NULL DEFAULT 0,
   "geom_station_start" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_station_end" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
CONSTRAINT "track_info_rule_partition_pkey" PRIMARY KEY ( "id" ) 
)

2）创建子表

DO $$ DECLARE
base TEXT;
sqlstring TEXT;
i INT;
BEGIN
    base = 'create table track_info_rule_partition_%s(check(action_start_time >= ''%s'' and action_start_time < ''%s'')) inherits (track_info_rule_partition)';
    FOR i IN 0..30
    loop
    sqlstring = format (
        base,
        to_char( '2022-05-15' :: DATE + ( i || ' day' ) :: INTERVAL, 'YYYYMMDD' ),
        '2022-05-15' :: DATE + ( i || ' day' ) :: INTERVAL,
        '2022-05-15' :: DATE + ( i + 1 || ' day' ) :: INTERVAL 
    );
    EXECUTE sqlstring;
    
END loop;

END $$ LANGUAGE plpgsql;

3)创建规则

DO $$ DECLARE
base TEXT;
sqlstring TEXT;
i INT;
BEGIN
        base = 'CREATE OR REPLACE RULE track_info_rule_partition_%s AS ON INSERT TO track_info_rule_partition
    WHERE action_start_time >= ''%s'' and action_start_time < ''%s''
    DO INSTEAD
    INSERT INTO track_info_rule_partition_%s VALUES(NEW.*)';
    FOR i IN 0..30
    loop
    sqlstring = format (
        base,
        to_char( '2022-05-15' :: DATE + ( i || ' day' ) :: INTERVAL, 'YYYYMMDD' ),
        '2022-05-15' :: DATE + ( i || ' day' ) :: INTERVAL,
        '2022-05-15' :: DATE + ( i + 1 || ' day' ) :: INTERVAL,
        to_char( '2022-05-15' :: DATE + ( i || ' day' ) :: INTERVAL, 'YYYYMMDD' )
        
    );
    EXECUTE sqlstring;
    
END loop;

END $$ LANGUAGE plpgsql;

4）执行插入验证观察数据是否分布在子表和数据是否正常分布

2，声明式，分为list，range，hash三种方式

list方式

1）创建list分区父表

CREATE TABLE "public"."track_info_list_partition" (
   "id" serial,
   "object_type" int2 NOT NULL DEFAULT 0,
   "object_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "object_value" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL,
   "action_start_time" TIMESTAMP ( 0 ) NOT NULL,
   "action_end_time" TIMESTAMP ( 0 ) NOT NULL,
   "action_type" int2 NOT NULL,
   "action_geom" TEXT COLLATE "pg_catalog"."default" NOT NULL,
   "geom_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_number" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_address" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "ctime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
   "utime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
   "is_del" int2 NOT NULL DEFAULT 0,
   "geom_station_start" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_station_end" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
CONSTRAINT "track_info_list_partition_pkey" PRIMARY KEY ( "id", "action_type" ) 
) PARTITION BY list ( action_type );

2）创建分区子表

do                                                                    
$$
declare base text; sqlstring text; i int;
begin
base = 'create table track_info_list_partition_%s partition of track_info_list_partition for values in (''%s'')';
for i in 0..9 loop
    sqlstring = format(base, i,  i);
    execute sqlstring;
end loop;                                          
end     
$$language plpgsql;

3）执行插入验证观察数据是否分布在子表和数据是否正常分布

range方式

1）创建大表

CREATE TABLE "public"."track_info_range_partition" (
   "id" serial,
   "object_type" int2 NOT NULL DEFAULT 0,
   "object_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "object_value" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL,
   "action_start_time" TIMESTAMP ( 0 ) NOT NULL,
   "action_end_time" TIMESTAMP ( 0 ) NOT NULL,
   "action_type" int2 NOT NULL,
   "action_geom" TEXT COLLATE "pg_catalog"."default" NOT NULL,
   "geom_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_number" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_address" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "ctime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
   "utime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
   "is_del" int2 NOT NULL DEFAULT 0,
   "geom_station_start" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_station_end" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
CONSTRAINT "track_info_range_partition_pkey" PRIMARY KEY ( "id", "action_start_time" ) 
) PARTITION BY range ( action_start_time );

2）创建分区子表

DO
   $$ DECLARE
   base TEXT;
date_start TEXT = '2022-05-15';
sqlstring TEXT;
i INT;
BEGIN
      base = 'create table track_info_range_partition_%s partition of track_info_range_partition for values from (''%s'') to (''%s'')';
   FOR i IN 0..30
   loop
   sqlstring = format (
      base,
      to_char( date_start :: timestamptz + ( i || ' day' ) :: INTERVAL, 'YYYYMMDD' ),
      date_start :: timestamptz + ( i || ' day' ) :: INTERVAL,
      date_start :: timestamptz + ( i + 1 || ' day' ) :: INTERVAL 
   );
   EXECUTE sqlstring;
   
END loop;

END $$ LANGUAGE plpgsql;

3）执行插入验证观察数据是否分布在子表和数据是否正常分布

hash方式

1）创建大表

CREATE TABLE "public"."track_info_hash_partition" (
   "id" serial,
   "object_type" int2 NOT NULL DEFAULT 0,
   "object_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "object_value" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL,
   "action_start_time" TIMESTAMP ( 0 ) NOT NULL,
   "action_end_time" TIMESTAMP ( 0 ) NOT NULL,
   "action_type" int2 NOT NULL,
   "action_geom" TEXT COLLATE "pg_catalog"."default" NOT NULL,
   "geom_name" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_number" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_address" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "ctime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
   "utime" TIMESTAMP ( 0 ) NOT NULL DEFAULT now( ),
   "is_del" int2 NOT NULL DEFAULT 0,
   "geom_station_start" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
   "geom_station_end" VARCHAR ( 255 ) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '' :: CHARACTER VARYING,
CONSTRAINT "track_info_hash_partition_pkey" PRIMARY KEY ( "id", "geom_name" ) 
) PARTITION BY hash ( geom_name );

2）创建分区子表

DO
   $$ DECLARE
   base TEXT;
sqlstring TEXT;
i INT;
BEGIN
      base = 'create table track_info_hash_partition_%s partition of track_info_hash_partition for values with(modulus 10, remainder %s)';
   FOR i IN 0..9
   loop
   sqlstring = format ( base, i, i );
   EXECUTE sqlstring;
   
END loop;

END $$ LANGUAGE plpgsql;

3）执行插入验证观察数据是否分布在子表和数据是否正常分布

3，分区表的维护

继承式分区表的维护，主要为 INHERIT 与 NO INHERIT。语法如下:

ALTER TABLE track_info_trigger_partition_20220520 NO INHERIT track_info_trigger_partition;

ALTER TABLE track_info_trigger_partition_20220520 INHERIT track_info_trigger_partition;

NO INHERIT的情况下，不可以通过父表对子表进行CRUD，INHERIT之后可以正常的CRUD。

声明式分区表的维护，主要为 ATTACH 与 DETACH。语法如下:

ALTER TABLE track_info_list_partition DETACH PARTITION track_info_list_partition_4;

ALTER TABLE track_info_list_partition ATTACH PARTITION track_info_list_partition_4 FOR VALUES in (4)

DETACH的情况下，不可以通过父表对子表进行CRUD，ATTACH之后可以正常的CRUD。

分区表的应用场景与优化实践
在不确定中寻找确定性
03-23 808
当表的数据量非常大，达到几千万甚至上亿行时，全表扫描会很慢，这时候分区可以帮助缩小扫描范围。比如，在一个电商系统中，订单表可能按月份分区，这样查询某个月的订单时，只需要扫描对应的分区，而不是整个表。或者在日志系统中，按天分区，方便归档和删除旧日志。 另外，如果数据有明显的分区键，比如时间字段，按时间分区可以方便地管理历史数据，比如删除旧数据时直接删除整个分区，效率更高。 当然，分区键的选择不当可能导致性能问题，或者分区过多带来的管理复杂性。 分区表是将一个大表分成多个较小的、更易管理的部分，每个部分称为一个
在 PostgreSQL 里如何处理数据的分区表的分区数量和大小的平衡？
发现生活，分享智慧，一起成长！
07-18 1462
在 PostgreSQL 中，处理数据的分区表的分区数量和大小的平衡是一个非常重要的问题。通过合理地设计分区方案，根据数据量、查询模式、系统资源和数据增长趋势等因素来确定分区数量和大小，我们可以充分发挥分区表的优势，提高数据库的性能和管理效率。同时，我们还需要进行定期的维护和管理，包括数据清理和归档、分区的合并和拆分、索引的维护以及监控和性能优化等，以确保分区表的性能和可用性。分区表的分区数量和大小的平衡是一个需要综合考虑多种因素的过程，需要我们根据实际情况进行灵活的调整和优化。
pgsql 创建分区表
csdnmuyi的博客
05-09 868
【代码】pgsql 创建分区表。
PostgreSQL 创建表分区
12-16
创建表分区步骤如下： 1. 创建主表 CREATE TABLE users ( uid int not null primary key, name varchar(20)); 2. 创建分区表(必须继承上面的主表) CREATE TABLE users_0 ( check (uid >= 0 and uid< 100>= 100)) INHERITS (users); 3. 在分区表上建立索引，其实这步可以省略的哦 CREATE INDEX users_0_uidindex on
关系型数据库-PostgreSQL
最新发布
yangwei2048的博客
11-08 1578
PostgreSQL是一款功能强大的开源关系型数据库，基于C语言实现。它采用BSD开源协议，允许商业化封装。PostgreSQL社区活跃，版本迭代快，最新版本为15.x，推荐使用12.x稳定版或14.x体验新特性。相比MySQL，PostgreSQL支持更丰富的数据类型、序列概念、插件扩展和主从同步复制。安装时推荐Linux环境，通过yum安装并初始化数据库后，默认创建postgres用户进行管理。配置方面需修改pg_hba.conf和postgresql.conf以支持远程连接和日志设置。基本操作包括用户
postgresql 创建表分区
smallfish | http://chenxiaoyu.org/blog
09-08 1050
测试版本：pg 8.3 (ubuntu)在pg里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。创建表分区步骤如下：1. 创建主表CREATE TABLE users ( uid int not null primary key, name varchar(20));2. 创建分区表(必须继承上面的主表)C
PostgreSQL分区表
Java后端技术栈
07-25 1603
分区表数据库分区表将表数据分成更小的物理分片，以此提高性能、可用性、易管理性。分区表是关系型数据库中比较常见的对大表的优化方式，数据库管理系统一般都提供了分区管理，而业务可以直接访问分区表而不需要调整业务架构，当然好的性能需要合理的分区访问方式。分区表是数据库中常见的技术，而PostgreSQL中的分区表有许多专有的特性，比如分区表实现方案多、分区为普通表、分区维护方案、SQL优化还有一些分区表的问题。
pgsql14自动创建表分区
工具人的博客
05-15 798
截至2025年，PostgreSQL和MySQL仍未原生支持自动创建分区表的功能。目前，PostgreSQL主要依赖三种方法处理分区表需求。触发器是最传统的方式，通过触发器检查数据并创建新分区，但频繁触发可能影响性能，已较少用于生产环境。pg_partman是一个专门用于自动管理分区表的扩展，配置简单且使用广泛，支持按时间间隔自动执行分区维护任务。pg_cron则通过编写自定义脚本并定期执行来检查并创建分区，虽然对性能影响较小，但需要手动编写脚本和设置定时任务。每种方法各有优劣，用户可根据具体需求选择合适方
pgsql 表分区
04-02
另一种实现方式是使用继承表特性来手动构建分区方案。这种方法更加灵活，允许开发者自定义复杂的业务逻辑，但需要额外配置触发器或规则以完成写入重定向操作。 下面是一个简单的例子展示如何设置继承分区及其对应的...
PostgreSQL表分区与自动管理脚本实践
根据提供的脚本名称如“使用动态SQL-实现自动按时间创建表分区.SQL”可以推断，该项目很可能采用的是声明式分区方案，因为它更易于管理和维护，支持RANGE、LIST、HASH等多种分区类型。对于按日分区的场景，通常选择...
postgresql16分区表解析
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
10-11 939
PostgreSQL 16 引入了对分区表的多项改进，增强了其性能和可用性。本文介绍PostgreSQL 16 中分区表功能，包括基本概念、创建方法、管理技巧以及一些最佳实践。
Python-实现轻松创建PostgreSQL的分区表DDL
08-10
实现轻松创建PostgreSQL的分区表DDL
postgresql 10.1 分区表之 list 分区
weixin_30702413的博客
12-25 710
查看数据库版本 select version(); PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit list 分区 创建分区主表 drop table tmp_par_list create...
Postgresql数据库中分区表的分类和创建
weixin_58595285的博客
11-08 2631
分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。分区表在物理上是分开存储的，但在逻辑上仍作为一个整体呈现给用户。这一特性特别适用于处理大量数据的场景，旨在提高查询性能、管理和维护大数据集的效率。
postgresql 创建分区表 以及拆分分区表（修改分区）
热门推荐
yang_z_1的博客
06-09 1万+
postgresql数据库 创建分区表 以及拆分分区表（修改分区） 前言 在日常维护啊数据库的过程中，往往发现一部分表由于日积月累的原因，表内的的数据量越来越大，会影响我们的查询效率，特别是往往只查询部分数据时（比如时间范围）查询会相当慢，那有没有解决办法吗？ 办法肯定有的，那就不得不说我们今天的主角了：分区表 那什么是分区表?下面请听我一一讲解： 一 什么是分区表？ 分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。 ...
Postgresql-11 根据多字段创建分区表
langyan122的专栏
03-02 4752
1、表分区的意义 随着系统的使用，业务表的数据会变得越来越庞大，当达到一定程度时，会严重影响系统性能，将表分区，可以很好的解决这个问题。分区表的优点包括： （1）大大提高查询性能；对表分区后，查询时会根据相应的查询条件直接对子表进行查询。 （2）插入、更新操作的性能更高；插入时，只对子表进行顺序扫描，比扫描整张表高效的多。 （3）批量删除时，可以效率更高；对于不需要的数据，可以直接Drop掉整张子表。 （4）对于一些使用频率比较低的数据，可以将对应的子表迁移到成本更低的存储介质上面，降低成本。
Postgresql建分区表步骤sql
cainiaobulan的博客
09-29 3704
建分区表步骤
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值