PolarDB:ALTER TABLE ... ENABLE/DISABLE CONSTRAINT

概要

大規模なテーブルに制約を追加または管理する場合、検証やインデックス作成などの操作では、多くの場合、全表スキャンと長期間のロックが必要となり、ビジネスのパフォーマンスと可用性に影響を与える可能性があります。ENABLE/DISABLE CONSTRAINT は、CHECK、FOREIGN KEY、PRIMARY KEY、および UNIQUE 制約のアクティブ状態を変更することで、データベースのパフォーマンスを最適化します。

主に DISABLE CONSTRAINT と ENABLE CONSTRAINT の 2 つのコマンドが利用可能です。

• DISABLE CONSTRAINT: 制約を無効にします。新しい書き込み (INSERT および UPDATE) に対する検証は行われません。

• ENABLE CONSTRAINT: 制約を有効にします。次の 2 つの検証モードがあります。

  • ENABLE CONSTRAINT (デフォルト): 新しいデータを検証し、すべての既存データをスキャンします。大規模なテーブルの場合、このプロセスは時間がかかり、大量の I/O リソースを消費する可能性があります。

  • ENABLE CONSTRAINT ... NOT VALID: 新しいデータのみを検証し、既存データのチェックをスキップして高速な操作を実現します。既存データがすでに有効であると確信している場合に、このモードを使用します。

前提条件

開始する前に、クラスターのバージョンがリビジョンバージョン 2.0.11.9.11.0 以降の PolarDB for PostgreSQL (Oracle 互換) であることを確認してください。

依存関係

• 拡張機能: この機能は polar_constraint 拡張機能に依存します。ALTER TABLE ... ENABLE/DISABLE CONSTRAINT の手順に従ってインストールおよび設定する必要があります。

• 制約:

  • 参照される制約の無効化:

    アクティブな FOREIGN KEY によって参照されている場合、PRIMARY KEY または UNIQUE 制約を無効にすることはできません。参照している外部キーを最初に無効にする必要があります。

  • 外部キーの有効化:

    参照される PRIMARY KEY または UNIQUE 制約が有効になっている場合にのみ、FOREIGN KEY を有効にできます。

• 互換性:

  • これは PolarDB 固有の機能です。ネイティブの PostgreSQL は、PRIMARY KEY および UNIQUE 制約の無効化をサポートしていません。

  • 構文は PostgreSQL に基づいており、ENABLE VALIDATE や ENABLE NOVALIDATE などの Oracle 構文とは異なります。

拡張機能のインストール

この機能を使用する前に、クラスターに polar_constraint 拡張機能をインストールする必要があります。

1. 次のコマンドを実行して、拡張機能がインストールされているかどうかを確認します。

   SELECT extname, extversion FROM pg_extension WHERE extname = 'polar_constraint';

   拡張機能とそのバージョン情報が返された場合、拡張機能はインストールされています。

2. (オプション) 拡張機能がインストールされていない場合は、次の手順に従います。

   1. (オプション) リビジョンバージョンが 2.0.11.9.25.0 より前のクラスターの場合：PolarDB コンソールの[設定と管理] > [パラメーター設定] ページに移動し、polar_constraint を追加して、クラスターパラメーター shared_preload_libraries を変更します。

      説明

      • パラメーターにすでに他の拡張機能が含まれている場合は、コンマを使用して区切ります。例: pg_stat_statements,polar_constraint。

      • この操作によりクラスターが再起動され、サービスが中断されます。この操作は、オフピーク時間またはメンテナンスウィンドウ内に実行してください。

   2. データベースに拡張機能をインストールします。

      -- 拡張機能をインストールします
      CREATE EXTENSION IF NOT EXISTS polar_constraint;
      CREATE EXTENSION IF NOT EXISTS polar_constraint;

構文

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

ベストプラクティス: 適切な操作の選択

各コマンドは、データベーステーブルを異なる方法でロックし、パフォーマンスに影響を与えます。この機能を安全に使用するには、これらの違いを理解することが重要です。

ユースケース: 大規模なテーブルに安全に制約を追加する

ADD CONSTRAINT を使用して大規模なテーブルに直接制約を追加すると、即時の全表スキャンがトリガーされ、ACCESS EXCLUSIVE ロックが保持されます。このプロセスは、すべての読み取りおよび書き込み操作を長時間ブロックするため、本番環境のサービスに深刻な影響を与える可能性があります。影響を最小限に抑えるには、次の 2 段階のメソッドを使用します。

ユースケースの例

数億行を持つ products テーブルに CHECK 制約を追加して、price 列が 0 より大きいことを確認します。

手順の概要

1. 既存データを検証せずに有効な制約を追加する: 既存データを検証せずに制約定義を迅速に追加します。ロック期間は短いです。

2. バックグラウンドで既存データを検証する: オフピーク時に、既存データをスキャンして検証し、テーブルの列が制約を満たしていることを確認します。このプロセスでは同時読み取りが可能で、サービスへの影響は最小限です。

手順

1. 既存データを検証せずに、有効な制約を迅速に追加します。これを行うには、ALTER TABLE コマンドで NOT VALID オプションを使用します。このアプローチはメタデータのみを変更するため、操作は非常に高速です。

   -- テストテーブルとデータを作成します
   CREATE TABLE products (
       id SERIAL PRIMARY KEY,
       name TEXT,
       price NUMERIC
   );
   
   -- 準拠していない行を挿入します
   INSERT INTO products (name, price) VALUES ('Bag', -1); 
   
   -- 準拠している行を挿入します
   INSERT INTO products (name, price) VALUES ('Book', 10); 
   
   -- 既存データを検証せずに有効な制約を追加します
   ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0) NOT VALID;

2. (オプション) 制約が新しいデータに適用されていることを確認します。

   -- 新しい不正なデータはブロックされます
   INSERT INTO products (name, price) VALUES ('Pen', -5);
   -- エラー:
   -- ERROR: new row for relation "products" violates check constraint "chk_price_positive"
   -- DETAIL: Failing row contains (3, Pen, -5).
   
   -- 更新された不正なデータはブロックされます
   UPDATE products SET price = -2 WHERE id = 1;
   -- エラー:
   -- ERROR:  new row for relation "products" violates check constraint "chk_price_positive"
   -- DETAIL:  Failing row contains (1, Bag, -2).

3. 既存データが制約を満たしていることを検証します。SELECT を使用して準拠していないデータを見つけ、UPDATE を使用して修正します。

   -- 準拠していない既存データを見つけます
   SELECT * FROM products WHERE NOT (price > 0);
   
   -- 準拠していない既存データを修正します
   UPDATE products SET price = 10 WHERE id = 1;

ユースケース: 一括データインポートの最適化

COPY などのコマンドで大規模なデータセットをインポートする場合、有効な制約はすべての行を検証するため、パフォーマンスのボトルネックになる可能性があります。インポート速度を劇的に向上させるには、次のワークフローを使用します。

1. インポート前にテーブルの制約を無効にします。

2. 一括データインポートを実行します。

3. インポートが完了したら、制約を再度有効にします。

ユースケースの例

大規模な CSV ファイルを target_table にインポートします。

手順

1. データインポートの前に、すべて制約を一時的に無効にします。

   -- target_table にはすでに一意キー、外部キー、およびチェック制約があると仮定します
   ALTER TABLE target_table DISABLE CONSTRAINT uq_target;
   ALTER TABLE target_table DISABLE CONSTRAINT fk_target;
   ALTER TABLE target_table DISABLE CONSTRAINT chk_target;

   説明

   通常、プライマリキーを無効にすることは推奨されておらず、また不可能です。UNIQUE、FOREIGN KEY、および CHECK 制約の無効化に焦点を当ててください。

2. データインポートを実行します。

   制約チェックが無効になっているため、インポートは大幅に高速になります。

   COPY target_table FROM '/path/to/data.csv' WITH CSV;

3. 制約を再度有効にし、すべてのデータを検証します。

   データの信頼度に基づいて、次のいずれかのメソッドを選択します。

   • オプション A: 検証なしで有効化 (最速)
     インポートされたデータがクリーンであると確信している場合、または後で検証する予定がある場合に使用します。
     

     説明

     ENABLE CONSTRAINT ... NOT VALID は、新しくインポートされたデータの検証をスキップし、将来の変更に対してのみ制約を適用します。

     -- 一意制約を有効にします
     ALTER TABLE target_table ENABLE CONSTRAINT uq_target NOT VALID;
     
     -- 外部キー制約を有効にします
     ALTER TABLE target_table ENABLE CONSTRAINT fk_target NOT VALID;
     
     -- CHECK 制約を有効にします
     ALTER TABLE target_table ENABLE CONSTRAINT chk_target NOT VALID;

   • オプション B: 検証付きで有効化 (低速、安全)
     これを使用して、すべてのデータ (インポートされたデータを含む) が有効であることを確認します。これは全表スキャンを実行し、書き込みをブロックする可能性があるため、オフピーク時に実行してください。
     

     -- 一意制約を有効にします。これにより、テーブル内のすべてのデータの検証がトリガーされます。
     ALTER TABLE target_table ENABLE CONSTRAINT uq_target;
     -- 検証が失敗した場合、エラーが返されます: ERROR: could not enable unique constraint "uq_target"
     
     -- 外部キー制約を有効にします。これにより、テーブル内のすべてのデータの検証がトリガーされます。
     ALTER TABLE target_table ENABLE CONSTRAINT fk_target;
     -- 検証が失敗した場合、エラーが返されます: ERROR: insert or update on table "target_table" violates foreign key constraint "fk_target"
     
     -- CHECK 制約を有効にします。これにより、テーブル内のすべてのデータの検証がトリガーされます。
     ALTER TABLE target_table ENABLE CONSTRAINT chk_target;
     -- 検証が失敗した場合、エラーが返されます: ERROR: check constraint "chk_target" is violated by some row

よくある質問