データ保存期間の設計｜アーカイブと削除方式の比較と一覧表例

データ保存期間の設計とは、テーブルごとに「いつから数えて、どれだけ残し、期限が来たらどう扱い、どの方式で実行するか」を決めて一覧表にすることです。 期間の数字だけを決めても、起算日と実行方式が決まっていないと処理が作れません。

この記事では、保存期間一覧表のテンプレートを Markdown とタブ区切りで示し、架空の備品予約システムでの記入例を付けます。あわせて、一括DELETE・分割DELETE・パーティションの切り離し・退避テーブルへの移動を MySQL と PostgreSQL の両方で 150 万件のテーブルに対して実行し、かかった時間と、外部キーで止まるときのエラー文をそのまま載せます。

確認環境：Ubuntu 24.04、PostgreSQL 16.15、MySQL 8.0.46（InnoDB）。確認日 2026-10-06。出力例のデータベース名は appdb に置き換えています。実行時間は手元の1台で各1回計った値で、機器やデータによって変わります。

この記事の結論

• 保存期間はテーブル単位で決める。 決めるのは「起算日・保持期間・期限後の扱い・実行方式」の4項目。
• 期限後の扱いは3つから選ぶ。 削除（消す）、退避（別の場所へ移す）、匿名化（行は残して個人が分かる列だけ消す）。
• 量が多いテーブルは日付パーティションの切り離し、少ないテーブルは分割DELETE。 今回の計測では、30万件を消すのに DELETE は 0.2〜7秒、パーティションの切り離しは 0.001〜0.07秒でした。
• 外部キーがあるテーブルは、子から先に消す。 親から消すとエラーで止まります。
• アーカイブ・バックアップ・論理削除は目的が違う。 後半の判断表で選べます。

データ保存期間の早見表（決める4項目と選択肢）

実行方式は、次の表で選べます。時間は後半の計測結果（150万件のうち約30万件が対象）です。

保存期間一覧表のテンプレートと記入例

1行が1テーブルです。空のテンプレートは次のとおりです。

テーブル    データの内容  起算日（列）  保持期間    期限後の扱い  実行方式    実行タイミング 月あたりの増加件数   期間の根拠   判断する担当

架空の備品予約システムでの記入例です。期間の数字は例で、実際の期間は適用される法令・契約・社内規程を確認して決めます。

テーブル    データの内容  起算日（列）  保持期間    期限後の扱い  実行方式    実行タイミング 月あたりの増加件数   期間の根拠   判断する担当
op_logs 操作ログ    記録日時（logged_at） 12か月    削除  月単位パーティションのDROP 毎月1日 2:00   100000  社内規程（調査に使う期間）   運用の担当
reservations    予約  利用日（reserved_on）    2年  退避（reservations_archive、退避後5年で削除）   退避テーブルへINSERTしてからDELETE 毎月1日 3:00   5000    社内規程（利用記録）  総務の担当
reservation_items   予約の明細   親の利用日   親と同じ    親と同時に退避 親より先に処理 毎月1日 3:00   12000   親に合わせる  総務の担当
members 会員  退会日時（withdrawn_at）  180日    匿名化（氏名とメールを消す）  UPDATE  毎日 4:00 20  利用規約    会員管理の担当
mail_outbox メール送信依頼 送信日時（sent_at）   90日 削除  分割DELETE（1万件ずつ） 毎日 4:30 30000   再送の調査に使う期間  運用の担当
sessions    ログインセッション   有効期限（expires_at）    1日  削除  分割DELETE（1万件ずつ） 毎時  60000   保持の必要なし 運用の担当
items   備品マスタ   ―   無期限 ―   ―   ―   10  予約から参照される   総務の担当

記入するときの要点は4つです。

• 「無期限」も1行として書く。 書いていないテーブルは「決めていない」のか「残すと決めた」のか区別できません。
• 起算日は列名まで書く。 「作成から1年」と「完了から1年」では、長く進行中のままの行の扱いが変わります。
• 親子のテーブルは続けて並べる。 子の期間を「親と同じ」と書けば、処理順（子が先）も表から読み取れます。
• 月あたりの増加件数を書く。 実行方式を選ぶ材料になります。保持期間 × 増加件数が、そのテーブルに常に残る行数の目安です（op_logs なら 12 × 100,000 = 120万件）。

期限後の扱い：削除・退避・匿名化の選び方

匿名化は UPDATE で行います。退会から 180 日を過ぎた会員の氏名とメールアドレスを消す例です（PostgreSQL 16 で実行。基準日は 2026-10-06 に固定）。

CREATE TABLE members (
  id bigint PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  withdrawn_at timestamptz,
  anonymized_at timestamptz
);
INSERT INTO members VALUES
 (1, '見本 太郎', 'taro@example.com', '2025-01-15 10:00+09', NULL),
 (2, '見本 花子', 'hanako@example.com', '2026-08-01 10:00+09', NULL),
 (3, '見本 次郎', 'jiro@example.com', NULL, NULL);

UPDATE members
SET name = '退会済み会員', email = NULL, anonymized_at = timestamptz '2026-10-06 00:00+09'
WHERE withdrawn_at < timestamptz '2026-10-06 00:00+09' - interval '180 days'
  AND anonymized_at IS NULL;

SELECT id, name, email, withdrawn_at::date AS withdrawn, anonymized_at::date AS anonymized
FROM members ORDER BY id;

UPDATE 1
 id |     name     |       email        | withdrawn  | anonymized 
----+--------------+--------------------+------------+------------
  1 | 退会済み会員 |                    | 2025-01-15 | 2026-10-06
  2 | 見本 花子    | hanako@example.com | 2026-08-01 | 
  3 | 見本 次郎    | jiro@example.com   |            | 

anonymized_at を持たせると、同じ行を何度も更新せずに済み、いつ処理したかも残ります。どの列を消せば十分かは、自社の規程と法務の確認に従って決めます。

計測に使ったテーブルとデータ

操作ログのテーブルに、2025-07-01 から 2026-09-30 までの 15 か月分・150 万件を等間隔で入れました。保持期間を 12 か月とし、2025-10-01 より前の 301,970 件（3か月分）を期限切れとして処理します。

PostgreSQL のデータ作成です。

CREATE TABLE op_logs_src AS
SELECT n::bigint AS id,
       timestamptz '2025-07-01 00:00+09'
         + (n - 1) * ((timestamptz '2026-10-01 00:00+09' - timestamptz '2025-07-01 00:00+09') / 1500000) AS logged_at,
       (n % 500) + 1 AS user_id,
       (ARRAY['login','reserve','cancel','return'])[n % 4 + 1] AS action,
       'item-' || (n % 200) AS detail
FROM generate_series(1, 1500000) AS n;

CREATE TABLE op_logs (
  id bigint PRIMARY KEY,
  logged_at timestamptz NOT NULL,
  user_id int NOT NULL,
  action text NOT NULL,
  detail text
);
INSERT INTO op_logs SELECT * FROM op_logs_src;
CREATE INDEX op_logs_logged_at_idx ON op_logs (logged_at);
VACUUM ANALYZE op_logs;

SELECT count(*) AS total,
       count(*) FILTER (WHERE logged_at < '2025-10-01 00:00+09') AS expired
FROM op_logs_src;

  total  | expired 
---------+---------
 1500000 |  301970

MySQL のデータ作成です。

SET SESSION cte_max_recursion_depth = 2000000;

CREATE TABLE op_logs_src (
  id bigint PRIMARY KEY,
  logged_at datetime NOT NULL,
  user_id int NOT NULL,
  action varchar(20) NOT NULL,
  detail varchar(50)
);
INSERT INTO op_logs_src
WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1500000)
SELECT n,
       TIMESTAMPADD(SECOND, FLOOR((n - 1) * TIMESTAMPDIFF(SECOND, '2025-07-01', '2026-10-01') / 1500000), '2025-07-01'),
       (n % 500) + 1,
       ELT(n % 4 + 1, 'login', 'reserve', 'cancel', 'return'),
       CONCAT('item-', n % 200)
FROM seq;

CREATE TABLE op_logs (
  id bigint PRIMARY KEY,
  logged_at datetime NOT NULL,
  user_id int NOT NULL,
  action varchar(20) NOT NULL,
  detail varchar(50),
  KEY idx_logged_at (logged_at)
);
INSERT INTO op_logs SELECT * FROM op_logs_src;
ANALYZE TABLE op_logs;

方式ごとに op_logs を作り直してから計っています。

方式1：一括DELETE と分割DELETE

一括DELETE

-- PostgreSQL
DELETE FROM op_logs WHERE logged_at < timestamptz '2025-10-01 00:00+09';

DELETE 301970
Time: 189.829 ms

-- MySQL
SET @t = NOW(6);
DELETE FROM op_logs WHERE logged_at < '2025-10-01';
SELECT ROW_COUNT() AS deleted, TIMESTAMPDIFF(MICROSECOND, @t, NOW(6)) / 1000 AS ms;

+---------+-----------+
| deleted | ms        |
+---------+-----------+
|  301970 | 7386.6740 |
+---------+-----------+

一括DELETE は書くのが最も簡単です。一方で、対象の全行を1つのトランザクションで処理するため、終わるまで対象行のロックを持ち続け、途中で止めると全部が巻き戻ります。対象が数万件までのテーブルに向いています。

PostgreSQL では、DELETE のあとにテーブルの大きさを見ると次のようになりました。

SELECT pg_size_pretty(pg_total_relation_size('op_logs')) AS size_after_delete;
VACUUM op_logs;
SELECT pg_size_pretty(pg_total_relation_size('op_logs')) AS size_after_vacuum;

 size_after_delete 
-------------------
 162 MB

VACUUM
Time: 122.995 ms

 size_after_vacuum 
-------------------
 162 MB

30 万件を消して VACUUM をかけても、ファイルの大きさは 162 MB のままでした。空いた領域は同じテーブルへの次の書き込みで再利用されますが、ディスクの空きとしてはすぐに戻りません。「ディスクを空けたい」が目的なら、後述のパーティションの削除が合っています。

分割DELETE（1万件ずつコミット）

1回に消す件数を決めて、対象がなくなるまで繰り返します。PostgreSQL の DELETE には LIMIT がないので、主キーをサブクエリで絞ります。プロシージャの中で COMMIT することで、1回ごとにロックを手放します。

-- PostgreSQL
CREATE OR REPLACE PROCEDURE purge_op_logs(p_before timestamptz, p_chunk int)
LANGUAGE plpgsql AS $$
DECLARE
  n bigint;
  total bigint := 0;
  loops int := 0;
BEGIN
  LOOP
    DELETE FROM op_logs
    WHERE id IN (SELECT id FROM op_logs
                 WHERE logged_at < p_before
                 ORDER BY logged_at
                 LIMIT p_chunk);
    GET DIAGNOSTICS n = ROW_COUNT;
    EXIT WHEN n = 0;
    total := total + n;
    loops := loops + 1;
    COMMIT;
  END LOOP;
  RAISE NOTICE 'deleted % rows in % loops', total, loops;
END $$;

CALL purge_op_logs(timestamptz '2025-10-01 00:00+09', 10000);

NOTICE:  deleted 301970 rows in 31 loops
CALL
Time: 5633.410 ms (00:05.633)

MySQL は DELETE に ORDER BY と LIMIT を書けます。自動コミットのまま呼ぶと、1回の DELETE ごとにコミットされます。

-- MySQL
DELIMITER //
CREATE PROCEDURE purge_op_logs(IN p_before datetime, IN p_chunk int)
BEGIN
  DECLARE n bigint DEFAULT 1;
  DECLARE total bigint DEFAULT 0;
  DECLARE loops int DEFAULT 0;
  WHILE n > 0 DO
    DELETE FROM op_logs WHERE logged_at < p_before ORDER BY logged_at LIMIT p_chunk;
    SET n = ROW_COUNT();
    SET total = total + n;
    IF n > 0 THEN SET loops = loops + 1; END IF;
  END WHILE;
  SELECT total AS deleted, loops;
END//
DELIMITER ;

SET @t = NOW(6);
CALL purge_op_logs('2025-10-01', 10000);
SELECT TIMESTAMPDIFF(MICROSECOND, @t, NOW(6)) / 1000 AS ms;

+---------+-------+
| deleted | loops |
+---------+-------+
|  301970 |    31 |
+---------+-------+
+-----------+
| ms        |
+-----------+
| 3416.9970 |
+-----------+

分割DELETE の目的は、合計時間を縮めることではなく、1回のロックとトランザクションを短くすることです。今回の PostgreSQL では、合計時間は一括の 0.19 秒に対して 5.6 秒と長くなりました。そのかわり、1回あたりは約 0.18 秒で終わり、途中で止めてもそこまでの分は消えたまま残るので、次回は続きから再開できます。利用者の操作と同じ時間帯に動かす処理では、繰り返しの間に短い待ち時間を入れると、ほかの処理に順番を譲れます。

方式2：日付パーティションを切り離す

テーブルを月ごとのパーティションに分けておくと、期限切れの月を表ごと外すだけで済みます。行を1件ずつ消さないので、件数が増えても時間がほとんど変わりません。

PostgreSQL：DROP と DETACH

CREATE TABLE op_logs_p (
  id bigint NOT NULL,
  logged_at timestamptz NOT NULL,
  user_id int NOT NULL,
  action text NOT NULL,
  detail text,
  PRIMARY KEY (id, logged_at)
) PARTITION BY RANGE (logged_at);

-- 2025-07 から 2026-09 まで、月ごとのパーティションを作る
DO $$
DECLARE d date := date '2025-07-01';
BEGIN
  WHILE d < date '2026-10-01' LOOP
    EXECUTE format(
      'CREATE TABLE op_logs_p_%s PARTITION OF op_logs_p FOR VALUES FROM (%L) TO (%L)',
      to_char(d, 'YYYYMM'),
      d::text || ' 00:00+09',
      (d + interval '1 month')::date::text || ' 00:00+09');
    d := (d + interval '1 month')::date;
  END LOOP;
END $$;

INSERT INTO op_logs_p SELECT * FROM op_logs_src;

期限切れの3か月を、3通りの書き方で外します。

-- (1) そのまま削除する
DROP TABLE op_logs_p_202507;

-- (2) 切り離して、独立した表として残す
ALTER TABLE op_logs_p DETACH PARTITION op_logs_p_202508;

-- (3) 親テーブルへの読み書きを止めずに切り離す
ALTER TABLE op_logs_p DETACH PARTITION op_logs_p_202509 CONCURRENTLY;

SELECT count(*) FROM op_logs_p;
SELECT count(*) AS detached_rows FROM op_logs_p_202508;

DROP TABLE
Time: 9.922 ms
ALTER TABLE
Time: 0.763 ms
ALTER TABLE
Time: 1.202 ms

  count  
---------
 1198030

 detached_rows 
---------------
        101751

3か月分・約30万件が、合計 12 ミリ秒ほどで親テーブルから外れました。切り離した op_logs_p_202508 は通常の表として残るので、ファイルへ書き出したあとで DROP できます。

PostgreSQL 16 の公式ドキュメントには、(1) と (2) は親テーブルに ACCESS EXCLUSIVE ロックを取り、(3) の CONCURRENTLY を付けた形は SHARE UPDATE EXCLUSIVE ロックで済むと書かれています。(3) を BEGIN の中で実行すると ERROR: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block になるので、トランザクションブロックの外で実行します。

MySQL：DROP PARTITION と EXCHANGE PARTITION

CREATE TABLE op_logs_p (
  id bigint NOT NULL,
  logged_at datetime NOT NULL,
  user_id int NOT NULL,
  action varchar(20) NOT NULL,
  detail varchar(50),
  PRIMARY KEY (id, logged_at)
)
PARTITION BY RANGE COLUMNS (logged_at) (
  PARTITION p202507 VALUES LESS THAN ('2025-08-01'),
  PARTITION p202508 VALUES LESS THAN ('2025-09-01'),
  PARTITION p202509 VALUES LESS THAN ('2025-10-01'),
  PARTITION p202510 VALUES LESS THAN ('2025-11-01'),
  PARTITION p202511 VALUES LESS THAN ('2025-12-01'),
  PARTITION p202512 VALUES LESS THAN ('2026-01-01'),
  PARTITION p202601 VALUES LESS THAN ('2026-02-01'),
  PARTITION p202602 VALUES LESS THAN ('2026-03-01'),
  PARTITION p202603 VALUES LESS THAN ('2026-04-01'),
  PARTITION p202604 VALUES LESS THAN ('2026-05-01'),
  PARTITION p202605 VALUES LESS THAN ('2026-06-01'),
  PARTITION p202606 VALUES LESS THAN ('2026-07-01'),
  PARTITION p202607 VALUES LESS THAN ('2026-08-01'),
  PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
  PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
INSERT INTO op_logs_p SELECT * FROM op_logs_src;

-- (1) そのまま削除する
SET @t = NOW(6);
ALTER TABLE op_logs_p DROP PARTITION p202507;
SELECT TIMESTAMPDIFF(MICROSECOND, @t, NOW(6)) / 1000 AS drop_ms;

-- (2) 同じ定義の空の表と中身を入れ替えて、独立した表として残す
CREATE TABLE op_logs_202508 (
  id bigint NOT NULL,
  logged_at datetime NOT NULL,
  user_id int NOT NULL,
  action varchar(20) NOT NULL,
  detail varchar(50),
  PRIMARY KEY (id, logged_at)
);
SET @t = NOW(6);
ALTER TABLE op_logs_p EXCHANGE PARTITION p202508 WITH TABLE op_logs_202508;
SELECT TIMESTAMPDIFF(MICROSECOND, @t, NOW(6)) / 1000 AS exchange_ms;

-- 入れ替え後、空になったパーティションを外す
ALTER TABLE op_logs_p DROP PARTITION p202508;

SELECT (SELECT COUNT(*) FROM op_logs_p) AS main,
       (SELECT COUNT(*) FROM op_logs_202508) AS detached;

+---------+
| drop_ms |
+---------+
| 71.0440 |
+---------+
+-------------+
| exchange_ms |
+-------------+
|      8.9040 |
+-------------+
+---------+----------+
| main    | detached |
+---------+----------+
| 1296498 |   101751 |
+---------+----------+

MySQL には DETACH がありません。EXCHANGE PARTITION で空の表と中身を入れ替えると、同じ結果になります。一括DELETE が約 7.4 秒だったのに対し、DROP PARTITION は 0.07 秒でした。

パーティションにするときの前提

パーティションの切り離しは速いかわりに、テーブル定義に条件が付きます。どちらも実際に出たエラーです。

MySQL 8.0 のリファレンスマニュアルにも、InnoDB の外部キーとパーティションは併用できず、パーティション表は外部キーを持てず、外部キーから参照されることもできないと書かれています。外部キーが必要なテーブルは分割DELETE か退避、外部キーが要らないログ・履歴はパーティションという分け方になります。

方式3：退避テーブルへ INSERT してから DELETE

期限後も検索できるようにしたいデータは、同じ列を持つ退避テーブルへ移します。INSERT と DELETE を同じトランザクションに入れ、同じ条件を使うのが要点です。

-- PostgreSQL
CREATE TABLE op_logs_archive (
  LIKE op_logs INCLUDING DEFAULTS,
  archived_at timestamptz NOT NULL DEFAULT now()
);

BEGIN;
INSERT INTO op_logs_archive (id, logged_at, user_id, action, detail)
SELECT id, logged_at, user_id, action, detail
FROM op_logs WHERE logged_at < timestamptz '2025-10-01 00:00+09';
DELETE FROM op_logs WHERE logged_at < timestamptz '2025-10-01 00:00+09';
COMMIT;

SELECT (SELECT count(*) FROM op_logs) AS main,
       (SELECT count(*) FROM op_logs_archive) AS archive;

INSERT 0 301970
Time: 253.341 ms
DELETE 301970
Time: 213.719 ms
COMMIT
Time: 5.440 ms

  main   | archive 
---------+---------
 1198030 |  301970

PostgreSQL は、DELETE の RETURNING を使って1文にまとめることもできます。今回の時間は 469 ms で、2文に分けた場合（合計 472 ms）とほぼ同じでした。

WITH moved AS (
  DELETE FROM op_logs WHERE logged_at < timestamptz '2025-10-01 00:00+09'
  RETURNING id, logged_at, user_id, action, detail
)
INSERT INTO op_logs_archive (id, logged_at, user_id, action, detail)
SELECT * FROM moved;

INSERT 0 301970
Time: 468.883 ms

MySQL は2文で書きます。

-- MySQL
CREATE TABLE op_logs_archive (
  id bigint PRIMARY KEY,
  logged_at datetime NOT NULL,
  user_id int NOT NULL,
  action varchar(20) NOT NULL,
  detail varchar(50),
  archived_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

START TRANSACTION;
INSERT INTO op_logs_archive (id, logged_at, user_id, action, detail)
SELECT id, logged_at, user_id, action, detail FROM op_logs WHERE logged_at < '2025-10-01';
DELETE FROM op_logs WHERE logged_at < '2025-10-01';
COMMIT;

SELECT (SELECT COUNT(*) FROM op_logs) AS main,
       (SELECT COUNT(*) FROM op_logs_archive) AS archive;

INSERT: 301970 行  2131.2270 ms
DELETE: 301970 行  7078.2710 ms
COMMIT までの合計: 9267.9660 ms

+---------+---------+
| main    | archive |
+---------+---------+
| 1198030 |  301970 |
+---------+---------+

（MySQL の時間は、各文の前後で NOW(6) の差を取った値を並べています。）

退避テーブルを設計するときの決めごとは次の3つです。

外部キーがあるテーブルの削除順

親（予約）と子（予約の明細）が外部キーでつながっているとき、親から消すと止まります。

CREATE TABLE reservations (id bigint PRIMARY KEY, reserved_on date NOT NULL);
CREATE TABLE reservation_items (
  id bigint PRIMARY KEY,
  reservation_id bigint NOT NULL REFERENCES reservations (id),
  item_name text NOT NULL
);
INSERT INTO reservations VALUES (1, '2024-05-10'), (2, '2024-06-20'), (3, '2026-09-01');
INSERT INTO reservation_items VALUES
  (1, 1, 'プロジェクター'), (2, 1, 'スクリーン'), (3, 2, '会議用マイク'), (4, 3, 'ノートPC');

DELETE FROM reservations WHERE reserved_on < date '2025-10-01';

PostgreSQL 16 のエラーです。

ERROR:  update or delete on table "reservations" violates foreign key constraint "reservation_items_reservation_id_fkey" on table "reservation_items"
DETAIL:  Key (id)=(1) is still referenced from table "reservation_items".

MySQL 8.0 のエラーです（item_name を varchar(50)、制約名を fk_items_reservation として同じ表を作成）。

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`appdb`.`reservation_items`, CONSTRAINT `fk_items_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`))

子から先に、親と同じ条件で消します。

BEGIN;
DELETE FROM reservation_items
WHERE reservation_id IN (SELECT id FROM reservations WHERE reserved_on < date '2025-10-01');
DELETE FROM reservations WHERE reserved_on < date '2025-10-01';
COMMIT;

SELECT (SELECT count(*) FROM reservations) AS reservations,
       (SELECT count(*) FROM reservation_items) AS items;

DELETE 3
DELETE 2
COMMIT
 reservations | items 
--------------+-------
            1 |     1

削除順の決め方

どのテーブルがどのテーブルを参照しているかは、システムカタログから一覧にできます。

-- PostgreSQL
SELECT conrelid::regclass AS child, confrelid::regclass AS parent
FROM pg_constraint
WHERE contype = 'f' AND connamespace = 'public'::regnamespace
ORDER BY 1;

       child       |    parent    
-------------------+--------------
 reservation_items | reservations

-- MySQL
SELECT table_name AS child, referenced_table_name AS parent, constraint_name
FROM information_schema.referential_constraints
WHERE constraint_schema = 'appdb';

+-------------------+--------------+----------------------+
| child             | parent       | CONSTRAINT_NAME      |
+-------------------+--------------+----------------------+
| reservation_items | reservations | fk_items_reservation |
+-------------------+--------------+----------------------+

この一覧から、次の手順で順番を決めます。

1. どこからも参照されていないテーブル（child にだけ出てくるもの）を最初に処理する。
2. 処理済みのテーブルを一覧から除き、残りの中でどこからも参照されていないものを次に処理する。
3. これを繰り返す。最後に残るのが、いちばん上の親です。
4. 子の対象は「親の条件」で決める。子の日付で決めると、親が残っているのに明細だけ消える行が出ます。

外部キーに ON DELETE CASCADE を付けておけば、親を消すと子も消えます。ただし保存期間の処理では、退避が必要な子を、退避する前に消してしまうことになりやすいので、順番を明示して子から処理する形が扱いやすくなります。

PostgreSQL では、パーティション表が親（参照される側）の場合も同じです。子の行が残っているパーティションを切り離そうとすると止まります。

CREATE TABLE orders_p (
  id bigint NOT NULL, ordered_at date NOT NULL, PRIMARY KEY (id, ordered_at)
) PARTITION BY RANGE (ordered_at);
CREATE TABLE orders_p_2024 PARTITION OF orders_p FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_p_2025 PARTITION OF orders_p FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE order_items (
  id bigint PRIMARY KEY, order_id bigint NOT NULL, ordered_at date NOT NULL,
  FOREIGN KEY (order_id, ordered_at) REFERENCES orders_p (id, ordered_at)
);
INSERT INTO orders_p VALUES (1, '2024-03-01'), (2, '2025-03-01');
INSERT INTO order_items VALUES (1, 1, '2024-03-01'), (2, 2, '2025-03-01');

ALTER TABLE orders_p DETACH PARTITION orders_p_2024;

ERROR:  removing partition "orders_p_2024" violates foreign key constraint "order_items_order_id_ordered_at_fkey1"
DETAIL:  Key (order_id, ordered_at)=(1, 2024-03-01) is still referenced from table "order_items".

子の行を先に消すと、切り離しと削除が通ります。

DELETE FROM order_items WHERE ordered_at < date '2025-01-01';
ALTER TABLE orders_p DETACH PARTITION orders_p_2024;
DROP TABLE orders_p_2024;

DELETE 1
ALTER TABLE
DROP TABLE

アーカイブ・バックアップ・論理削除の違いと選び方

3つとも「データを残す」ように見えますが、目的が違います。

バックアップはアーカイブの代わりになりません。 バックアップから1行だけを取り出すには復元の作業が必要で、保持世代を過ぎると消えます。逆に、退避テーブルも本体と同じデータベースにあれば同じ障害の影響を受けるので、バックアップの対象に含めます。

保存期間の処理を作る手順

1. テーブルを全部並べる。 無期限のものも含めて一覧表に書く。
2. 起算日の列を決める。 列がなければ追加する（完了日時、退会日時など）。
3. 保持期間と根拠を書く。 根拠が書けない行は、判断する担当と期間を相談する。
4. 期限後の扱いを選ぶ。 削除・退避・匿名化。
5. 増加件数から実行方式を選ぶ。 外部キーの有無も確認する。
6. 親子の処理順を決める。 参照の一覧から、子を先にする。
7. 期限切れの件数を数える SQL を先に作る。 処理の前後で件数を記録する。
8. 少ない件数で試してから、定期実行に載せる。 初回は溜まった分が多いので、分割して流す。

実行方式を選ぶ判断表です。

確かめて分かったこと

• 分割DELETE は合計時間が短くなるとは限らない。 PostgreSQL では一括 0.19 秒に対して分割は 5.6 秒でした。MySQL では一括 7.4 秒に対して分割 3.4 秒でした。分割の利点は、1回のロックが短いことと、途中から再開できることです。
• PostgreSQL は DELETE と VACUUM のあともファイルの大きさが変わらなかった（162 MB のまま）。 空きは同じテーブルで再利用されます。ディスクを空ける目的ならパーティションの DROP が合います。
• パーティションの切り離しは桁が違う。 約10万件の1か月分が、PostgreSQL の DETACH で 0.8 ms、MySQL の EXCHANGE PARTITION で 8.9 ms でした。
• MySQL には DETACH がなく、EXCHANGE PARTITION で空の表と入れ替える。 入れ替えたあと、空になったパーティションを DROP します。
• MySQL のパーティション表には外部キーを付けられない（ERROR 1506）。 外部キーが必要なテーブルは別の方式を選びます。
• PostgreSQL は、子から参照されている行があるパーティションを切り離せない。 エラー文に、残っている子のキーが表示されます。

よくある質問

Q. データの保存期間はどうやって決めればよいですか？ A. テーブルごとに「起算日・保持期間・期限後の扱い・実行方式」の4項目を一覧表に書いて決めます。期間の数字は、法令・契約・社内規程と、そのデータを調べる必要がある期間から決め、根拠の列に書き残します。

Q. アーカイブとバックアップの違いは何ですか？ A. アーカイブは使わなくなったデータを本体から移して保管すること、バックアップは壊れたデータを元に戻すための写しです。アーカイブすると本体は小さくなり、バックアップでは本体は変わりません。

Q. 大量の行を DELETE すると時間がかかるときはどうすればよいですか？ A. 1万件ずつなどに分けてコミットする分割DELETE にするか、日付パーティションにして期限切れの月を表ごと外します。今回の計測では、30万件の DELETE が MySQL で約 7.4 秒、同じ量を含むパーティションの DROP は 0.07 秒でした。

Q. 外部キーのエラーで削除できないときはどうすればよいですか？ A. 参照している側（子）のテーブルから先に、親と同じ条件で消します。参照の関係は、PostgreSQL なら pg_constraint、MySQL なら information_schema.referential_constraints で一覧にできます。

Q. 論理削除した行はいつまで残せばよいですか？ A. 論理削除の行にも保持期間を決め、一覧表に書きます。期限を過ぎたら、退避テーブルへ移すか削除して、本体のテーブルが増え続けないようにします。

まとめ

• 保存期間はテーブル単位で、起算日・保持期間・期限後の扱い・実行方式の4項目を一覧表で決める。
• 期限後の扱いは、削除・退避・匿名化の3つから選ぶ。 無期限のテーブルも1行として書く。
• 量が多いログ・履歴は日付パーティションにして、期限切れの月を表ごと外す。 30万件が 0.001〜0.07 秒で外れた。
• 量が少ないテーブルや外部キーがあるテーブルは分割DELETE。 目的は合計時間ではなく、ロックを短く保つこと。
• 退避は INSERT と DELETE を同じトランザクション・同じ条件で行い、件数を記録する。
• 外部キーがあるときは子から先に消す。 参照の一覧をカタログから取り、順番を決める。
• アーカイブ・バックアップ・論理削除は目的が違う。 バックアップはアーカイブの代わりにならない。

参考・出典

• PostgreSQL 16 Documentation「5.11. Table Partitioning」 https://www.postgresql.org/docs/16/ddl-partitioning.html （2026-10-06 確認）
• MySQL 8.0 Reference Manual「Partitioning Limitations Relating to Storage Engines」 https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-storage-engines.html （2026-10-06 確認）

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。