浅谈PostgreSQL表分区的三种方式

目录
  • 一、简介
  • 二、三种方式
    • 2.1、Range范围分区
    • 2.2、List列表分区
    • 2.3、Hash哈希分区
  • 三、总结

一、简介

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

  • Range:范围分区;
  • List:列表分区;
  • Hash:哈希分区。

本文通过示例讲解如何进行这三种方式的分区。

二、三种方式

为方便,我们通过Docker的方式启动一个PostgreSQL。我们要选择较高的版本,否则不支持Hash分区,命令如下:

docker run -itd \

    --name pkslow-postgres \

    -e POSTGRES_DB=pkslow \

    -e POSTGRES_USER=pkslow \

    -e POSTGRES_PASSWORD=pkslow \

    -p 5432:5432 \

    postgres:13

2.1、Range范围分区

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

CREATE TABLE pkslow_person_r (
    age int not null,
    city varchar not null
) PARTITION BY RANGE (age);

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

create table pkslow_person_r1 partition of pkslow_person_r for values from (MINVALUE) to (10);
create table pkslow_person_r2 partition of pkslow_person_r for values from (11) to (20);
create table pkslow_person_r3 partition of pkslow_person_r for values from (21) to (30);
create table pkslow_person_r4 partition of pkslow_person_r for values from (31) to (MAXVALUE);

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

接着我们插入一些数据:

insert into pkslow_person_r(age, city) VALUES (1, 'GZ');
insert into pkslow_person_r(age, city) VALUES (2, 'SZ');
insert into pkslow_person_r(age, city) VALUES (21, 'SZ');
insert into pkslow_person_r(age, city) VALUES (13, 'BJ');
insert into pkslow_person_r(age, city) VALUES (43, 'SH');
insert into pkslow_person_r(age, city) VALUES (28, 'HK');

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

我们查询也一样的:

但实际上是有分区表存在的:

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

查询分区表,就只能查到那个特定分区的数据了:

2.2、List列表分区

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

-- 创建主表
create table pkslow_person_l (
                          age int not null,
                          city varchar not null
) partition by list (city);

-- 创建分区表
CREATE TABLE pkslow_person_l1 PARTITION OF pkslow_person_l FOR VALUES IN ('GZ');
CREATE TABLE pkslow_person_l2 PARTITION OF pkslow_person_l FOR VALUES IN ('BJ');
CREATE TABLE pkslow_person_l3 PARTITION OF pkslow_person_l DEFAULT;

-- 插入测试数据
insert into pkslow_person_l(age, city) VALUES (1, 'GZ');
insert into pkslow_person_l(age, city) VALUES (2, 'SZ');
insert into pkslow_person_l(age, city) VALUES (21, 'SZ');
insert into pkslow_person_l(age, city) VALUES (13, 'BJ');
insert into pkslow_person_l(age, city) VALUES (43, 'SH');
insert into pkslow_person_l(age, city) VALUES (28, 'HK');
insert into pkslow_person_l(age, city) VALUES (28, 'GZ');

当我们查询第一个分区的时候,只有广州的数据:

2.3、Hash哈希分区

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

-- 创建主表
create table pkslow_person_h (
                          age int not null,
                          city varchar not null
) partition by hash (city);

-- 创建分区表
create table pkslow_person_h1 partition of pkslow_person_h for values with (modulus 4, remainder 0);
create table pkslow_person_h2 partition of pkslow_person_h for values with (modulus 4, remainder 1);
create table pkslow_person_h3 partition of pkslow_person_h for values with (modulus 4, remainder 2);
create table pkslow_person_h4 partition of pkslow_person_h for values with (modulus 4, remainder 3);

-- 插入测试数据
insert into pkslow_person_h(age, city) VALUES (1, 'GZ');
insert into pkslow_person_h(age, city) VALUES (2, 'SZ');
insert into pkslow_person_h(age, city) VALUES (21, 'SZ');
insert into pkslow_person_h(age, city) VALUES (13, 'BJ');
insert into pkslow_person_h(age, city) VALUES (43, 'SH');
insert into pkslow_person_h(age, city) VALUES (28, 'HK');

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

随便查询一张分区表如下:

可以看到同是SZ的哈希值是一样的,肯定会分在同一个分区,而BJ的哈希值取模后也属于同一个分区。

三、总结

本文讲解了PostgreSQL分区的三种方式。

代码请查看:https://github.com/LarryDpk/pkslow-samples

以上就是浅谈PostgreSQL表分区的三种方式的详细内容,更多关于PostgreSQL表分区的资料请关注我们其它相关文章!

(0)

相关推荐

  • PostgreSQL LIST、RANGE 表分区的实现方案

    简 介 PG分区:就是把逻辑上的一个大表分割成物理上的几块. 分区的优点 1. 某些类型的查询性能得到提升 2. 更新的性能也可以得到提升,因为某块的索引要比在整个数据集上的索引要小. 3. 批量删除可以通过简单的删除某个分区来实现. 4. 可以将很少用的数据移动到便宜的.转速慢的存储介质上. 分区实现原理 10.x版本之前PG表分区的实现原理:PG中是通过表的继承来实现的,建立一个主表,里面是空的,然后每个分区去继承它.无论何时,该主表里面都必须是空的 官网建议:只有当表本身大小超过了机器物理

  • PostgreSQL之分区表(partitioning)

    PostgreSQL有一项非常有用的功能,分区表,或者partitioning.当某个TABLE的记录非常的多,千万甚至更多的时候,我们其实需要将他分割成子表.一个庞大的TABLE,就像水果仓库杂乱无章地堆放着无数的苹果桃子和桔子,查找不方便,性能降低,比较合理的做法是将仓库分成三个子区域,分表放苹果桃子和桔子.一张大表就变成了三个小表的集合. 通过合理的设计,可以将选择一定的规则,将大表切分多个不重不漏的子表,这就是传说中的partitioning.比如,我们可以按时间切分,每天一张子表,比如

  • 浅析postgresql 数据库 TimescaleDB 修改分区时间范围

    今天遇到一个问题 之前用TimescaleDB创建的超表 是已7天为单位的 由于7天的数据量过大 影响查询效率 需要把7天的分区改为一天一分区 创建超表 create_hypertable() 1.创建普通版表 CREATE TABLE "超表名" ( "collect_time" timestamp(6) NOT NULL, "code" varchar(36) COLLATE "pg_catalog"."defa

  • 利用python为PostgreSQL的表自动添加分区

    PostgreSQL引进"分区"表特性,解放了之前采用"表继承"+"触发器"来实现分区表的繁琐.低效.而添加分区,都是手动执行SQL. 演示目的:利用python来为PostgreSQL的表自动添加分区. python版本:python3+ pip3 install psycopg2 一.配置数据源 database.ini 文件:记录数据库连接参数 [adsas] host=192.168.1.201 database=adsas user=a

  • PostgreSQL 创建表分区

    创建表分区步骤如下: 1. 创建主表 CREATE TABLE users ( uid int not null primary key, name varchar(20)); 2. 创建分区表(必须继承上面的主表) CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users); CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users); 3.

  • PostgreSQL教程(三):表的继承和分区表详解

    一.表的继承: 这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生,然而它的实现方式和设计原理却是简单易懂,现在就让我们从一个简单的例子开始吧.     1. 第一个继承表:   复制代码 代码如下: CREATE TABLE cities (   --父表         name        text,         population float,         altitude     int     );     CREATE TABLE capitals (

  • 如何为PostgreSQL的表自动添加分区

    PostgreSQL 引进"分区"表特性,解放了之前采用"表继承"+ "触发器"来实现分区表的繁琐.低效.而添加分区,都是手动执行 SQL. 演示目的:利用 python 来为 PostgreSQL 的表自动添加分区. python版本: python3+ pip3 install psycopg2 一.配置数据源 database.ini 文件:记录数据库连接参数 [adsas] host=192.168.1.201 database=adsa

  • 浅谈PostgreSQL 11 新特性之默认分区

    文章目录 PosgtreSQL 11 支持为分区表创建一个默认(DEFAULT)的分区,用于存储无法匹配其他任何分区的数据.显然,只有 RANGE 分区表和 LIST 分区表需要默认分区. CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate); CREATE TABLE measuremen

  • PostgreSQL分区表(partitioning)应用实例详解

    前言 项目中有需求要垂直分表,即按照时间区间将数据拆分到n个表中,PostgreSQL提供了分区表的功能.分区表实际上是把逻辑上的一个大表分割成物理上的几小块,提供了很多好处,比如: 1.查询性能大幅提升 2.删除历史数据更快 3.可将不常用的历史数据使用表空间技术转移到低成本的存储介质上 那么什么时候该使用分区表呢?官方给出的指导意见是:当表的大小超过了数据库服务器的物理内存大小则应当使用分区表,接下来结合一个例子具体记录一下创建分区表的详细过程. 创建分区表 首先看一下需求,现在有一张日志表

  • 浅谈PostgreSQL表分区的三种方式

    目录 一.简介 二.三种方式 2.1.Range范围分区 2.2.List列表分区 2.3.Hash哈希分区 三.总结 一.简介 表分区是解决一些因单表过大引用的性能问题的方式,比如某张表过大就会造成查询变慢,可能分区是一种解决方案.一般建议当单表大小超过内存就可以考虑表分区了.PostgreSQL的表分区有三种方式: Range:范围分区: List:列表分区: Hash:哈希分区. 本文通过示例讲解如何进行这三种方式的分区. 二.三种方式 为方便,我们通过Docker的方式启动一个Postg

  • 浅谈SpringBoot主流读取配置文件三种方式

    读取配置SpringBoot配置文件三种方式 一.利用Bean注解中的Value(${})注解 @Data @Component public class ApplicationProperty { @Value("${application.name}") private String name; } 该方式可以自动读取当前配置文件appliation.yml  或者application.properties中的配置值 区别在于读取yml文件时候支持中文编码,peoperties需

  • 浅谈Java实体对象的三种状态以及转换关系

    最新的Hibernate文档中为Hibernate对象定义了四种状态(原来是三种状态,面试的时候基本上问的也是三种状态),分别是:瞬时态(new, or transient).持久态(managed, or persistent).游状态(detached)和移除态(removed,以前Hibernate文档中定义的三种状态中没有移除态),如下图所示,就以前的Hibernate文档中移除态被视为是瞬时态. 瞬时态:当new一个实体对象后,这个对象处于瞬时态,即这个对象只是一个保存临时数据的内存区

  • 浅谈C++中virtual的三种用法

    virtual用法一 #include using namespace std; class A{ public: virtual void display(){ cout<<"A"<<ENDL; } }; class B : public A{ public: void display(){ cout<<"B"<<ENDL; } }; void doDisplay(A *p) { p->display(); d

  • 浅谈MyBatis执行SQL的两种方式

    目录 前言 准备接口和Mapper配置文件: 使用SqlSession 发送 SQL 使用 Mapper 接口发送 SQL 比较两种发送 SQL 方式 前言 本文介绍MyBatis执行SQL语句的2种方式:SqlSession和Mapper接口以及它们的区别. 准备接口和Mapper配置文件: 定义UserMapper接口: package cn.cvs.dao; import cn.cvs.pojo.User; import java.util.List; public interface U

  • 浅谈SQL Server中的三种物理连接操作(性能比较)

    在SQL Server中,我们所常见的表与表之间的Inner Join,Outer Join都会被执行引擎根据所选的列,数据上是否有索引,所选数据的选择性转化为Loop Join,Merge Join,Hash Join这三种物理连接中的一种.理解这三种物理连接是理解在表连接时解决性能问题的基础,下面我来对这三种连接的原理,适用场景进行描述. 嵌套循环连接(Nested Loop Join) 循环嵌套连接是最基本的连接,正如其名所示那样,需要进行循环嵌套,嵌套循环是三种方式中唯一支持不等式连接的

  • 浅谈Javascript事件处理程序的几种方式

    事件就是用户或浏览器自身执行的某种动作.比如说click,mouseover,都是事件的名字.而相应某个事件的函数就叫事件处理程序(或事件侦听器).为事件指定处理程序的方式有好几种. 一:HTML事件处理程序. 如: 复制代码 代码如下: <script type="text/javascript"> function show(){ alert('hello world!'); } </script> <input type="button&q

  • 浅谈jquery页面初始化的4种方式

    <script src="Scripts/jquery-1.8.2.min.js"></script> <script type="text/javascript"> $(document).ready(function () { alert("第一种方法."); }); $(function () { alert("第二种方法."); }); jQuery(function ($) { a

  • 浅谈ubuntu执行.sh文件几种方式区别

    前言 不同的script执行方式会造成不一样的结果,尤其对bash的环境影响很大,脚本的执行方式有大概以下几种方式. 首先写一个简单的test.sh文件: #!/bin/bash read -p "Please input your first name:" firstname read -p "Please input your last name:" lastname echo -e "\nYour full name is: $firstname $

  • Spring AOP拦截-三种方式实现自动代理详解

    这里的自动代理,我讲的是自动代理bean对象,其实就是在xml中让我们不用配置代理工厂,也就是不用配置class为org.springframework.aop.framework.ProxyFactoryBean的bean. 总结了一下自己目前所学的知识. 发现有三种方式实现自动代理 用Spring一个自动代理类DefaultAdvisorAutoProxyCreator: <bean class="org.springframework.aop.framework.autoproxy.

随机推荐