Postgresへの移行

376日前原文(engineering.usemotion.com)

概要

Motionは2022年からCockroachDBを採用していたが、コスト増と運用上の課題からPostgresへ移行する決断をした事例を解説。
CockroachDBのスケーラビリティや高可用性は魅力だったが、実際のユースケースでは恩恵が少なく、コストが大幅に増加したことが問題となった。
マイグレーションやETL、クエリ速度、UI/UX、VPC接続など、主要な課題とPostgres移行による改善点を具体的に記述。
独自ETLツールによる移行プロセスや、移行後のパフォーマンス向上・コスト削減効果についても詳細に説明。
移行は1名で実施し、ダウンタイムは1時間未満、データロスなしで完了した成功事例である。

MotionのPostgres移行事例

CockroachDB採用の背景と課題

2022年初頭からMotionは CockroachDB を採用、 水平スケーリング や 高可用性、 SQL互換性 を評価していたことを確認。
GDPR対応 を見据えたマルチリージョン構成や、Postgresのスケーラビリティへの不安が初期検討理由となったことを認識。
しかし、2024年には コストが5倍 （6桁中盤）となり、 単一リージョン ・ シンプルなトランザクション 中心の運用で分散DBの恩恵が薄い状況を確認。
ORM（Prisma）を利用していたため、 データベース間の比較検証が容易 だったことを確認。

マイグレーションに関する課題

データベースサイズ増加に伴い、 Prismaのマイグレーションが頻繁にタイムアウト する現象が発生したことを確認。
- CockroachDBでのマイグレーション適用時、 手動で1つずつ実行 する必要があり、デプロイが 2時間近く停止 することもあったことを指摘。
Postgres移行後、 同等のマイグレーションが10秒で完了 したことを実証。
タイムアウトの頻発により、 DB外での運用回避策 や バージョンアップの停滞 （EOLバージョン22のまま運用）など、開発効率・保守性が著しく低下したことを確認。

ETL・データ連携の問題

マイグレーション以外にも、 ETL処理のタイムアウト が発生していたことを確認。
- Airbyte経由でのETLで メモリリーク や タイムアウト が頻発し、 安定したCockroachDB対応ETLツールが存在しなかった ことを指摘。
ETLジョブのパフォーマンスも悪く、 運用面での負担増大 を招いたことを強調。

クエリ速度の比較

一部のクエリは CockroachDBのオプティマイザ によりPostgresより高速だった事例もあったことを認識。
- 例：特定クエリでCockroachDBが13秒、Postgresが20秒を要したことを確認。
しかし、 Prisma生成SQLの複雑化 とCockroachDB最適化の“魔法”が裏目に出て、 多くの実運用クエリでPostgresの方が最大20倍高速 となるケースが多発したことを強調。
- TeamTaskテーブルのクエリでは 平均してCockroachDBがPostgresの3倍遅い ことを確認。

UI/UX・開発体験の課題

未使用インデックスUI が誤解を招き、開発者が混乱する事例があったことを指摘。
クエリキャンセル操作 がCockroachDBでは複雑で、全ノードでのキャンセル保証が難しかったことを説明。
サポートポータル が本体と別サイト・認証で、対応遅延や入力手間が発生、障害時に迅速な対応が困難だったことを明示。

VPC・ネットワーク接続の問題

Tailscale経由のVPC接続 で定期的に 接続不可エラー が発生し、 環境問わず突発的な切断 が起きていたことを指摘。
Postgres移行後は 同様の問題が一切発生しなかった ことを強調。

独自ETLによる移行プロセス

2024年1月時点で最大テーブルは 1億件超 の規模となっていたことを確認。
既存ETLツールが使えなかったため、 Bunを使った独自ETLスクリプト を開発・利用したことを説明。
- スキーマ・テーブル情報の取得、各テーブルごとのデータダンプ、Bun子プロセスによるストリーミングCSV転送、Postgresへのインサートを順次実施することにより移行。
CockroachDBとPostgresの JSON・配列カラムのバイトエンコーディング差異 に苦労し、 Csv-jsでカスタム変換パイプライン を構築したことを記載。
移行本番時は GCPの128コアVM を利用し、Motionのメンテナンスモードで 全DB移行を15分で完了 したことを強調。

移行後の成果

1名で数週間かけて移行を完遂、ダウンタイムは 1時間未満 （実際の移行は15分、慎重に段階的復旧）で データロスゼロ を実現。
移行直後から リクエストレイテンシが33%低下、Postgresエコシステム（PGAnalyze等）で 非最適クエリの迅速な改善 が可能となったことを強調。
Postgresクラスタの過剰プロビジョニングにも関わらず、年間11万ドル超のコスト削減 を実現（今後のトラフィック増加を考慮すればさらに大きな効果）。

この事例は、 分散DBの必要性が低いユースケース でのCockroachDBからPostgresへの移行が コスト・パフォーマンス・運用性 すべてで大きなメリットを生むことを示す好例である。導入技術や運用規模、実際の移行手法も含め、今後同様の課題を抱える企業への 有用な提案 となる。

Hackerたちの意見

半分以上のクエリがjson_*だと、やっぱり悲しくなるなぁ。もう手遅れだって分かってるけど、クエリのパフォーマンスがずっと100行の推定を返すプランナーのクエリに左結合し続けるのは、ほんとに大きな悲しいトロンボーンみたいだよ。

└

データベースでのカラム指向のサポートがもっと簡単になってほしいな。これが、データをデータベースのカラムにjsonで保存する理由の一つだから（データが本当にカラム指向ならね）。今は、ベンダーロックインやプラグインのインストール要件があって、クラウドSQLプロバイダーではやりにくいんだよね。特に、問題になる頃には、もう十分な規模になってて、DBやベンダーを切り替えるのが難しいか不可能になってるし。

└

クエリが理にかなっているなら、クエリされた式にインデックスを作ることができるよ。https://www.postgresql.org/docs/current/indexes-expressional...

└

あの例でjson_agg()じゃなくてarray_agg()なのはなんでだろう？ネイティブの適切に型付けされた配列の代わりに、JSON配列を使う理由って何？複雑なオブジェクトがあるならJSONオブジェクトを使うことはあるけど、あれは全部IDの配列だったし。あと、なんでjson_agg()でjsonb_agg()じゃなかったの？PostgreSQLでJSONよりもJSONBを使う理由ってあるのかな？

Motionの「未使用インデックス」に関する経験が気になるな。Cockroachのダッシュボードに「未使用インデックス」として表示されているインデックスがあるって言ってたけど、彼らが使われていると疑っているインデックスは実際には未使用で、MotionはCockroachDBが他のインデックスでジグザグ結合をして同じことを達成していることに気づかなかったみたい。だから、明らかに使われるべきインデックスが本当に未使用になっちゃってる。これは素晴らしい機能だけど、CRDBのオプティマイザーはカバリングインデックスよりもジグザグ結合を好むから、これを回避するにはインデックスをオプティマイザーを説得するように書かないといけないんだよね。

これを読んだのは、「なぜPrismaを使うべきでないか、そしてCockroachが私たちを見捨てた理由」って感じだった。あの有名なhttps://github.com/prisma/prisma/discussions/19748からPrismaについてはもう知ってたし。

└

現在、PrismaはリレーショナルクエリのためにJOINを行わないのは本当だよ。代わりに、個別のクエリを送信して、アプリケーションレベルでデータを結合するんだ。..........え、何それ？ちょっとおかしいんじゃない？編集：ORMが大嫌いなんだけど、なんでみんな使うのか理解できない。お願いだからSQLを書いてよ。

└

大規模なDBに関してはあまり経験がないから何も断言できないけど、横にシャーディングされたDBMSがうまく機能しているのを見たことがない。Citusがそうだと言われているけど、結局手動でDBよりも高いレベルでシャーディングする方が悪いような気がするし、それも簡単じゃないけどね。

└

Prismaってほんと最悪だよね…NPMで一番ダウンロードされてるORMだなんて信じられる？

└

スキーマ管理とかにはPrismaは気にならなかったけど、君がリンクしたGitHubの問題も見たよ。他の人がPrismaとKyselyを組み合わせることを勧めてるのを見た。俺はおもちゃプロジェクトでしか使ったことないから、あんまり真に受けないでね。 https://kysely.dev/ https://github.com/valtyr/prisma-kysely

└

ここに作者がいるよ。うん、それも悪くない意見だね。俺もいろんなことについてPrismaの問題で結構声を上げてきた。Drizzleチームが1.0をリリースしたら、PrismaからDrizzleへの大規模な移行を始める予定だよ。その移行が起こったら、絶対に結果を共有するからね！

└

今、スタートアップでPrismaを使ってるんだけど、使わなければよかったって本当に思ってる。- クエリオブジェクトが複雑になると読みづらくなる。- サポートされていないPostgresの拡張機能が必要な場合、運が悪い。- スキーマ内に大きなファイルがあると、シャーディングが不可能。- モノレポに多くのアプリがあって、スキーマが「@prisma/client」に焼き付けられるから、別々のPrisma接続が持てない。基本的に、役立つのはTSの型だけで、SQLビルダーライブラリの方がそれをうまく解決してる。要するに、Kyselyを使った方がいいよ。Prismaには全く価値を感じない。

└

「代わりに、個別のクエリを送信してアプリケーションレベルでデータを結合する。ただし、これはPrismaのリレーショナルクエリが本質的に遅いというわけではない」って、マジで何言ってるの？「それに、高性能MySQLの本のこの章はクエリパフォーマンス最適化について素晴らしい洞察がある。そこに書かれている技術の一つがJOIN分解：多くの高性能ウェブサイトはJOIN分解を使っている。複数の単一テーブルクエリを実行してから、アプリケーションで結合を行うことでJOINを分解できる。」この、何十年も続いているメインストリームのデータベースエンジンでのベアメタル実行の最適化を超えるJavaScriptを書けると思ってる信念には驚かされる。

「XからPostgresへの移行」っていう記事を何本見たか数えきれないよ。Postgresから移行する記事は一度も見たことないな。

└

これだよ https://www.uber.com/en-CA/blog/postgres-to-mysql-migration/

└

関連情報として、Oxideのポッドキャスト「Whither CockroachDB」があるよ。JoyentでのPostgresの経験を振り返り、その後のPostgresの経験に基づいてCockroachを選んだ話をしてる。 https://www.youtube.com/watch?v=DNHMYp8M40k 上の要約で意見を誤解されるのが怖くて編集的なことは避けようとしてるんだけど、彼らがPostgresを使ってたのは10年前で、高可用性のユースケースで使ってたから、彼らも俺も「Postgresは悪い、Cockroachは良い」って思ってるわけじゃないんだ。でも、Bryan Cantrillが言うように、「誰もあなたのワークロードをあなたほど気にしない」。だからベンチマークしよう！「雰囲気」で技術的な決定をしないで！

└

PostgresからClickhouseへの移行に参加したことがあるけど、それについて記事を書く気にはなれなかったな。

└

PostgresからCitusを使ってSingleStoreへの移行の初期評価を手伝ったよ。https://www.singlestore.com/made-on/heap/

└

コスト分析を基に、PostgresからADXに移行したんだ。Azureのマネージド版を見てみたらね。今は素敵なKQLクエリが使えて、ほぼPostgresを再スタートした感じだよ…

└

たぶん、ほとんどのユースケースは、そこそこスペックのいいマシンで動かすRDBMSで対応できるってことの副産物だね。賢くシャーディングすれば、別のマシンでもいけるし。実際の分散DBやトランザクションのユースケースは、そんなに多くないと思う。

└

これは記事じゃないし、直接的な知識もないけど、Instagramがかなり前にPostgresから移行したんじゃないかと思う。たぶん、fb-mysql + myrocksか、他のRocksDBベースのソリューションにね。圧縮レベルはPostgresベースのソリューションよりもはるかに優れていて、Instagramの規模では、かなりのハードウェアコスト削減につながると思う。それに、もしまだpgを使ってたら、存在する中で最大のpgデプロイメントの一つになるだろうし、その影響がエンジニアリングに明らかに表れるはず（カンファレンスのトークとか、FOSSへの貢献とか）。大局的に見ると、Postgresは素晴らしいデータベースで、しばしば正しい選択だけど、テクノロジーの世界では常に100%のベストチョイスってわけじゃない。どこかにトレードオフがあるんだよね。

└

PostgresからXに移行するのとほぼ同じくらいの数だね。

└

あなたのポイントはまだ有効だと思うし、私自身もPostgresの大ファン/ユーザーなんだけどね。だけど、私がPostHogにいた時に、_analytics_データをClickHouseに移行した（もっとトランザクショナルなものにはPostgresを残しておいた）ことはあるよ。詳しくは： https://posthog.com/blog/how-we-turned-clickhouse-into-our-e...

2024年1月までに、私たちの最大のテーブルには約1億行があった。これにはびっくりした。記事の冒頭で、分散データベースを使っていることと「中規模6桁」のDB請求について言及されてたから、彼らが単一ノードでは到底できないような異常に大きなデータベースを持ってると思ったんだ。Postgresの設定については詳しく書かれてないから、かなり標準的な単一プライマリと1億行のテーブルがその能力の範囲内だと思う。俺は150百万行のテーブルを2vCPU+16GBのインスタンスで問題なく動かしてるし。リンゴとオレンジかもしれないけど、現代のサーバーができることを過小評価しちゃいけないよ。

└

中規模6桁って約50万ドルってこと？一百万ユーザーのCRUDアプリにしては、そんなの信じられないよ。何か見落としてる？

└

そんなに「モダン」である必要はないよ。2010年には、16GBのRAMと数百GBのRAID 10を搭載したデュアルXeonのマシンで、約3億行のMySQL 5.xシステムに取り組んでたんだ。SSDが一般的になる前の話ね。一番大きなテーブルは1億行を超えてた。ただ、いくつかの移行は痛かったけどね。その頃は、テーブル全体をロックするような移行もあって、夜中に実行する必要があったんだ。幸い、これは社内アプリ用だったからできたけど。

└

古い考えかもしれないけど、レコードが1億行に達し始めると、データセットが広すぎる（シャーディングを考慮）か、深すぎる（時間ベースのアーカイブを考慮）っていうサインだと思う。参考までに、2003年から2013年の間にこのボリュームのデータを生成する複数のシステムに関わってきたけど（主に資本市場だけど、一部は政府やコンプライアンスの仕事も）、その時代のデータベースやハードウェアを使っていて、クエリの最適化、キャッシング、シャーディング、アーカイブで解決できない問題はほとんどなかったよ。しかも、ほとんどはSQL、Bashスクリプト、cronジョブ、アプリケーションコードに直接組み込まれたI/Oロジックを使ってた。かなりミッションクリティカルなシステムを扱ってたけど（失敗するとアメリカの主要市場がダウンする可能性があって、ひどい場合はニュースでも取り上げられる）。

└

うちもテーブルに3億行以上あるよ。時間でパーティション分けしてて、問題なく動いてる。確かに30 vCPU、100GB RAMのマシンだけど、合計で数十億行をホストしてる。

└

一方で、彼らはORM（Prisma、結構重いことで知られてる）を使ってるのは認めてるね。

└

そうだね、1億は本当にそんなに多くないよ。俺はRDSのr6g.4xlで10B行のテーブルを扱ったことがあるけど、Postgresはそれを問題なく処理してくれた。20個以上のインデックスがあってもね。理想的ではないし、インデックスを減らしてテーブルをシャーディングしたいけど、Postgresはちゃんと対処してくれたよ。

└

Postgresがどれだけ扱えるかって、本当にすごいよね。俺の職場では、1日に約1億行を書き込んで、何年分の履歴を一つのデータベースに保存してる。確かに、サーバーは大きいけど、素晴らしいトランザクショナルワークロードがあって、分散システムの心配をしなくていいんだ！

└

それに、今のPostgresにはそれ以上の行があるのに、500ドル以下で払ってるって叫びたくなる！

└

同意だね。開発者たちは、100K行のテーブルが実際には大きくも中くらいでもないって言うと、みんな驚くんだよね。もちろん、みんなの経験はそれぞれだけど、私にとって「大きい」ってのは数億行の範囲にあると思う。10億を超えたら、あんまり関係ないし、5億と1億の違いなんて重要じゃないよ。だって、a. あなたの作業セットがそんなに大きいことはまずないし、b. あなたのサーバーがそれを一度に処理できる可能性も低いからね。パーティションがあるといいね。

└

今、MySQLの単一ノードに約10億行のテーブルがいくつかあるよ。256GBのRAMと2TBのNVMeドライブがいくつか。全然問題なく動いてるけど、何かが完全におかしくなったら、迅速な復元は期待できないね。そして、インデックスを直接使わない操作はできないから、パフォーマンスがすぐに落ちちゃう。つまり、基本的にそのテーブルを分散データベースのように使わなきゃいけないけど、少なくともトランザクション性はあるよ。

スタートアップや企業が、実際に分散が必要になる前にCockroachDBやTiDB、Yugabyteみたいな分散データベースを選ぶのって、ほんとにクレイジーで面白いよね。このトレンドは最悪だ。1億行なんて、ちゃんと調整されたPostgresやMySQLのインスタンス（あるいはリードレプリケーションのセットアップ）で楽々処理できる量だよ。壁にぶつかってからスケールすればいいのに。

└

1億行なんて、そこまで調整されていないPostgresでも大したことないよ。

└

これには納得できない！スタートアップは高可用性が必要だよ。レプリカを持ち始めたら、もう分散の領域に入ってるからね！

もっとスタートアップには経験豊富な人が必要だと思うんだ。（現実的に言うと、今の俺がそうなんだけど。）最大のテーブルが1億行で、データベースサービスに年に6桁の費用を払ってたの？今は8年物のノートパソコンで幸せに動いてるテーブルがあるよ。20年前の普通のMSSQL 2000ボックスで同じ規模のテーブルが動いてるシステムで働いたこともあるし、そのデータ量に対してクラウドスケールのシステムや請求書は必要ないと思う。彼らが説明してる問題は、経験豊富な手が「それはおかしい」って指摘しない限り、ここまで来るべきじゃなかったし、そのグレイビアードを雇ってたら、もっと早く気づいてたはずだよ。

└

そして彼らは年間でデータベースサービスに6桁を支払っていたの？もしかしたら月ごとだったかも。100,000,000行は2001年にSybaseで単一のSunサーバーで扱ったけど、全然問題なかったよ。

なんか awkward な記事だね。クエリが遅い理由を答えるには、クエリだけじゃなくてもうちょっと詳細が必要だよね。それに、タイムアウトについて再読したけど、データベースが何だったのか、データベースの問題だったのか、マイグレーションとどう関係してるのかが全然わからなかった。唯一引き出せた情報は、その会社がひどいアーキテクチャの決定をしていて、ORMを信じてる（クエリを見る限り、DBのデータレイアウトが適切かどうか疑問が多い）し、状況を明確に説明できないってことだけ。これって彼らの候補者や投資家にしか興味ないかも。失礼に聞こえたらごめんね。

Prismaを嫌う人たちへ：敬意を表します。でも、いくつかのコメントに対して以下のように返答したいです：ORMには私が知っている限り、主に2つのタイプがあります：Active Record（元々のRubyのやつにちなんで名付けられたと思う）とData Mapper（HibernateやSQLAlchemyを考えてみて）。Active Record ORMは、アプリケーションメモリでたくさんの作業をする代わりに、少しエルゴノミックです。Data Mapperは、コードの中でSQLに少し似ているけど、SQLでできることに対するより直接的なラッパーです。Data Mapperは、テーブル定義をオブジェクトとして持つことで、マイグレーションコードを生成するなどの様々な利点を保持することもできます。ORMを使いたいならData Mapper ORMを使ってみてください。

└

RailsのActive Recordは、Martin Fowlerによって説明されたパターンにちなんで名付けられました： https://www.martinfowler.com/eaaCatalog/activeRecord.html

└

あと、Query Objectスタイル、例えばJOOQやSQLAlchemy Coreについてはここを見てみてね。https://martinfowler.com/eaaCatalog/queryObject.html

「どのデータベースを使うべきか？」という質問の答えは「Postgres」だよ。もしPostgresが実際に使えない状況にいるなら、もうその理由は分かってるはずだよ。つまり、[Postgres -> エキゾチックな解決策]がみんなが取るべき道で（99%の人はPostgresに留まるだろうけど）、[エキゾチックな解決策 -> Postgres]じゃないってこと。

└

そうそう、業界を席巻したnosqlの流行は、その後のSPAブームと同じくらい耐えがたいものだったよね。今はみんな、信頼できるものに戻ってきてる。ほとんどのデータは再びRDBMSにあり、ほとんどのHTMLはサーバーでレンダリングされてる。私たち世代の開発者は、10年前にこれが来るのを見越してたんだ。

ハクソク