通过表分区优化 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万）
• 索引：两个表（简单表和分区表）在时间列上都有索引

主要发现

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中文网其他相关文章！