首页 > 数据库 >PostgreSQL-分区表介绍

PostgreSQL-分区表介绍

时间:2023-09-14 17:56:52浏览次数:32  
标签:city PostgreSQL 分区 介绍 person 分区表 VALUES test age

一、分区简介

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

表的分区就是将一个逻辑上的大表(主要指数据量大),切分为多个小的物理的分片。

1.分区的优点

1) 在某些情况下,尤其是当表中大多数被频繁访问的行位于单个分区或少量分区中时,查询性能可以得到显着提高。分区替代了索引的前几列,从而减小了索引的大小,并使索引中频繁使用的部分更有可能装入内存。

2) 当查询或更新访问单个分区的很大一部分时,可以通过对该分区进行顺序扫描而不是使用索引和分散在整个表中的随机访问读取,来提高性能。

直接从分区表查询数据比从一个大而全的全量数据表中读取数据效率更高。

3) 如果计划将这种需求计划到分区设计中,则可以通过添加或删除分区来完成批量加载和删除。使用ALTER TABLE DETACH PARTITION或使用DROP TABLE删除单个分区比批量操作要快得多。这些命令还完全避免了由批量DELETE引起的VACUUM开销。

数据维护成本降低。比如:某一部分数据失效,不需要执行命令来更新数据,可以直接解绑指定关系,解除绑定的数据和分区表都依然保留,需要时可以随时恢复绑定。通过 Flyway 等数据脚本管理能够方便的控制数据维护,避免人为直接操作数据。

4) 很少使用的数据可以迁移到更廉价、更迟缓的存储介质上。

一个表只能放在一个物理空间上,使用分区表之后可以将不同的表放置在不同的物理空间上,从而达到冷数据放在廉价的物理机器上,热点数据放置在性能强劲的机器上。

通常只有在表很大的情况下,这些好处才是值得的。表可以从分区中受益的确切时间取决于应用程序,尽管经验法则是表的大小应超过数据库服务器的物理内存。

2.分区的使用限制

1) 无法创建跨所有分区的排除约束。只能单独约束每个叶子分区。

2) 因为PostgreSQL只能在每个分区中单独进行唯一性约束;因此,分区表上的唯一约束必须包括所有分区键列。

3) 如果需要 BEFORE ROW 触发器,则必须定义在单个分区(而不是分区父表)。

4) 不允许在同一分区树中混合临时和永久关系。因此,如果父表是永久性的,则其分区也必须是永久性的;如果父表是临时的,则其分区也必须是临时的。当使用临时关系时,分区树的所有成员必须来自同一会话。

3.分区的三种方式

1) 范围(Range )分区:表被划分为由键列或列集定义的“范围”,分配给不同分区的值的范围之间没有重叠。例如:可以按日期范围或特定业务对象的标识符范围,来进行分区。
2) 列表(List)分区:通过显式列出哪些键值出现在每个分区中来对表进行分区。
3) 哈希(Hash)分区:(自PG11才提供HASH策略)通过为每个分区指定模数和余数来对表进行分区。每个分区将保存行,分区键的哈希值除以指定的模数将产生指定的余数。

 

二、三种分区方式

1.Range范围分区

先创建一张表带有年龄,然后我们根据年龄分段来进行分区,创建表语句如下:

CREATE TABLE test_person_r (

    age int not null,

    city varchar not null

) PARTITION BY RANGE (age);

这个语句已经指定了按age字段来分区了,接着创建分区表:

create table test_person_r1 partition of test_person_r for values from (MINVALUE) to (10);

create table test_person_r2 partition of test_person_r for values from (11) to (20);

create table test_person_r3 partition of test_person_r for values from (21) to (30);

create table test_person_r4 partition of test_person_r for values from (31) to (MAXVALUE);

这里创建了四张分区表,分别对应年龄是0到10岁、11到20岁、21到30岁、30岁以上。

接着我们插入一些数据:

insert into test_person_r(age, city) VALUES (1, 'GZ');

insert into test_person_r(age, city) VALUES (2, 'SZ');

insert into test_person_r(age, city) VALUES (21, 'SZ');

insert into test_person_r(age, city) VALUES (13, 'BJ');

insert into test_person_r(age, city) VALUES (43, 'SH');

insert into test_person_r(age, city) VALUES (28, 'HK');

可以看到这里的表名还是test_person_r,而不是具体的分区表,说明对于客户端是无感知的。

而且分区表与主表的字段是一致的。

 

2.List列表分区

类似的,列表分区是按特定的值来分区,比较某个城市的数据放在一个分区里。这里不再给出每一步的讲解,代码如下:

-- 创建主表

create table test_person_l (

                          age int not null,

                          city varchar not null

) partition by list (city);

 

-- 创建分区表

CREATE TABLE test_person_l1 PARTITION OF test_person_l FOR VALUES IN ('GZ');

CREATE TABLE test_person_l2 PARTITION OF test_person_l FOR VALUES IN ('BJ');

CREATE TABLE test_person_l3 PARTITION OF test_person_l DEFAULT;

 

-- 插入测试数据

insert into test_person_l(age, city) VALUES (1, 'GZ');

insert into test_person_l(age, city) VALUES (2, 'SZ');

insert into test_person_l(age, city) VALUES (21, 'SZ');

insert into test_person_l(age, city) VALUES (13, 'BJ');

insert into test_person_l(age, city) VALUES (43, 'SH');

insert into test_person_l(age, city) VALUES (28, 'HK');

insert into test_person_l(age, city) VALUES (28, 'GZ');

 

3.Hash哈希分区

哈希分区是指按字段取哈希值后再分区。具体的语句如下:

-- 创建主表

create table test_person_h (

                          age int not null,

                          city varchar not null

) partition by hash (city);

 

-- 创建分区表

create table test_person_h1 partition of test_person_h for values with (modulus 4, remainder 0);

create table test_person_h2 partition of test_person_h for values with (modulus 4, remainder 1);

create table test_person_h3 partition of test_person_h for values with (modulus 4, remainder 2);

create table test_person_h4 partition of test_person_h for values with (modulus 4, remainder 3);

 

-- 插入测试数据

insert into test_person_h(age, city) VALUES (1, 'GZ');

insert into test_person_h(age, city) VALUES (2, 'SZ');

insert into test_person_h(age, city) VALUES (21, 'SZ');

insert into test_person_h(age, city) VALUES (13, 'BJ');

insert into test_person_h(age, city) VALUES (43, 'SH');

insert into test_person_h(age, city) VALUES (28, 'HK');

可以看到创建分区表的时候,我们用了取模的方式,所以如果要创建N个分区表,就要取N取模。

标签:city,PostgreSQL,分区,介绍,person,分区表,VALUES,test,age
From: https://www.cnblogs.com/yeyuzhuanjia/p/17703058.html

相关文章

  • Lnton羚通视频分析算法平台人员闯入算法检测告警详细介绍
    Lnton羚通的算法算力云平台有以下显著特点:高性能、高可靠性、高可扩展性和低成本。用户可以通过该云平台获取高效、强大的算法计算服务,快速而灵活地运行各种复杂的计算模型和算法。该平台广泛涵盖机器学习、人工智能、大数据分析和图像识别等领域。此外,云平台还提供丰富的算法库和......
  • PostgreSQL技术大讲堂 - 第29讲:执行计划与成本估算
     PostgreSQL从小白到专家,是从入门逐渐能力提升的一个系列教程,内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容,希望对热爱PG、学习PG的同学们有帮助,欢迎持续关注CUUGPG技术大讲堂。 第29讲:执行计划与成本估算 内容1:PostgreSQL中查询执行......
  • 【Java框架】Java十大常用框架介绍
     一、SpringMVCSpringWebMVC是一种基于Java的实现了WebMVC设计模式的请求驱动类型的轻量级Web框架,即使用了MVC架构模式的思想,将web层进行职责解耦,基于请求驱动指的就是使用请求-响应模型,框架的目的就是帮助我们简化开发,SpringWebMVC也是要简化我们日常Web开发的。模型(M......
  • 进程 线程 协程 简单介绍
    一、进程1.1进程可看做是正在执行的程序进程需要一定的资源(如CPU、时间、内存、文件和I/O设备)来完成其任务。这些资源在创建进程或者执行进程时被分配。1.2进程的组成有:PCB、程序段、数据段。PCB(进程控制块,processcontrolblock):保存进程运行期间相关的数据,是进程存在......
  • eosio.system智能合约介绍(四)合约部署介绍
    01目的本文档结合系统合约,介绍了合约部署的核心概念和步骤,包括合约部署过程以及setcode和setabi方法,帮助初学者和开发者快速了解和掌握EOS系统合约部署的相关知识。02概述“eosio.system”智能合约是eos的系统命令合约。这个合约几乎实现了eos全部的系统命令,包括创建账户、资源质押......
  • Docker介绍
    Docker介绍什么是dockerDocker最初是dotCloud公司创始人SolomonHykes在法国期间发起的一个公司内部项目,它是基于dotCloud公司多年云服务技术的一次革新。在2013年底,dotCloud公司决定改名为Docker。Docker最初是在Ubuntu12.04上开发实现的;RedHat则从RHEL6.5......
  • 关于 ui5-middleware-fe-mockserver Mock Server 的使用介绍
    @sap-ux/ui5-middleware-fe-mockserver是一个用于SAPUI5本地开发的中间件,它的作用是模拟后端服务,以便在没有实际后端服务器的情况下进行前端开发和调试。这个MockServer具有丰富的功能,可以帮助开发人员模拟不同的后端场景,包括模拟数据、模拟OData服务、模拟HTTP请求等......
  • 向量数据库简介和5个常用的开源项目介绍
    在人工智能领域,有大量的数据需要有效的处理。随着我们对人工智能应用,如图像识别、语音搜索或推荐引擎的深入研究,数据的性质变得更加复杂。这就是向量数据库发挥作用的地方。与存储标量值的传统数据库不同,向量数据库专门设计用于处理多维数据点(通常称为向量)。这些向量表示多个维......
  • SAP ABAP Dump Analysis(ST22) 工具的使用和背景介绍
    笔者在SAP中国研究院16年的工作生涯里,曾经在多个产品开发和产品支持团队工作过。产品支持(ProductSupport)团队的职责就是分析和处理SAP客户提交的Incident.不少客户提交的Incident只能在客户的生产系统上重现,而我们SAPSupport工程师是不允许直接在客户生产系统进行......
  • ubuntu22.04安装并初始化postgresql
    ubuntu22.04自带其实是有数据库的,通过以下命令可以查看当前数据库的版本信息aptshowpostgresql 如果要安装新的数据库,可以执行以下命令sudoaptupdatesudoaptinstallpostgresqlpostgresql-contribpostgresql-contrib或者说contrib包,包含一些不属于P......