テーブルパーティショニングによるPostgreSQLの一括削除の最適化

データベース管理では、大規模なデータ操作を効率的に処理することが重要です。一般的な課題の 1 つは、全体のパフォーマンスを低下させることなく、大きなテーブルに対して一括削除を実行することです。この記事では、PostgreSQL のテーブル パーティショニング機能がプロセスと
をどのように大幅に高速化できるかについて説明します。 スムーズなデータベース操作の維持に役立ちます。

ここで私の作品をもっとチェックしてください。

大量削除の課題

PostgreSQL テーブルから多数の行を削除するのは、時間のかかる操作になる可能性があります。これには以下が含まれます:

1. テーブルをスキャンして削除する行を見つけます
2. 行の削除とインデックスの更新
3. テーブルに掃除機をかけてスペースを再利用します

数百万行のテーブルの場合、このプロセスによりトランザクションの長時間実行やテーブルのロックが発生し、データベースの応答性に影響を与える可能性があります。

テーブルのパーティショニングを開始する

テーブルのパーティショニングは、大きなテーブルをパーティションと呼ばれる、より管理しやすい小さな部分に分割する手法です。これらのパーティションは、親テーブルと同じスキーマを共有する個別のテーブルです。

私のベンチマーク設定

パーティショニングの利点を定量化するために、コンテナ化された環境で PostgreSQL を使用する 3 つのシナリオでベンチマークを設定しました。

1. 単純なテーブル: 標準のパーティション化されていないテーブル
2. パーティション化テーブル (行削除): 週ごとにパーティション化されたテーブル。最初の週から行を削除します
3. パーティション化テーブル (パーティションの削除): #2 と同じですが、最初の週のパーティション全体を削除します

PostgreSQLコンテナの仕様

• PostgreSQL バージョン: 16.4
• Docker バージョン: 27.0.3
• リソース制限:
  • CPU 制限: 8 CPU
  • メモリ制限: 1 GB

データの特性

• 総レコード: 400 万件
• 配分: 4 週間にわたって均等に配分 (1 週間あたり 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 中国語 Web サイトの他の関連記事を参照してください。