PostgreSQL ベストプラクティス

PostgreSQL を本番運用するうえで押さえておきたい設計・クエリ・運用の指針をまとめたものです。 数値はあくまで出発点です。実際のワークロードで計測しながら調整してください。

目次

1. スキーマ設計
2. データ型の選び方
3. インデックス
4. クエリの書き方
5. トランザクションとロック
6. VACUUM と統計情報
7. 設定チューニング
8. 接続管理
9. セキュリティ
10. バックアップとリカバリ
11. マイグレーション（安全なスキーマ変更）
12. 監視
13. パーティショニング
14. アンチパターン集
15. チェックリスト

1. スキーマ設計

命名規則

• テーブル名・カラム名は 小文字の snake_case にする（例: user_accounts, created_at）。
  • 大文字や記号を使うと常にダブルクォートが必要になり、ミスの元になります。
• 予約語（user, order, group など）は避ける。
• テーブル名は単数形か複数形のどちらかにプロジェクト内で統一する。

主キー

• すべてのテーブルに主キーを定義する（論理レプリケーションでも必要になります）。
• 連番なら serial ではなく bigint GENERATED ALWAYS AS IDENTITY を使う。
  • int は約21億件で枯渇します。最初から bigint にしておくのが安全です。
• 外部に公開する ID や分散環境で生成する ID には UUID を検討する。
  • ランダムな UUIDv4 はインデックスの局所性が悪く、書き込み性能が落ちやすい。 時系列順に並ぶ UUIDv7 を推奨します（PostgreSQL 18 以降は uuidv7() が組み込み）。

CREATE TABLE users (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text        NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT users_email_key UNIQUE (email)
);

制約でデータを守る

アプリ側だけでなく、DB 側でも整合性を担保します。

• NOT NULL を基本にし、NULL を許すのは意味があるときだけにする。
• FOREIGN KEY、UNIQUE、CHECK を積極的に使う。
• 制約にはわかりやすい名前を付けると、エラーメッセージから原因を追いやすくなります。

ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount > 0);

外部キーにはインデックスを

PostgreSQL は 外部キーの参照側カラムに自動でインデックスを作りません。 親テーブルの DELETE/UPDATE や JOIN が遅くなるため、参照側にインデックスを作成します。

CREATE INDEX orders_user_id_idx ON orders (user_id);

正規化と JSONB

• 基本は正規化（第3正規形程度）で設計する。
• jsonb は「スキーマが可変な付帯情報」に限定する。検索・結合・集計に頻繁に使う項目は通常のカラムにする。

2. データ型の選び方

-- 予約期間の重複を DB レベルで禁止する例
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE reservations (
    room_id  bigint    NOT NULL,
    during   tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

3. インデックス

基本方針

• インデックスは 実際のクエリから逆算して作る。「念のため」に作らない。
  • インデックスが増えるほど書き込みが遅くなり、ストレージと VACUUM のコストも増えます。
• 使われていないインデックスは定期的に見直して削除する（監視を参照）。

インデックスの種類

複合インデックスの列順

• 等価条件の列を先に、範囲条件の列を後に置く。
• (a, b) のインデックスは WHERE a = ? にも使えるが、WHERE b = ? 単体には基本的に使えない。

-- WHERE user_id = ? AND created_at >= ? ORDER BY created_at DESC
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);

便利なテクニック

-- 部分インデックス: 条件に合う行だけを対象にして小さく保つ
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';
 
-- 式インデックス: 関数を通した検索に対応
CREATE INDEX users_email_lower_idx ON users (lower(email));
 
-- カバリングインデックス: Index Only Scan を狙う
CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount, status);
 
-- 部分一致検索（LIKE '%foo%'）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm_idx ON products USING gin (name gin_trgm_ops);

本番環境では CONCURRENTLY

通常の CREATE INDEX はテーブルへの書き込みをブロックします。本番では必ず次のようにします。

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
-- 失敗すると INVALID なインデックスが残るので、確認して削除・再作成する

4. クエリの書き方

実行計画を見る

遅いクエリは推測せず、まず実行計画を確認します。

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

確認ポイント:

• 推定行数（rows=）と実際の行数（actual rows=）が大きくずれていないか → ずれていれば統計情報の問題
• 大きなテーブルへの想定外の Seq Scan
• Sort や Hash でディスクを使っていないか（external merge、Batches > 1）
• Buffers: shared read が多い → キャッシュに乗っていない

注意: EXPLAIN ANALYZE は実際にクエリを実行します。更新系は BEGIN; ... ROLLBACK; で囲んでください。

よくある改善ポイント

• SELECT * を避け、必要な列だけを取る。

• インデックス列を関数や演算で包まない。

  -- NG: インデックスが使われない
  WHERE date(created_at) = '2026-10-29'
  -- OK
  WHERE created_at >= '2026-10-29' AND created_at < '2026-10-30'

• N+1 クエリを避ける。JOIN や WHERE id = ANY($1) でまとめて取得する。

• ページングは OFFSET よりキーセット方式にする。OFFSET は読み飛ばす行もすべて処理します。

  -- NG: ページが深くなるほど遅い
  SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100000;
  -- OK: キーセットページネーション
  SELECT * FROM posts WHERE id < $last_id ORDER BY id DESC LIMIT 20;

• 存在確認は EXISTS を使う（COUNT(*) > 0 は全件数える）。

• NOT IN とサブクエリの組み合わせに注意。サブクエリに NULL が含まれると結果が空になります。NOT EXISTS を使ってください。

• 大量 INSERT は一括で。複数行の INSERT ... VALUES (...), (...) か COPY を使う。

• UPSERT は INSERT ... ON CONFLICT を使う。複雑な同期処理には MERGE も使えます。

プリペアドステートメント / パラメータバインド

• SQL インジェクション対策として、文字列連結でクエリを組み立てない。必ずプレースホルダを使う。

5. トランザクションとロック

• トランザクションは短く保つ。トランザクション内で外部 API 呼び出しやユーザー入力待ちをしない。
• 長時間のトランザクションは VACUUM を妨げ、テーブルの肥大化（bloat）を招きます。
• 分離レベルはデフォルトの READ COMMITTED が基本。整合性要件が厳しい処理では SERIALIZABLE を使い、直列化エラー（SQLSTATE 40001）時のリトライ処理を必ず実装する。
• デッドロックを避けるため、複数行・複数テーブルを更新するときは 常に同じ順序でロックを取る。
• ジョブキューのような処理には SELECT ... FOR UPDATE SKIP LOCKED が便利です。

SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

タイムアウトを設定する

暴走したクエリや放置されたトランザクションから DB を守ります。

ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_user SET lock_timeout = '5s';

6. VACUUM と統計情報

PostgreSQL は MVCC の仕組み上、UPDATE/DELETE で古い行（dead tuple）が残ります。これを回収するのが VACUUM です。

• autovacuum は絶対に無効にしない。
• 更新の多い大きなテーブルは、テーブル単位で autovacuum を積極的に動くよう調整する。

ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor  = 0.01,  -- デフォルト 0.2（20%）は大きなテーブルには遅すぎる
    autovacuum_analyze_scale_factor = 0.005
);

• 大量のデータ投入や削除の後は、手動で ANALYZE を実行して統計情報を更新する。
• トランザクション ID の周回（wraparound）を監視する。放置すると DB が書き込みを停止します。

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

• VACUUM FULL はテーブルを排他ロックするため、本番では基本的に使わない。肥大化の解消が必要なら pg_repack などを検討する。
• 列同士に相関がある場合は拡張統計（CREATE STATISTICS）で推定精度を上げられます。

7. 設定チューニング

主要なパラメータと目安です（専用 DB サーバーを想定）。

ログ関連もあわせて設定しておくと調査が楽になります。

log_min_duration_statement = 500ms   # 遅いクエリを記録
log_lock_waits = on
log_temp_files = 0
log_checkpoints = on
log_autovacuum_min_duration = 1s

• 設定値の当たりを付けるには PGTune のようなツールも参考になります。
• 変更は1つずつ行い、効果を計測してから次に進みます。

8. 接続管理

• PostgreSQL は 1接続につき1プロセスを使うため、接続数が多いとメモリと CPU を圧迫します。
• アプリ側のコネクションプールに加え、必要に応じて PgBouncer などの接続プーラーを導入する。
  • transaction モードでは、セッション単位の機能（セッション変数、LISTEN、一部のプリペアドステートメントなど）に制約がある点に注意。
• プールサイズは「多ければ速い」わけではありません。CPU コア数の数倍程度から始めて計測します。
• idle in transaction のまま放置されている接続を監視する。

9. セキュリティ

• 最小権限の原則: アプリ用ロールにスーパーユーザーやテーブル所有権を与えない。
  • マイグレーション用（DDL 権限あり）、アプリ用（DML のみ）、読み取り専用（SELECT のみ）とロールを分ける。
• public スキーマへのデフォルトの権限を見直す。

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

• 認証方式は scram-sha-256 を使う（md5 や trust は避ける）。
• pg_hba.conf で接続元を必要な範囲に限定する。DB をインターネットに直接公開しない。
• 通信は TLS（SSL）で暗号化する。
• マルチテナントでは行レベルセキュリティ（RLS）の導入を検討する。
• SECURITY DEFINER 関数を作るときは search_path を明示的に固定する。
• パスワードや接続文字列をコードに直書きせず、シークレット管理の仕組みを使う。

10. バックアップとリカバリ

• バックアップはリストアできて初めて意味がある。定期的にリストア訓練を行う。
• 論理バックアップ（pg_dump）だけでなく、物理バックアップ + WAL アーカイブによる PITR（ポイントインタイムリカバリ） を用意する。
  • 運用ツールとして pgBackRest や Barman がよく使われます。
• バックアップは別のリージョンやストレージにも保管する。
• RPO（どこまで遡れればよいか）と RTO（何分で復旧したいか）を決めてから方式を選ぶ。
• 高可用性が必要ならストリーミングレプリケーションを構成し、フェイルオーバー手順を整備する（Patroni など）。
• レプリカはバックアップの代わりになりません（誤操作の DELETE もそのまま複製されます）。

# 論理バックアップ（カスタム形式・並列リストア可能）
pg_dump -Fc -d mydb -f mydb.dump
pg_restore -j 4 -d mydb_restore mydb.dump

11. マイグレーション（安全なスキーマ変更）

本番の大きなテーブルへの DDL は、ロックによってサービス停止を招くことがあります。

基本ルール

• マイグレーションの前に lock_timeout を短く設定し、ロック待ちで後続クエリを詰まらせないようにする。

  SET lock_timeout = '3s';

• マイグレーションはバージョン管理し、ツール（Flyway, Sqitch, 各フレームワークの仕組みなど）で適用する。

危険な操作と安全な代替手段

-- 外部キーを安全に追加する例
ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

• 大量データの UPDATE（バックフィル）は 小さなバッチに分けて実行し、長いトランザクションを作らない。
• 定数のデフォルト値付きカラム追加（ADD COLUMN ... DEFAULT 0）は PostgreSQL 11 以降テーブル書き換えが発生しないため高速です。

12. 監視

必ず入れておきたい拡張: pg_stat_statements

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;
 
-- 合計実行時間が長いクエリ Top 10
SELECT calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time, 2) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

見ておくべき指標

-- 長時間実行中のクエリ
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - query_start > interval '1 minute'
ORDER BY duration DESC;
 
-- ブロックしている/されているセッション
SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
 
-- 使われていないインデックス
SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

未使用インデックスを削除する前に、統計がリセットされた時期とレプリカ側での利用有無も確認してください。

13. パーティショニング

• 巨大なテーブル（目安として数千万〜数億行以上） や、古いデータを定期的に削除するテーブルに有効です。
  • 古いパーティションを DROP / DETACH するだけで削除でき、大量 DELETE と VACUUM のコストを避けられます。
• 時系列データには 範囲（RANGE）パーティショニング が一般的です。
• クエリの WHERE 句にパーティションキーが含まれないと、全パーティションを走査してしまいます。
• パーティションを細かくしすぎるとプランニングが遅くなるので、数百程度までに抑えます。
• パーティションの作成・削除は pg_partman などで自動化すると楽になります。

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    payload     jsonb,
    PRIMARY KEY (id, occurred_at)          -- 主キーにパーティションキーを含める必要がある
) PARTITION BY RANGE (occurred_at);
 
CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

14. アンチパターン集

15. チェックリスト

設計

• すべてのテーブルに主キーがある（bigint IDENTITY または UUIDv7）
• 日時は timestamptz、金額は numeric / 整数
• NOT NULL・外部キー・CHECK 制約を定義している
• 外部キーの参照側カラムにインデックスがある

クエリ

• 主要なクエリの実行計画を EXPLAIN (ANALYZE, BUFFERS) で確認した
• N+1 や大きな OFFSET がない
• すべてのクエリでパラメータバインドを使っている

運用

• pg_stat_statements を有効にしている
• スロークエリログを出力している
• statement_timeout / idle_in_transaction_session_timeout を設定している
• 接続プーラーを導入している（または必要性を検討した）
• autovacuum が正常に動いており、XID の age を監視している

セキュリティ

• アプリ用ロールは最小権限
• 認証は scram-sha-256、通信は TLS
• pg_hba.conf で接続元を制限している

可用性

• PITR 可能なバックアップがある
• リストア手順を実際に試した
• マイグレーションは安全な手順（CONCURRENTLY / NOT VALID / lock_timeout）で行っている

参考資料

• PostgreSQL 公式ドキュメント
• PostgreSQL 日本語ドキュメント
• PostgreSQL Wiki: Don’t Do This
• PostgreSQL Wiki: Tuning Your PostgreSQL Server