PostgreSQL 11 新特性解读: 分区表增加哈希分区

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介:

PostgreSQL 11 的一个重量级新特性为分区表得到较大增强,例如支持哈希分区(HASH)表,因此 PostgreSQL 支持范围分区(RANGE)、列表分区(LIST)、>哈希分区(HASH)三种分区方式,本文简单演示下哈希分区表。

Hash Partitioning

The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.

Hash分区表的分区定义包含两个属性,如下:

  • modulus: 指Hash分区个数。
  • remainder: 指Hash分区键取模余。

创建分区表语法

CREATE TABLE table_name (  ...  )
[ PARTITION BY { RANGE | LIST | HASH }  (  { column_name |  ( expression )  }
 CREATE TABLE table_name
PARTITION OF parent_table [  (
)  ] FOR VALUES partition_bound_spec

创建数据生成函数

为了便于生成测试数据,创建以下两个函数用来随机生成指定长度的字符串,创建 random_range(int4, int4) 函数如下:

CREATE OR REPLACE FUNCTION random_range(int4, int4)
RETURNS int4
LANGUAGE SQL
AS 
$$

    SELECT ($1 + FLOOR(($2 - $1 + 1) * random() ))::int4;

$$
;

接着创建random_text_simple(length int4)函数,此函数会调用random_range(int4, int4)函数。

CREATE OR REPLACE FUNCTION random_text_simple(length int4)
RETURNS text
LANGUAGE PLPGSQL
AS 
$$

DECLARE
    possible_chars text := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    output text := '';
    i int4;
    pos int4;
BEGIN

    FOR i IN 1..length LOOP
        pos := random_range(1, length(possible_chars));
        output := output || substr(possible_chars, pos, 1);
    END LOOP;

    RETURN output;
END;

$$
;

random_text_simple(length int4)函数可以随机生成指定长度字符串,如下随机生成含三位字符的字符串。

mydb=> SELECT random_text_simple(3);
 random_text_simple 
--------------------
 LL9
(1 row)

随机生成含六位字符的字符串,如下所示:

mydb=> SELECT random_text_simple(6);
 random_text_simple 
--------------------
 B81BPW
(1 row)

后面会用到这个函数生成测试数据。

创建哈希分区父表

CREATE TABLE student (
 stuname text ,
 ctime   timestamp(6) without time zone
) PARTITION BY HASH(stuname);

创建索引

CREATE INDEX idx_stuendt_stuname on student using btree(stuname);

创建子表

CREATE TABLE student_p0 PARTITION OF student FOR VALUES WITH(MODULUS 4, REMAINDER 0);
CREATE TABLE student_p1 PARTITION OF student FOR VALUES WITH(MODULUS 4, REMAINDER 1);
CREATE TABLE student_p2 PARTITION OF student FOR VALUES WITH(MODULUS 4, REMAINDER 2);
CREATE TABLE student_p3 PARTITION OF student FOR VALUES WITH(MODULUS 4, REMAINDER 3);

查看分区表定义

francs=> \d+ student
                                              Table "francs.student"
 Column  |              Type              | Collation | Nullable | Default | Storage  | Stats target | Description 
---------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
 stuname | text                           |           |          |         | extended |              | 
 ctime   | timestamp(6) without time zone |           |          |         | plain    |              | 
Partition key: HASH (stuname)
Indexes:
    "idx_stuendt_stuname" btree (stuname)
Partitions: student_p0 FOR VALUES WITH (modulus 4, remainder 0),
            student_p1 FOR VALUES WITH (modulus 4, remainder 1),
            student_p2 FOR VALUES WITH (modulus 4, remainder 2),
            student_p3 FOR VALUES WITH (modulus 4, remainder 3)

从以上看出表 student 和它的四个分区。

插入测试数据

使用之前创建的函数 random_text_simple() 生成100万测试数据,如下。

INSERT INTO student(stuname,ctime) SELECT random_text_simple(6),clock_timestamp() FROM generate_series(1,1000000);            

查看分区表数据

表数据如下

francs=> SELECT * FROM student LIMIT 3;
 stuname |        ctime        
---------+---------------------
 4JJOPN  | 2018-09-20 10:45:06
 NHQONC  | 2018-09-20 10:45:06
 8V5BGH  | 2018-09-20 10:45:06
(3 rows)

统计分区数据量

francs=> SELECT tableoid::regclass,count(*) from student group by 1 order by 1;
  tableoid  | count  
------------+--------
 student_p0 | 250510
 student_p1 | 249448
 student_p2 | 249620
 student_p3 | 250422
(4 rows)

可见数据均匀分布到了四个分区。

根据分区键查询

francs=> EXPLAIN ANALYZE SELECT * FROM student WHERE stuname='3LXBEV';
                                                                QUERY PLAN                                                          
      
------------------------------------------------------------------------------------------------------------------------------------

 Append  (cost=0.42..8.44 rows=1 width=15) (actual time=0.017..0.018 rows=1 loops=1)
   ->  Index Scan using student_p3_stuname_idx on student_p3  (cost=0.42..8.44 rows=1 width=15) (actual time=0.017..0.017 rows=1 loops=1)
         Index Cond: (stuname = '3LXBEV'::text)
 Planning Time: 0.198 ms
 Execution Time: 0.042 ms
(5 rows)

根据分区键stuname查询仅扫描分区 student_p3,并走了索引。

根据非分区键查询

francs=> EXPLAIN ANALYZE SELECT * FROM student WHERE ctime='2018-09-20 10:53:55.48392';
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..13761.36 rows=4 width=15) (actual time=37.891..39.183 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..12760.96 rows=4 width=15) (actual time=23.753..35.006 rows=0 loops=3)
         ->  Parallel Seq Scan on student_p0  (cost=0.00..3196.99 rows=1 width=15) (actual time=0.014..28.550 rows=1 loops=1)
               Filter: (ctime = '2018-09-20 10:53:55.48392'::timestamp without time zone)
               Rows Removed by Filter: 250509
         ->  Parallel Seq Scan on student_p3  (cost=0.00..3195.34 rows=1 width=15) (actual time=29.543..29.543 rows=0 loops=1)
               Filter: (ctime = '2018-09-20 10:53:55.48392'::timestamp without time zone)
               Rows Removed by Filter: 250422
         ->  Parallel Seq Scan on student_p2  (cost=0.00..3185.44 rows=1 width=15) (actual time=8.260..8.260 rows=0 loops=3)
               Filter: (ctime = '2018-09-20 10:53:55.48392'::timestamp without time zone)
               Rows Removed by Filter: 83207
         ->  Parallel Seq Scan on student_p1  (cost=0.00..3183.18 rows=1 width=15) (actual time=22.135..22.135 rows=0 loops=1)
               Filter: (ctime = '2018-09-20 10:53:55.48392'::timestamp without time zone)
               Rows Removed by Filter: 249448
 Planning Time: 0.183 ms
 Execution Time: 39.219 ms
(18 rows)

根据非分区键ctime查询扫描了分区表所有分区。

总结

本文演示了 PostgreSQL 哈希分区表的创建、测试数据的生成导入和查询计划,后面博客演示分区表增强的其它方面。

参考

新书推荐

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html
_5_PostgreSQL_

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
存储 关系型数据库 数据库
深入了解 PostgreSQL:功能、特性和部署
PostgreSQL,通常简称为Postgres,是一款强大且开源的关系型数据库管理系统(RDBMS),它在数据存储和处理方面提供了广泛的功能和灵活性。本文将详细介绍 PostgreSQL 的功能、特性以及如何部署和使用它。
675 1
深入了解 PostgreSQL:功能、特性和部署
|
5月前
|
关系型数据库 MySQL 测试技术
深入探索MySQL 8:隐藏索引与降序索引的新特性
深入探索MySQL 8:隐藏索引与降序索引的新特性
|
5月前
|
关系型数据库 MySQL 数据库
MySQL 8.0 新特性之不可见主键
【6月更文挑战第9天】MySQL 8.0 引入了不可见主键特性,提供更灵活的数据库管理方式。不可见主键能减少业务逻辑干扰,提高数据安全性和隐私,同时在某些场景下更适用。示例展示了如何创建和使用不可见主键,但需要注意它可能带来的理解和调试难题。此特性增加了设计和管理数据库的选项,适用于对数据隐私有高要求的场景。随着技术发展,不断学习和探索新特性将提升数据库性能和功能。
79 9
|
5月前
|
SQL JSON 关系型数据库
MySQL 8.0新特性?
【6月更文挑战第14天】MySQL 8.0新特性?
50 1
|
5月前
|
SQL 关系型数据库 MySQL
|
SQL 监控 关系型数据库
PostgreSQL普通表转换成分区表
如何使用pg_rewrite扩展将普遍表转换成分区表
|
6月前
|
关系型数据库 数据库 PostgreSQL
|
关系型数据库 大数据 PostgreSQL
PostgreSQL16-新特性-并行聚合
PostgreSQL16-新特性-并行聚合
142 0
|
关系型数据库 MySQL Java
C++实战-基于c++11新特性的mysql连接池
C++实战-基于c++11新特性的mysql连接池
82 0
|
存储 SQL JSON
MySQL学习---17、MySQL8其它新特性
MySQL学习---17、MySQL8其它新特性