Postgres 生成カラム：落とし穴とベストプラクティス

レプリケートされるテーブルで PostgreSQL の生成カラムを使用する場合、押さえておくべき重要なポイントがいくつかあります。こうした落とし穴は、レプリケーション処理や宛先システム側におけるデータ整合性に影響を及ぼす可能性があります。

The problem with generated columns

pgoutput 経由で公開されない: 生成列は、論理レプリケーションプラグインである pgoutput を通じて公開されません。これは、PostgreSQL から別のシステムへデータをレプリケーションする際に、生成列の値がレプリケーションストリームに含まれないことを意味します。
主キーに関する問題: 生成列が主キーの一部になっている場合、宛先側で重複排除の問題を引き起こす可能性があります。生成列の値がレプリケーションされないため、宛先システムでは行を正しく識別して重複排除するために必要な情報を保持できません。
スキーマ変更に関する問題: すでにレプリケーションされているテーブルに生成列を追加した場合、新しいカラムは宛先側では値が埋まりません。これは、Postgres がその新しいカラムに対して RelationMessage を提供しないためです。その後、同じテーブルに新しい非生成カラムを追加すると、ClickPipe がスキーマを突き合わせようとした際に、宛先側で生成列を見つけられず、レプリケーションプロセスが失敗する原因となります。

これらの制約を回避するために、次のベストプラクティスを検討してください。

レプリケーション先で生成カラムを再作成する: レプリケーションプロセスに生成カラムの処理を任せるのではなく、dbt (data build tool) やその他のデータ変換メカニズムなどのツールを使用して、レプリケーション先でこれらのカラムを再作成することを推奨します。
PRIMARY KEY で生成カラムを使用しない: レプリケーション対象となるテーブルを設計する際には、PRIMARY KEY の一部として生成カラムを含めないようにするのが望ましいです。

今後のバージョンでは、次の点を支援する UI 機能を追加する予定です:

生成カラムを含むテーブルの特定: UI に、生成カラムを含むテーブルを特定する機能を追加します。これにより、この問題の影響を受けるテーブルを把握しやすくなります。
ドキュメントとベストプラクティス: UI には、レプリケートテーブルで生成カラムを使用する際のベストプラクティスが含まれ、一般的な落とし穴を回避するためのガイダンスも提供されます。