PostgreSQL 创建分区及数据迁移

最新推荐文章于 2024-07-14 07:42:23 发布

小蓝在开发

最新推荐文章于 2024-07-14 07:42:23 发布

阅读量802

点赞数

分类专栏： bug is friend 文章标签： python postgresql 数据库触发器

本文链接：https://blog.csdn.net/qq_14907917/article/details/107432423

版权

本文探讨了随着数据量增加导致的查询性能问题，并介绍了数据库表分区作为解决方案。详细阐述了PostgreSQL中如何实现表分区，包括创建子表、触发器的设置以及数据迁移过程。通过测试，证明了按时间范围查询在分区表上的高效性，适合用于后台数据显示。最后，提到了使用Python进行数据迁移和分区操作的封装方法。

摘要由CSDN通过智能技术生成

分区原因

随着使用时间的增加，数据库中的数据量也不断增加，因此数据库查询越来越慢。

加速数据库的方法很多，如添加特定的索引，将日志目录换到单独的磁盘分区，调整数据库引擎的参数等。这些方法都能将数据库的查询性能提高到一定程度。

对于许多应用数据库来说，许多数据是历史数据并且随着时间的推移它们的重要性逐渐降低。如果能找到一个办法将这些可能不太重要的数据隐藏，数据库查询速度将会大幅提高。可以通过DELETE来达到此目的，但同时这些数据就永远不可用了。（查询效率是指：使用分区时的字段作为过滤条件）

因此，需要一个高效的把历史数据从当前查询中隐藏起来并且不造成数据丢失的方法。本文即将介绍的数据库表分区即能达到此效果。

数据库表分区术语介绍

主表/父表/Master Table　该表是创建子表的模板。它是一个正常的普通表，但正常情况下它并不储存任何数据。
子表/分区表/Child Table/Partition Table　这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表

表分区在Postgresql 的实现：

-- 创建表 y_mgr_biz_day
CREATE TABLE "y_mgr_biz_day" (
  "mgrid" int4 NOT NULL,
  "rdate" date NOT NULL,
  "typ" int2 NOT NULL,
  "bizid" int4 NOT NULL,
  "q_click" int4 DEFAULT 0,
  "q_share" int4 DEFAULT 0,
  "q_deal" int4 DEFAULT 0,
  "q_money" int4 DEFAULT 0
);
-- 插入数据
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-06', 1, 1, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-06', 1, 6, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-06', 2, 1, 2, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-06', 0, 3, 3, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-25', 2, 3, 2, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (605, '2023-03-25', 2, 1, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (605, '2023-03-25', 0, 4, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (605, '2023-03-25', 1, 6, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-06', 1, 3, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-26', 1, 235, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (648, '2023-03-31', 1, 6, 7, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-06', 1, 1, 7, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-06', 1, 6, 2, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-06', 1, 2, 10, 1, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-07', 2, 1, 3, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-26', 2, 3, 4, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-07', 0, 8, 17, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (608, '2023-03-08', 0, 4, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-07', 1, 5, 30, 4, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-07', 0, 8, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (614, '2023-03-31', 1, 6, 0, 2, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (605, '2023-03-26', 2, 3, 2, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-06', 1, 1, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-06', 1, 6, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-06', 2, 1, 2, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-06', 0, 3, 3, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-25', 2, 3, 2, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (605, '2023-03-25', 2, 1, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (605, '2023-03-25', 0, 4, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (605, '2023-03-25', 1, 6, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-06', 1, 3, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-26', 1, 235, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (648, '2023-03-31', 1, 6, 7, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-06', 1, 1, 7, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-06', 1, 6, 2, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-06', 1, 2, 10, 1, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-07', 2, 1, 3, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (598, '2023-03-26', 2, 3, 4, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-03-07', 0, 8, 17, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (608, '2023-03-08', 0, 4, 1, 0, 0, 0);
INSERT INTO "y_mgr_biz_day" VALUES (597, '2023-0