PostgreSQL分区表：基础语法与运维实践

引言

简介：什么是数据库分区

数据库分区是一种将大型表物理上分割成多个较小的部分的技术。每个部分称为一个分区，这些分区可以分布在不同的存储设备上，以提高查询性能和管理效率。

为什么使用分区表

  • 提高查询性能：通过减少需要扫描的数据量，查询速度可以显著提升。
  • 简化数据管理：分区使得数据的备份、恢复和归档更加容易。
  • 优化存储：可以将不同分区放在不同的存储介质上，以平衡性能和成本。
  • 增强可维护性：分区表的维护操作（如索引重建、数据清理）可以逐分区进行，减少对整个系统的干扰。

PostgreSQL中的分区支持概述

PostgreSQL从9.0版本开始引入了分区支持，并在后续版本中不断改进。目前，PostgreSQL支持多种分区类型，包括范围分区、列表分区和散列分区。通过这些分区技术，可以有效地管理和优化大规模数据表。

分区类型

范围分区（Range Partitioning）

范围分区是根据一个或多个列的值范围来划分表。例如，可以根据日期或数值范围来创建分区。

示例

CREATE TABLE sales (id SERIAL ,sale_date DATE NOT NULL,amount NUMERIC,PRIMARY KEY (id,sale_date)
) PARTITION BY RANGE (sale_date);CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

这个语句创建了一个名为 sales 的表，该表有三个字段：id（一个自动递增的主键）、sale_date（一个非空的日期字段）和 amount（一个数值字段）。重要的是，这个表被声明为一个分区表，基于 sale_date 字段的范围进行分区。并创建了 sales 表的两个分区子表：sales_2022 和 sales_2023。每个子表都负责存储 sales 表中特定日期范围内的数据。其中，sales_2022 存储 sale_date 从 2022-01-01 到 2022-12-31 的数据，sales_2023 存储 sale_date 从 2023-01-01 到 2023-12-31 的数据。

列表分区（List Partitioning）

列表分区是根据一个或多个列的具体值来划分表。适用于列值有限且已知的情况。

示例

CREATE TABLE employees (id SERIAL ,department VARCHAR(50) NOT NULL,name VARCHAR(100),PRIMARY KEY (id,department)
) PARTITION BY LIST (department);CREATE TABLE employees_sales PARTITION OF employees FOR VALUES IN ('Sales');
CREATE TABLE employees_marketing PARTITION OF employees FOR VALUES IN ('Marketing');

这个语句创建了一个名为 employees 的表，该表有三个字段：id（一个自动递增的主键）、department（一个非空的 VARCHAR 类型字段，用于存储部门名称）和 name（一个 VARCHAR 类型字段，用于存储员工姓名）。这个表被声明为一个分区表，基于 department 字段的列表值进行分区。并创建了 employees 表的两个分区子表：employees_sales 和 employees_marketing。employees_sales 负责存储 department 字段值为 ‘Sales’ 的员工数据，而 employees_marketing 负责存储 department 字段值为 ‘Marketing’ 的员工数据。

散列分区（Hash Partitioning）

散列分区是根据列值的哈希函数结果来划分表。适用于需要均匀分布数据的情况。

示例

CREATE TABLE users (id SERIAL PRIMARY KEY,username VARCHAR(50) NOT NULL,email VARCHAR(100)
) PARTITION BY HASH (id);CREATE TABLE users_0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

这个语句创建了一个名为 users 的表，该表有三个字段：id（一个自动递增的主键）、username（一个非空的 VARCHAR 类型字段，用于存储用户名）和 email（一个 VARCHAR 类型字段，用于存储电子邮件地址）。这个表被声明为一个分区表，基于 id 字段的哈希值进行分区。并创建了 users 表的四个分区子表。这些子表根据 id 字段的哈希值进行分区，具体使用了模运算（MODULUS）和余数（REMAINDER）来确定数据应该存储在哪个分区中。

  • users_0 存储 id 哈希值模 4 余 0 的数据。

  • users_1 存储 id 哈希值模 4 余 1 的数据。

  • users_2 存储 id 哈希值模 4 余 2 的数据。

  • users_3 存储 id 哈希值模 4 余 3 的数据。

创建分区表

基本语法

创建分区表的基本语法如下：

CREATE TABLE table_name (column1 data_type,column2 data_type,...
) PARTITION BY {RANGE | LIST | HASH} (column_name);

范围分区示例

CREATE TABLE orders (order_id SERIAL ,order_date DATE NOT NULL,amount NUMERIC,PRIMARY KEY(order_id,order_date)
) PARTITION BY RANGE (order_date);CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

列表分区示例

CREATE TABLE products (product_id SERIAL,category VARCHAR(50) NOT NULL,name VARCHAR(100)，PRIMARY KEY(product_id,category)
) PARTITION BY LIST (category);CREATE TABLE products_electronics PARTITION OF products FOR VALUES IN ('Electronics');
CREATE TABLE products_clothing PARTITION OF products FOR VALUES IN ('Clothing');

散列分区示例

CREATE TABLE customers (customer_id SERIAL PRIMARY KEY,name VARCHAR(100),email VARCHAR(100)
) PARTITION BY HASH (customer_id);CREATE TABLE customers_0 PARTITION OF customers FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE customers_1 PARTITION OF customers FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE customers_2 PARTITION OF customers FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE customers_3 PARTITION OF customers FOR VALUES WITH (MODULUS 4, REMAINDER 3);

注意事项

  • 分区键的选择：选择合适的分区键是关键。分区键应具有高选择性，且能够均匀分布数据。
  • 分区策略的设计：根据业务需求和数据特性设计合理的分区策略。例如，* 对于时间序列数据，范围分区通常是最佳选择。

管理分区表

添加新分区

--创建分区表
CREATE TABLE orders_2024 (order_id SERIAL ,order_date DATE NOT NULL,amount NUMERIC,PRIMARY KEY(order_id,order_date)
) ;
--将分区表追加到主表
ALTER TABLE orders ATTACH PARTITION orders_2024 FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

删除分区

ALTER TABLE orders DETACH PARTITION orders_2022;
DROP TABLE orders_2022;

修改现有分区

1. 重命名分区

ALTER TABLE orders RENAME PARTITION orders_2022 TO orders_2022_old;

2.修改分区范围

ALTER TABLE orders DETACH PARTITION orders_2022;
ALTER TABLE orders ATTACH PARTITION orders_2022 FOR VALUES FROM ('2022-01-01') TO ('2022-06-30');

需要注意的是，修改分区范围可能会影响数据的完整性和查询结果，因为该分区现在只包含指定日期范围内的数据。如果原始 orders_2022 分区包含超出此范围的数据，则这些数据在重新附加后将不再作为 orders 表的一部分。

分区维护的最佳实践

  • 定期检查和优化：定期检查分区表的性能和存储情况，必要时进行优化。
  • 数据归档：及时归档不再需要的历史数据，释放存储空间。
  • 监控和日志：启用监控和日志记录，以便及时发现和解决问题。

查询优化

如何利用分区提高查询性能

  • 减少扫描范围：查询时，数据库引擎只扫描相关的分区，而不是整个表，从而减少I/O操作。
  • 并行处理：多个分区可以并行处理，提高查询速度。
  • 索引优化：在每个分区上创建局部索引，可以进一步提高查询性能。

使用EXPLAIN分析查询计划

EXPLAIN命令可以帮助你理解查询的执行计划，从而优化查询性能。

EXPLAIN SELECT * FROM sales WHERE sale_date BETWEEN '2022-01-01' AND '2022-12-31';

通过查看输出，你可以看到哪些分区被扫描，以及查询的执行步骤。

索引在分区表中的作用

1，局部索引：在每个分区上创建索引，适用于大多数查询场景。
2，全局索引：跨所有分区创建索引，适用于需要跨分区查询的场景。

数据迁移与维护

将现有表转换为分区表

创建分区表结构：

CREATE TABLE sales_new (id SERIAL PRIMARY KEY,sale_date DATE NOT NULL,amount NUMERIC
) PARTITION BY RANGE (sale_date);

创建分区：

CREATE TABLE sales_2022 PARTITION OF sales_new FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
CREATE TABLE sales_2023 PARTITION OF sales_new FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

迁移数据：

INSERT INTO sales_new SELECT * FROM sales WHERE sale_date BETWEEN '2022-01-01' AND '2022-12-31';
INSERT INTO sales_new SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

切换表名：

BEGIN;
ALTER TABLE sales RENAME TO sales_old;
ALTER TABLE sales_new RENAME TO sales;
COMMIT;

分区表的数据导入导出

数据导入：

COPY sales_2022 FROM '/path/to/sales_2022.csv' CSV HEADER;
COPY sales_2023 FROM '/path/to/sales_2023.csv' CSV HEADER;

数据导出：

COPY (SELECT * FROM sales WHERE sale_date BETWEEN '2022-01-01' AND '2022-12-31') TO '/path/to/sales_2022.csv' CSV HEADER;
COPY (SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31') TO '/path/to/sales_2023.csv' CSV HEADER;

定期维护和检查分区表

  • 定期检查分区：确保分区的完整性和一致性。
  • 索引维护：定期重建索引，以保持查询性能。
  • 数据清理：定期删除不再需要的历史数据。

监控与故障排除

监控分区表的性能指标

  • 查询性能：使用EXPLAIN和EXPLAIN ANALYZE分析查询性能。
  • I/O性能：监控磁盘I/O操作，确保分区表的I/O负载均衡。
  • 锁争用：监控锁争用情况，避免因锁导致的性能瓶颈。

常见问题及解决方法

  • 查询性能下降：检查查询计划，优化索引和分区策略。
  • 数据不一致：定期检查数据完整性，修复损坏的分区。
  • 磁盘空间不足：及时归档历史数据，释放存储空间。

分区表的优势总结

  • 提高查询性能：通过减少扫描范围和并行处理，显著提升了查询速度。
  • 简化数据管理：分区使得数据的备份、恢复和归档更加容易。
  • 优化存储：可以将不同分区放在不同的存储介质上，平衡性能和成本。
  • 增强可维护性：分区表的维护操作可以逐分区进行，减少对整个系统的干扰。

分区表作为PostgreSQL的一项重要特性，为大数据管理和查询提供了有效的解决方案。通过合理地使用分区表，可以提高查询效率、简化数据备份和恢复操作，并提高系统的可维护性和可扩展性。对于数据库管理员和开发者来说，掌握分区表的基础语法和运维实践将是提升数据库性能和管理效率的关键。

通过本文的阅读，能够深入了解PostgreSQL分区表的概念、优势以及基础语法和运维实践，为构建高效、可扩展的数据库系统提供有力的支持。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/15040.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

【C语言刷力扣】58.最后一个单词的长度

【C语言刷力扣】58.最后一个单词的长度

题目： 解题思路; 倒序遍历，先将末尾的空格过滤，再统计至第一个空格。 条件i > 0 放在前面先判断，条件s[i] ! 放后面，反之遇到单字符会溢出。 时间复杂度： 空间复杂度： int lengthOfLas…
阅读更多...
【数据运营】数据资产私域运营：探索并实现数据价值变现的新途径

【数据运营】数据资产私域运营：探索并实现数据价值变现的新途径

随着数字化浪潮的席卷，数据已成为现代企业的核心竞争力之一。然而，仅仅拥有数据并不足以在激烈的市场竞争中脱颖而出，关键在于如何有效地管理和运营这些数据资产，将其转化为实实在在的商业价值。本文将从数据资产私域运营的定义、…
阅读更多...
360天擎终端安全管理 远程控制客户端终端进行的安全防护/终端管理：病毒查杀/插件管理/系统修复/漏洞管理等操作

360天擎终端安全管理 远程控制客户端终端进行的安全防护/终端管理：病毒查杀/插件管理/系统修复/漏洞管理等操作

文章目录 目录 文章目录 使用流程 小结 概要使用流程技术细节小结 概要 如果首页上出现只有5台。但是公司实际上有20台电脑。还有很多未进行安装360天擎的用户主机。我们下发指示通告内容。这个的话需要一个一个排查才能知道谁没有安装。可以查看终端管理页面看到主机IP知道已…
阅读更多...
数字人直播骗局大起底！源码部署究竟有哪些优势？

数字人直播骗局大起底！源码部署究竟有哪些优势？

随着数字人直播的应用频率不断上升，越来越多的人开始关注到了它所蕴含着的广阔前景和巨大收益潜力，于是，纷纷打听起了入局相关的事宜。而这也就让许多不法分子盯上了这一项目，并炮制出了各式各样的数字人直播骗局来收割韭菜。 其中…
阅读更多...
OpenAI官方发布：利用ChatGPT提升写作的12条指南

OpenAI官方发布：利用ChatGPT提升写作的12条指南

近日，OpenAI官方发布了学生如何利用ChatGPT提升写作的12条指南，值得深入研究学习。 在如今AIGC应用爆发增长的时间点，如何充分利用生成式AI工具，如ChatGPT，有效切快速的提升写作和学习能力，成为每个学生、…
阅读更多...
探索大型语言模型（LLMs）能否在不泄露私人信息的情况下联合其他大型语言模型共同解决问题

探索大型语言模型（LLMs）能否在不泄露私人信息的情况下联合其他大型语言模型共同解决问题

概述 谷歌的 Gemini Ultra（2023 年）和 OpenAI 的 GPT-4 （2023 年）等大规模语言模型在许多任务中都表现出了令人印象深刻的性能。然而，这些模型不仅推理成本高昂，而且运行于数据中心，而数据中心…
阅读更多...
CloudDM Team Docker 版安装指南

CloudDM Team Docker 版安装指南

CloudDM Team 是一款全新的国产自研数据库管理工具，在《全新的企业级数据库数据安全管控平台》 一文中全面介绍了其核心功能和特点。本文将会介绍如何在 Ubuntu Linux 中安装并初步使用这款数据库管理工具。 准备工作 安装 Docker CloudDM Team 安装过程中需要用…
阅读更多...
第22天Linux下常用工具

第22天Linux下常用工具

目录 第 1 章 vim 编辑器 1.1 vim 安装 1.2 vim 的使用 1.3 vim 的 4 种工作模式 第 2 章 gcc 编译器 2.1 编译流程（以 main.c 为例） 2.2 gcc 的常用参数 2.3 进行多模块编译 第 3 章 动态库静态库的制作 3.1 库的作用 3.2 库的分类与特点 …
阅读更多...
/// ts中的三斜线指令 | 前端

/// ts中的三斜线指令 | 前端

第一次看到注意到这行代码，不知道的还以为是注释呢，查了资料才知道这是typescript中的三斜线指令，那有什么作用呢？ 1. 这行代码是TypeScript中的一个三斜线指令（Triple-Slash Directive），用于…
阅读更多...
Schnorr 和 BLS 算法详解

Schnorr 和 BLS 算法详解

Schnorr 签名和 BLS 签名在区块链技术中都有着重要的应用。它们各自具备独特的优势，使其在不同的区块链应用场景中得到广泛使用。 Schnorr签名算法 Schnorr签名算法是一种基于离散对数问题的数字签名算法， 由德国密码学家 克劳斯施诺尔 （Cl…
阅读更多...
C++类和对象 - 拷贝构造, 赋值重载

C++类和对象 - 拷贝构造, 赋值重载

拷贝构造函数 拷贝构造作用：一个已经存在的对象去初始化另一个要创建的对象 日常写代码中会出现如下场景: class Data { public:Data(int year, int month, int day) // 拷贝构造函数{this->_year year;this->_month month;this->_day day;} priva…
阅读更多...
为什么咨询公司需要项目管理软件：7大关键优势

为什么咨询公司需要项目管理软件：7大关键优势

在咨询公司中，同时管理多个项目、客户和目标并非易事，尤其需要兼顾长期发展。项目管理软件就成为了各类项目型企业（包括咨询公司）的重要工具。 对于顾问来说，项目管理软件可以简化工作流程、增强客户关系并提高效率。本…
阅读更多...
3大核心技术，免费开源的智能合同审查分析软件的技术介绍

3大核心技术，免费开源的智能合同审查分析软件的技术介绍

本智能合同审查分析系统致力于解决法律领域中复杂文档与信息处理的难题，采用最先进的深度学习与自然语言处理（NLP）技术，提供精准的实体识别与关系抽取功能。系统基于BERT、GPT等主流模型，实现自动识别和关联法律文档中…
阅读更多...
GitCode光引计划有奖征文大赛

GitCode光引计划有奖征文大赛

一、活动介绍 GitCode平台汇聚了众多杰出的G-Star项目，它们犹如璀璨星辰，用各自的故事和成就，为后来者照亮前行的道路。我们诚邀广大开发者、项目维护者及爱好者，共同撰写并分享项目在GitCode平台上托管的体验，挖掘平…
阅读更多...
UE4 Cook 从UAT传递参数给UE4Editor

UE4 Cook 从UAT传递参数给UE4Editor

需求 一句Cook的命令如下： ${EnginePath}/Engine/Build/BatchFiles/RunUAT.sh BuildCookRun -project${ClientPath}/${ProjectName}.uproject -noP4 -platformIOS -cooksinglepackage -client -clientconfig${CookConfig} -iterate -skipbuild -nocompile -NoMutex…
阅读更多...
api驱动的云服务是什么意思？

api驱动的云服务是什么意思？

API驱动的云服务是指利用API技术来驱动和提供云服务的模式。在这种模式下，云服务提供商会公开一系列的API接口，允许开发者或应用程序通过调用这些API来实现对云服务的访问和操作。API驱动的云服务是现代云计算技术的重要组成部分，API驱动的云…
阅读更多...
Springboot 启动端口占用如何解决

Springboot 启动端口占用如何解决

Springboot 启动端口占用如何解决 1、报错信息如下 *************************** APPLICATION FAILED TO START ***************************Description:Web server failed to start. Port 9010 was already in use.Action:Identify and stop the process thats listening o…
阅读更多...
【Rust调用Windows API】杀掉指定进程（包括兄弟进程、子进程、父进程）

【Rust调用Windows API】杀掉指定进程（包括兄弟进程、子进程、父进程）

前言 前面一篇文章写了使用Rust调用Windows API 获取正在运行的全部进程信息 ，本篇实现杀掉指定进程。 通过标准库可以管理当前进程创建的子进程，要 kill 掉子进程也比较容易，这里不赘述了，主要实现通过调用Windows API来杀掉兄…
阅读更多...
基于Python的外卖点餐系统

基于Python的外卖点餐系统

作者：计算机学姐 开发技术：SpringBoot、SSM、Vue、MySQL、JSP、ElementUI、Python、小程序等，“文末源码”。 专栏推荐：前后端分离项目源码、SpringBoot项目源码、Vue项目源码、SSM项目源码、微信小程序源码 精品专栏：…
阅读更多...
世界坐标系、相机坐标系、图像物理坐标系、像素平面坐标系

世界坐标系、相机坐标系、图像物理坐标系、像素平面坐标系

坐标系及其转换在计算机视觉领域占据核心地位。理解如何从一个坐标系转换到另一个坐标系，不仅是理论上的需要，也是实际应用中不可或缺的技能。 一、世界坐标系的定义 世界坐标系是一个全局的坐标系统，用于定义场景中物体的位置。在这个坐标…
阅读更多...
最新文章

Copyright @ 2022~2023