PostgreSQL10 新特性（1）： 分区表特性

最新推荐文章于 2024-01-05 16:41:08 发布
最新推荐文章于 2024-01-05 16:41:08 发布
阅读量289 收藏 2
点赞数
文章标签： 数据库 python
原文链接：https://my.oschina.net/tianbing/blog/1559923
版权

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

概述

    分区表特性是PG10新加的一个比较重要的特性，也是众多PGer翘首以盼的一个功能，相比之前利用“继承表+约束+触发器/规则”实现的分区表，PG10分区表在分区管理上和数据写入上都带来了很大优势。

 

特性介绍

    事实上，PG10的分区特性也是在内置继承表的基础上实现的，所以创建的分区实质上也是普通的表结构。目前PG10支持范围分区和列表分区，哈希分区还不支持（据PG社区人员说，会在PG11加上该功能）。

1.  范围分区

        PG10的分区表在建表语法上，主表和分区是单独创建的（区别于Oracle）。下面的列表分区也是一样。

创建主表语法：
CREATE TABLE 表名 ( [{ 列名称 数据_类型} [, ... ] ] ) 
PARTITION BY RANGE ( [{ 列名称 } [, ...] ] );

范围分区的KEY值可由多个字段组成（最多32个字段）。

创建分区语法：
CREATE TABLE 表名 PARTITION OF 主表 FOR VALUES 
FROM{ ( 表达式 [, ...] ) | MINVALUE  } [, ...] 
TO { ( 表达式 [, ...] ) | MAXVALUE } [, ...] 
[ TABLESPACE 表空间名 ];
参数说明:
 // FROM ... TO 表示分区的起始值和结束值。
 // MINVALUE / MAXVALUE 表示无限小值和无限大值。
 // 默认FROM后面的值是包括值分区的约束内，TO后面的值不包括。

       示例：

highgo=# create table test(n int) partition by range(n);
CREATE TABLE
highgo=# create table test_1 partition of test for values from (MINVALUE) to (10);
CREATE TABLE
highgo=# create table test_2 partition of test for values from (10) to (100);
CREATE TABLE
highgo=# create table test_3 partition of test for values from (100) to (1000);
CREATE TABLE
highgo=# create table test_4 partition of test for values from (1000) to (10000);
CREATE TABLE
highgo=# \d+ test
                           数据表 "public.test"
 栏位 |  类型   | Collation | Nullable | Default | 存储  | 统计目标 | 描述 
------+---------+-----------+----------+---------+-------+----------+------
 n    | integer |           |          |         | plain |          | 
Partition key: RANGE (n)
Partitions: test_1 FOR VALUES FROM (MINVALUE) TO (10),
            test_2 FOR VALUES FROM (10) TO (100),
            test_3 FOR VALUES FROM (100) TO (1000),
            test_4 FOR VALUES FROM (1000) TO (10000)

highgo=# \d+ test_2
                          数据表 "public.test_2"
 栏位 |  类型   | Collation | Nullable | Default | 存储  | 统计目标 | 描述 
------+---------+-----------+----------+---------+-------+----------+------
 n    | integer |           |          |         | plain |          | 
Partition of: test FOR VALUES FROM (10) TO (100)
Partition constraint: ((n IS NOT NULL) AND (n >= 10) AND (n < 100))

highgo=# insert into test select generate_series(0, 9999);
INSERT 0 10000
highgo=# explain analyze select * from test;
                                                  QUERY PLAN                                             
      
---------------------------------------------------------------------------------------------------------
------
 Append  (cost=0.00..248.50 rows=17850 width=4) (actual time=0.018..9.374 rows=10000 loops=1)
   ->  Seq Scan on test_1  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.017..0.022 rows=10 loops=1
)
   ->  Seq Scan on test_2  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.018..0.052 rows=90 loops=1
)
   ->  Seq Scan on test_3  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.039..0.503 rows=900 loops=
1)
   ->  Seq Scan on test_4  (cost=0.00..142.00 rows=10200 width=4) (actual time=0.040..4.167 rows=9000 loo
ps=1)
 Planning time: 0.525 ms
 Execution time: 11.881 ms
(7 行记录)

2. 列表分区

        列表的KEY只支持一个字段。

创建主表语法：
CREATE TABLE 表名 ( [{ 列名称 数据_类型} [, ... ] ] ) 
PARTITION BY LIST( { 列名称 } );

 

创建分区语法：
CREATE TABLE 表名 PARTITION OF 主表 FOR VALUES
IN ( 表达式 [, ...] ) [ TABLESPACE 表空间名 ];

示例：

highgo=# CREATE TABLE sales (product_id int, saleroom int, province text) PARTITION BY LIST(province);
CREATE TABLE
highgo=# CREATE TABLE sales_east PARTITION OF sales FOR VALUES IN ('山东','江苏','上海');
CREATE TABLE
highgo=# CREATE TABLE sales_west PARTITION OF sales FOR VALUES IN ('山西','陕西','四川');
CREATE TABLE
highgo=# CREATE TABLE sales_north PARTITION OF sales FOR VALUES IN ('北京','河北','辽宁');
CREATE TABLE
highgo=# CREATE TABLE sales_south PARTITION OF sales FOR VALUES IN ('广东','福建');
CREATE TABLE
highgo=# \d+ sales
                               数据表 "public.sales"
    栏位    |  类型   | Collation | Nullable | Default |   存储   | 统计目标 | 描述 
------------+---------+-----------+----------+---------+----------+----------+------
 product_id | integer |           |          |         | plain    |          | 
 saleroom   | integer |           |          |         | plain    |          | 
 province   | text    |           |          |         | extended |          | 
Partition key: LIST (province)
Partitions: sales_east FOR VALUES IN ('山东', '江苏', '上海'),
            sales_north FOR VALUES IN ('北京', '河北', '辽宁'),
            sales_south FOR VALUES IN ('广东', '福建'),
            sales_west FOR VALUES IN ('山西', '陕西', '四川')

highgo=# \d+ sales_east 
                             数据表 "public.sales_east"
    栏位    |  类型   | Collation | Nullable | Default |   存储   | 统计目标 | 描述 
------------+---------+-----------+----------+---------+----------+----------+------
 product_id | integer |           |          |         | plain    |          | 
 saleroom   | integer |           |          |         | plain    |          | 
 province   | text    |           |          |         | extended |          | 
Partition of: sales FOR VALUES IN ('山东', '江苏', '上海')
Partition constraint: ((province IS NOT NULL) AND (province = ANY (ARRAY['山东'::text, '江苏'::text, '上海'::text])))

highgo=# insert into sales values (1001, 2345234, '山东');
INSERT 0 1
highgo=# insert into sales values (1002, 23233, '河北');
INSERT 0 1
highgo=# insert into sales values (1001, 4357233, '广东');
INSERT 0 1
highgo=# insert into sales values (1002, 67233, '陕西');
INSERT 0 1
highgo=# explain analyze select * from sales;
                                                   QUERY PLAN                                            
       
---------------------------------------------------------------------------------------------------------
-------
 Append  (cost=0.00..88.00 rows=4800 width=40) (actual time=0.017..0.038 rows=4 loops=1)
   ->  Seq Scan on sales_east  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.016..0.018 rows=1 loo
ps=1)
   ->  Seq Scan on sales_west  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.004..0.005 rows=1 loo
ps=1)
   ->  Seq Scan on sales_north  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.004..0.005 rows=1 lo
ops=1)
   ->  Seq Scan on sales_south  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.005..0.005 rows=1 lo
ops=1)
 Planning time: 0.531 ms
 Execution time: 0.089 ms
(7 行记录)

 

3. 获取系统信息

    a) 系统表

        i. pg_partitioned_table 记录主表信息的系统表

        

        ii. 分区的信息记录在pg_class相关的字段中

       

    b) 分区函数

       i. pg_get_partkeydef (Oid  relid) -- 根据主表OID返回分区类型及KEY

highgo=# select pg_get_partkeydef('test'::regclass);
 pg_get_partkeydef 
-------------------
 RANGE (n)
(1 行记录)

       ii. pg_get_partition_constraintdef (Oid  relid) -- 根据分区OID获取分区约束条件

highgo=# select pg_get_partition_constraintdef('test_1'::regclass);
 pg_get_partition_constraintdef 
--------------------------------
 ((n IS NOT NULL) AND (n < 10))
(1 行记录)

 

4. 其他操作

    a) ATTACH操作

        ATTACH操作是把和主表有相同表结构的主表变成该主表的一个分区。

范围分区：
ALTER TABLE 主表名 ATTACH PARTITION 表名 FOR VALUES
FROM{ ( 表达式 [, ...] ) | MINVALUE  } [, ...] 
TO { ( 表达式 [, ...] ) | MAXVALUE } [, ...];

列表分区：
ALTER TABLE 主表名 ATTACH PARTITION 表名 FOR VALUES
IN ( 表达式 [, ...] );

        示例：

范围分区：
highgo=# create table test_attach(like test);
CREATE TABLE
highgo=# alter table test attach partition test_attach for values from (10000) to (MAXVALUE);
ALTER TABLE

列表分区：
highgo=# CREATE TABLE sales_foreign (like sales) ;
CREATE TABLE
highgo=# ALTER TABLE sales ATTACH PARTITION sales_foreign FOR VALUES IN('美国','日本');
ALTER TABLE

        在对普通表进行ATTACH操作时，如果该普通表中有数据，则表中的数据会进行校验是否符合约束条件。

        b) DETACH 操作

        DETACH操作是把主表的分区变成普通表，是ATTACH的反操作。

i.语法
ALTER TABLE 主表名 DETACH PARTITION 分区名;

        示例：

范围分区：
highgo=# alter table test detach partition test_attach;
ALTER TABLE

列表分区：
highgo=# alter table sales detach partition sales_foreign;
ALTER TABLE

 

5. 分区的约束排除

    约束排除（Constraint exclusive）是一种查询优化技术，在应用在分区特性中，条件查询中可以定位到目标分区进行顺序扫描，以此来提高分区表检索性能，这也是使用分区根本意义所在。所以在分区应用在，约束排除的参数constraint_exclusion一定不要设置为关闭状态。（系统默认constraint_exclusion的值为partition，即只对分区表进行约束排除）

转载于:https://my.oschina.net/tianbing/blog/1559923

weixin_33826268
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL教程(三)：表的继承和分区表详解
09-10
【PostgreSQL 表的继承】 ...总结来说，PostgreSQL的表继承和分区表特性为企业级数据库解决方案提供了强大的工具，使得数据库设计更加灵活，数据管理更为高效。理解和掌握这些概念对于优化数据存储和查询性能至关重要。
PostgreSQL教程(一)：数据表详解
12-16
7. 修改表结构：有时我们需要对已创建的表进行修改，比如添加新字段、更改字段类型或删除字段。在PostgreSQL中，可以使用`ALTER TABLE`语句来实现： - 添加字段： 代码如下: ```sql ALTER TABLE products ADD ...
Postgresql杂谈 10—Postgresql中的分区表
Hyman的博客
07-31 2722
一、关于分区表 表分区是在大数据优化中的一种常见的分表方案，通过将大数据按照一定的规则（最常见的是按照时间）进行分表处理，将逻辑上的一个大表分割成物理上的几块表，插入数据时，数据会自动插入到不同的分区表中，从而实现查询或者其它操作的性能优化。相比于一个大表，分区表具有以下优点： （1）当查询或者更新一个分区的大部分记录时，采用顺序扫描而不是随机扫描，可以获得巨大的性能提升。 （2）使用不频繁的历史数据可以转移到一些低廉的存储介质上，而热数据放到性能较好的存储介质上，可以最大限度的减少成本...
PostgreSQL10：重量级新特性-支持分区表
kwame211的博客
12-29 2183
PostgreSQL10：重量级新特性-支持分区表   PostgreSQL10 一个重量级新特性是支持分区表，在这之前，PostgreSQL不支持内置分区表，若要实现功能，需通过继承的方式实现，详见 PostgreSQL: 分区表应用二(取模分区) 。 PostgreSQL 内置分区表目前仅支持以下两种形式分区 范围分区（Range Partitioning
PostgreSQL V10 分区表特性
HighGO
03-18 538
目录 环境 文档用途 详细信息 环境 系统平台：N/A 版本：10.3 文档用途 PostgreSQL V10 分区表的理解与使用 详细信息 分区表特性是PG10新加的一个很重要的特性。 之前的版本也能实现分区表功能，是根据“继承表+约束+规则或触发器”实现。 相对于之前的分区实现方式，PG10的分区特性有以下优势： 1）管理分区方便 2）数据插入效率高 ...
postgresql 10 新特性
weixin_34273046的博客
02-23 523
三大新特性 "better parallelism" (37%) "logical replication" (32%) "native partitioning" (31%) 后面的百分比代表人民的呼声。。。 原生分区 语法： CREATE TABLE table_name ( ... ) [ PARTITION BY { RANGE | LIS...
15.PG分区表-内置分区表
weixin_40068220的博客
11-24 5751
文章目录前言一、PG分区表-内置分区表1.创建分区表2.使用分区表3.内置分区表探索4.添加分区5.删除分区6.性能测试7.constraint_exclusion参数8.PG14更新分区数据9.内置分区注意事项 前言 一、PG分区表-内置分区表 PostgreSQL10一个重量级新特性是支持内置分区表，目前支持范围分区和列表分区。 1.创建分区表 创建分区表的主要语法包含两部分:创建主表和创建分区。 创建主表: create table table_name (...) [ partition by
PostgreSQL之分区表(partitioning)
12-16
在PostgreSQL数据库系统中，分区表（Partitioning）是一种优化大数据管理与查询性能的重要特性。当数据量达到千万甚至上亿级别时，单个大表的查询效率会显著下降，这是因为数据库需要遍历大量行来寻找目标数据。分区...
PostgreSQL 11-16版本主要特性.pdf
08-08
另外可以添加全局唯一索引，分区表可以强制执行唯一约束。分区裁剪现在要快得多，PostgreSQL 能够智能的处理跨分区关联以及跨分区聚合。 存储过程 PostgreSQL 11 现在支持存储过程创建语法，存储过程可以包含多个...
PostgreSQL平台新特性介绍.pptx
10-14
【PostgreSQL平台新特性介绍】 PostgreSQL是一种强大的开源关系型数据库管理系统，持续不断地进行技术创新，引入新特性以提升性能和易用性。本篇将详细探讨PostgreSQL平台的几个关键新特性，包括SQL执行优化、配置...
postgresql表继承与分区
weixin_45105610的博客
03-04 1649
目标 掌握表继承相关知识。 掌握分区（V10）相关知识。 数据分区 分区将表拆分为多个表，并且通常以一种访问表的应用程序注意不到任何差异的方式完成。 PG V10之前的版本：继承表+约束+规则或触发器。 PG V10特性：分区表特性，管理分区方便， 数据插入效率高。 表继承 例 创建cities父表，capitals子表继承于cities。 postgres=# create table cities(name text,population float,altitude int);  
数据库中分区表的详解，以postgresql数据库为例
weixin_73350116的博客
07-01 1783
postgresql的四种分区表详解
pg 查询分区表创建语句
最新发布
wyp19901226的博客
01-05 559
pg查询分区表创建语句，妥妥原创
gp查看表分区键及分区信息
代元培
09-11 5810
# 查看表 分区信息 分区键 SELECT pg_get_partition_def('core.t_payments'::regclass,true); SELECT pg_get_partition_def('wallet.dwd_wallet_trans'::regclass,true);
pg 10 分区表举例
weixin_33896069的博客
06-27 284
-- partition tableCREATE TABLE users( user_id serial not null, user_name varchar(20), logdate timestamp(0) with time zone not null) PARTITION BY RANGE (logdate);-- table ...
PostgreSQL中比较冷门的特性（一）
HighGO
07-17 2520
PostgreSQL包含大量优异的特性。他们中很大一部分是众所周知的，但也有一些极其好用的特性没有得到广泛的关注。本文主要介绍五个最建议使用的PostgreSQL特性。 一、listen与notify PostgreSQL附带一个简单的发布-订阅通知系统。 可以向正在侦听该主题的所有已连接订户广播特定主题的消息。 消息由Postgres服务器推送到侦听客户端。 不需要轮询，但数据库驱动程序应支持...
postgresql 10.1 分区表之 range 分区
一名数据库爱好者的专栏
12-15 5296
查看数据库版本 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 10分区表详解及性能测试报告
kwame211的博客
01-04 5698
作者简介： 中国比较早的postgresql使用者,2001年就开始使用postgresql,自2003年底至2014年一直担任PGSQL中国社区论坛PostgreSQL的论坛板块版主、管理员，参与Postgresql讨论和发表专题文章7000多贴.拥有15年的erp设计,开发和实施经验,开源mrp系统PostMRP就是我的作品,该应用软件是一套基于Postgresql专业的制造业管理软件系
PostgreSQL 10 新特性解析：分区表与逻辑复制
"这份文档详细介绍了PostgreSQL 10的新特性，包括原生分区表、逻辑复制和并行查询的增强等。文档旨在为PostgreSQL的用户和管理员提供关于新版本改进的深入理解，帮助他们更好地利用这些新功能来优化数据库性能和可靠...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值