HN 日本語サマリー

← 一覧へ戻る
インフラ・DevOps

管理不要なDBパーティションの設計

Designing DB partitions you don't have to babysit (explainanalyze.com)

17 pointsby rtolkachev1 コメント

要約

データベースのパーティショニングにおいて、`created_at`ではなく主キーでパーティション化し、バックグラウンドサービスが観測された成長に基づいて境界を管理するアプローチが提案されています。これにより、クエリは既存のキーを使用し続け、パーティションプルーニングが自動的に機能し、パーティション列がアプリケーションコードに漏れ出すことを防ぎます。この「サービスが監視・調整する」パターンは、ハッシュパーティショニングやリストパーティショニングにも適用可能です。

全文翻訳

ベビーシッター不要なDBパーティションの設計

[画像: Designing Partitioning You Don't Have to Babysit のアイキャッチ画像]

データベースアーキテクチャ システム設計

導入から6ヶ月後、p_futureパーティションには8億行が格納されています。これは、ワークロードが当初の成長予測を上回ったためであり、それを修正するためのALTER操作には誰もスケジュールしたくないメンテナンスウィンドウが必要です。境界管理は2行のDDLですが、より難しいのはアプリケーションコードに漏れ出さないパーティションキーを選択することです。

By Ruslan Tolkachev

要約

created_atではなく主キーでパーティション化し、バックグラウンドサービスに観測された成長に基づいて境界を管理させましょう。クエリは既存のキーを使い続け、パーティションプルーニングは自動的に機能し、パーティションカラムがアプリケーションコードに漏れ出すことはありません。同じ「監視・調整サービス」パターンは、異なる操作を伴うハッシュパーティショニングやリストパーティショニングにも適用できます。

パーティショニング導入の1週間後、注文ダッシュボードの読み込みが遅くなり始めました。チームの最初の直感は、新しいインデックス戦略を非難することでした。実際の原因はEXPLAINに現れます。id = 12345という単純なクエリで、Partitions: orders_p2025_01, orders_p2025_02, ...という36行が表示されます。WHERE句にcreated_atが含まれていないため、created_atがパーティションキーであるため、プランはすべてのパーティションを読み取ります。かつて1回のインデックスプローブだった検索が、36回になってしまいました。

提案された修正策は、常に提案されるものです。ダッシュボードクエリにcreated_at >= '2024-11-01'を追加します。これは機能します。プランは1つのパーティションに削減されます。次に監査ページも同様のことを行い、管理ツールも、移行スクリプトも同様です。3ヶ月後、日付フィルタなしのSELECT FROM ordersをフラグする内部リンタルールが導入され、コードレビューには「パーティションフィルタを追加しましたか?」という標準チェックが含まれます。パーティションキーはストレージの決定事項ではなくなり、すべてのクエリが遵守しなければならない契約となりました。忘れてもエラーは発生しません。ただ遅くなるだけです。

パーティションキーの問題

PostgreSQLとMySQLの両方で、主キーまたは一意制約にパーティションキーを含める必要があります。このルールは正確性のために存在します。主キーにパーティションキーが含まれていない場合、データベースはすべてのパーティションをスキャンせずに一意性を強制できません。

その結果、created_atでパーティション化したい場合、もはやPRIMARY KEY (id)だけでは不十分です。PRIMARY KEY (id, created_at)が必要になります。日付カラムは、アプリケーションが必要としていなくても、主キーの一部となってしまいます。

より微妙なコストは、idがデータベースにとってユニークではなくなることです。一意性はタプル(id, created_at)で強制されます。データベースは、異なるタイムスタンプを持つ限り、同じidを持つ2つの行を喜んで受け入れます。アプリケーションはおそらくidをユニークとして扱いますが、スキーマではそれを保証するものはありません。また、個別のUNIQUE (id)制約で保証を回復することもできません。MySQLとPostgreSQLの両方で、パーティションテーブルの一意制約にはパーティションキーカラムを含める必要があります。実質的に、一意性のプロパティは失われています。

これは単なる見た目の問題ではありません。クエリオプティマイザが生成できるクエリプランを変更します。

  • PRIMARY KEY (id)の場合、WHERE id = 1は定数時間検索です。MySQLのEXPLAINではこれをconstアクセスタイプとして表示します。オプティマイザは一致する行が1つだけであることを知っており、実行者はそれを見つけたら停止します。idでの結合はeq_refとなり、最も高速な結合アクセスタイプです。
  • PRIMARY KEY (id, created_at)の場合、同じクエリはref検索になります。データベースの観点からは、複数の行を返す可能性がある左端のインデックスカラムのプレフィックススキャンです。かつてeq_refだった結合はrefになります。カーディナリティ推定値は、データベースが保証する「1行」という仮定ではなく、インデックス統計にフォールバックするため、クエリツリーの上位でより悪いプランに向かわせる可能性があります。

古いconstプランを取り戻すには、すべての検索で完全な主キーを指定する必要があります。

-- かつてはconst検索、今はref検索(多数の行の可能性あり)
SELECT * FROM orders WHERE id = 1;

-- constに戻るが、呼び出し元がcreated_atを知っている場合のみ
SELECT * FROM orders WHERE id = 1 AND created_at = '2026-04-01 12:34:56';

これは、別の角度からのパーティションプルーニングと同じ漏洩です。パーティションキーは、日付とは無関係のクエリに、まずプルーニングを得るため、そして今度は単一行アクセスを得るために、強制的に入り込んできました。

-- パーティショニング前
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total_cents INT NOT NULL,
    created_at DATETIME NOT NULL
);

-- 月ごとのパーティショニング後
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    total_cents INT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)  -- created_atがPKに強制的に追加された
)
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')),
    ...
);

この時点ではすべて正常に動作します。テーブルは挿入を受け入れ、クエリは正しい結果を返し、パーティション境界が存在します。問題は、created_atをWHERE句に含めないクエリを実行したときに初めて現れます。

パーティションプルーニングは要求されて初めて機能する

パーティションプルーニングは、パーティショニングを価値あるものにする最適化です。クエリのWHERE句がパーティションキーを制限すると、データベースは一致する可能性のないパーティションをスキップできます。先週の注文に対するクエリは、先週のデータを含む1つまたは2つのパーティションのみを読み取ります。

その最適化は、パーティションキーがWHERE句に現れることに依存します。それ以外のものをフィルタリングするクエリはプルーニングされず、すべてのパーティションをスキャンします。

-- このクエリはすべてのパーティション(36個)をスキャンします。
SELECT * FROM orders WHERE id = 12345;

-- こちらは1つのパーティションにプルーニングされます
SELECT * FROM orders WHERE id = 12345 AND created_at >= '2026-03-01' AND created_at < '2026-04-01';

最初のクエリは、主キーで注文を取得するという、常に発生する種類の検索です。非パーティションテーブルでは、これは単一のインデックスシークです。プルーニングキーがWHERE句にないパーティションテーブルでは、各パーティションに対して個別のインデックスプローブが行われます。つまり、1回のインデックスプローブではなく36回のインデックスルックアップになります。絶対的な速さではまだ速いですが、パーティショニングが導入された理由とは逆の結果になります。

チームが通常採用する「修正」は、テーブルに触れるすべてのクエリにパーティションキーを追加することです。それはリーキーアブストラクションです。ストレージの決定が、すべての呼び出し元との契約になります。新しいコードはパーティションフィルタを記憶する必要があり、古いコードは監査される必要があり、ORMはこの周りで設定する必要があります。

エラーなし、ただ遅いだけ

プルーニングされるべきなのにされないクエリは、正しい結果を返します。プランはすべてのパーティションをスキャンするだけです。例外も警告もアプリケーションログのフラグもなく、誰も読まないEXPLAINだけが、ダッシュボードがタイムアウトするまで放置されます。ほとんどのチームは、クエリ実行中にデータベースが表面化するものからではなく、パーティションデプロイ後にスロークエリログをレビューすることで、失敗を発見します。

静的なパーティション境界は長持ちしない

もう一つ起こりがちなのは、テーブル作成時にパーティション境界をハードコーディングすることです。初期レイアウトは、その時点でのチームの成長予測を反映しています。6ヶ月後にはトラフィックパターンが変化し、一部のパーティションが他のパーティションの10倍の大きさになり、p_futureのキャッチオールパーティションがテーブルの半分を占めるようになります。

-- 作成時に定義:妥当に見える
PARTITION p2026_q1 VALUES LESS THAN (100000000),
PARTITION p2026_q2 VALUES LESS THAN (200000000),
...

-- 6ヶ月後:成長が加速し、p_futureがアクティブなワークロード全体を占める
PARTITION p_future VALUES LESS THAN MAXVALUE  -- 8億行、増加中

パーティションを手動で分割・再分散するのは、誰も担当したくない運用作業です。メンテナンスウィンドウのスケジュール設定、数百ギガバイトになる可能性のあるテーブルに対するALTER TABLE ... REORGANIZE PARTITIONの実行、アプリケーションチームとの調整、そして間違いをしないことが必要です。パフォーマンスインシデントが発生するまで、これは行われません。そしてその時点では、修正は高価になります。

より良いアプローチの形状

主キーは既に存在します。BIGINT AUTO_INCREMENTを使用するテーブルでは、単調増加します。つまり、新しい行はより大きなIDを持ちます。これがレンジパーティショニングが必要とするプロパティです。主キーがパーティションキーです。

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total_cents INT NOT NULL,
    created_at DATETIME NOT NULL
)
PARTITION BY RANGE (id) (
    PARTITION p0001 VALUES LESS THAN (100000000),
    PARTITION p0002 VALUES LESS THAN (200000000),
    PARTITION p0003 VALUES LESS THAN (300000000),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

idでフィルタリングするすべてのクエリ(ほとんどのクエリ)は、アプリケーションコードの変更なしに、パーティションプルーニングを無料で取得できます。IDによるレンジクエリは、少数のパーティションにプルーニングされます。ポイントルックアップは正確に1つにプルーニングされます。主キーは、重要なすべてのWHERE句に既に存在するため、パーティションキーです。

トレードオフは、パーティション境界がもはや時間によって直接定義されないことです。これは、時間ベースの保持ポリシーを破るように見えます。実際には、これは見た目ほどトレードオフではありません。パーティショニングのポイントは、多くの場合、保持ではなく、インデックスサイズを管理可能に保つこと、メンテナンス操作を安価にすること、そして悪いクエリの被害範囲を限定することです。保持が目標である場合、境界は保持ポリシーに合わせて選択することもできます。それらはスキーマに焼き付けられるのではなく、DDLタイムにパーティショナーサービスによって選択されるだけです。日付をキーにしない時間整合性のある境界を参照してください。

レンジパーティション管理の自動化

ここまでのすべてはレンジパーティショニングを前提としています。パーティションは、順序付けられた値(IDレンジ、日付レンジ)の連続した境界によって定義されます。運用作業は機械的です。アクティブなパーティションが満杯になるのを監視し、それが発生する前にMAXVALUEキャッチオールを新しい境界付きパーティションに分割し、保持しきい値を超えたパーティションを削除します。スケジュールで実行される小さなサービスがあれば、レイアウトを健全に保つのに十分です。難しいのはロジックではなく、安全に行うことです。ロックなしで大規模テーブルに対してDDLを実行し、部分的な障害を処理し、サービスが操作中にクラッシュした場合にクリーンに回復することです。

-- キャッチオールパーティションを新しい境界付きパーティションと新しいキャッチオールに分割
-- これはサービスが定期的に実行する操作です
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p0037 VALUES LESS THAN (3700000000),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

空のキャッチオールパーティションに対するREORGANIZE PARTITIONは高速です。移動するデータはありません。分割ポイントより上にデータが着地する前にキャッチオールを分割した場合、操作はメタデータのみです。サービスのジョブは、書き込みワークロードに先んじることです。キャッチオールがまだ小さいうち、または空のうちに分割し、数百万人もの行を既に保持しているときではありません。

本番環境でサービスをトリッキーにするもの

ロジックは数行のDDL文です。難しい部分はそれらを取り巻くすべてです。REORGANIZE中に書き込みをロックアウトしないこと、DDL途中でサービスがクラッシュしても生き残ること(リトライ時の冪等性)、同時にACCESS EXCLUSIVEロックを取得している他の移行ツールを処理すること、そして「サービスが停止し、キャッチオールが2億行になった、どうすればよいか」という明確なランブックを持つことです。本番グレードのパーティショナーは、通常、DDL自体よりも操作のブラケットに多くのコードを費やします。

唯一の正しいターゲットはありません。それは、そもそもパーティショニングを推進しているものによります。目標が保持によってOLTPのワーキングセットを小さく保つことである場合、境界の間隔はビジネス上の決定です。データはホットストアでどれくらいの期間クエリ可能であるべきか、1年、7年、その間か。目標がパフォーマンスである場合、各パーティションをそのインデックスがメモリに快適に収まるようにサイズ設定することは、合理的な経験則です。ただし、単一のパーティションに読み書きを集中させるような、重大なキーのスキューがないことが前提です。サービスはどちらかの目標に対して設定でき、観測された成長に基づいて境界の間隔を調整できます。

日付をキーにしない時間整合性のある境界

idでパーティショニングしても、時間ベースの境界を諦めることを意味しません。後からそれらを選択するだけです。サービスは、任意の時点のID境界を見つけるために、ライブテーブルに対して単一のクエリを実行できます。

-- 3月の初めのIDポインタはどこでしたか?
SELECT MAX(id) FROM orders WHERE created_at < '2026-03-01';
-- -> 3700842139

その値が、次の境界付きパーティションの上限になります。キャッチオールはその上に留まり、将来のパーティションは時間整合性のあるID境界でカットされます。

-- パーティションは依然としてIDレンジで定義されますが、月境界に整合するように選択されます
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p2026_03 VALUES LESS THAN (3700842140),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

結果として得られるパーティションp2026_03は、おおよそ2026年3月全体の注文を含みますが、created_atは主キーに現れることはなく、プルーニングを得るためにWHERE句に含める必要もなく、アプリケーションコードに漏れ出すこともありません。日付カラムは、サービスがDDLを実行する際に、境界作成時に一度だけ使用されます。クエリはidでフィルタリングを続け、無料でプルーニングを得ます。

保持も同様に機能します。12ヶ月より古いデータを削除するには、サービスはSELECT MAX(id) FROM orders WHERE created_at < NOW() - INTERVAL 12 MONTHを実行し、そのIDより下にある上限を持つすべてのパーティションを特定し、それらを削除します。MAXVALUEキャッチオールがこのパターンを機能させるものです。サービスが次のカットを決定している間、新しい行が着地する場所が常にあります。

サービスの外観

サービス自体は小さいです。スケジュールで実行され(高スループットテーブルの場合は毎時、低速なテーブルの場合は毎日)、各ティックでいくつかのことを行います。

  • インベントリ。 カタログから現在のパーティションレイアウトを読み取ります:パーティション名、上限、おおよその行数。
  • サイズチェック。 アクティブなパーティション(キャッチオール直下の境界付きパーティション)を確認します。ターゲットサイズの構成済みしきい値を超えて満杯になっている場合、次の境界をカットする時期です。
  • 境界選択。 カットする場所を選択します。時間整合性のあるパーティションの場合、次の月境界時点のIDをライブテーブルにクエリします。そのIDが新しいパーティションの上限になります。
  • 分割。 キャッチオールを新しい境界付きパーティションと、その上に新しいキャッチオールに再編成します。分割実行時にキャッチオールがまだ空である限り、これはメタデータのみです。
  • 保持プルーニング。 保持ウィンドウ(例:12ヶ月)を、同じcreated_atからidへのルックアップを通じてIDに変換し、そのIDより下にある上限を持つすべてのパーティションを削除します。
  • 同時実行ガード。 単一のアドバイザリロックまたはリーダー選出により、2つのインスタンスが同じテーブルに対して同時にDDLを実行しないようにします。
  • メトリクスとアラート。 パーティションごとのサイズと行数、前回のティックからの時間、アクティブパーティションがサービスが分割するよりも速く満杯になり始めた場合の明確なアラート。

cronジョブ、Kubernetes CronJob、または小さな常時稼働ワーカーとして実行します。運用フットプリントは意図的に小さいです。本番対応作業の大部分は、同時実行ガード、命名規則、および分割失敗の処理に費やされます。これらはすべて、サービスが各ティックで行うこと(カタログ読み取り数回とDDLステートメント1つ)を変更しません。

既存のツールとこのパターンの違い

自動化自体は新しいものではありません。いくつかのツールは既にスケジュールでパーティション境界を管理しています。アプローチの違いは、どのカラムでパーティショニングが行われるか、そしてその選択がスキーマ契約のどれだけをロックインするかです。

pg_partmanは、PostgreSQLエコシステムで広く使用されているパーティションマネージャーです。スケジュールで将来のパーティションを事前作成し、保持ウィンドウに対して古いパーティションを削除し、非パーティションテーブルをインプレースでパーティションテーブルに移行できます。そのデフォルト(およびそれの上に書かれたほとんどのチュートリアル)は、timestampカラムでの時間ベースのレンジパーティショニングを想定しています。これは、前のセクションで反対しているパターンです。日付カラムが主キーに含まれ、すべてのクエリに漏れ出します。

TimescaleDBはさらに進んでいます。その「ハイパーテーブル」は自動的に時間パーティション化されたPostgreSQLテーブルであり、チャンク作成、保持、圧縮はすべて拡張機能によって管理されます。これは、すべてのクエリが実際に時間でスコープされているワークロード(オブザーバビリティ、IoTテレメトリ、追記専用イベントストリーム)に適したツールです。一部のクエリは時間でスコープされ、他のクエリはそうでないOLTPテーブルには不向きです。なぜなら、時間カラムは必須であり、時間外のすべてのクエリは、手動の時間パーティショニングと同じパーティションキーをWHERE句に含める税金を支払うからです。

Vitessは、より広範なMySQLシャーディングソリューションの一部としてパーティション管理を含んでいます。そのパーティショニング規約は柔軟ですが、ほとんどの本番環境では時間ベースのデフォルトに落ち着きます。

これら3つの共通点:自動化レイヤーは、パーティションキーが事前に選択されている(通常は時間カラム)と想定し、その仮定の上に境界を管理します。

この記事のアプローチは、自動化パターン(小さなサービス、キャッチオールを事前に分割、保持の後ろを削除)を維持しますが、キーの選択を変更します。パーティションキーは主キーであり、時間整合性はSELECT MAX(id) WHERE created_at < Xルックアップを通じてDDLタイムで導出されます。スキーマレベルの契約はPRIMARY KEY (id)のままです。時間ベースの保持は、クエリごとではなく、境界ごとに計算されることで引き続き機能します。

トレードオフは、サービスの所有です。pg_partmanは十分にテストされた拡張機能です。DIYパーティショナーは実際の運用サーフェスエリアです:アドバイザリロック、障害回復、メトリクス、アラート。有用な質問は、created_atが既にすべての重要なクエリに自然なフィルタであるかどうかです。すべてのクエリが設計上時間でスコープされている場合(オブザーバビリティ、テレメトリ、監査ログ、あらゆる時系列データ)、時間カラムに対するpg_partmanまたはTimescaleDBが正しい答えです。パーティションキーは、既にそこにあったため漏れ出していません。

ワークロードが混合している場合(一部のクエリは日付でフィルタリングし、ほとんどはそうしない)は、PKにcreated_atを追加すると選択を迫られます。すべての非時間クエリを日付フィルタでレトロフィットするか、ポイントルックアップでの完全なパーティションスキャンを許容するかです。どちらのオプションもオーバーヘッドであり、それが既にスロークエリとして可視化されているか、単に日付とは無関係のクエリに「パーティショニングのため」としてAND created_at >= ?句が追加されることでコードベース全体に広がるかです。その時点で、小さなDDLサービスを所有することは、パーティションキーをすべての呼び出し元に永遠に伝播させるよりも安価です。

ハッシュパーティショニングとリストパーティショニングの自動化

同じ「サービスが監視・調整する」という考え方は、他のパーティショニング戦略にも転移しますが、操作は異なります。

ハッシュパーティショニングは、ハッシュ関数を介して固定数のパーティションに行を分散します。カーディナリティが高い場合、すべてのパーティションはほぼ同じ速度で成長します。成長に基づいて分割するものは何もありません。監視すべきはスキューです。低カーディナリティのカラムまたはホットキーは、他のパーティションよりも速く成長する1つのパーティションを生成します。これはパーティショニングが防止しようとした障害モードです。

ここでは自動化は境界の調整に関するものではありません。ハッシュパーティション数を変更すると、テーブル全体が再構築されます。これはバックグラウンドサービスがトリガーすべきものではありません。有用な作業は検出です。パーティションごとのサイズと成長を追跡し、早期にスキューを表面化させ、パーティションが肥大化するにつれてOPTIMIZEまたはVACUUM FULLを実行します。サービスは問題をフラグし、人間がテーブルを再形成するかどうかを決定します。結果として、ハッシュパーティション数は最初に正しく決定する必要があるという決定になります。過剰なプロビジョニング(今日16で十分な場合に64パーティション)は、後で完全な再構築に対する安価な保険です。

リストパーティショニングは、列挙された値を特定のパーティションにマッピングします。各リージョン、各重要なテナントごとに1つのパーティションがあり、残りはDEFAULTキャッチオールにあります。自動化の問題はパーティション昇格です。キャッチオールの値が独自のパーティションに値するほど大きくなったときです。

-- 初期状態:既知の大きなテナントには名前付きパーティション、残りはDEFAULT
CREATE TABLE events (
    id BIGINT AUTO_INCREMENT,
    tenant_id BIGINT NOT NULL,
    payload JSON NOT NULL,
    PRIMARY KEY (id, tenant_id)
)
PARTITION BY LIST (tenant_id) (
    PARTITION p_tenant_42 VALUES IN (42),
    PARTITION p_tenant_73 VALUES IN (73),
    PARTITION p_default VALUES IN (DEFAULT)
);

-- サービスは、テナントID=108がp_defaultの15%を占め、急速に成長していることに気づきます。
-- それは、そのテナントを独自のパーティションに昇格させます。
ALTER TABLE events REORGANIZE PARTITION p_default INTO (
    PARTITION p_tenant_108 VALUES IN (108),
    PARTITION p_default VALUES IN (DEFAULT)
);

昇格は、空のレンジキャッチオールを分割するよりも高価です(そのテナントの行は物理的にDEFAULTから新しいパーティションに移動する必要があります)。しかし、低トラフィック時間帯にバッチ処理してスケジュールできます。休眠中の値は、パーティション数を制限するために、逆方向にDEFAULTにマージできます。

パーティションキーに実際に属するもの

どのパーティショニングスキームを採用する前に問う価値のある質問:どのカラムが既にすべての重要なクエリに含まれていますか?ほとんどのOLTPシステムでは、答えは主キーです。それはすべてのルックアップ、すべての結合、すべての外部キーフェッチに含まれるため、それをパーティションキーとして使用するとプルーニングが無料になります(キー自体がシーケンシャルである場合。ランダムUUIDキーは挿入をすべてのパーティションのBツリーに散らばらせます)。他の答えは現実的ですが、より狭いです。tenant_idは、すべてのクエリがテナントスコープであるマルチテナントシステムで機能します。日付カラムは、ワークロードが時系列であり、すべてのクエリが既に日付でフィルタリングされている場合に機能します。これらの条件が満たされない場合、パーティションキーは、誰かがそれを含まないクエリを最初に記述したときにアプリケーションコードに漏れ出します。

失敗モードは、既にすべてのクエリに含まれていないカラムでパーティショニングし、その後、それを追加するためにクエリレイヤーをレトロフィットすることです。それは、日付とは無関係のクエリに、36パーティションスキャンを回避するためだけに、AND created_at >= ?を貼り付けるパスにつながります。