透過表格分區優化 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中文網其他相關文章！