NULL制約のロックを最小化して高速化する(翻訳)|TechRacho(テックラッチョ)〜エンジニアの「?」を「!」に〜|BPS株式会社エンジニアの「?」を「!」に。原著者の許諾を得て翻訳・公開いたします。Escobar画像は元記事からの引用です。NULL制約のロックを最小化して高速化する(翻訳)テーブル更新中にロックされたPostgreSQLDoctolibは医師や患者向けに年中無休で運営されるWebサービスです。あらゆる医師のアジェンダを取り扱うという任務を完了したので、現在の重要な課題はダウンタイムゼロです。医師が自分たちのアジェンダやイベント通知にいついかなるときでもアクセスできるようにしなければなりません。サービスとして、新機能リリースのためにときおりデータベーススキーマのマイグレーションを行っています。このマイグレーションはリスクを伴うことがあり、サービス中断を何としても避けるために注意深く行わなければなりません。マイグレーションは、データベーステーブルの変更を大規模に実施する場合は特別に慎重になります。Doctolibでは数日おきにデータのマイグレーションを行っていますが、残念なことにテーブルのデータ総量が著しく大きい(3000万行以上)ため、標準的なマイグレーションのベストプラクティスではサービスのダウンタイムを十分防止できないことがあります。私たちは現在危険な操作を防止するツールを使っていますが、マイグレーションによっては追加の安全策が必要になることもあります。データベース制約の利用はデータの破損防止のために重要ですが、データベース制約の追加操作を誤るとテーブルをロックする危険があります。本記事でこの後説明するように、私たちはこの問題を別の角度から検討することにしました。NULL制約を追加して操作中の読み書きを一切ロックせずに巨大なテーブルをマイグレーションすることができます。厄介なマイグレーションをPostgreSQLで行うより安全性の高いマイグレーションの必要性に気づかせてくれた、この事例についてもう少し詳しく説明します。NULL制約を追加します。データ自体はさほど巨大ではありませんが、うかつなマイグレーションを行うと一部のサービスでダウンタイムが生じる可能性があります。NULL制約を追加しようとする場合は、PostgreSQLで次のようにアトミックな操作として実行します。NULL;これによってPostgreSQLでは次が行われます。LOCKを取得(書き込みをテーブル全体でロックする)(https://www.postgresql.org/docs/current/static/explicit-locking.html)など)この操作は、次の場合にリスクを伴います。そのテーブルを意図的に変更する場合フルスキャンに時間がかかる場合(特に巨大なテーブルを扱う場合)これによって、アプリのワーカーが待ち状態になってアプリがフリーズし、問題がたちまち広がって操作中にサービス全体が停止する可能性があります。staging環境で試してみたところ、3000万行で操作に1.7秒を要しました。このテーブルに1秒間に100回書き込みを行っていたら、操作中に100を超えるデータベース接続がロックされたでしょう。NULL制約を追加し、慎重に作業を進めるためにサービスの利用頻度が最も低い適切な時間を選んでマイグレーション計画を立て、コストの大きいこのマイグレーション中はメンテナンスモードに切り替えればよいのでは、というものです。私たちがこのアドバイスを採用したくない理由がおわかりでしょうか。マイグレーション対象であるこのテーブルは、アプリの中でも重要な部分であり、医師や患者からのトラフィックが少ない夜中を選んでマイグレーションを決行する以外の選択肢がなくなってしまいます。残念ながら、夜勤中の医師にとってはこれでも問題が生じることが示されるでしょう。Doctolibユーザーに不便を強いるあらゆるリスクも回避できる、もっと賢い方法でなければなりません。CONSTRAINTで切り抜けるPostgreSQLのドキュメントを見てみましょう。NOT-NULL制約は、常にカラムの制約として記述される。NULL)を作成することと機能上同等である。CONSTRAINTはある意味でカラム制約と似ているのですが、これはそのテーブルに属しています。CONSTRAINTは次のようになります。CONSTRAINTは値の非NULLを強制できる点です。VALIDオプションを発行できることです。このオプションを指定すると、「この制約は既存データについては有効ではない可能性があるので(既存データの)チェックは必ずしも必要なわけではない」とあなたが認識していることがPostgreSQLに伝わります。以後のINSERTやUPDATEでは制約が強制されます。本質的にこのオプションは、テーブルで行われる可能性がある仰々しい初期チェックを行わなくなります。LOCKが取得されてテーブルへの書き込みは差し止められますが、すべての行に対するバリデーションを行わなくなるので非常に高速です(私たちのデモ環境では6ms)。CONSTRAINTを追加する。制約名;このVALIDATEコマンドは以下を実行します。TABLEコマンドなどと同様、スキーマ変更だけをロック)を取得。このテーブルへの読み書きは引き続き可能。PostgreSQLは、新しいデータについては(制約が)既に強制されていることを前提とするので、テーブル上の既存データをチェックして制約が有効であることを確認する。したがって、書き込みロックは完全に不要になる。nullの違い最終的な結果は同じになることもありますが、それでもいくつかの違いがあります。チェック制約は名前が必要であり、テーブルに属する必要があります。NULLカラムは後者のオプションのひとつに過ぎません。パフォーマンスの観点から、PostgreSQLドキュメントには次のように書かれています。PostgreSQLにおいては、明示的にnot-null制約を作成する方が効率が高い。Stackexchangeのベンチマークによると、書き込み時のパフォーマンスで0.5%以上のペナルティが生じます。私たちのテストでは1%程度だったので私たちの事例では無視できますが、状況によってはパラメータで懸念が生じることがあるかもしれません。;-)新しい標準?この種のマイグレーションは、サービス全体で用いられるコアのテーブルを変更する場合や、大量のデータが頻繁に変更される場合に非常にトリッキーになることがあります。幸いなことに、コアテーブルの変更の必要性は時間とともに減少します。CONSTRAINTSをコアテーブルに追加するような状況で使ったことがありません。このマイグレーションは1回成功していますが、まだDoctolibの標準として定めていません。何より、このソリューションは私たちの「驚き最小の法則」の哲学に沿っていません。私たちの事例では容認できますが、今後もそうとは限りません。この状況に直面するときがきたら、私たちのニーズに合ったソリューションを再度検討しなければならないでしょう。パリで私たちのチームに参加いただけるPostgreSQLラブな開発者を募集しています。www.doctolib.fr/jobs/engineeringまでどうぞ。コボラー、ITコンサル、ローカライズ業界、Rails開発を経てTechRachoの編集・記事作成を担当。チュートリアル第2版の監修および半分程度を翻訳、Railsガイドの初期翻訳ではほぼすべてを翻訳。その後も折に触れて更新翻訳中。enno.jpを運営。実は最近Go言語が好きで、Goで書かれたRubyライクなGoby言語のメンテナーでもある。仕事に関係ないすっとこブログ「あけてくれ」は2000年頃から多少の中断をはさんで継続、現在はnote.muに移転。Storageを使ってはいけない(翻訳)#Tags#会社紹介#自社サービス#勉強会#Rails#PHP#デザイン#実績#週刊RailsウオッチCONTACTお気軽にご相談ください。(翻訳)CONTACTTechRachoでは、パートナーシップをご検討いただける方からのご連絡をお待ちしております。お気軽にご意見・ご相談ください。