Postgres Tips: 20億行をCitusでbigintに変換する方法
Azure | Azure Data Services | 製品紹介
※本ブログは、Microsoft Tech Community Blog “Postgres Tips: How to convert 2 Billion Rows to Bigint with Citus – Microsoft Tech Community“ の抄訳です。
Microsoft が Citus Data を買収してから1年が経ち、私のチームが正式に「Ninjas」と呼ばれるような変更がありましたが、それでもまだ珍しく面白いPostgresの「パズル」を解くことがよくあります。ご存じでない方のために説明すると、Citusは、Azure上のマネージドの Postgres サービスの一部としてHyperscale (Citus) という名称で利用可能になった Postgres を水平方向にスケールアウトするオープンソースの Postgres 拡張機能です。
最近、ConvertFlowの友人で長年のCitusの顧客は、特に興味深いPostgresの課題で私たちのチームにアプローチしました。ConvertFlowには「HTAP」ワークロードと呼ばれるものがあり、マーケティング担当者はウェブサイトの訪問者が、案件候補や顧客、あるいは購入を繰り返してくれるようにパーソナライズされた方法を提供します。興味のある方に説明するとすれば、HTAP はマルチテナント SaaS や、リアルタイム分析ワークロードのマルチテナント SaaSも混在していることを意味します。
課題：20億行と継続的な成長はConvertFlowがすぐに整数があふれるという問題に直面することを意味する
ConvertFlow には、アプリケーションの中心にある Citus データベース クラスタ内の複数のサーバにまたがって、Postgres トラッキング テーブルのペアが作成されています。SaaS アプリケーション ワークフローの適切なチャンクは、これらの Postgres テーブルを更新または読み取ります。ConvertFlowの使用量はここ数年で急増しているので、これらのテーブルは時間の経過とともに本当に大きくなりました。今日、2つのトラッキング テーブル全体で20億行あり、それらのテーブルだけで1分間に4万の読み取りを取得し、その数は増加していっています。
この種の成長は、残念ながら、多くの場合、問題につながります：整数のオーバーフローです。
各 ConvertFlow の Postgres トラッキング テーブルは 10 億行を少し超えており、各テーブルはオートインクリメントに設定された整数の主キーを使用していました。つまり、各テーブルには、行が追加されるたびに増加する整数を含む行があることになります。残念ながら、Postgres は整数型の最大サイズが 2,147,483,647 に制限されています。これは、主キー列が既に約半分まできており、ConvertFlow の継続的な成長により、整数のオーバーフローが問題になることを意味します。
幸いにも、Postgresにはbigintと呼ばれる別の数値データ型があり、制限は 9,223,372,036,854,775,807、またはおよそ 900 京です。Citus データベース クラスタは非常に大きくなる可能性がありますが、bigint の制限に近いものは見つかりませんでした。
この実現が私たちをソリューションに導いた：顧客のPostgresテーブルで整数のデータ型からbigint型に切り替えるのは、これで大丈夫ですね。簡単に聞こえますよね?
Postgresで利用可能な数値型
データ型に関する PostgreSQL ドキュメントの章から、組み込みの数値型の表
Numeric Types available in Postgres
より困難な課題：積極的なPostgresのロックは、とても多くのダウンタイムを引き起こす可能性がある
さて、あるあまり知られていない細かいことがここで邪魔になりました：Postgresは列の型を変更するときにロックします。具体的には、Postgresは型を変更するときに、非常に積極的なタイプのロックであるACCESS EXCLUSIVEロックを取得します。Postgres では、ACCESS EXCLUSIVE ロックは更新、挿入、さらには更新の間の読み取りを防止します。その結果、実用的な目的について言えば、Postgres のデータ型の変更は、アプリケーションを落としてしまいます。
いくつかの簡単なテストでは、20 億行の変更が継続する間、約 10 時間のダウンタイムが発生することが示されました。データ量を考えると、これは比較的速いですが、10時間はメンテナンスウィンドウとして長すぎることは間違いありませんでした。そこで、この懸念に対処するための計画を考案しました。
計画：新たにbigint型の列を作成し、データをコピーし、後でそちらを使うように変更する
計画はとても簡単に始まりました。Citus クラスター内の Postgres テーブルのそれぞれに新しい bigint 型の列を作成し、既存の列から新しい列にデータをミラーリングし、アプリケーションを切り替えて新しい列を使用します。
これはすばらしいアイデアで、新しい値または更新された値に対して Postgres にトリガーを設定し、既存の値が入ってきたときにコピーするバッチ更新をゆっくりと行うことができます。さらに改善するには、オープンソースのpg_cron拡張を使用してバッチ更新をスケジュールして、深夜まで起きて作業するのではなく夜間にバッチ処理することが出来ます。
メンテナンスウィンドウは、後で新しい Postgres の bigint 型の列を使用するように切り替えるときに発生します。確実に少し遅くなりますが、他方で、ロックがはるかに少なくなることにもなります。さらに、既存の行のバッチ更新を実行することで、アクティビティの多い期間を回避できます。整数が利用できなくなるまで、準備期間はまだ数ヶ月残っていたので、処理が終了する時間はあまり緊急の要件ではありませんでした。
落とし穴:常に主キーの存在を維持する方法?
私たちはすぐに驚くべき落とし穴を見落としていることに気づきました。整数列は、問題のテーブルの主キーの一部であり、ConvertFlowはRailsアプリを使用しており、アプリの要件を満たすためには、常に主キーの存在を維持する必要がありました。
トランザクションで主キーを削除して新しいキーを作成することもできますが、20 億行も存在すると、分散 Postgres テーブルでも 1 時間か 2 時間かかる可能性が高く、メンテナンスウィンドウを短くする必要がありました。
修正:Postgresの素晴らしいADD table_constraint_using_index
幸いなことに、Postgres には、この場合に使用できる別のクールな機能があります： ADD table_constraint_using_index です。この引数は、制約を作成する際に、一意のインデックスを主キーに昇格させるために使用できます。つまり、Postgresは主キーの有効性を確認するためにフルテーブルスキャンを行う必要はありません。その結果、既存の主キーを削除し、その bigint 列を使用して新しいキーを作成する場合があります。この場合でも ALTER TABLEが完了するまでの短いロックが必要ですが、この方法の方がはるかに高速になります。ConvertFlow とこのアイデアについて話し合い、Rails の移行をいくつか書き、すべてをテストした後、メンテナンスウィンドウをスケジュールしました。
すべてが理解できたので、私たちは作業を始めました。私たちは生のSQLの代わりにRailsの移行ですべてを行うことにしましたが、私たちが実行したものをより一般に適用するための純粋なSQLバージョンが以下です：
— First, make basic modifications to get the new column added. We’ve got a composite primary key, so the new constraint has 2 columns in it to match the original.
ALTER TABLE target_table ADD COLUMN column_bigint BIGINT;
ALTER TABLE target_table ADD CONSTRAINT future_primary_key UNIQUE (column_bigint, other_column
);
— Next, ensure that all incoming updates and inserts populate that column:
CREATE OR REPLACE FUNCTION populate_bigint
RETURNS trigger AS
$BODY$
new.column_bigint := new.original_column;
$BODY$;
CREATE TRIGGER populate_bigint_trigger BEFORE INSERT OR UPDATE ON target_table
FOR EACH ROW
EXECUTE populate_bigint();
— Now, populate the column for all existing rows. We’re doing this in chunks so we can pause and resume more easily.
UPDATE target_table SET column_bigint = original_column WHERE column_bigint IS NULL AND original_column BETWEEN 0 AND 10000;
UPDATE target_table SET column_bigint = original_column WHERE column_bigint IS NULL AND original_column BETWEEN 10000 AND 20000;
— Repeat last line with changes as needed. We scheduled this using pg_cron so we didn’t have to keep running different commands.
— Now that everything’s caught up, take a few minutes of downtime, and switch over. We’re doing this in a transaction, just in case.
BEGIN;
ALTER TABLE target_table DROP CONSTRAINT original_pkey;
ALTER TABLE target_table ADD CONSTRAINT new_pkey PRIMARY KEY USING INDEX future_primary_key;
END;
まとめ：遅いといっても、20 億行でのダウンタイムは 20 分でした。
私たちはこれを実行し、動きました！結局、約20分のダウンタイムが必要になり、そのほとんどは長時間実行されるトランザクションが完了するのを待つもので、その間、私たちはうまくいっていることを確認するためにテストを行っていました。これは、ConvertFlow チームが計画していたメンテナンスウィンドウにとてもうまい具合に合いました。私たちの最初の10時間以上という見積もりに対する素晴らしい改善であり、プロセスのすべての単一の時点で、いずれかの詳細な事柄を見逃していた場合にも、我々はやり直す簡単な方法を用意していました。成功です！
整数オーバーフローが問題となるデータベースサイズであれば、Citus もチェックする価値があります。これらのPostgresの Tips は、Citus の拡張機能の有無にかかわらずうまく機能しますが、スケールアウトする準備ができているのは決して不都合なことではありません。
