PolarDB:シーケンスと自動生成列の使用ガイド

特徴

PolarDB for PostgreSQL (Oracle 互換) は、一意のシーケンス値を生成するためのいくつかの方法を提供します。これらのメソッドは、さまざまなシナリオのニーズを満たし、Oracle Database との高い互換性があります。

• 従来のシーケンス
  従来のシーケンスは、CREATE SEQUENCE コマンドを使用して作成される独立したデータベースオブジェクトです。データを挿入する際には、my_seq.NEXTVAL のように NEXTVAL 疑似列を呼び出して、シーケンスから次の値を明示的に取得する必要があります。その後、その値をテーブルに挿入します。このメソッドは、シーケンスとテーブルが分離されているため、最も柔軟性があります。
  

• 自動生成列
  この機能は Oracle 12c で導入されました。シーケンスをテーブル列に暗黙的にバインドします。テーブルを作成する際に、列定義に GENERATED ... AS IDENTITY 句を追加します。これにより、新しい行が挿入されると、データベースが自動的に列の値を生成します。INSERT 文でこれを処理する必要はありません。このメソッドはより便利で、最新のデータベース設計のトレンドに沿っています。
  

自動生成列には、その動作ポリシーに基づいて 3 つのタイプがあります。

• GENERATED ALWAYS AS IDENTITY: 強制的な自動生成。システムは常に列の値を生成します。INSERT 文で列に明示的な値を指定することはできません。値を指定しようとすると、エラーが発生します。このポリシーは最も厳格で、データベースが ID を完全にコントロールすることを保証します。

• GENERATED BY DEFAULT AS IDENTITY: デフォルトの自動生成。システムは INSERT 文で値が指定されていない場合にのみ、列の値を生成します。特定の値を指定すると、システムは指定された値を使用します。ただし、明示的に NULL を指定すると、エラーが発生します。

• GENERATED BY DEFAULT ON NULL AS IDENTITY: デフォルトまたは NULL での自動生成。これは最も柔軟なポリシーです。システムは、INSERT 文で列が指定されていない場合、または列に明示的に NULL 値を指定した場合に、自動的に値を生成します。このメソッドは、データ移行や、カスタム ID と自動生成 ID の混在が必要なシナリオで役立ちます。

前提条件

お使いの PolarDB for PostgreSQL (Oracle 互換) クラスターは、マイナーエンジンバージョンが 2.0.14.17.36.0 以降である必要があります。

利点

• 開発の簡素化: 自動生成列は、ID 生成ロジックをテーブル定義にカプセル化します。これにより、シーケンスを呼び出す必要がないため、INSERT 文がよりシンプルになります。

• 一意性の保証: データベースは、生成されたシーケンス値が一意であることを保証します。このプロセスにより、アプリケーション層で ID を生成する際に発生する可能性のある競合を回避できます。

• 高い柔軟性: GENERATED BY DEFAULT ON NULL ポリシーは、自動生成と手動指定の両方をサポートします。これにより、データインポートや既存データの移行など、複雑なシナリオに最適です。

推奨事項

次の表は、さまざまなシーケンス生成メソッドの特徴を比較したものです。

ベストプラクティス

• 新しいアプリケーションには、GENERATED BY DEFAULT ON NULL AS IDENTITY を使用してください。最も柔軟性があります。

• GENERATED BY DEFAULT ON NULL は、データ移行や既存の ID を含むデータをインポートする場合に最適な選択肢です。

• ビジネス要件で、手動介入なしでデータベースが ID を厳密に制御する必要がある場合は、GENERATED ALWAYS AS IDENTITY を使用してください。

• 高同時実行の挿入シナリオでは、IDENTITY 列または従来のシーケンスに対して、CACHE 20 以上の適切な CACHE 値を設定してください。これにより、パフォーマンスが大幅に向上します。

従来のシーケンスの使用

このメソッドでは、シーケンスとテーブルは 2 つの別々のオブジェクトです。

1. シーケンスの作成

   まず、CREATE SEQUENCE コマンドを使用してシーケンスオブジェクトを作成します。開始値、ステップサイズ、最大値/最小値などのプロパティをカスタマイズできます。

   CREATE SEQUENCE emp_seq
       START WITH 1000       -- 開始値は 1000
       INCREMENT BY 1        -- 1 ずつ増加
       MAXVALUE 9999         -- 最大値は 9999
       NOCYCLE               -- サイクルしない
       CACHE 20;             -- パフォーマンス向上のために 20 個の値をキャッシュ

   パラメーターの説明

   • START WITH: 開始値。

   • INCREMENT BY: 増分ステップサイズ。

   • MAXVALUE/NOMAXVALUE: 最大値。

   • MINVALUE/NOMINVALUE: 最小値。

   • CYCLE/NOCYCLE: シーケンスがサイクルするかどうかを指定します。

   • CACHE/NOCACHE: キャッシュする値の数。

   • ORDER/NOORDER: 順序を保証するかどうかを指定します。

2. INSERT 文でのシーケンスの使用

   テーブルにデータを挿入する際、シーケンスの NEXTVAL 疑似列を呼び出して新しい ID を取得します。

   -- シーケンスを使用するテーブルを作成
   CREATE TABLE employees (
       emp_id NUMBER(10) PRIMARY KEY,
       emp_name VARCHAR2(50) NOT NULL
   );
   
   -- データ挿入時に emp_seq.NEXTVAL を呼び出す
   INSERT INTO employees (emp_id, emp_name) VALUES (emp_seq.NEXTVAL, 'John Smith');
   INSERT INTO employees (emp_id, emp_name) VALUES (emp_seq.NEXTVAL, 'Jane Doe');
   
   -- 結果をクエリ
   SELECT * FROM employees;
   -- emp_id  |  emp_name
   -- --------+------------
   -- 1000    | John Smith
   -- 1001    | Jane Doe
   

3. 結果のクエリ

   SELECT * FROM employees;

   次の結果が返されます。

    emp_id |  emp_name  
   --------+------------
      1000 | John Smith
      1001 | Jane Doe
   (2 rows)

4. シーケンスの管理

   user_sequences ビューをクエリして、シーケンスの現在のステータスを確認します。ALTER SEQUENCE コマンドを使用してプロパティを変更することもできます。

   -- シーケンスの現在値の表示 (最初に NEXTVAL を一度呼び出す必要があります)
   SELECT emp_seq.CURRVAL FROM dual;
   
   -- シーケンスのステップサイズとキャッシュを変更
   ALTER SEQUENCE emp_seq INCREMENT BY 2 CACHE 50;

自動生成列 (ID 列) の使用

このメソッドは、シーケンスをテーブル列に直接バインドします。よりモダンで便利です。

シーケンスの管理

従来のシーケンスと、自動生成列に関連付けられた内部シーケンスの両方を管理および保守できます。

SELECT sequence_name, min_value, max_value, increment_by,
       last_number, cache_size, cycle_flag
FROM user_sequences;

-- emp_seq シーケンスのステップサイズを 2、最大値を 99999、キャッシュを 50 に変更
ALTER SEQUENCE emp_seq
    START WITH 1005
    INCREMENT BY 2
    MAXVALUE 99999
    CACHE 50;

-- 既存のシーケンスを削除
DROP SEQUENCE emp_seq;

-- シーケンスを再作成し、新しい開始値を設定
CREATE SEQUENCE emp_seq START WITH 5000 INCREMENT BY 1;

ユースケース

GENERATED BY DEFAULT ON NULL 機能は、新規ユーザーの ID を自動生成することでユーザー登録を簡素化する一方、データ移行やバッチインポートのために指定された既存の ID もサポートします。

1. ユーザーアカウントテーブルを作成します。

   CREATE TABLE user_accounts (
       user_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (
           START WITH 100000
           CACHE 100
       ),
       username VARCHAR2(50) UNIQUE NOT NULL,
       email VARCHAR2(100) UNIQUE NOT NULL,
       created_date DATE DEFAULT SYSDATE
   );

2. ケース 1: 新規ユーザーが登録します。ID は自動的に生成されます。

   INSERT INTO user_accounts (username, email) VALUES ('john_doe', 'john@example.com');

3. ケース 2: 古いシステムからデータを移行します。既存の ID が指定されます。

   INSERT INTO user_accounts (user_id, username, email, created_date) VALUES (99999, 'legacy_user', 'leg***@system.com', DATE '2020-01-01');

4. ケース 3: バッチインポート。自動生成された ID と指定された ID を混在させます。

   INSERT ALL
       INTO user_accounts (username, email) VALUES ('alice', 'ali**@test.com')
       INTO user_accounts (user_id, username, email) VALUES (88888, 'system_acct', 'sys***@app.com')
   SELECT * FROM dual;

5. 最終結果をクエリします。

   SELECT * FROM user_accounts ORDER BY user_id;

   次の結果が返されます。

    user_id |  username   |       email       |    created_date     
   ---------+-------------+-------------------+---------------------
      88888 | system_acct | sys***@app.com    | 2025-09-17 10:21:38
      99999 | legacy_user | leg***@system.com | 2020-01-01 00:00:00
     100000 | john_doe    | john@example.com  | 2025-09-17 10:21:38
     100001 | alice       | ali**@test.com    | 2025-09-17 10:21:38
   (4 rows)