通过表分区优化 PostgreSQL 批量删除

在数据库管理中，高效处理大规模数据操作至关重要。一项常见的挑战是在大型表上执行批量删除而不降低整体性能。本文着眼于 PostgreSQL 的表分区功能如何显着加快进程
帮助维持平稳的数据库操作。

在这里查看我的更多作品。

大规模删除的挑战

从 PostgreSQL 表中删除大量行可能是一项耗时的操作。它涉及：

1. 扫描表以查找要删除的行
2. 删除行并更新索引
3. 用吸尘器清理桌子以回收空间

对于具有数百万行的表，此过程可能会导致长时间运行的事务和表锁定，可能会影响数据库响应能力。

输入表分区

表分区是一种将大表分为更小、更易于管理的部分（称为分区）的技术。这些分区是单独的表，与父表共享相同的架构。

我的基准设置

为了量化分区的好处，我在容器化环境中使用 PostgreSQL 的三个场景设置了基准：

1. 简单表：标准的非分区表
2. 分区表（行删除）：按周分区的表，删除第一周的行
3. 分区表（分区删除）： 与 #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

1. 显着的速度提升：删除分区比从简单表中删除行快 196 倍。
2. 存储权衡： 由于额外的元数据和每分区索引，分区表使用了大约 25% 的存储空间。
3. 插入影响最小：分区仅略微增加了数据填充时间（约 2.8%）。

为什么它有效

1. 有针对性的操作：分区允许数据库处理数据的子集，从而缩小操作范围。
2. 元数据操作：删除分区主要是元数据操作，避免扫描和删除单个行的需要。
3. 减少锁争用：较小的分区意味着更少的锁，从而实现更好的并发性。

实施亮点

这是如何在 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 数据库保持响应速度和高效。

完整基准代码和详细结果的链接

以上是通过表分区优化 PostgreSQL 批量删除的详细内容。更多信息请关注PHP中文网其他相关文章！