スタートアップのための Postgres 生存ガイド

 (hatchet.run)
3 ポイント 投稿者 GN⁺ 4 시간 전 | 1件のコメント | WhatsAppで共有
  • Hatchet が 2年間プロダクションで直面した問題をもとに、初期の スキーマ・クエリ設計 から大量書き込みやテーブル移行まで、段階別の運用原則を整理
  • 高速な読み取りのためにインデックスと ORDER BY を一致させる一方、クエリプランナ は統計とコストに応じて順次スキャンを選ぶことがあるため、EXPLAIN ANALYZE で推定値と実際の実行を比較する必要がある
  • 書き込み性能と安定性は、短いトランザクション、必要な行だけのロック、CREATE INDEX CONCURRENTLY、接続プーリングに依存し、バッチ処理 は Hatchet の測定でスループットを約10倍に高めた
  • 高頻度書き込み環境では、標準の autovacuum 設定 では dead tuple と transaction ID を適時に回収できないことがあり、transaction ID wraparound に達すると大規模なダウンタイムが発生する
  • 規模が大きくなったら、FOR UPDATE SKIP LOCKED ベースのジョブキュー、パーティショニング、トリガーとバッチバックフィルを活用しつつ、ORM の抽象化の外で SQL を直接制御 できる必要がある

対象読者と ORM の限界

  • SQL、行、テーブル、インデックスの基本概念を知っている開発者が、プロダクションの Postgres 問題に対応できるよう構成されたガイド
  • Postgres マニュアル は包括的だが、障害時に素早く参照するのは難しいため、Hatchet が 2年間で得た運用経験を中心に圧縮している
  • ORM を使っていても原則は適用できるが、規模が大きくなるほど抽象化レイヤーを離れて SQL を直接書く 必要がある最適化が増える
    • Prisma TypedSQL のような機能で、ORM と直接 SQL を併用できる
    • Go ベースの Hatchet は、同様の動作を提供する sqlc を使っている
    • Claude がクエリを書く環境には supabase/agent-skills を推奨

変更しにくいスキーマ設計

  • デプロイ後は スキーマ変更 が最も難しいため、テーブルと主キーのたたき台を作ったうえで、アプリケーションに必要なクエリを書きながら反復的に設計すべき
  • 設計過程では、次の質問でテーブルの使われ方を確認する
    • 読み取りと書き込みのどちらが高頻度か
    • 読み取る際に最もよく使うフィルタは何か
    • 最も頻繁に更新する列は何か
  • データベース正規化 の 1NF・2NF・3NF を適用できるが、正規形がクエリ効率や高速開発に必要な使い勝手と衝突することもある
    • 一部の状況では、データを jsonb 列に入れるほうが単純
  • スキーマ設計に適用している経験則は次のとおり
    • 主キーには identity 列の自動増分整数か、Postgres 組み込み UUID を使う
    • identity 列は bigserial よりやや速い
    • 時刻には常に timestamptz を使う
    • すべてのテーブルに主キーを置く
    • 一貫性と正確性が重要な低容量テーブルでは cascade delete を含む外部キーを使うが、高容量環境では注意が必要

読み取りクエリとインデックス

  • 高速な SELECT を理解するための単純なモデルは、Postgres がインデックスで1行を高速に見つけるか、順次スキャン (seq scan) でテーブルの全行を読むかのどちらか、というもの
  • 高速な単一行検索には次の構造を使う
    • 明示的なインデックス
    • インデックスの特殊形である unique constraint
    • Postgres が自動でインデックス化する主キー
  • 基本インデックスには btree を使い、参照に最適化された形でデータを保存する別テーブルのようなものと考えられる
    • 行の探索時間はおおむね log(n) で、n はテーブルの行数
  • インデックスを使えない場合は順次スキャンになるが、現代のデータベースは行を高速にメモリへ載せるため、2万行未満 のテーブルならほぼ即座に終わることがある

結合と複合インデックス

  • 内部結合の対象には概ね主キーを使うべきで、そうでないならスキーマ設計や正規化に問題がある可能性がある
  • ON 句も WHERE 句のように扱い、結合条件に適切な インデックス を使う必要がある
  • 大きなテーブルの一覧取得は、アプリケーションで最初に遅くなりやすいクエリになりがち
    • 組織と作成時刻を一緒にフィルタし並べ替えるなら、複合インデックスを使える
CREATE INDEX CONCURRENTLY idx_documents_org_created
    ON documents (organization_id, created_at DESC);
  • 複雑なクエリでは、ORDER BY の列をインデックスの最後に置き、並び順も合わせるのが経験則
    • Postgres は btree を双方向にスキャンできるため、単一列では DESC が無意味なこともあるが、複合インデックスでは合わせておくほうがよい
    • 降順インデックスの詳細な挙動は 関連資料 を参照

書き込み、ロック、マイグレーション

  • 成功する書き込みの第一条件は、トランザクションを短く保つ こと
    • 特別な理由がない限り、トランザクション中に外部サービスを問い合わせない
  • 第二条件は、必要な行だけをロックすること
    • 行を更新すると、そのトランザクションがコミットされるまでその行にロックがかかる
    • システム負荷が高まるほど、ロックの影響も目立つ
  • 既存の大規模テーブルで通常の CREATE INDEX を実行するとテーブルがロックされ、insert と update がブロックされるため、常に CREATE INDEX CONCURRENTLY を使う
  • 優れた スキーママイグレーション 能力は、反復開発の速度を上げ、稼働時間を延ばす
    • 可能な限り列の削除や除去を避け、追加方式で変更する
    • 可能ならトランザクション内で実行し、ロールバックや部分適用に備える
    • より発展的な方法として expand and contract マイグレーションを使える
  • マイグレーションでは、まず全書き込みをブロックするかどうかを判断すべき
    • CONCURRENTLY なしのインデックス作成は、全書き込みを止めてダウンタイムを引き起こす可能性がある
    • ALTER TABLE 作業は再確認が必要で、大規模テーブルへの check constraint 追加も書き込みをブロックしうる
    • check constraint を NOT VALID で追加すれば、そのブロックを避けられる

接続管理

  • すべてのクエリとトランザクションはデータベース 接続 を使い、接続は CPU とメモリのコストが高いため、長く維持すべき
  • 接続を頻繁に作成・破棄するとリソースが無駄になる
    • 同時に大量の新規接続が発生する connection storm は、Postgres 内部ロックに関わるデバッグしにくい問題を引き起こすことがある
  • 外部接続プーラ pgbouncer をまず検討し、使えない場合はインメモリ接続プールを代替にする
    • Hatchet はユーザーのデータベースが外部プーラを使っていると仮定できないため、Go 向けの pgxpool を使っている

クエリプランナと統計

  • 結合が多い、あるいは複数の結合方式が混在する複雑なクエリは、単にインデックスを追加するだけでは解決しない
    • インデックス自体にもオーバーヘッドがあるため、無制限に増やすべきではない
  • クエリプランナ は SQL を内部のデータベース演算に変換し、インデックス利用の有無などを決めるが、限られた情報のため最適な計画を選べないことがある
  • プランナが使う情報はテーブル統計で、pg_stats から確認できる
SELECT *
FROM pg_stats
WHERE tablename = 'mytable';
  • 統計は ANALYZE 時に収集され、autovacuum 実行時にも更新される
    • autovacuum の頻度を上げれば、クエリ統計も最新に保てる
    • クエリがうまく動かない一般的な原因の1つは、分析頻度の不足
  • クエリを順次スキャンかどうかで単純に判断すると、細かな最適化でプランナの予測不能性を高める事態を減らせる
    • 主キーとインデックス中心で参照すれば、プランナも計画を選びやすい

実行計画の分析と順次スキャン

  • Google CloudSQL のようにクエリをサンプリングして遅いクエリを保存するプロバイダもあるが、すべてのサービスが対応しているわけではない
  • EXPLAIN ANALYZE はクエリを実際に実行し、テーブル統計に基づく推定行数と実際のスキャン行数を比較する
    • プロダクションでは実クエリが実行されるため注意が必要
    • 実行せず計画だけ確認したいなら、ANALYZE を外した EXPLAIN を使う
  • 詳細計画を JSON で保存し、explain.dalibo.com で可視化できる
psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json
  • 統計とインデックスが正常なのに順次スキャンになるなら、プランナが 順次スキャンのコストのほうが低い と計算した可能性がある
    • インデックスは実テーブルデータがある heap とは別に保存されるため、インデックスで見つけた複数行を heap から再読込するコストが発生する
    • クエリを大きく組み替えられないなら、順次スキャンを受け入れるか、パーティショニングを検討すべき

大量書き込みとバッチ処理

  • 各クエリには、データベースとの往復時間、アプリケーションの接続プールから接続を取得する時間、Postgres の処理時間というオーバーヘッドがある
    • Postgres 内部ロックも高スループット環境ではボトルネックになりうる
  • 1つのクエリに複数行をまとめれば、こうしたコストを減らせる
    • 最も単純な方法は、暗黙的トランザクションとして複数クエリをまとめてサーバーへ送ること
    • Go では pgx の SendBatch を使える
  • Hatchet では バッチ処理でスループットが約10倍 に増え、追加の挿入最適化は 高速 Postgres 挿入ガイド にまとめられている

autovacuum と transaction ID wraparound

  • autovacuum は dead tuple の整理と transaction ID の管理を担い、高頻度書き込み環境では設定調整が必要になることがある
  • tuple はファイルシステムに保存された行の1バージョン
    • 行を更新または削除しても、それより前に開始したすべてのトランザクションがコミットまたはロールバックするまでは、既存バージョンが残る
    • どのトランザクションからももう読めないバージョンが dead tuple
  • 書き込み速度が速すぎると、autovacuum が dead tuple の生成速度に追いつけず、データベースの状態が急速に悪化することがある
  • pg_stat_activity でアクティブプロセスを確認したとき、autovacuum クエリが 約1時間以上 実行中なら、設定変更を検討すべき
  • autovacuum が回収する前に transaction ID を使い切ると、transaction ID wraparound が発生し、大規模なダウンタイムにつながる

テーブルとインデックスの肥大化

  • Postgres はディスク上の 8KB ページ に行を保存し、既存ページに新しい行を入れられないと新しいページを作成する
  • dead tuple 回収後にページが部分的に空くと、テーブル肥大化 (table bloat) が発生し、ディスク使用量が大きく増えることがある
    • 最良の予防策は、肥大化する前に autovacuum を調整すること
    • すでに肥大化したテーブルには pg_repack のような拡張を使える
    • 組み込みの VACUUM FULL は、ほとんどの場合よい選択ではない
    • Postgres 19 には同時テーブル再パックのための REPACK...CONCURRENTLY が追加予定だが、Hatchet はまだ試していない
  • インデックス肥大化 もテーブル肥大化の特殊形で、適切な autovacuum 設定で抑えられる
    • すでに肥大化したインデックスには、組み込みコマンド REINDEX INDEX CONCURRENTLY を使える

FOR UPDATE SKIP LOCKED ベースの並行処理

  • FOR UPDATE SKIP LOCKED は、選択した行を現在のトランザクション用に確保しつつ、他のクエリを妨げない
  • Hatchet はこれを ジョブキュー に使っており、1つのクエリで待機中ジョブをロックして状態を RUNNING に変更できる
WITH eligible_tasks AS (
    SELECT *
    FROM tasks
    WHERE status = 'QUEUED'
    ORDER BY id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;
  • 互いに独立した行を同時更新する場合や、複数のアプリケーションインスタンスがオブジェクトの lease を管理する場合にも有用
    • Hatchet は複数エンジンに tenant lease を分配するために使っている

パーティショニング

  • Postgres 組み込みの パーティショニング は、timestamp や hash などの行値を基準にテーブルを分割する
  • 時系列データや Hatchet の過去ジョブデータでは、次の利点がある
    • パーティションごとに独立して autovacuum を実行でき、テーブル全体の autovacuum 処理規模を拡大できる
    • 古いデータを行単位で削除せず、パーティションテーブルを drop してほぼ即座に除去できる
  • 計画段階で Postgres が不要なパーティションを除外できないと、読み取りクエリにオーバーヘッドが生じることがある

大規模テーブル間のデータ移行

  • ここで言う大規模テーブル移行はスキーマ変更ではなく、1つのテーブルから別のテーブルへ 大量データを移す 作業
  • 非常に大きなテーブルを単一トランザクションでコピーすると、何時間もかかることがある
    • 長時間トランザクションは autovacuum の正常動作を妨げ、dead tuple の肥大化を招く
    • 移行元テーブルに継続して書き込みがあると、新テーブルにはそのデータが反映されない
  • Hatchet はトランザクション外で大きな バッチバックフィル を実行し、移行開始後の新規書き込みは Postgres トリガーで新テーブルへコピーする
    • 主キーの unique constraint を利用して重複書き込みを防ぐ

関連記事

1件のコメント

 
GN⁺ 4 시간 전
Hacker Newsのコメント

  • 本番データベースなら、まず バックアップ・リストア計画 を立てるべきではないかと思う。高可用性は初期段階では任意かもしれないが、サバイバルガイドにバックアップとリストアがないのは不思議だ
    PostgreSQL のバックアップでは、今でも Barman(https://pgbarman.org/) をよく使っているのか気になる

    • PostgreSQL の専門家でないなら自前運用はせず、RDS のようなマネージドデータベース を使うほうがよい。自前ホスティングで節約できる費用は、実証済みの高可用性、バックアップ・リストア、ポイントインタイムリカバリ、リードレプリカを得るコストに比べればわずかだ
    • pgBackRest を使っている。以前使っていた夜間バックアップの自前ソリューションより優れた ポイントインタイムリカバリ を提供してくれるし、Backblaze B2 へのバックアップも比較的簡単に設定でき、特に問題もなかった
    • ほとんどの場合、cron で pg_dump_all を実行して zstd で圧縮し、S3 や FTP などにコピーする程度で十分だ。データが大きくなるとフルバックアップの時間とコストが負担になるが、この単純な方式でもかなり長く持ちこたえられる
    • 電源障害時にも耐久性が保証されるデータベースなら、アトミックなボリュームスナップショット でバックアップできる。復旧時間を短縮するには先にチェックポイントを作成し、データ破損を防ぐにはスナップショットのアトミック性が必ず保証されていなければならない
      AWS で数 TB 規模の MongoDB を EBS スナップショットでバックアップし、高速な増分バックアップとリストアを実現した。ポイントインタイムリカバリはできないが、時間単位で頻繁に取得できるため、PostgreSQL 専用ツールと併用する補助戦略として適している
    • すでに Kubernetes を運用しているなら、CloudNativePG を使えばよい

  • いくつか補足したい点がある。一般的な UUIDv4 より UUIDv7 を使い、ロックする行数だけでなく、すべてのクエリでロック順序を id ASC のように決定的に統一しないとデッドロックを避けられない
    EXPLAIN (GENERIC_PLAN) を使うと、パラメータプレースホルダーを保ったままクエリをコピーでき、PostgreSQL が実際の値を知らないときの最適化プランも見られる。空のテーブルや小さいテーブルでは SET enable_seqscan = off でインデックス利用の可能性を確認できる
    みんながデフォルトで使う B-tree インデックスは重く肥大化しやすいので、並べ替えや範囲検索なしの単純な参照だけなら、ハッシュインデックスも検討に値する。ユニークなハッシュインデックスは作れないが、ハッシュ排他制約で似た効果を得ることはでき、複数列ユニークインデックスはサポートされない
    GIN・GiST インデックス も学んでおくとよい。MySQL ユーザーには意外かもしれないが、フルテキスト検索に変えなくても普通の LIKE '%foo%' クエリを高速化できる

    • ロックする行集合に一貫した ORDER BY がない場合だけでなく、テーブルのロック順序 が異なるときにもデッドロックは発生する。あるトランザクションが table_a、table_b の順でロックし、別のトランザクションが逆順でロックすると、各テーブル内で ORDER BY と FOR UPDATE を使っていてもデッドロックになる
      理論上は明らかだが、実際にはすべての書き込みが触るテーブルをグローバルに把握する必要があるため、デバッグははるかに難しく、特定の拡張機能で実際に遭遇したことがある。JSONB のキー・バリュー参照に GIN を試しているが、性能向上は非常に大きく、AND と OR の間の性能差もかなりあった
    • どんな UUID でも主キーに使うと主キー結合が多発してコストが高く、たいてい得るものは少ない。デフォルトでは 連番の主キー を使い、外部公開が必要なら補助インデックス付きの UUIDv4 カラムを追加するほうが安全だ。UUIDv7 が UUIDv4 より B-tree 性能で実際に優れているのか気になる
    • シーケンシャルスキャンを無効にすると、インデックスが 1 つでもあれば PostgreSQL がそのインデックスを無理やり使うのではないかと思う。したがって、正しいインデックス かどうかまでは分からない気がする
    • UUIDv7 と UUIDv4 の変換ツールとして、https://github.com/ali-master/uuidv47 と https://github.com/stateless-me/uuidv47 が何度か紹介されていた

  • この助言も良いが、一緒に働いたスタートアップはスケーラビリティより手前にある組織的な問題にまずぶつかっていた。ORMは使わず、意味のあるフィールドの代わりに連番の主キーを使い、JSONBは本当に必要なときだけ限定的に使うほうがよい
    元データは挿入のみ可能な追記専用にして、更新・削除しないようにすべきだ。性能と利便性のための非正規化された補助テーブルは変更してもよいが、真実の源泉にしてはならない
    コネクションプールは使うべきだが接続数には注意し、問題がないならPgBouncerまでは不要かもしれない。明確な理由がなければ明示的トランザクションは避け、開いたままRPCのような長時間処理をしてはならず、SERIALIZABLEもほとんど使わないほうがよい
    SELECT FOR UPDATEのような明示的ロックが必要なら、設計が誤っている可能性がある。type intの値に応じて1つのテーブルの行に複数の意味を持たせて型システムを再発明したり、自分自身を参照するnode・edgeテーブルでグラフデータベースをまねたりしてはならない。たいていは普通の正規化テーブルで解決できる

    • 作業中のPHPバックエンドでは権限チェックなどのためにオブジェクトをインスタンス化する必要があるので、ORMは非常に有用。ORMなしで実装するとずっと作業が増えそうだが、なぜ悪い選択なのか気になる
    • 開発者の給与が最大のコストなら、ORMを使うなという原則は議論の余地がある。テーブルのビジネス要件、顧客からの圧力、厳しい予算の下では、DBAと正しい設計を長く議論している間にもコストは燃え続けるので、型カラムやグラフ的構造を避けろという原則も言うほど簡単ではない
    • すばやく製品を立ち上げる必要があるスタートアップにとって、ORMは十分に良い選択だ。N+1クエリや遅延ローディング方式のような落とし穴を理解していれば、クエリ管理やパラメータ化をまた自前で作るよりましな折衷案だ
      プロジェクト初期にデータベーススキーマを過度に考えたり性急に最適化したりするより、製品開発に時間を使うほうを選びたい
    • SELECT FOR UPDATEをいろいろな場所で有用に使ったが、何が問題なのか気になる。追記専用の真実の源泉を使うと、こうしたロックが不要になるのかも知りたい
    • 追記専用の元データは魅力的だが、関わった複数のシステムでは、疑わしい利益のためにかなりの数のテーブルの保存量を爆発的に増やしていただろう。有用な手法ではあるが、あらゆる場所に強制する原則なのかは疑問だ
      逆に、従来どおり変更可能な関係テーブルを真実の源泉にして、トリガーで変更ログを記録する方式はどうなのか気になる

  • カスケード削除は嫌いだ。ほとんどの開発者はデータベースではなくPython、Node、Goのようなアプリケーション層で生きているので、テーブルAの行を消したらテーブルBのデータまで消えるカスケード削除は魔法のように見えやすい。設定を誤るとより危険なので、長期保守では明示的な削除文のほうがよく、外部キーだけを正しく使っても整合性は保てる
    大規模テーブル移行の落とし穴と回避法はその通りだが、pg-oscのようなツールはすでにある。コマンドを1つ実行したあと、データがコピーされる24時間のあいだ緊張しながら観察する程度に単純であるべきだ
    アプリケーションとデータベースのデプロイは早い段階から分離すべきだ。スキーマとアプリケーション変更を完全に同時にトランザクションとしてデプロイすることはできないので、本番に入ったら新しいカラムをnullableにするかデフォルト値を置く、テーブル名やカラム名を変えないなど、後方互換なスキーマ変更だけを行う習慣が必要だ
    スキーマ管理戦略も早めに決めるべきだ。シニア開発者が自分のPCから本番DBにDDLを手動実行するデプロイ手順は避けるべきで、慣れているLiquibaseやFlywayのようなツールを使える

    • 宣言的スキーマ管理ツールのpgschemaを作った

  • クエリプランナは平均的なケースを最適化するが、アプリケーションでは最悪ケースを最適化したほうが有用なことがある。平均的なユーザーは行数が少ないため特定のインデックスで10ms以内に結果が出たが、利用量の多いユーザーは同じクエリでもパラメータによって1秒以上かかった
    さらに複雑なクエリで別のインデックス経路を強制したところ、平均性能は少し遅くなったが最悪ケースも100ms未満に減った。会社にとっては平均で10ms節約することよりも、タイムアウト防止のほうがはるかに重要だった

  • SKIP LOCKEDは、アプリケーションが作業しているあいだトランザクションを開いて行をロックする対話型トランザクションベースの作業キューに有用だ。高性能アプリケーションでは、そのようなトランザクション自体を避けて行を即座にpendingへ更新すればよいので、SKIP LOCKEDは不要になる
    規模が大きくなるほどデータベースメモリに保持する状態を減らすべきで、対話型トランザクションもそうした状態に含まれる。スケール環境では冪等性は原子性より有利だ

  • 長時間トランザクションはデータベース状態を損なう可能性があるので、強い根拠があるときだけ使うべきだ。idle_in_transaction_session_timeoutでアイドルトランザクションがロックやタプルを長く握り続けないようにし、マイグレーションにはlock_timeoutを設定して、1つのDDLがシステム全体を止めないようにすべきだ
    高コストなクエリ1本がシステムを麻痺させないよう、statement_timeoutも設定すべきだ

  • スタートアップ初期にPostgreSQLを運用してみると、この記事は監視とアラートを十分に強調していない。PostgreSQLには必ず避けるべき重要な障害類型がいくつかあり、アラートで危険を早期に捉えられる
    AWSがトランザクションIDラップアラウンドに近づいたとメールを送ってきても、スタートアップでは、とくにボクシングデーのような日には簡単に見落とせる。AWSが監視しているシグナルはメールではなくページャーにつなぐべきだ

  • コネクションプール実装にはあまり知られていない大きな違いがある。ほとんどのアプリケーション接続プールは**先入れ先出し（FIFO）で低レイテンシと接続可用性を最適化するが、接続を常に温かいまま保つので不要な接続を減らしにくい
    PgBouncerや一部の外部プーラは    後入れ先出し（LIFO）**を使って、PostgreSQLに到達する接続数とスループットを最適化する。最も新しい接続を先に再利用すると、余った接続は自然に冷えて終了する
    新しいアプリケーションにはFIFOで十分だが、規模が大きくなったらPgBouncerのようなツールで数百の接続を90%ほど減らすほうがよい。接続ごとにプロセスを作るPostgreSQLの構造は、接続数が少ないほどよりうまく動く

  • 非常に限定的な状況では、アプリケーションメモリ上で結合してよい結果が得られた。データベースとの往復を減らそうとして、複雑な JOIN、UNION、CASE が絡み合った単一クエリを作ってしまうことがある
    その代わりに単純なクエリを複数独立して実行し、その後で結果を走査しながらマップで関連行を結び付けると、往復や反復のコストが増えても、クエリ計画がより予測しやすくなり、かえって有利になる場合がある。これは限定的にのみ使うものであり、一部の ORM が内部的にこのように動作しているからといって、無条件に推奨するものではない

    • この方式の効果は状況に大きく左右される。結合によって元データよりはるかに大きい デカルト積 が作られるなら、元の集合だけを取得してローカルで組み合わせるほうが、DB 負荷とネットワークトラフィックを減らせることがある
      ただし、選択的な内部結合は元データよりはるかに小さい結果を作るため、すべてのレコードを取得してローカルで積集合やフィルタリングを行うほうが、はるかに高コストになる。インデックス結合では、クエリプランナーがインデックスを活用して、無差別なテーブルスキャン、ソート、フィルタリングを避けられる場合もある
    • 複雑な単一クエリの代わりに、ビューを2つ作ってから結合する方式も使われていると理解している