スタートアップ向けPostgres生存ガイド
The startup's Postgres survival guide (hatchet.run)
要約
この記事は、スタートアップがPostgresデータベースを安定稼働させるための実践的なガイドです。スキーマ設計、クエリ最適化、インデックス活用、トランザクション管理、接続プーリングといった基本的ながら重要なトピックを網羅し、本番環境でのデータベースのトラブルシューティングに役立つヒントを提供します。特に、パフォーマンス低下の原因となる一般的な落とし穴と、それらを回避するための具体的な手法に焦点を当てています。
全文翻訳
スタートアップのためのPostgresサバイバルガイド
[画像: Alexander Belanger]Alexander Belanger共同創業者Hatchet
この半年ほど、私はエンジニア向けの内部ドキュメントを作成してきました。これは、2年間のPostgresでの苦労を、ある程度まとまったドキュメントにまとめる試みです。Postgresのマニュアルは大好きですが、あまりにも網羅的すぎるため、いざという時に参照するのが難しいと感じています。この記事が他の人々の役にも立ち、本番環境でPostgresを運用して学んだこと(あるいは他のヒント)についてもフィードバックをいただけると嬉しいです。
Hatchetを始める前は、SQLには慣れていましたが、知識の範囲は基本的に「クエリが遅いならインデックスが必要」という程度でした。このドキュメントの出発点はそこです。SQLの基本、行、テーブル、そしてインデックスがおおよそ何であるかについては理解していると仮定します。
そして、もしClaudeがすべてのクエリを書いているなら、これは時間の無駄かもしれません!supabase/agent-skillsをお勧めします
ORMに関する簡単な注記
このガイドは依然として役立つはずですが、いくつかのヒントを、選択したORMに翻訳する必要があるかもしれません。スケーリングに伴う多くの最適化は、抽象化レイヤーを越えてSQLを書くことができない限り、ORMでは不可能です。これは優雅に、あるいはそうでない方法で行うことができます。Prisma TypedSQLやそれに類するものは、この目的に対して興味深いように見えます。Hatchetではsqlcを使用しており、非常に似た動作が得られます。Goスタックを使用しているなら強くお勧めします。
目次
基本的なこと:良い読み書きとスキーマ
まずは基本から始めましょう。低ボリュームでのクエリとスキーマです。
良いスキーマの書き方
デプロイ後、スキーマは将来的に変更するのが最も難しいため、時間をかける価値があります。反復的にスキーマを構築することをお勧めします。まずテーブルと主キーのラフな近似から始め、次にアプリケーションのニーズに基づいてそれらのテーブルに対するクエリを記述します。これはいくつかの質問で近似できます。「このテーブルは読み取り/書き込みが多いか?」「読み取りで最も一般的なフィルターは何か?」「どの列を最も頻繁に更新するか?」
より正式に行いたい場合は、データベース正規化を1NF/2NF/3NFに進むことを検討できますが、正規形はクエリの効率性や使いやすさと相反することがあるとわかっています。これは、速く動く必要がある場合に非常に重要です。時にはjsonbカラムにデータをダンプする方が簡単なこともあります。
スキーマに関する私の経験則は以下の通りです:
- 主キーには、IDカラム(自動インクリメント整数、
bigserialよりわずかに高性能)または組み込みUUIDを使用する - 常に
timestamptzを使用する - 常に主キーを使用する
- 低ボリュームテーブル、特にデータベースの一貫性と正確性が重要な場合は、カスケード削除付きの外部キーを使用する。高ボリュームでは注意が必要。
良い読み取りクエリの書き方
SELECTクエリから始めましょう。高速なSELECTのための有用な(ただし少し不正確な)メンタルモデルは次のとおりです。内部的には、Postgresはテーブルから単一行を非常に速く見つけるか、シーケンシャルスキャンと呼ばれるものを使用してテーブルのすべての行を読み取ります😞。
単一行を非常に速く見つけるのは、以下でフィルターする場合です:
- 明示的なインデックス
- 一意制約(インデックスの特殊なケース)
- 主キー(これらはPostgresで自動的にインデックスが付けられます)
インデックスはデフォルトでbtree実装を使用します。インデックスをPostgresの別のテーブルとして考えるのが最も役立ちます。データはルックアップに最適化された特定の形式で格納されます(これについては後述)。これらのツリーは、単一行を見つけるのに約log(n)時間(nはテーブルの行数)で済むため、非常に高速です。
Postgresがインデックスを使用できない場合、シーケンシャルスキャン、またはseq scanと呼ばれるものを使用します。Seqスキャンはインデックスルックアップよりもはるかに遅いですが、最新のデータベースは行をメモリにロードするのが非常に高速なため、最初はほとんど気づかないでしょう。20,000行未満のテーブルでのSeqスキャンはほぼ瞬時です。
効率的なJOINの書き方
内部JOINの場合、主キーを内部JOINに使用しないことにはほとんど議論の余地がありません。それは通常、スキーマ設計または正規化の問題を示しています。ON句はWHERE句と同じように尊重してください。同じ原則が適用されます。インデックスを使用してください。
複合インデックスとORDER BYをインデックスに合わせる
アプリケーションで最初に見つかる遅いクエリは、大規模テーブルでのリストクエリであることがよくあります。たとえば、次のようになります:
SELECT *
FROM documents
WHERE organization_id = <uuid>
AND created_at >= NOW() - INTERVAL '1 day'
ORDER BY created_at DESC
LIMIT 50;
この場合、複合インデックスを使用できます。適切なものは次のようになります:
CREATE INDEX CONCURRENTLY idx_documents_org_created
ON documents (organization_id, created_at DESC);
より複雑なケースでは、経験則として、ORDER BYの列はインデックスの最後の列にし、ORDER BYの順序に合わせて列を配置することです。Postgresはbtreeを双方向にスキャンできるため、DESCは無関係な場合もありますが、複合インデックスでは良い習慣です。詳細についてはこちらを参照してください。
良い書き込みクエリの書き方
成功する書き込みの前提は次のとおりです:
- トランザクションを短く保つ。 外部サービスにクエリを実行するのは、本当に特別な理由がない限り、トランザクションの途中で行わないでください。
- 書き込みのためにロックしている行に注意する。 言い換えれば、必要なものだけをロックしてください。行を更新するたびに、トランザクションがコミットされるまで、その行に対して短時間ロックを取得しています。
システムが忙しくなるにつれて、ロックの影響がより顕著になり始めます。特に、将来的に単純なCREATE INDEXコマンドでインデックスを作成しようとすると、テーブルがロックされ、挿入と更新が防止されることがわかります!既存の大きなテーブルにインデックスを作成する場合は、常にCREATE INDEX CONCURRENTLYを使用してください。
マイグレーション
マイグレーションを本当にうまく書けるようになることは、重要な技術的利点です。これにより、より速くイテレーションでき、稼働時間が増加します。出発点として、マイグレーションは加算的(つまり、列を削除または削除しない)に保ち、可能な限りトランザクション内で実行するようにしてください。これにより、ロールバックや部分的なマイグレーションの処理が容易になります。より高度になったら、expand and contractマイグレーションを検討し始めることができます。
良いマイグレーションのための最も単純なメンタルモデルは次のとおりです。これは私のすべての書き込みをブロックするか、しないか?CONCURRENTLYなしでインデックスを作成すると、すべての書き込みがブロックされるため、ダウンタイムが発生する可能性があります。一般的に、ALTER TABLEを呼び出す操作は、再検討する価値があります。たとえば、非常に大きなテーブルに新しいCHECK制約を追加すると、書き込みがブロックされる可能性があります(NOT VALIDキーワードで追加しない限り)。
コネクション管理
データベースに対してトランザクションまたはクエリを実行するたびに、コネクションを利用しています。コネクションは多くの次元(CPUとメモリ)で高価であり、高いコネクションチャーンは多くの不必要なリソースの無駄につながる可能性があるため、コネクションは長寿命であるべきです。コネクションストーム(同時に大量の新しいコネクションを使用し始めること)は、内部Postgresロックに関連するデバッグが非常に困難なエッジケースにつながる可能性もあります。
これらのコネクションの落とし穴が多いため、pgbouncerのような外部コネクションプーラーは素晴らしいです!何らかの理由でこれを追加できない場合は、インメモリコネクションプーラーが優れた第二の選択肢です。たとえば、Hatchetはオープンソースであるため、すべてのユーザーデータベースがコネクションプーラーを使用しているとは想定していません。そのため、この目的でpgxpool(Goのインメモリコネクションプーラー)を使用しています。
中級:クエリプランナー、バルク更新、autovacuum
最も漏れやすい抽象化、クエリプランナーの紹介
ある時点を超えると、クエリが単純なインデックスでは対応できないほど複雑になることがあります(そして、テーブルに際限なくインデックスを追加すべきではありません。それらにはオーバーヘッドが伴います)。クエリには多くのJOINステートメントや、データのクエリパスが明確でないさまざまな種類のJOINが含まれる場合があります。
この時点で、クエリプランナーを気にかける必要があります。最良の場合、クエリプランナーは漏れやすい抽象化です。これは内部実装であり、あなたはそれをほとんど制御できませんが、その自発的で時には非合理的な動作を知る必要があります。LLMを扱うようなものです!
クエリプランナーは、あなたが渡したクエリを見て、それをデータベース内の内部操作のセットにどのように変換すべきかを決定します。たとえば、クエリを見て、インデックスを使用する必要があると判断するかもしれません。理想的な世界では、クエリプランナーは、すべてのクエリとパラメータセットに対して、使用すべき完璧なプランを知っているでしょう。しかし、クエリプランナーは限られた情報に基づいて動作しており、時には最良のオプションを選択しないこともあります。
この限られた情報とは、テーブル統計情報です。Postgresで直接クエリできます:
SELECT *
FROM pg_stats
WHERE tablename = 'mytable';
これらの統計情報は、すべてのANALYZEに対して収集されます。これはautovacuumが実行されるときにも発生します(下記参照)。したがって、autovacuumの実行頻度が高いほど、クエリ統計情報もより最新になります。クエリが不適切に動作する一般的な理由は、十分な頻度で分析されていないことです。
クエリをバイナリ(シーケンシャルスキャンするかしないか)として見るのが有用だと考える理由は、クエリをマイクロ最適化すればするほど、クエリプランナーが暴走するリスクが高まるからです。主キーとインデックスによるクエリに固執すれば、クエリプランナーははるかに容易に動作します。
クエリに明らかに問題がないのに、まだ遅い場合はどうすればよいでしょうか?一部のPostgresデータベースプロバイダー(Google CloudSQLなど)はクエリをサンプリングして遅いものを保存しますが、多くはそうしません。ここでEXPLAIN ANALYZEがあなたの味方になります。これはクエリのクエリプランを出力し、クエリを実行します(本番環境でこれを実行する際は注意してください。ANALYZEなしのEXPLAINを使用してクエリプランを取得できます)。そして、テーブル統計情報に基づいた推定値と、実際にスキャンされた行数を比較します。私は通常、SQLクエリをファイルに入れ、EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)をプレフィックスとして付け、次のように実行します:
psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json
そして、explain.dalibo.comを使用して実行プランを視覚化します。
シーケンシャルスキャンが理にかなる場合もある
インデックスが使用されるべきだと考えているのに、テーブル統計情報が最新でインデックスも有効であるにもかかわらず、クエリプランナーが依然としてシーケンシャルスキャンしている場合があります。このような場合、Postgresは通常、シーケンシャルスキャンのコストがインデックススキャンのコストよりも小さいと推定しています。インデックススキャンにはオーバーヘッドが伴います。インデックスはテーブルの実際のデータ(ヒープと呼ばれる)とは別に格納されます。ヒープ内のすべての行を見つけるのは高価になる可能性があります!
クエリを劇的に再構築できない限り、シーケンシャルスキャンを受け入れるか、パーティショニング(下記参照)のようなものを検討する必要があるかもしれません。
大量のデータの書き込み
アプリケーションがスケーリングし、大量のデータを高速に書き込む必要があるとしましょう。各クエリには、(前に話したコネクションオーバーヘッドとは別に)いくらかのオーバーヘッドが伴います。これには、データベースへのラウンドトリップ時間、内部アプリケーションコネクションプールがコネクションを取得する時間、Postgresがクエリを処理する時間(高スループットシナリオでボトルネックになりうる一連の内部Postgresロックを含む)が含まれます。
このオーバーヘッドを削減するために、1つのバッチに複数の行をパックできます。最も簡単な方法は、暗黙的なトランザクションで、すべてのクエリを一度にPostgresサーバーに送信することです(Goでは、pgxを使用してSendBatchを実行できます)。バッチ処理は非常に強力です。スループットを約10倍にできることがわかりました。私はこれと、データを高速に書き込むためのその他のヒントについて、こちらでさらに詳しく書いています。
デフォルトのautovacuum設定はデータベースを破壊する可能性がある
AutovacuumはPostgresデータベースにおける重要な操作であり、特に書き込みが多いシナリオでは調整が必要な場合があります。autovacuumデーモンは、デッドタプル(dead tuples)のクリーンアップやトランザクションIDの管理など、いくつかの責任を負っています。
デッドタプルとは何でしょうか?タプルはファイルシステム上の行のインスタンスです。行を更新または削除するたびに、その行のバージョンがPostgresに残され、その行が更新または削除される前に開始されたすべてのトランザクションがコミットまたはロールバックされるまで保持されます。これらの行はどのトランザクションからも読み取れなくなったため、デッドタプルとなります。
十分な速さでデータを書き込んでいる場合、autovacuumが追いつけなくなり、非常に不健康な状態に急速に陥る可能性があります。これは、データベース上のアクティブなプロセスをクエリしたときに確認できます:
SELECT
pid,
usename,
application_name,
client_addr,
query_start,
now() AS now,
state,
now() - query_start AS duration,
LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;
autovacuumクエリが約1時間以上実行されている場合は、autovacuum設定の変更を検討する価値があります!詳細についてはこの記事を参照してください。
これを監視する価値があります。autovacuumによって回収される前にシステム内のすべてのトランザクションIDを使い果たすと、恐ろしいトランザクションIDラップアラウンド状態に達します。これは大規模なダウンタイムを意味します。
その他の種類のブロート
デッドタプルに加えて、忙しいPostgresシステムでよく遭遇するブロートには、さらに2種類あります:
- 部分的に満たされたページによるテーブルブロート。Postgresはディスク上のページにレコードを格納します。各ページは8KBのサイズです。Postgresが既存のページに新しい行を収容できなくなると、新しいページが作成されます。しかし、デッドタプルが回収されると、ページが完全に満たされなくなる可能性があり、Postgresのディスク使用量が増加することがあります。テーブルブロートを回避する最善の方法は、ブロートが発生する前にautovacuumを調整することです。しかし、ブロートしたテーブルを助けるための拡張機能(
pg_repackなど)があります。組み込みのPostgresVACUUM FULLはめったに良い考えではありません。Postgres 19ではREPACK...CONCURRENTLYが追加される予定です。これはまだテストしていませんが、同時テーブルリパッキングの有望なソリューションになる可能性があります。 - インデックスブロートはテーブルブロートの特殊なケースであり、同様に良好なautovacuum設定で解決されます。しかし、Postgresにはこれを処理するための組み込みコマンドがあり、それは
REINDEX INDEX CONCURRENTLYです。
高度なトピック
Hatchetで特に役立った高度なPostgres機能のセットで締めくくりたいと思います。
FOR UPDATE SKIP LOCKED
このPostgres機能の最も良い考え方は、他のクエリを妨げることなく、トランザクションで使用するために選択した行を予約することです。私たちは主にこれをジョブキューの実装に使用しています。Postgresでの単一クエリキューは次のように実装できます:
-- name: PopTasks :many
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.*;
これは、多くの独立した行の更新を行っている場合や、アプリケーションの多くのインスタンスにわたってオブジェクトのリースを管理している場合(たとえば、Hatchetエンジン間でテナントリースを分散するために使用しています)にも非常に役立ちます。
パーティショニング
Postgresには組み込みのパーティショニングがあり、タイムスタンプやハッシュなどの行の値に基づいてテーブルをサブ分割できます。これは時系列データ(私たちの場合は、履歴タスクデータ)に非常に役立ちます。なぜなら:
- 各パーティションは独立してautovacuumできるため、テーブルのautovacuumをスケールアップできます。
- 古いデータの削除はほぼ瞬時です。行を反復処理するのではなく、テーブルパーティションをドロップするだけです。
パーティショニングには欠点があります。Postgresがプランニングフェーズ中にパーティションをプルーニングしない場合(最近のリリースでPostgresが大幅に改善された点)、読み取りクエリにオーバーヘッドが発生する可能性があります。
パーティショニングに関する私たちの経験については、こちらでさらに詳しく書いています。
大規模テーブルマイグレーションのトリック
(これはデータベースマイグレーションではなく、あるテーブルから別のテーブルに大量のデータを移動することを指します。これは年に数回行うことになります)
本当に大きなテーブルをマイグレーションしようとすると、単一のトランザクションでデータをコピーするのに何時間もかかることがあります。これは良くありません。長時間実行されるトランザクションは、autovacuumが適切に機能するのを妨げ、結果としてシステム全体がデッドタプルで肥大化します。また、古いテーブルへの書き込みを続けたい場合、新しいテーブルには新しいデータが表示されません。
そのため、トランザクションなしで安全にデータをマイグレーションし、マイグレーション開始後の新しい書き込みを新しいテーブルにコピーする方法を見つける必要があります。私たちが学んだトリックの1つは、Postgresトリガーを使用し、一意制約を主キーに使用して重複書き込みを防ぎながら、トランザクション外で大規模なバッチバックフィルを実行することです。
以上です!Postgresのスケーリングに関する他のヒント、またはPostgresのスケーリング全般に関する質問があれば、お気軽にご連絡ください。