スタートアップ向けPostgres生存ガイド

スタートアップのPostgres生存ガイド Postgresがダウンするのを防ぐためのガイド。 Alexander Belanger 共同創業者 Hatchet 過去半年ほど、エンジニア向けの内部ドキュメントを作成してきました。これは、2年間のPostgresとの格闘をある程度まとまったドキュメントにしようとする試みです。Postgresのマニュアルは好きですが、問題が発生したときに参照するのが難しいと感じています。なぜなら、あまりにも網羅的すぎるからです。これは他の人にも役立つかもしれないと思い、本番環境でPostgresを運用して学んだこと（または他のヒント）があれば、フィードバックをいただけると幸いです。 Hatchetを始める前は、SQLには慣れていましたが、知識の範囲は基本的に「クエリが遅ければインデックスが必要」という程度でした。このドキュメントの出発点はそこです。SQLの基本、行、テーブルについて理解しており、インデックスが何であるか大体わかっていることを前提とします。そして、もしClaudeがすべてのクエリを書いているなら、これは時間の無駄かもしれません！ supabase/agent-skillsを推奨します。 ORMに関する簡単な注意 このガイドは依然として役立つはずですが、いくつかのヒントを好みのORMに翻訳する必要があるかもしれません。スケールアップする際の多くの最適化は、抽象化レイヤーを越えてSQLを書くことができない限り、ORMでは不可能です。これは優雅に、またはそうでない方法で行うことができます。Prisma TypedSQLや同等のものは興味深いです。Hatchetではsqlcを使用しており、非常に似た動作が得られます。Goスタックを使用しているなら強くお勧めします。 目次 簡単なこと：良い読み取り、書き込み、スキーマ 良いスキーマを書く 良い読み取りクエリを書く パフォーマンスの良いJOINを書く 複合インデックスとORDER BYをインデックスに合わせる 良い書き込みクエリを書く マイグレーション 接続管理 中級：クエリプランナー、バルク更新、オートバキューム 最も漏れやすい抽象化、クエリプランナーを紹介します シーケンシャルスキャンが理にかなる場合 大量のデータを書き込む デフォルトのオートバキューム設定がデータベースを破壊する可能性があります その他の種類のブロート 高度なこと FOR UPDATE SKIP LOCKED パーティショニング 大規模テーブルマイグレーションのトリック 簡単なこと：良い読み取り、書き込み、スキーマ 基本から始めましょう：低ボリュームでのクエリとスキーマ。 良いスキーマを書く デプロイ後、スキーマは将来的に変更するのが最も難しいため、時間をかける価値があります。スキーマは反復的に構築することをお勧めします。まずテーブルと主キーのラフな近似を作成し、次にアプリケーションのニーズに基づいてそれらのテーブルに対してクエリを記述します。いくつかの質問でこれを近似できます。このテーブルは読み取り/書き込みが多いですか？読み取りで最も一般的なフィルターは何ですか？どの列を最も頻繁に更新しますか？より正式にしたい場合は、データベース正規化（1NF/2NF/3NF）を検討できますが、正規形はクエリの効率や使いやすさと対立することがあります。これは、速く動く必要がある場合に重要です。時にはjsonb列にデータをダンプする方が簡単なこともあります。 スキーマに関する私の経験則は次のとおりです。 主キーにはID列（自動インクリメント整数、bigserialよりもわずかにパフォーマンスが良い）または組み込みUUIDを使用する 常にtimestamptzを使用する 常に主キーを使用する 低ボリュームテーブル、特にデータベースの一貫性と正確性が重要な場合は、カスケード削除付きの外部キーを使用する。高ボリュームでは注意が必要。 良い読み取りクエリを書く SELECTクエリから始めましょう。高速なSELECTのための有用な（ただし少し不正確な）メンタルモデルは次のとおりです。内部的には、Postgresはテーブルから単一行を非常に迅速に見つけるか、シーケンシャルスキャンと呼ばれるものを使用してテーブルのすべての行を読み取ります😞。インデックス、一意制約（インデックスの特別なケース）、または主キー（Postgresでは自動的にインデックスが付けられます）でフィルターする場合、テーブルから単一行を非常に迅速に見つけます。 インデックスはデフォルトでbtree実装を使用します。インデックスをPostgresの別のテーブルのように考えるのが最も役立ちます。データはルックアップに最適化された特定の形式で格納されています（これについては後述）。これらのツリーは、単一行を見つけるのに約log(n)時間（nはテーブルの行数）で済むため、非常に高速です。 Postgresがインデックスを使用できない場合、シーケンシャルスキャン、またはseq scanと呼ばれるものを使用します。Seq scanはインデックスルックアップよりもはるかに遅いですが、最新のデータベースは行をメモリにロードするのが非常に速いため、最初はほとんど気づかないでしょう。20,000行未満のテーブルでのseq scanはほぼ瞬時です。 パフォーマンスの良いJOINを書く 内部JOINの場合、主キーを内部JOINとして使用することに異論を唱えることはめったにありません。通常、これはスキーマ設計または正規化の問題を示しています。ON句はWHERE句と同じように扱い、同じ原則が適用されます。インデックスを使用してください。 複合インデックスとORDER BYをインデックスに合わせる アプリケーションで最初に遅くなるクエリは、大規模テーブルでのリストクエリであることがよくあります。たとえば、次のようになります。 ```sql SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY last_login DESC; ``` この場合、複合インデックスを使用できます。適切なものは次のようになります。 ```sql CREATE INDEX users_created_at_last_login_idx ON users (created_at, last_login DESC); ``` より複雑なケースでは、良い経験則は次のとおりです。ORDER BYの列はインデックスの最後の列であるべきであり、ORDER BYの順序に合わせて列を配置する必要があります。Postgresはbtreeを双方向にスキャンできるため、DESCは無関係な場合もありますが、複合インデックスでは良い習慣です。詳細はこちらをご覧ください。 良い書き込みクエリを書く 成功する書き込みの前提は次のとおりです。トランザクションを短く保つ。本当に特別な理由がない限り、トランザクションの途中で外部サービスにクエリを実行しないでください。書き込み用にロックする行に注意してください。つまり、必要なものだけをロックしてください。行を更新するたびに、トランザクションがコミットされるまでの短い期間、その行のロックを取得します。システムが忙しくなるにつれて、ロックの影響がより顕著になります。 特に、将来的に単純なCREATE INDEXコマンドでインデックスを作成しようとすることがあります。これはテーブルをロックし、挿入と更新を防止します！既存の大規模テーブルにインデックスを作成する場合、常にCREATE INDEX CONCURRENTLYを使用してください。 マイグレーション マイグレーションを非常にうまく書けるようになることは、重要な技術的利点です。これにより、イテレーションがはるかに速くなり、アップタイムが増加します。開始点として、マイグレーションは加算的（つまり、列を削除したり削除したりしない）に保ち、可能な限りトランザクションで実行するようにしてください。これにより、ロールバックや部分的なマイグレーションの処理がはるかに容易になります。より高度になったら、拡張と縮小のマイグレーションを検討できます。 良いマイグレーションの最も単純なメンタルモデルは次のとおりです。これはすべての書き込みをブロックしますか、それともブロックしませんか？CONCURRENTLYなしでインデックスを作成すると、すべての書き込みがブロックされるため、ダウンタイムが発生する可能性があります。一般的に、ALTER TABLEを呼び出す操作は再検討する価値があります。たとえば、非常に大きなテーブルに新しいCHECK制約を追加すると、書き込みがブロックされる可能性があります（NOT VALIDキーワードで追加しない限り）。 接続管理 データベースに対してトランザクションまたはクエリを実行するたびに、接続が使用されます。接続は多くの次元（CPUとメモリ）で高価であり、高い接続チャーンは多くの不要なリソースの無駄につながる可能性があるため、接続は長寿命であるべきです。接続ストーム（同時に大量の新しい接続を使用し始める場合）は、内部Postgresロックに関連するデバッグが非常に困難なエッジケースにつながる可能性もあります。 これらの接続の落とし穴が多いため、pgbouncerのような外部接続プーラーは素晴らしいです！何らかの理由でこれらを追加できない場合は、インメモリ接続プーラーが優れた第二の選択肢です。たとえば、Hatchetはオープンソースであるため、すべてのユーザーデータベースが接続プーラーを使用しているとは限りません。そのため、この目的のためにpgxpool（Goのインメモリ接続プーラー）を使用しています。 中級：クエリプランナー、バルク更新、オートバキューム 最も漏れやすい抽象化、クエリプランナーを紹介します