zukucode
主にWEB関連の情報を技術メモとして発信しています。
PostgreSQL

PostgreSQL 期限切れデータを少しずつ削除する設計

期限切れの一時データを定期的に削除する場合、対象をすべて一度に消すと、件数が増えたときに処理時間やトランザクションが大きくなることがあります。

今回は、削除対象を小さなまとまりに区切り、繰り返し処理する方法を紹介します。

ここで扱うのは、保存期間を決めて削除してよい一時データです。注文履歴や監査記録などの保存方針を、この例の期限へ一律に合わせるものではありません。

件数を区切る目的

たとえば、期限切れデータが1,200件あり、1回に500件削除するとします。

処理の流れ
500件を削除してコミット
500件を削除してコミット
200件を削除してコミット
対象が500件未満だったので終了

コミットは、トランザクション内の変更を確定することです。全体を1つの大きなトランザクションにせず、処理単位ごとに確定します。

途中で止まった場合も、すでに確定した削除をやり直す必要はありません。次回は、まだ残っている期限切れデータから処理できます。

PostgreSQLでは先に削除対象を選ぶ

PostgreSQLのDELETEには直接のLIMIT句がないため、先に件数を絞って対象を選ぶ方法を使えます。PostgreSQL公式ドキュメントでも、この考え方が紹介されています。

以下は構造を説明するSQLです。temporary_recordsは、一意なidと変更しない有効期限expires_atを持ち、他のデータから参照されていない一時データのテーブルとします。

1回分の削除SQL
WITH targets AS (
    SELECT id
    FROM temporary_records
    WHERE expires_at < @Cutoff
    ORDER BY expires_at, id
    LIMIT @BatchSize
)
DELETE FROM temporary_records AS records
USING targets
WHERE records.id = targets.id;

@Cutoffと@BatchSizeは、データアクセス処理から渡すパラメーターです。この表記のままSQLコンソールへ貼って実行する例ではありません。

この例は1つの削除処理だけを実行する前提です。並行する削除や、有効期限を延長する更新もある場合は、対象行のロックや削除条件の再確認など、競合を扱う設計が追加で必要です。

削除の基準時刻を1回だけ決める

ループごとに現在時刻を取得すると、処理中に新しく期限切れになったデータも対象へ入ってきます。

最初に基準時刻を決めておけば、「今回の実行では、この時刻より古いものを処理する」と範囲を固定できます。

ただし、古い期限を持つ行が後から追加される場合などは、基準時刻を固定するだけで件数が有限になるとは限りません。そのため、実行ごとの回数上限も設ける例にします。

CleanupJob.cs
using System;
using System.Threading;
using System.Threading.Tasks;

public interface IExpiredRecordStore
{
    // 1バッチの削除を確定してから、削除件数を返す
    Task<int> DeleteBatchAsync(
        DateTimeOffset cutoff,
        int batchSize,
        CancellationToken cancellationToken);
}

public sealed class CleanupJob(IExpiredRecordStore store)
{
    public async Task<int> RunAsync(
        DateTimeOffset cutoff,
        CancellationToken cancellationToken)
    {
        const int batchSize = 500;
        const int maxBatches = 20;
        var total = 0;

        for (var batch = 0; batch < maxBatches; batch++)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var deleted = await store.DeleteBatchAsync(
                cutoff, batchSize, cancellationToken);

            if (deleted < 0 || deleted > batchSize)
                throw new InvalidOperationException("削除件数が想定範囲外です。");

            total += deleted;
            if (deleted < batchSize) break;
        }

        return total;
    }
}

.NET 8以降で使えるC#の制御部分です。データベース接続は省略しています。呼び出し側で決めた同じcutoffを毎回渡します。

DeleteBatchAsyncには、1バッチ分をコミットしてから削除件数を返す実装が必要です。名前やinterfaceだけで、その保証が付くわけではありません。

最後に0件の削除があっても問題ない

対象がちょうど1,000件なら、500件、500件、0件という呼び出しになります。

500件返された段階では、まだ対象があるかはわかりません。次の呼び出しで0件となり、終了します。

回数上限へ到達した場合は、対象が残っている可能性があります。上限到達を全件削除完了と扱わず、次回の定期実行で続きを処理します。運用で区別したいなら、戻り値へ上限到達の情報も追加します。

途中停止を扱いやすい対象に絞る

この例は、残っている行を再び検索すれば続きを処理できるため、途中停止から再開しやすい構成です。

一方、1行を削除する前にメールを送信するなど、外部への処理も含む場合は、同じ方法だけでは足りません。削除前に止まると、次回にメールを重複送信する可能性があります。

削除と外部処理をまとめたい場合は、別途重複対策や処理状態の保存を検討します。

再実行による重複処理を防ぐ考え方

小分けでも負荷が小さいとは限らない

500件という数字に特別な意味はありません。1件の大きさ、外部キー、削除トリガー、検索条件などで負荷は変わります。

親を500件消すと大量の子データも削除される構成なら、実際の変更件数は500件を超えることがあります。このSQLの例は、関連データの連鎖削除を扱っていません。

また、対象を探すために毎回テーブル全体を読むなら、削除件数だけを小さくしても検索の負荷は残ります。expires_atとidの並びに合うインデックスが役立つかを、実行計画とデータ量から確認します。

確認項目見たいこと
対象が0件1回の取得で終了する
対象が500件未満1バッチで終了する
対象が500件の倍数最後の0件で終了できる
途中で停止要求が来る次のバッチへ進まず停止できる
回数上限に到達する残りを次回へ回せる
複数回の呼び出し同じ基準時刻が渡される

削除を小分けにする目的は、単にループを書くことではありません。1回の処理範囲と確定の単位を決め、途中で止まっても残りを扱いやすくすることです。

PostgreSQL

関連記事