データベースのテーブル・パーティショニング

caution
以下にない質問がある場合は、このイシューに追加してください。@gitlab-org/database-team/triage にタグ付けしていただければ、できるだけ早く回答いたします。Slack で回答が得られた場合は、将来このドキュメントを更新できるように、イシューにも記録してください。

テーブル・パーティショニングは強力なデータベース機能で、テーブルのデータを小さな物理テーブルに分割し、1つの大きなテーブルとして機能させることができます。アプリケーションがパーティショニングを念頭に置いて動作するように設計されている場合、次のような複数の利点があります：

  • クエリのパフォーマンスが大幅に向上します。データベースは、SQL の完全な機能を提供しながら、検索空間からデータの多くを安価に削除できるためです。

  • パーティション全体を削除することで、データベースへの影響を最小限に抑えながら一括削除を行うことができます。これは、リテンションウィンドウから外れたデータを定期的に削除する必要がある機能に自然に適合します。

  • VACUUM やインデックスの再構築のような管理タスクは、単一の巨大なテーブル全体ではなく、個々のパーティションに対してオペレーションできます。

残念ながら、すべてのモデルがパーティショニング・スキームに適合するわけではありません。さらに、テーブルは作成時にしかパーティショニングできないため、多忙なデータベースにパーティショニングを適用するのは容易ではありません。バックエンド開発者が既存のテーブルをパーティショニングできるように、一連のマイグレーション・ツールが用意されていますが、マイグレーション・プロセスはかなり重く、複数のリリースにまたがって複数のステップを踏む必要があります。パーティショニングとそれに関連するマイグレーションには限界があるため、この機能を活用しようとする前に、パーティショニングがどのようにユースケースに適合するかを理解する必要があります。

パーティショニングを使用するタイミングの決定

パーティショニングは適切に適用されれば非常に便利ですが、テーブルのデータと作業負荷がパーティショニング・スキームに自然に適合するかどうかを見極めることが不可欠です。パーティショニングが特定の問題に適しているかどうかを判断するために、いくつかの詳細を理解してください：

  • テーブル・パーティショニング。パーティション・キーは、パーティション間でデータをどのように分割するかを決定するカラムまたはカラムのセットです。パーティション・キーはデータベースがデータを読み書きする際に使用し、どのパーティションにアクセスするかを決定します。パーティション・キーは、そのテーブルにアクセスするほとんどすべてのクエリのWHERE 節に含まれるカラムでなければなりません。

  • データの分割方法。データベースはどのような方法でデータをパーティションに分割するのでしょうか？利用可能な選択肢は、range 、hash 、list です。

適切なパーティショニング戦略を決定します。

利用可能なパーティショニング戦略の選択肢は、range 、hash 、list です。

レンジ・パーティショニング

GitLabマイグレーションヘルパーが最もよくサポートするスキームは、日付範囲パーティショニングです。この場合、パーティショニングキーはタイムスタンプか日付カラムでなければなりません。このタイプのパーティショニングがうまく機能するためには、ほとんどのクエリは特定の日付範囲のデータにアクセスしなければなりません。

より具体的な例として、audit_events 。これは、アプリケーションデータベースでパーティショニングされた最初のテーブルです（GitLab 13.5リリースでデプロイ予定）。このテーブルは、アプリケーションで発生したセキュリティイベントの監査エントリを追跡します。ほとんどの場合、ユーザーは特定の時間枠で発生した監査アクティビティを見たいのです。その結果、日付範囲のパーティショニングは、データへのアクセス方法として自然に適合しました。

これをより詳細に見るために、単純化したaudit_events スキーマを想像してください：

CREATE TABLE audit_events (
  id SERIAL NOT NULL PRIMARY KEY,
  author_id INT NOT NULL,
  details jsonb NOT NULL,
  created_at timestamptz NOT NULL);

ここで、UIにおける典型的なクエリが、1週間のような特定の日付範囲のデータを表示するとします：

SELECT *
FROM audit_events
WHERE created_at >= '2020-01-01 00:00:00'
  AND created_at < '2020-01-08 00:00:00'
ORDER BY created_at DESC
LIMIT 100

テーブルがcreated_at 列でパーティショニングされている場合、ベーステーブルは次のようになります：

CREATE TABLE audit_events (
  id SERIAL NOT NULL,
  author_id INT NOT NULL,
  details jsonb NOT NULL,
  created_at timestamptz NOT NULL,
  PRIMARY KEY (id, created_at))
PARTITION BY RANGE(created_at);
note
パーティショニングされたテーブルのプライマリ・キーには、プライマリ・キーの定義の一部としてパーティション・キーを含める必要があります。

また、テーブルのパーティションのリストが以下のようにあるかもしれません：

audit_events_202001 FOR VALUES FROM ('2020-01-01') TO ('2020-02-01')
audit_events_202002 FOR VALUES FROM ('2020-02-01') TO ('2020-03-01')
audit_events_202003 FOR VALUES FROM ('2020-03-01') TO ('2020-04-01')

各パーティションは、audit_events テーブルと同じ構造を持つ別の物理テーブルですが、パーティション・キーが指定された範囲内にある行のデータのみを含みます。例えば、パーティションaudit_events_202001 には、created_at 列が2020-01-01 以上、2020-02-01以下の行が含まれます。

先ほどの例のクエリをもう一度見てみると、データベースはWHERE 、一致する行がすべてaudit_events_202001 パーティションにあることを認識できます。すべてのパーティション内のデータを検索するのではなく、適切なパーティション内の単月分のデータのみを検索することができます。大きなテーブルでは、データベースがアクセスする必要のあるデータ量を劇的に減らすことができます。しかし、次のようなパーティショニング・キーに基づくフィルタリングを行わないクエリを想像してみてください：

SELECT *
FROM audit_events
WHERE author_id = 123
ORDER BY created_at DESC
LIMIT 100

この例では、一致するデータがどのパーティションにも存在する可能性があるため、データベースはどのパーティションも検索から除外することができません。その結果、各パーティションを個別にクエリし、行を1つの結果セットに集約する必要があります。author_id 、インデックスが作成されるため、パフォーマンスへの影響は許容範囲内でしょうが、より複雑なクエリではオーバーヘッドが大きくなる可能性があります。パーティショニングは、データのアクセスパターンがパーティショニング戦略をサポートしている場合にのみ活用すべきです。

ハッシュ・パーティショニング

ハッシュ・パーティショニングは論理テーブルを一連のパーティショニングされたテーブルに分割します。各パーティションは、ハッシュと余りにマッチするID範囲に対応します。例えば、BY HASH(id) をパーティショニングする場合、hash(id) % 64 == 1 の行はパーティションWITH (MODULUS 64, REMAINDER 1)に入ります。

ハッシュ・パーティショニングを行う場合、アプリケーションから発行されるパフォーマンス重視のクエリには必ずWHERE hashed_column = ? 条件を含める必要があります。これが不可能な場合、ハッシュ・パーティショニングはユースケースに適していない可能性があります。

ハッシュ・パーティショニングには主な利点が1つあります。それは、1つの数値id 列に対して一意性を強制できる唯一のパーティショニング・タイプであるということです。(レンジ・パーティショニングでも可能ですが、正しい選択であることは稀です）。

ハッシュ・パーティショニングには欠点もあります：

  • パーティションの数を前もって知っておく必要があります。
  • 現在のパーティションが大きくなりすぎると、新しいデータを余分なパーティションに移すことが難しくなります。
  • WHERE id BETWEEN ? and ? のような範囲クエリはサポートされていません。
  • WHERE other_id = ? のような他のキーによる検索はサポートされていません。

このため、将来的なテーブルの増加に対応するために、多くのハッシュ・パーティションを選択した方がよい場合があります。

テーブルのパーティショニング（範囲）

残念ながら、テーブルのパーティショニングは作成時にしか行えないため、多忙なデータベースに適用するのは容易ではありません。バックエンド開発者が既存のテーブルをパーティショニングできるように、一連のマイグレーションツールが開発されました。このマイグレーションプロセスは複数のリリースにまたがる複数のステップを必要とします。

注意点

パーティショニングマイグレーションヘルパーは、元のテーブルのパーティショニングされた複製を作成し、トリガとバックグラウンドマイグレーションを組み合わせて新しいテーブルにデータをコピーすることで動作します。パーティショニングマイグレーションと並行して元のテーブルスキーマに変更を加えることができますが、マイグレーションを機能させる基本的なメカニズムを壊さないように注意しなければなりません。例えば、パーティショニングされるテーブルにカラムが追加された場合、パーティショニングされるテーブルとトリガ定義の両方が一致するように更新されなければなりません。

ステップ1: パーティショニングコピーの作成（リリースN）

最初のステップは、元のテーブルのパーティショニングされたコピーを作成するマイグレーションを追加することです。このマイグレーションは、元のテーブルのデータに基づいて適切なパーティションを作成し、元のテーブルからの書き込みをパーティションドコピーに同期させるトリガーをインストールします。

audit_events テーブルをcreated_at カラムでパーティショニングするマイグレーション例は以下のようになります：

class PartitionAuditEvents < Gitlab::Database::Migration[2.1]
  include Gitlab::Database::PartitioningMigrationHelpers

  def up
    partition_table_by_date :audit_events, :created_at
  end

  def down
    drop_partitioned_table_for :audit_events
  end
end

これが実行されると、元のテーブルでの挿入、更新、削除は新しいテーブルにも複製されます。更新と削除については、対応する行がパーティショニングされたテーブルに存在する場合にのみオペレーションが有効になります。

ステップ2: パーティション分割されたコピーの埋め戻し（リリースN）

2番目のステップは、元のテーブルからパーティショニングされたコピーに既存のデータを埋め戻すバックグラウンド・ジョブをスケジュールするデプロイメント後のマイグレーションを追加することです。

上記の例を続けると、マイグレーションは次のようになります：

class BackfillPartitionAuditEvents < Gitlab::Database::Migration[2.1]
  include Gitlab::Database::PartitioningMigrationHelpers

  disable_ddl_transaction!

  restrict_gitlab_migration gitlab_schema: :gitlab_main

  def up
    enqueue_partitioning_data_migration :audit_events
  end

  def down
    cleanup_partitioning_data_migration :audit_events
  end
end

このステップでは、BATCH_SIZE と SUB_BATCH_SIZE を50,000 と2,500として、内部でバッチバックグラウンドマイグレーションをキューに入れます。詳細については、Batched Backgroundマイグレーションガイドを参照してください。

ステップ3: バックフィル後のクリーンアップ (リリースN+1)

このステップは、ステップ(2)を含むリリースの少なくとも1リリース後に実行する必要があります。これにより、セルフマネージドインストールでバックグラウンドマイグレーションが適切に実行される時間が得られます。このステップでは、バックグラウンドマイグレーションの後始末を行うデプロイメント後のマイグレーションをもう1つ追加します。これには、残っているジョブを強制的に実行させることや、ジョブの取りこぼしや失敗によって失われた可能性のあるデータをコピーすることが含まれます。

もう一度例を続けると、このマイグレーションは次のようになります：

class CleanupPartitionedAuditEventsBackfill < Gitlab::Database::Migration[2.1]
  include Gitlab::Database::PartitioningMigrationHelpers

  disable_ddl_transaction!

  restrict_gitlab_migration gitlab_schema: :gitlab_main

  def up
    finalize_backfilling_partitioned_table :audit_events
  end

  def down
    # no op
  end
end

このマイグレーションが完了すると、元のテーブルとパーティショニングされたテーブルには同一のデータが含まれるはずです。この移行が完了した後、元のテーブルとパーティショニングされたテーブルには同一のデータが含まれるはずです。 元のテーブルにインストールされたトリガは、データが今後も同期していることを保証します。

ステップ4: パーティション分割されたテーブルとパーティション分割されていないテーブルの入れ替え（リリースN+1）

このステップは、パーティション分割されていないテーブルをパーティション分割されたコピーに置き換えます。

この方法のいくつかの制限は、スワップマイグレーション前、またはマイグレーション中に処理しなければなりません（MUST）：

  • セカンダリ・インデックスと外部キーは、パーティショニングされたテーブルに自動的に再作成されません。
  • インデックスに依存するいくつかのタイプの制約（UNIQUEとEXCLUDE）は、基礎となるインデックスが存在しないため、パーティショニングされたテーブルでは自動的に再作成されません。
  • 元のパーティショニングされていないテーブルを参照する外部キーは、パーティショニングされたテーブルを参照するように更新されなければなりません。これはPostgreSQL 11ではサポートされていません。
  • 元のテーブルを参照するビューは、パーティショニングされたテーブルを参照するように自動的に更新されません。
# frozen_string_literal: true

class SwapPartitionedAuditEvents < ActiveRecord::Migration[6.0]
  include Gitlab::Database::PartitioningMigrationHelpers

  def up
    replace_with_partitioned_table :audit_events
  end

  def down
    rollback_replace_with_partitioned_table :audit_events
  end
end

このマイグレーションが完了すると、以下のようになります：

  • パーティショニングされたテーブルは、パーティショニングされていない（元の）テーブルを置き換えます。
  • 以前に作成された同期トリガは削除されます。

パーティショニングされたテーブルはアプリケーションで使用できるようになりました。

テーブルのパーティショニング (Hash)

ハッシュパーティショニングは、データをIDのハッシュに基づいてパーティションに分割します。これは、テーブルに対するほとんどのクエリがWHERE id = ? のような句を含んでいる場合にのみうまく機能し、PostgreSQLは要求されたIDに基づいてどのパーティションを検索するかを決定することができます。

もう1つの重要な欠点は、ハッシュパーティショニングではテーブル作成後にパーティションを追加できないことです。正しいパーティション数を前もって選択しなければなりません。

ハッシュ・パーティショニングは、データベース・レベルで複数のパーティションにまたがるIDの一意性を保証できる唯一のパーティショニングです。

テーブルのパーティショニング（リスト）

GitLab 15.4で導入されました。

パーティショニングするテーブルにパーティショニング・キー・カラムを追加します。パーティショニング・キーを以下の制約に含めます：

  • プライマリ・キー。
  • パーティショニングされるテーブルを参照するすべての外部キー。
  • すべての一意制約。

ステップ1 - パーティション・キーの追加

パーティショニング・キーのカラムを追加します。例えば、Railsマイグレーションの場合：

class AddPartitionNumberForPartitioning < Gitlab::Database::Migration[2.1]
  enable_lock_retries!

  TABLE_NAME = :table_name
  COLUMN_NAME = :partition_id
  DEFAULT_VALUE = 100

  def change
    add_column(TABLE_NAME, COLUMN_NAME, :bigint, default: 100)
  end
end

ステップ2 - 必要なインデックスの作成

パーティショニング・キー・カラムを含むインデックスを追加します。例えば、Railsマイグレーションの場合：

class PrepareIndexesForPartitioning < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  TABLE_NAME = :table_name
  INDEX_NAME = :index_name

  def up
    add_concurrent_index(TABLE_NAME, [:id, :partition_id], unique: true, name: INDEX_NAME)
  end

  def down
    remove_concurrent_index_by_name(TABLE_NAME, INDEX_NAME)
  end
end

ステップ3 - 一意制約の適用

主キー・インデックスを含む、パーティショニング・キー列を含むすべての一意インデックスを変更します。次の2つのステップで必要となる[primary_key_column, :partition_id] 、一意インデックスを追加することから始めることができます。例えば、Railsマイグレーションでは、以下のようになります：

class PrepareUniqueContraintForPartitioning < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  TABLE_NAME = :table_name
  OLD_UNIQUE_INDEX_NAME = :index_name_unique
  NEW_UNIQUE_INDEX_NAME = :new_index_name

  def up
    add_concurrent_index(TABLE_NAME, [:id, :partition_id], unique: true, name: NEW_UNIQUE_INDEX_NAME)

    remove_concurrent_index_by_name(TABLE_NAME, OLD_UNIQUE_INDEX_NAME)
  end

  def down
    add_concurrent_index(TABLE_NAME, :id, unique: true, name: OLD_UNIQUE_INDEX_NAME)

    remove_concurrent_index_by_name(TABLE_NAME, NEW_UNIQUE_INDEX_NAME)
  end
end

ステップ4 - 外部キー制約の適用

パーティショニング・キー・カラムを含む外部キーを強制します。たとえば、Railsマイグレーションでは、次のようになります：

class PrepareForeignKeyForPartitioning < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  SOURCE_TABLE_NAME = :source_table_name
  TARGET_TABLE_NAME = :target_table_name
  COLUMN = :foreign_key_id
  TARGET_COLUMN = :id
  FK_NAME = :fk_365d1db505_p
  PARTITION_COLUMN = :partition_id

  def up
    add_concurrent_foreign_key(
      SOURCE_TABLE_NAME,
      TARGET_TABLE_NAME,
      column: [PARTITION_COLUMN, COLUMN],
      target_column: [PARTITION_COLUMN, TARGET_COLUMN],
      validate: false,
      on_update: :cascade,
      name: FK_NAME
    )

    # This should be done in a separate post migration when dealing with a high traffic table
    validate_foreign_key(TABLE_NAME, [PARTITION_COLUMN, COLUMN], name: FK_NAME)
  end

  def down
    with_lock_retries do
      remove_foreign_key_if_exists(SOURCE_TABLE_NAME, name: FK_NAME)
    end
  end
end

パーティショニングカラムを更新したい場合、on_update: :cascade オプションは必須です。これにより、すべての従属行に更新がカスケードされます。このオプションを指定せずに、ターゲットテーブルのパーティションカラムを更新するとKey is still referenced from table ... エラーが発生し、ソーステーブルのパーティションカラムを更新するとKey is not present in table ... エラーが発生します。

このマイグレーションは、以下を使用して自動的に生成することができます：

./scripts/partitioning/generate-fk --source source_table_name --target target_table_name

ステップ5 - 主キーの入れ替え

パーティション・キー・カラムを含む主キーを入れ替えます。これは、すべての参照外部キーにパーティション・キーを含めてから行います。例えば、Railsマイグレーションでは、次のようになります：

class PreparePrimaryKeyForPartitioning < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  TABLE_NAME = :table_name
  PRIMARY_KEY = :primary_key
  OLD_INDEX_NAME = :old_index_name
  NEW_INDEX_NAME = :new_index_name

  def up
    swap_primary_key(TABLE_NAME, PRIMARY_KEY, NEW_INDEX_NAME)
  end

  def down
    add_concurrent_index(TABLE_NAME, :id, unique: true, name: OLD_INDEX_NAME)
    add_concurrent_index(TABLE_NAME, [:id, :partition_id], unique: true, name: NEW_INDEX_NAME)

    unswap_primary_key(TABLE_NAME, PRIMARY_KEY, OLD_INDEX_NAME)
  end
end
note
ActiveRecord は複合プライマリキーをサポートしていないので、モデル内で明示的にプライマリキーを設定することを忘れないでください。
class Model < ApplicationRecord
  self.primary_key = :id
end

ステップ 6 - 親テーブルを作成し、既存のテーブルを初期パーティションとしてアタッチします。

データベースチームが提供する以下のヘルパーを使用して、既存のテーブルを最初のパーティションとしてアタッチする親テーブルを作成できます。

たとえば、Railsのポストマイグレーションでリストパーティショニングを使用する場合です：

class PrepareTableConstraintsForListPartitioning < Gitlab::Database::Migration[2.1]
  include Gitlab::Database::PartitioningMigrationHelpers::TableManagementHelpers

  disable_ddl_transaction!

  TABLE_NAME = :table_name
  PARENT_TABLE_NAME = :p_table_name
  FIRST_PARTITION = 100
  PARTITION_COLUMN = :partition_id

  def up
    prepare_constraint_for_list_partitioning(
      table_name: TABLE_NAME,
      partitioning_column: PARTITION_COLUMN,
      parent_table_name: PARENT_TABLE_NAME,
      initial_partitioning_value: FIRST_PARTITION
    )
  end

  def down
    revert_preparing_constraint_for_list_partitioning(
      table_name: TABLE_NAME,
      partitioning_column: PARTITION_COLUMN,
      parent_table_name: PARENT_TABLE_NAME,
      initial_partitioning_value: FIRST_PARTITION
    )
  end
end

class ConvertTableToListPartitioning < Gitlab::Database::Migration[2.1]
  include Gitlab::Database::PartitioningMigrationHelpers::TableManagementHelpers

  disable_ddl_transaction!

  TABLE_NAME = :table_name
  TABLE_FK = :table_references_by_fk
  PARENT_TABLE_NAME = :p_table_name
  FIRST_PARTITION = 100
  PARTITION_COLUMN = :partition_id

  def up
    convert_table_to_first_list_partition(
      table_name: TABLE_NAME,
      partitioning_column: PARTITION_COLUMN,
      parent_table_name: PARENT_TABLE_NAME,
      initial_partitioning_value: FIRST_PARTITION,
      lock_tables: [TABLE_FK, TABLE_NAME]
    )
  end

  def down
    revert_converting_table_to_first_list_partition(
      table_name: TABLE_NAME,
      partitioning_column: PARTITION_COLUMN,
      parent_table_name: PARENT_TABLE_NAME,
      initial_partitioning_value: FIRST_PARTITION
    )
  end
end
note
シーケンス名はルーティングテーブルが所有することになり、ActiveRecord 。これはActiveRecord がルーティングテーブルに変更された後で片付けることができます。
class Model < ApplicationRecord
  self.sequence_name = 'model_id_seq'
end

パーティショニング制約のマイグレーションが終了するまでに10分以上かかる場合、混雑している時間帯にマイグレーション後を実行しないように、非同期で実行するようにできます。

次のマイグレーションAsyncPrepareTableConstraintsForListPartitioning を先頭に付け、async: true オプションを使用してください。この変更により、パーティショニング制約がNOT VALID としてマークされ、週末にテーブルの既存のデータを検証するスケジュールされたジョブがエンキューされます。

次に、2回目のマイグレーション後のPrepareTableConstraintsForListPartitioning 、パーティショニング制約を検証済みとマークするだけです。なぜなら、既存のデータは前の週末にすでにテストされているからです。

使用例：

class AsyncPrepareTableConstraintsForListPartitioning < Gitlab::Database::Migration[2.1]
  include Gitlab::Database::PartitioningMigrationHelpers::TableManagementHelpers

  disable_ddl_transaction!

  TABLE_NAME = :table_name
  PARENT_TABLE_NAME = :p_table_name
  FIRST_PARTITION = 100
  PARTITION_COLUMN = :partition_id

  def up
    prepare_constraint_for_list_partitioning(
      table_name: TABLE_NAME,
      partitioning_column: PARTITION_COLUMN,
      parent_table_name: PARENT_TABLE_NAME,
      initial_partitioning_value: FIRST_PARTITION,
      async: true
    )
  end

  def down
    revert_preparing_constraint_for_list_partitioning(
      table_name: TABLE_NAME,
      partitioning_column: PARTITION_COLUMN,
      parent_table_name: PARENT_TABLE_NAME,
      initial_partitioning_value: FIRST_PARTITION
    )
  end
end