【postgresql】pg的分区表-范围分区

最新推荐文章于 2025-04-24 23:18:57 发布
最新推荐文章于 2025-04-24 23:18:57 发布
阅读量1.7k 收藏 2
点赞数 4
分类专栏： 数据库运维-PostgreSQL 文章标签： postgresql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_43346403/article/details/141747257
版权

1.pg的分区表 

包含三种分区表 
PostgreSQL内置支持3种方式的分区。
范围分区(range) 
表被划分为由键列或列集定义的"范围"，分配给不同分区的值的范围之间没有重叠。
例如:可以按日期范围或特定业务对象的标识符范围，来进行分区。

列表分区(List)
通过显式列出哪些键值出现在每个分区中来对表进行分区。

哈希分区(hash)
PostgreSQL 11开始提供HASH策略
通过为每个分区指定模数和余数来对表进行分区。
每个分区将保存行，分区键的哈希值除以指定的模数将产生指定的余数。

2.创建分区表的方法 

1)创建父表--------指定分区键，分区策略(RANGE,LIST,HASH(PG11.X才提供HASH策略))
2)创建分区表------指定父表,分区键范围(分区键范围重叠之后会直接报错)
3)在分区上创建索引--通常分区键上的索引是必须的。
4)导入、写入数据

3.创建分区表 

(1)创建父表 
create table sspudb_r1
(
id numeric(20,0) NOT NULL,
name character varying(20) NOT NULL,
age numeric(10,0) NOT NULL,
sex character varying(10) NOT NULL,
cardid numeric(30,0) NOT NULL,
joindate timestamp without time zone NOT NULL,
region character varying(12) NOT NULL,
tel character varying(12) NOT NULL,
email character varying(30) NOT NULL,
recommend character varying(10),
identifier character varying(100),
primary key (id,joindate)
)
PARTITION BY RANGE(joindate);

(2)创建子分区 
create table sspudb_r1_phis  partition OF sspudb_r1 for values from (MINVALUE) TO ('2010-01-01 00:00:00');
create table sspudb_r1_p2010 partition of sspudb_r1 for values from ('2010-01-01 00:00:00') to ('2011-01-01 00:00:00');
create table sspudb_r1_p2011 partition of sspudb_r1 for values from ('2011-01-01 00:00:00') to ('2012-01-01 00:00:00');
create table sspudb_r1_p2012 partition of sspudb_r1 for values from ('2012-01-01 00:00:00') to ('2013-01-01 00:00:00');
create table sspudb_r1_p2013 partition of sspudb_r1 for values from ('2013-01-01 00:00:00') to ('2014-01-01 00:00:00');
create table sspudb_r1_p2014 partition of sspudb_r1 for values from ('2014-01-01 00:00:00') to ('2015-01-01 00:00:00');
create table sspudb_r1_p2015 partition of sspudb_r1 for values from ('2015-01-01 00:00:00') to ('2016-01-01 00:00:00');
create table sspudb_r1_p2016 partition of sspudb_r1 for values from ('2016-01-01 00:00:00') to ('2017-01-01 00:00:00');
create table sspudb_r1_p2017 partition of sspudb_r1 for values from ('2017-01-01 00:00:00') to ('2018-01-01 00:00:00');
create table sspudb_r1_p2018 partition of sspudb_r1 for values from ('2018-01-01 00:00:00') to ('2019-01-01 00:00:00');
create table sspudb_r1_p2019 partition of sspudb_r1 for values from ('2019-01-01 00:00:00') to ('2020-01-01 00:00:00');
create table sspudb_r1_p2020 partition of sspudb_r1 for values from ('2020-01-01 00:00:00') to ('2021-01-01 00:00:00');

(3)检查新创建的分区表 
sspudb=> \d+ sspudb_r1
                                              Partitioned table "public.sspudb_r1"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Compression | St
ats target | Description 
------------+-----------------------------+-----------+----------+---------+----------+-------------+---
-----------+-------------
 id         | numeric(20,0)               |           | not null |         | main     |             |   
           | 
 name       | character varying(20)       |           | not null |         | extended |             |   
           | 
 age        | numeric(10,0)               |           | not null |         | main     |             |   
           | 
 sex        | character varying(10)       |           | not null |         | extended |             |   
           | 
 cardid     | numeric(30,0)               |           | not null |         | main     |             |   
           | 
 joindate   | timestamp without time zone |           | not null |         | plain    |             |   
           | 
 region     | character varying(12)       |           | not null |         | extended |             |   
           | 
 tel        | character varying(12)       |           | not null |         | extended |             |   
           | 
 email      | character varying(30)       |           | not null |         | extended |             |   
           | 
 recommend  | character varying(10)       |           |          |         | extended |             |   
           | 
 identifier | character varying(100)      |           |          |         | extended |             |   
           | 
Partition key: RANGE (joindate)
Indexes:
    "sspudb_r1_pkey" PRIMARY KEY, btree (id, joindate)
Partitions: sspudb_r1_p2010 FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2011-01-01 00:00:00'),
            sspudb_r1_p2011 FOR VALUES FROM ('2011-01-01 00:00:00') TO ('2012-01-01 00:00:00'),
            sspudb_r1_p2012 FOR VALUES FROM ('2012-01-01 00:00:00') TO ('2013-01-01 00:00:00'),
            sspudb_r1_p2013 FOR VALUES FROM ('2013-01-01 00:00:00') TO ('2014-01-01 00:00:00'),
            sspudb_r1_p2014 FOR VALUES FROM ('2014-01-01 00:00:00') TO ('2015-01-01 00:00:00'),
            sspudb_r1_p2015 FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00'),
            sspudb_r1_p2016 FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2017-01-01 00:00:00'),
            sspudb_r1_p2017 FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00'),
            sspudb_r1_p2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'),
            sspudb_r1_p2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00'),
            sspudb_r1_p2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00'),
            sspudb_r1_phis FOR VALUES FROM (MINVALUE) TO ('2010-01-01 00:00:00')


(4)创建全局索引 
CREATE INDEX idx_sspudb_r1_joindate ON sspudb_r1 USING BTREE(joindate);

sspudb=> \d+ sspudb_r1
...............................
Partition key: RANGE (joindate)
Indexes:
    "sspudb_r1_pkey" PRIMARY KEY, btree (id, joindate)
    "idx_sspudb_r1_joindate" btree (joindate)
Partitions: sspudb_r1_p2010 FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2011-01-01 00:00:00'),
            sspudb_r1_p2011 FOR VALUES FROM ('2011-01-01 00:00:00') TO ('2012-01-01 00:00:00'),
            sspudb_r1_p2012 FOR VALUES FROM ('2012-01-01 00:00:00') TO ('2013-01-01 00:00:00'),
            sspudb_r1_p2013 FOR VALUES FROM ('2013-01-01 00:00:00') TO ('2014-01-01 00:00:00'),
            sspudb_r1_p2014 FOR VALUES FROM ('2014-01-01 00:00:00') TO ('2015-01-01 00:00:00'),
            sspudb_r1_p2015 FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00'),
            sspudb_r1_p2016 FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2017-01-01 00:00:00'),
            sspudb_r1_p2017 FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00'),
            sspudb_r1_p2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'),
            sspudb_r1_p2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00'),
            sspudb_r1_p2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00'),
            sspudb_r1_phis FOR VALUES FROM (MINVALUE) TO ('2010-01-01 00:00:00')


(5)检查分区情况 
sspudb=> select
sspudb-> nmsp_parent.nspname as parent_schema,
sspudb-> parent.relname as parent,
sspudb-> nmsp_child.nspname as child_schema,
sspudb-> child.relname as child_schema
sspudb-> from pg_inherits join pg_class parent on pg_inherits.inhparent = parent.oid 
sspudb-> join pg_class child on pg_inherits.inhrelid = child.oid 
sspudb-> join pg_namespace nmsp_parent on nmsp_parent.oid = parent.relnamespace 
sspudb-> join pg_namespace nmsp_child on nmsp_child.oid = child.relnamespace
sspudb-> where parent.relname = 'sspudb_r1';
 parent_schema |  parent   | child_schema |  child_schema   
---------------+-----------+--------------+-----------------
 public        | sspudb_r1 | public       | sspudb_r1_phis
 public        | sspudb_r1 | public       | sspudb_r1_p2010
 public        | sspudb_r1 | public       | sspudb_r1_p2011
 public        | sspudb_r1 | public       | sspudb_r1_p2012
 public        | sspudb_r1 | public       | sspudb_r1_p2013
 public        | sspudb_r1 | public       | sspudb_r1_p2014
 public        | sspudb_r1 | public       | sspudb_r1_p2015
 public        | sspudb_r1 | public       | sspudb_r1_p2016
 public        | sspudb_r1 | public       | sspudb_r1_p2017
 public        | sspudb_r1 | public       | sspudb_r1_p2018
 public        | sspudb_r1 | public       | sspudb_r1_p2019
 public        | sspudb_r1 | public       | sspudb_r1_p2020
(12 rows)

--查看索引 
select * from pg_indexes where tablename like 'sspudb_r1%';
sspudb=> select * from pg_indexes where tablename like 'sspudb_r1%';
 schemaname |    tablename    |          indexname           | tablespace |                                           indexdef        
                                    
------------+-----------------+------------------------------+------------+-----------------------------------------------------------
------------------------------------
 public     | sspudb_r1       | sspudb_r1_pkey               |            | CREATE UNIQUE INDEX sspudb_r1_pkey ON ONLY public.sspudb_r1 USING btree (id, joindate)
 public     | sspudb_r1_phis  | sspudb_r1_phis_pkey          |            | CREATE UNIQUE INDEX sspudb_r1_phis_pkey ON public.sspudb_r1_phis USING btree (id, joindate)
 public     | sspudb_r1_p2010 | sspudb_r1_p2010_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2010_pkey ON public.sspudb_r1_p2010 USING btree (id, joindate)
 public     | sspudb_r1_p2011 | sspudb_r1_p2011_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2011_pkey ON public.sspudb_r1_p2011 USING btree (id, joindate)
 public     | sspudb_r1_p2012 | sspudb_r1_p2012_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2012_pkey ON public.sspudb_r1_p2012 USING btree (id, joindate)
 public     | sspudb_r1_p2013 | sspudb_r1_p2013_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2013_pkey ON public.sspudb_r1_p2013 USING btree (id, joindate)
 public     | sspudb_r1_p2014 | sspudb_r1_p2014_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2014_pkey ON public.sspudb_r1_p2014 USING btree (id, joindate)
 public     | sspudb_r1_p2015 | sspudb_r1_p2015_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2015_pkey ON public.sspudb_r1_p2015 USING btree (id, joindate)
 public     | sspudb_r1_p2016 | sspudb_r1_p2016_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2016_pkey ON public.sspudb_r1_p2016 USING btree (id, joindate)
 public     | sspudb_r1_p2017 | sspudb_r1_p2017_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2017_pkey ON public.sspudb_r1_p2017 USING btree (id, joindate)
 public     | sspudb_r1_p2018 | sspudb_r1_p2018_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2018_pkey ON public.sspudb_r1_p2018 USING btree (id, joindate)
 public     | sspudb_r1_p2019 | sspudb_r1_p2019_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2019_pkey ON public.sspudb_r1_p2019 USING btree (id, joindate)
 public     | sspudb_r1_p2020 | sspudb_r1_p2020_pkey         |            | CREATE UNIQUE INDEX sspudb_r1_p2020_pkey ON public.sspudb_r1_p2020 USING btree (id, joindate)
 public     | sspudb_r1       | idx_sspudb_r1_joindate       |            | CREATE INDEX idx_sspudb_r1_joindate ON ONLY public.sspudb_r1 USING btree (joindate)
 public     | sspudb_r1_phis  | sspudb_r1_phis_joindate_idx  |            | CREATE INDEX sspudb_r1_phis_joindate_idx ON public.sspudb_r1_phis USING btree (joindate)
 public     | sspudb_r1_p2010 | sspudb_r1_p2010_joindate_idx |            | CREATE INDEX sspudb_r1_p2010_joindate_idx ON public.sspudb_r1_p2010 USING btree (joindate)
 public     | sspudb_r1_p2011 | sspudb_r1_p2011_joindate_idx |            | CREATE INDEX sspudb_r1_p2011_joindate_idx ON public.sspudb_r1_p2011 USING btree (joindate)
 public     | sspudb_r1_p2012 | sspudb_r1_p2012_joindate_idx |            | CREATE INDEX sspudb_r1_p2012_joindate_idx ON public.sspudb_r1_p2012 USING btree (joindate)
 public     | sspudb_r1_p2013 | sspudb_r1_p2013_joindate_idx |            | CREATE INDEX sspudb_r1_p2013_joindate_idx ON public.sspudb_r1_p2013 USING btree (joindate)
 public     | sspudb_r1_p2014 | sspudb_r1_p2014_joindate_idx |            | CREATE INDEX sspudb_r1_p2014_joindate_idx ON public.sspudb_r1_p2014 USING btree (joindate)
 public     | sspudb_r1_p2015 | sspudb_r1_p2015_joindate_idx |            | CREATE INDEX sspudb_r1_p2015_joindate_idx ON public.sspudb_r1_p2015 USING btree (joindate)
 public     | sspudb_r1_p2016 | sspudb_r1_p2016_joindate_idx |            | CREATE INDEX sspudb_r1_p2016_joindate_idx ON public.sspudb_r1_p2016 USING btree (joindate)
 public     | sspudb_r1_p2017 | sspudb_r1_p2017_joindate_idx |            | CREATE INDEX sspudb_r1_p2017_joindate_idx ON public.sspudb_r1_p2017 USING btree (joindate)
 public     | sspudb_r1_p2018 | sspudb_r1_p2018_joindate_idx |            | CREATE INDEX sspudb_r1_p2018_joindate_idx ON public.sspudb_r1_p2018 USING btree (joindate)
 public     | sspudb_r1_p2019 | sspudb_r1_p2019_joindate_idx |            | CREATE INDEX sspudb_r1_p2019_joindate_idx ON public.sspudb_r1_p2019 USING btree (joindate)
 public     | sspudb_r1_p2020 | sspudb_r1_p2020_joindate_idx |            | CREATE INDEX sspudb_r1_p2020_joindate_idx ON public.sspudb_r1_p2020 USING btree (joindate)
(26 rows)

(6)写入数据 


insert into sspudb_r1 values
(1,'xsq1',18,'male',622722199009121121,'2010-10-01 10:10:10','北京',13651254654,'sspudb@163.com','xsq','first_people'),
(2,'xsq3',18,'male',622722199003121121,'2011-10-01 10:10:10','北京1',13641254654,'sspudb1@163.com','xsq1','first_people1'),
(3,'xsq3',19,'male',622722199033121121,'2012-10-01 10:10:10','北京3',13641254634,'sspudb3@163.com','xsq3','first_people1'),
(4,'xsq3',14,'male',622722199403121121,'2014-10-01 10:10:10','北京4',13641254654,'sspudb4@163.com','xsq1','first_people1'),
(5,'xsq3',15,'male',622722199503121121,'2015-10-01 10:10:10','北京5',13645254654,'sspudb1@163.com','xsq1','first_people1'),
(6,'xsq6',16,'male',622722199603121121,'2016-10-01 10:10:10','北京6',13641256654,'sspudb6@163.com','xsq6','first_people1'),
(7,'xsq3',17,'male',622722199703121121,'2017-10-01 10:10:10','北京7',13647254654,'sspudb7@163.com','xsq1','first_people1'),
(8,'xsq8',18,'male',622722199803121121,'2018-10-01 10:10:10','北京8',13648254654,'sspudb8@163.com','xsq1','first_people1');

(7)检查分区表的数据 
SELECT v.tableoid::regclass,count(v.*) FROM sspudb_r1 v GROUP BY v.tableoid;
sspudb=> SELECT v.tableoid::regclass,count(v.*) FROM sspudb_r1 v GROUP BY v.tableoid;
    tableoid     | count 
-----------------+-------
 sspudb_r1_p2016 |     1
 sspudb_r1_p2011 |     1
 sspudb_r1_p2018 |     1
 sspudb_r1_p2014 |     1
 sspudb_r1_p2010 |     1
 sspudb_r1_p2017 |     1
 sspudb_r1_p2015 |     1
 sspudb_r1_p2012 |     1
(8 rows)

4.总结 

Pg的分区表功能与其他数据库有所不同，其他数据库都是直接创建主表就可以完成分区表的创建，而PG却多了创建子表的步骤。主表和子表分开创建。

PostgreSQL高效分区表实现-pg_pathman
计算机毕业论文源码，学生个人网页制作html源码。贴近用户做网络推广和互联网优化。
06-18 2万+
PG分区在数据应用中十分广泛。关于PostgreSQL:1.什么是PostgreSql:PostgreSQL是一个功能强大的开源对象关系型目前PostgreSQL社区版本的分区表功能比较弱，需要通过继承和触发器或RULE来实现分区表的功能，由于查询和更新涉及约束的检查、插入则涉及触发器或规则重写，导致分区功能性能较差。 商业版本EDB，以及数据仓库Greenplum都有比较好的分区支持。 去年GP开源后，阿里云RDS PostgreSQL的小伙伴将Greenplum的分区表功能po...
【PostgreSQL】PostgreSQL查看分区表的分区条件
tttzzzqqq2018的博客
01-19 1467
分区边界（relpartbound）列包含了存储分区表的分区边界信息。这通常是一个字节序列，你可能需要使用特定的函数（如 pg_get_expr()）来解析它，以查看实际的分区条件。请注意，分区条件的确切表示方式可能会因 PostgreSQL 版本而有所不同。在查看分区条件之前，建议先了解你的 PostgreSQL 版本，并查阅相应版本的文档。上述查询中，pg_get_expr(relpartbound, relid) 用于获取分区边界的文本表示形式。
PostgreSQL分区表,实战细节满满
IT邦德
10-06 3581
本文详细的阐述了PostgreSQL分区表的细节，分享给大家
postgresql----表分区
weixin_33949359的博客
06-22 452
--下面的描述不记得在哪里抄来的了？！ 表分区就是把逻辑上一个大表分割成物理上的多个小块，表分区可提供如下若干好处： 1.某些类型的查询性能可以得到极大提升。 2.更新的性能可以得到提升，因为表的每块索引要比整个数据集上的索引要小，如果索引不能全部放在内存里，那么在索引上的读写都会产生磁盘访问。 3.批量删除可以用简单的删除某个分区 4.将很少使用的数据移动到便宜的慢一些的存储介质上。 ...
PostgreSQL 分区表——范围分区SQL实践
最新发布
qq_41725131的博客
04-24 1229
文本基于PostgreSQL原生分区能力，以创建时间作为分区键进行水平分表验证，要想发挥分区表的优势，多需要带上`分区键`进行操作，例如上文的删除以及更新操作中，带上分区键比未带分区键操作，性能存在10倍左右差异，但是进行`大批量`的操作，分区表的优势`不明显`，甚至有些场景还会非常慢。
postgresql 10.1 分区表之 range 分区
一名数据库爱好者的专栏
12-15 5424
查看数据库版本 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 create table语法 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP }...
PostgreSQL分区表
liuzhilong的博客
08-27 6257
PostgreSQL分区表的特性和维护实践
postgresql的分区表
归来仍少年
07-17 4514
postgresql的分区表
PostgreSQL表分区和子表及删除所有的数据库表.zip
02-19
PostgreSQL表分区和子表及删除所有的数据库表 最近需求要求统计DNS近7天每天的解析情况。数据量相对大，所以我这边对表进行分区。 对每天的数据进行分区存储。主表只存储近7天的数据，7天之前的数据删掉。所以我...
Python-实现轻松创建PostgreSQL的分区表DDL
08-10
本文将探讨如何使用Python来实现PostgreSQL的分区表DDL（数据定义语言）的自动化创建。 首先，让我们了解分区表的基本概念。分区表通过将数据根据预定义的规则分布到不同的子表中，提高了查询效率，尤其对于大数据...
postgresql中分区表相关操作
zkkkkkkkkkkkkk的博客
04-08 3924
二、创建分区表 三、新增分区 3.1、新增分区 3.2、批量新增分区 四、删除分区 4.1、删除分区 4.2、批量删除分区 五、查看分区表详情
PostgreSQL之分区表(partitioning)
09-09
通过合理的设计，可以将选择一定的规则，将大表切分多个不重不漏的子表，这就是传说中的partitioning。比如，我们可以按时间切分，每天一张子表，比如我们可以按照某其他字段分割，总之了就是化整为零，提高查询的效能
pg表分区
liangbo
04-26 1966
一旦分区存在，那样做就会导致错误，因为当分区存在时是不支持仅在分区表上增加或删除约束的。c.在分区表的键列上创建一个索引，还有其他需要的索引（键索引并不是必需的，但是大部分场景中它都能很有帮助）。这会自动在每个分区上创建一个索引，并且后来创建或者附着的任何分区也将会包含索引。不过，可以把一个包含数据的常规表或者分区表作为分区加入到另一个分区表，或者从分区表中移走一个分区并且把它变成一个独立的表。2.创建分区子表（约束在子表创建,无法在主表创建跨越所有分区的约束）3.索引创建(主表创建，分区也生效)
postgresql分区表
guoxianqi2012的专栏
03-23 2548
分区 From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community Jump to: navigation, search [编辑] 分区 目录 [隐藏] 1 分区 1.1 概述 1.2 实现分区 1.3 分区和约束排除   PostgreSQL 支持基本的表分区功
PG分区表
Why Do You Run
09-03 1140
PG-分区 建分区表语句： PG版本为： PostgreSQL 11.8 声明式分区表（推荐）： 创建表时并 指定分区字段 为logdate CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate); 创建
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值