在数据库管理中，高效处理大规模数据操作至关重要。一项常见的挑战是在大型表上执行批量删除而不降低整体性能。本文探讨 postgresql 的表分区功能如何显着加快进程
帮助维持平稳的数据库操作。
在这里查看我的更多作品。
大规模删除的挑战
从 postgresql 表中删除大量行可能是一项耗时的操作。它涉及：
- 扫描表以查找要删除的行
- 删除行并更新索引
- 用吸尘器清理桌子以回收空间
对于具有数百万行的表，此过程可能会导致长时间运行的事务和表锁定，可能会影响数据库响应能力。
输入表分区
表分区是一种将大表分为更小、更易于管理的部分（称为分区）的技术。这些分区是单独的表，与父表共享相同的架构。
我的基准设置
为了量化分区的好处，我在容器化环境中使用 postgresql 的三个场景设置了基准：
- 简单表：标准的非分区表
- 分区表（行删除）：按周分区的表，删除第一周的行
- 分区表（分区删除）： 与 #2 相同，但删除整个第一周的分区
postgresql 容器规范
- postgresql 版本：16.4
- docker 版本：27.0.3
- 资源限制：
- cpu 限制：8 个 cpu
- 内存限制：1 gb
数据特征
- 总记录：400万条
- 分配：4周内均匀分配（每周100万）
- 索引：两个表（简单表和分区表）在时间列上都有索引
主要发现
| scenario | deletion time | table size |
|---|---|---|
| simple table | 1.26s | 728 mb |
| partitioned (delete rows) | 734ms | 908 mb |
| partitioned (drop partition) | 6.43ms | 908 mb |
- 显着的速度提升：删除分区比从简单表中删除行快 196 倍。
- 存储权衡： 由于额外的元数据和每分区索引，分区表使用了大约 25% 的更多存储空间。
- 插入影响最小：分区仅略微增加了数据填充时间（约 2.8%）。
为什么它有效
- 有针对性的操作：分区允许数据库处理数据的子集，从而缩小操作范围。
- 元数据操作：删除分区主要是元数据操作，避免扫描和删除单个行的需要。
- 减少锁争用：较小的分区意味着更少的锁，从而实现更好的并发性。
实施亮点
这是如何在 postgresql 中设置分区表的简化示例：
CREATE TABLE records (<br>
id BIGSERIAL,<br>
time TIMESTAMPTZ NOT NULL,<br>
body TEXT<br>
) PARTITION BY RANGE (time);
<p>CREATE TABLE records_week_1 PARTITION OF records<br>
FOR VALUES FROM ('2023-01-01') TO ('2023-01-08');</p>
<p>-- Create index on the partition<br>
CREATE INDEX idx_records_week_1_time ON records_week_1 (time);</p>
<p>-- To delete a week's worth of data:<br>
ALTER TABLE records DETACH PARTITION records_week_1;<br>
DROP TABLE records_week_1;<br>
</p>
结论
对于处理时间序列数据的数据库或任何常见大规模删除的场景，实施表分区可以带来显着的性能提升。虽然存储和插入速度之间存在较小的权衡，但删除效率的收益通常远远超过这些成本。
通过利用分区，即使数据增长，您也可以保持高性能，确保您的 postgresql 数据库保持响应速度和高效。
完整基准代码和详细结果的链接
