PostgreSQL分区表原理、案例的灵活应用

2024-08-30 11:04
文章标签 应用 原理 postgresql 案例 灵活 分区表

本文主要是介绍PostgreSQL分区表原理、案例的灵活应用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

PostgreSQL分区表的灵活应用

通常情况下，扫描一个大表会很慢,需要扫描整张表格，如果能够把大表分拆成小表，查询数据的时猴，只扫描数据所属的小表，就能大大降低扫描时间，提高查询速度。

1、简介

PostgreSQL10之前的版本不支持内置分区表，若要实现分区功能，需通过继承的方式实现。

PostgreSQL 10.x 之前的版本提供了一种“手动”方式使用分区表的方式，需要使用继承 + 触发器的来实现分区表，步骤较为繁琐，需要定义附表、子表、子表的约束、创建子表索引，创建分区删除、修改，触发器等。

PostgreSQL 10.x 开始提供了内置分区表（内置是相对于 10.x 之前的手动方式）。内置分区简化了操作，将部分操作内置，最终简单三步就能够创建分区表。但是只支持范围分区（RANGE）和列表分区（LIST），11.x 版本添加了对 HASH 分区。

如果要充分使用分区表的查询优势，必须使用分区时的字段作为过滤条件。
除了在查询上的优势，分区表的使用也可提高删除数据的性能，因为删除一个分区要比删除分区上的所有数据要快的多。这些命令也完全避免了由批量DELETE造成的VACUUM负载。

概念：分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。

文章目录

  • PostgreSQL分区表的灵活应用
    • 1、简介
    • 2、列表分区
    • 3、范围分区
    • 4、分区管理
    • 5、常用的分表方式，范围分区（包括分区表嵌套，添加、删除分区）
      • 5.1、创建主表
      • 5.2、创建分区表
      • 5.3、创建生成数据的函数
      • 5.4、插入数据到表格
      • 5.5、断开分区
      • 5.6、再创建与原来那个分区表一样的表结构,添加两个分区
      • 5.7、把原先那个总表的2021分区表数据导入现在分区表
      • 5.8、把这个分区表加入到到之前的分区表分区中
      • 5.9、查看当前的分区表信息

2、列表分区

--创建主表
CREATE TABLE info_list (id bigint NOT NULL,protocol varchar(16),ip varchar(50),create_time timestamp
) partition by list(create_time);--创建分区表
create table info_list20200801 partition of info_list for values in ('2020-08-01');
create table info_list20200802 partition of info_list for values in ('2020-08-02');
create table info_list20200803 partition of info_list for values in ('2020-08-03');
--分区表建索引
CREATE INDEX idx_info_list20200801 ON info_list20200801 (create_time);
CREATE INDEX idx_info_list20200802 ON info_list20200802 (create_time);
CREATE INDEX idx_info_list20200803 ON info_list20200803 (create_time);

3、范围分区

注意:如分表的范围为2020-08-01至2020-08-02，则包含前者，不包含后者。相当于时a<=create_time<b。

--1、创建主表(根据create_time进行范围分区)
CREATE TABLE info_range (id bigint NOT NULL,protocol varchar(16),ip varchar(50),create_time timestamp
) partition by range(create_time);
--2、创建分表（根据下面表范围，如果插入2020-08-04,则会报错;如范围为2020-08-01至2020-08-02，则包含前者，不包含后者。相当于时a<=create_time<b;）
create table info_range20200801 partition of info_range for values from ('2020-08-01') to ('2020-08-02');
create table info_range20200802 partition of info_range for values from ('2020-08-02') to ('2020-08-03');
create table info_range20200803 partition of info_range for values from ('2020-08-03') to ('2020-08-04');--3、创建索引
CREATE INDEX idx_info_range20200801 ON info_range20200801 (create_time);
CREATE INDEX idx_info_range20200802 ON info_range20200802 (create_time);
CREATE INDEX idx_info_range20200803 ON info_range20200803 (create_time);

4、分区管理

--断开分区
alter table info_range detach partition info_range20200803;
--连接分区
alter table info_range attach partition info_range20200803 for values from ('2020-08-03') to ('2020-08-04');
--删除分区
drop table info_range20200803;

PG11以上

哈希分区
通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余数。

5、常用的分表方式，范围分区（包括分区表嵌套，添加、删除分区）

如给大表分区后的某个分区还是特别大，可以弄个二级分区，就像结构树那样嵌套下去（具体做法请参考5.5~5.8）

5.1、创建主表

CREATE TABLE sales_orders (order_id SERIAL,                       -- 订单ID，自动递增customer_id INT NOT NULL,              -- 客户ID，不允许为空order_date DATE NOT NULL,              -- 订单日期，不允许为空amount NUMERIC(10, 2) NOT NULL,        -- 订单金额，精确到小数点后两位，不允许为空status VARCHAR(20),                    -- 订单状态，例如 'completed' 或 'pending'CONSTRAINT sales_orders_pkey PRIMARY KEY (order_id, order_date) -- 设定主键约束，唯一标识每一条订单
)
PARTITION BY RANGE (order_date);          -- 根据订单日期进行范围分区

5.2、创建分区表


CREATE TABLE sales_orders_2021PARTITION OF sales_ordersFOR VALUES FROM ('2021-01-01') TO ('2021-12-31'); -- 2021年的订单分区CREATE TABLE sales_orders_2022PARTITION OF sales_ordersFOR VALUES FROM ('2022-01-01') TO ('2022-12-31'); -- 2022年的订单分区CREATE TABLE sales_orders_2023_q1_q2PARTITION OF sales_ordersFOR VALUES FROM ('2023-01-01') TO ('2023-06-30'); -- 2023年第一季度和第二季度的订单分区CREATE TABLE sales_orders_2023_q3_q4PARTITION OF sales_ordersFOR VALUES FROM ('2023-07-01') TO ('2023-12-31'); -- 2023年第三季度和第四季度的订单分区CREATE TABLE sales_orders_2024PARTITION OF sales_ordersFOR VALUES FROM ('2024-01-01') TO ('2024-12-31'); -- 2024年的订单分区

5.3、创建生成数据的函数

CREATE OR REPLACE FUNCTION insert_random_data(start_date DATE,   -- 起始日期end_date DATE,     -- 结束日期num_rows INT        -- 生成的记录数量
) RETURNS VOID LANGUAGE plpgsql AS $$
BEGININSERT INTO sales_orders (customer_id,   -- 客户IDorder_date,    -- 订单日期amount,        -- 订单金额status         -- 订单状态)SELECT(random() * 1000)::int AS customer_id,           -- 随机生成客户IDgenerate_series(start_date, end_date, '1 day'::interval)::date AS order_date, -- 从起始日期到结束日期生成日期系列(random() * 500 + 50)::numeric(10, 2) AS amount, -- 随机生成订单金额，范围在50到550之间CASE WHEN random() > 0.5 THEN 'completed' ELSE 'pending' END AS status -- 随机生成订单状态FROM generate_series(start_date, end_date, '1 day'::interval) -- 生成日期系列LIMIT num_rows;   -- 限制插入的记录数
END;
$$;

5.4、插入数据到表格

SELECT insert_random_data('2021-01-01', '2021-12-30', 50000);
SELECT insert_random_data('2022-01-01', '2022-12-30', 50000);test=# select count(*) from sales_orders;count  
--------100000
(1 row)test=#

5.5、断开分区

alter table sales_orders detach partition sales_orders_2021;test=# select count(*) from sales_orders;count 
-------50000
(1 row)

5.6、再创建与原来那个分区表一样的表结构,添加两个分区

这种情况一般应用于，如给大表分区后的某个分区还是特别大，可以弄个二级分区，就像结构树那样嵌套下去

-- 创建2021年订单分区表
CREATE TABLE sales_orders_2021_p (order_id SERIAL,                    -- 订单ID，自动递增customer_id INT NOT NULL,           -- 客户ID，不允许为空order_date DATE NOT NULL,           -- 订单日期，不允许为空amount NUMERIC(10, 2) NOT NULL,     -- 订单金额，精确到小数点后两位，不允许为空status VARCHAR(20),                 -- 订单状态，例如 'completed' 或 'pending'CONSTRAINT sales_orders_2021_p_pkey PRIMARY KEY (order_id, order_date)  -- 主键约束
)
PARTITION BY RANGE (order_date);       -- 根据订单日期进行范围分区--新建两个分区
create table sales_orders_2021_p_1 partition of sales_orders_2021_p for VALUES FROM ('2021-01-01') TO ('2021-5-31');
create table sales_orders_2021_p_2 partition of sales_orders_2021_p for VALUES FROM ('2021-5-31') TO ('2021-12-31');

5.7、把原先那个总表的2021分区表数据导入现在分区表

insert into sales_orders_2021_p select * from sales_orders_2021;

5.8、把这个分区表加入到到之前的分区表分区中

test=# select count(*) from sales_orders;count 
-------50000
(1 row)alter table sales_orders ATTACH PARTITION sales_orders_2021_p FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');test=# select count(*) from sales_orders;count  
--------100000
(1 row)

5.9、查看当前的分区表信息

--以下就是带有二级分区的分区表
test=# \d+ sales_orders;Partitioned table "public.sales_orders"Column    |         Type          | Collation | Nullable |                    Default                     | Storage  | Compression | Stats target | Description 
-------------+-----------------------+-----------+----------+------------------------------------------------+----------+-------------+--------------+-------------order_id    | integer               |           | not null | nextval('sales_orders_order_id_seq'::regclass) | plain    |             |              | customer_id | integer               |           | not null |                                                | plain    |             |              | order_date  | date                  |           | not null |                                                | plain    |             |              | amount      | numeric(10,2)         |           | not null |                                                | main     |             |              | status      | character varying(20) |           |          |                                                | extended |             |              | 
Partition key: RANGE (order_date)
Indexes:"sales_orders_pkey" PRIMARY KEY, btree (order_id, order_date)
Partitions: sales_orders_2021_p FOR VALUES FROM ('2021-01-01') TO ('2021-12-31'), PARTITIONED,sales_orders_2022 FOR VALUES FROM ('2022-01-01') TO ('2022-12-31'),sales_orders_2023_q1_q2 FOR VALUES FROM ('2023-01-01') TO ('2023-06-30'),sales_orders_2023_q3_q4 FOR VALUES FROM ('2023-07-01') TO ('2023-12-31'),sales_orders_2024 FOR VALUES FROM ('2024-01-01') TO ('2024-12-31')
test=# \d+;List of relationsSchema |               Name               |       Type        | Owner | Persistence | Access method |    Size    | Description 
--------+----------------------------------+-------------------+-------+-------------+---------------+------------+-------------public | sales_orders                     | partitioned table | fbase | permanent   |               | 0 bytes    | public | sales_orders_2021                | table             | fbase | permanent   | heap          | 3048 kB    | public | sales_orders_2021_p              | partitioned table | fbase | permanent   |               | 0 bytes    | public | sales_orders_2021_p_1            | table             | fbase | permanent   | heap          | 1248 kB    | public | sales_orders_2021_p_2            | table             | fbase | permanent   | heap          | 1768 kB    | public | sales_orders_2021_p_order_id_seq | sequence          | fbase | permanent   |               | 8192 bytes | public | sales_orders_2022                | table             | fbase | permanent   | heap          | 2976 kB    | public | sales_orders_2023_q1_q2          | table             | fbase | permanent   | heap          | 16 kB      | public | sales_orders_2023_q3_q4          | table             | fbase | permanent   | heap          | 16 kB      | public | sales_orders_2024                | table             | fbase | permanent   | heap          | 16 kB      | public | sales_orders_order_id_seq        | sequence          | fbase | permanent   |               | 8192 bytes | 
(11 rows)

这篇关于PostgreSQL分区表原理、案例的灵活应用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1120637。

相关文章

Golang操作DuckDB实战案例分享

Golang操作DuckDB实战案例分享

《Golang操作DuckDB实战案例分享》DuckDB是一个嵌入式SQL数据库引擎,它与众所周知的SQLite非常相似,但它是为olap风格的工作负载设计的,DuckDB支持各种数据类型和SQL特性... 目录DuckDB的主要优点环境准备初始化表和数据查询单行或多行错误处理和事务完整代码最后总结Duck
阅读更多...
Redis主从/哨兵机制原理分析

Redis主从/哨兵机制原理分析

《Redis主从/哨兵机制原理分析》本文介绍了Redis的主从复制和哨兵机制,主从复制实现了数据的热备份和负载均衡,而哨兵机制可以监控Redis集群,实现自动故障转移,哨兵机制通过监控、下线、选举和故... 目录一、主从复制1.1 什么是主从复制1.2 主从复制的作用1.3 主从复制原理1.3.1 全量复制
阅读更多...
将Python应用部署到生产环境的小技巧分享

将Python应用部署到生产环境的小技巧分享

《将Python应用部署到生产环境的小技巧分享》文章主要讲述了在将Python应用程序部署到生产环境之前,需要进行的准备工作和最佳实践,包括心态调整、代码审查、测试覆盖率提升、配置文件优化、日志记录完... 目录部署前夜：从开发到生产的心理准备与检查清单环境搭建：打造稳固的应用运行平台自动化流水线：让部署像
阅读更多...
Redis主从复制的原理分析

Redis主从复制的原理分析

《Redis主从复制的原理分析》Redis主从复制通过将数据镜像到多个从节点,实现高可用性和扩展性,主从复制包括初次全量同步和增量同步两个阶段,为优化复制性能,可以采用AOF持久化、调整复制超时时间、... 目录Redis主从复制的原理主从复制概述配置主从复制数据同步过程复制一致性与延迟故障转移机制监控与维
阅读更多...
MySQL不使用子查询的原因及优化案例

MySQL不使用子查询的原因及优化案例

《MySQL不使用子查询的原因及优化案例》对于mysql,不推荐使用子查询,效率太差,执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,本文给大家... 目录不推荐使用子查询和JOIN的原因解决方案优化案例案例1：查询所有有库存的商品信息案例2：使用EX
阅读更多...
SpringCloud配置动态更新原理解析

SpringCloud配置动态更新原理解析

《SpringCloud配置动态更新原理解析》在微服务架构的浩瀚星海中,服务配置的动态更新如同魔法一般,能够让应用在不重启的情况下,实时响应配置的变更,SpringCloud作为微服务架构中的佼佼者,... 目录一、SpringBoot、Cloud配置的读取二、SpringCloud配置动态刷新三、更新@R
阅读更多...
Linux中Curl参数详解实践应用

Linux中Curl参数详解实践应用

《Linux中Curl参数详解实践应用》在现代网络开发和运维工作中,curl命令是一个不可或缺的工具,它是一个利用URL语法在命令行下工作的文件传输工具,支持多种协议,如HTTP、HTTPS、FTP等... 目录引言一、基础请求参数1. -X 或 --request2. -d 或 --data3. -H 或
阅读更多...
在Ubuntu上部署SpringBoot应用的操作步骤

在Ubuntu上部署SpringBoot应用的操作步骤

《在Ubuntu上部署SpringBoot应用的操作步骤》随着云计算和容器化技术的普及,Linux服务器已成为部署Web应用程序的主流平台之一,Java作为一种跨平台的编程语言,具有广泛的应用场景,本... 目录一、部署准备二、安装 Java 环境1. 安装 JDK2. 验证 Java 安装三、安装 mys
阅读更多...
Python中构建终端应用界面利器Blessed模块的使用

Python中构建终端应用界面利器Blessed模块的使用

《Python中构建终端应用界面利器Blessed模块的使用》Blessed库作为一个轻量级且功能强大的解决方案,开始在开发者中赢得口碑,今天,我们就一起来探索一下它是如何让终端UI开发变得轻松而高... 目录一、安装与配置：简单、快速、无障碍二、基本功能：从彩色文本到动态交互1. 显示基本内容2. 创建链
阅读更多...
Redis主从复制实现原理分析

Redis主从复制实现原理分析

《Redis主从复制实现原理分析》Redis主从复制通过Sync和CommandPropagate阶段实现数据同步,2.8版本后引入Psync指令,根据复制偏移量进行全量或部分同步,优化了数据传输效率... 目录Redis主DodMIK从复制实现原理实现原理Psync: 2.8版本后总结Redis主从复制实
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2