チューニング

パーティショニング — 大きなテーブルを分ける技法

パーティショニングの用途と PostgreSQL 宣言的パーティション・MySQL PARTITION BY、制約上の注意点。

パーティショニングとは

パーティショニングは、論理的には 1 つのテーブルを、物理的には複数の小さなテーブル（パーティション）に分割する技法です。代表的な分割キーは日付（月ごと / 年ごと）やid のハッシュ / 範囲。

主な嬉しさ:

古いデータをまるごと切り離し / 削除できる: パーティション単位での DROP は DELETE より桁違いに速い（VACUUM 負荷もなし）
スキャン範囲を限定（パーティションプルーニング）: WHERE date >= '2026-01-01' なら該当パーティションだけ読む
インデックスが小さく保てる: 全体 1 つの巨大 B-Tree より、小さい B-Tree × N のほうがキャッシュ効率が良い
バルク ETL / メンテが楽: パーティション単位で REINDEX や VACUUM を分割実行可能

PostgreSQL: 宣言的パーティション

PostgreSQL 10 以降は宣言的パーティションが使え、親テーブルに PARTITION BY を指定して、子パーティションを個別に作成します。種類は RANGE / LIST / HASH。

PostgreSQL 11 以降は DEFAULT パーティション（どれにも該当しないデータの受け皿）や、ATTACH PARTITION での既存テーブル取り込みも可能。14 以降は更新によるパーティション跨ぎ移動も効率化されました。

sql

-- 月次 RANGE パーティション
CREATE TABLE orders (
  id BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  user_id BIGINT NOT NULL,
  total NUMERIC NOT NULL,
  PRIMARY KEY (id, created_at)  -- パーティションキーを含める
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_01 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE orders_2026_02 PARTITION OF orders
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- 古いパーティションを切り離し
ALTER TABLE orders DETACH PARTITION orders_2025_01;
-- DROP TABLE orders_2025_01;  -- 退避が済んだら削除

MySQL: PARTITION BY

MySQL も CREATE TABLE ... PARTITION BY 構文でパーティショニング可能です（RANGE / LIST / HASH / KEY）。InnoDB では MySQL 8.0 以降、ネイティブパーティションが標準です。

自動的な未来パーティション追加機能はない（PostgreSQL にもない）ので、月次で次月パーティションを事前に作成する cron / pg_partman / MySQL Event が現場の定番です。

sql

-- MySQL: 月次 RANGE パーティション
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  user_id BIGINT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

-- 古いパーティションを一瞬で削除
ALTER TABLE orders DROP PARTITION p202501;

ユニーク制約と外部キーの罠

パーティションテーブルにはいくつか制約があります:

ユニーク制約 / 主キーにはパーティションキーを含める必要がある（PostgreSQL / MySQL 共通）。PRIMARY KEY (id) だけで済ませたいのに、(id, created_at) にしないといけない、となる典型ハマりどころ
MySQL では外部キーが使えない（パーティション表を FK の参照先・参照元どちらにもできない）
PostgreSQL は 11 以降で FK の参照元になれるが、参照先には制約あり（親パーティションを指せない）
グローバルに一意にしたいキーがあると設計がかなり面倒になる → 「複合ユニーク + 生成 id は UUID v7」のような折衷案が現実的

採用する前に、本当にこの規模で必要かを見極めるのが大事。数千万行程度なら、適切なインデックス + 通常テーブルで十分なことも多いです。

いつ導入するか

判断のおおまかな目安:

導入したほうが良い: 「古いデータを定期的に削除 / アーカイブ」要件がある、テーブルが数億行〜数十億行、日付範囲で絞るクエリが大半、メンテナンス時間を短縮したい
まだ早い: 全件スキャンや全パーティションをまたぐ検索が多い（プルーニングが働かないと逆に遅い）、外部キーが必須のモデル、チームに運用知見がない

迷ったらまずインデックス最適化とアーカイブ用の別テーブル + 定期バッチ移動で戦い、限界が見えてからパーティションに切り替えるのが堅実です。