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