postgresql-database-engineering

PostgreSQL データベースエンジニアリング

クエリの最適化やインデックス戦略から、高可用性、レプリケーション、本番データベースの管理まで、PostgreSQL データベースエンジニアリングのあらゆる領域を網羅した包括的なスキルです。このスキルを使うと、大規模環境で高性能な PostgreSQL データベースを設計、最適化、保守できます。

このスキルを使用する場面

次のような場合に、このスキルを使用します。

• 高性能なアプリケーション向けのデータベーススキーマを設計する
• 遅いクエリを最適化し、データベースのパフォーマンスを向上させる
• 複雑なクエリパターン向けのインデックス戦略を実装する
• 大規模テーブル（1 億行以上）のパーティショニングを設定する
• ストリーミングレプリケーションと高可用性を構成する
• 本番ワークロード向けに PostgreSQL の設定を調整する
• バックアップおよびリカバリの手順を実装する
• パフォーマンスの問題やクエリのボトルネックをデバッグする
• pgBouncer または PgPool を使ったコネクションプーリングを設定する
• データベースの健全性とパフォーマンス指標を監視する
• データベースの移行やスキーマの変更を計画する
• データベースのセキュリティとアクセス制御を実装する
• PostgreSQL データベースを水平または垂直にスケーリングする
• VACUUM 操作とデータベースの保守を管理する
• データ配布用の論理レプリケーションを設定する

コアコンセプト

PostgreSQL のアーキテクチャ

PostgreSQL は、いくつかの主要コンポーネントで構成されるプロセスベースのアーキテクチャを使用します。

• Postmaster プロセス：接続を管理するメインのサーバープロセス
• バックエンドプロセス：クライアント接続ごとに 1 つ起動し、クエリを処理します
• 共有メモリ：共有バッファー、WAL バッファー、ロックテーブル
• バックグラウンドワーカー：Autovacuum、チェックポインター、WAL ライター、統計情報収集プロセス
• 先行書き込みログ（WAL）：耐久性とレプリケーションのためのトランザクションログ
• ストレージレイヤー：大きな値を格納する TOAST、空き領域用の FSM、可視性用の VM

MVCC（マルチバージョン同時実行制御）

PostgreSQL の基盤となる同時実行制御の仕組みです。

• スナップショット：各トランザクションは一貫性のあるデータのスナップショットを参照します
• タプルバージョン：同時アクセスに備えて複数の行バージョンが共存します
• トランザクション ID：xmin（作成したトランザクション）、xmax（削除したトランザクション）
• 可視性ルール：どの行バージョンをトランザクションから参照できるかを決定します
• VACUUM：不要なタプルの領域を回収し、トランザクション ID の周回を防ぎます
• FREEZE：古い行をすべてのトランザクションから可視としてマークします

主な影響：

• 読み取りロックがないため、読み取り処理が書き込み処理をブロックしません
• 書き込み処理が読み取り処理をブロックしません
• 更新時に新しい行バージョンが作成されます
• 定期的な VACUUM が不可欠です
• 不要なタプルは VACUUM されるまで蓄積します

トランザクション分離レベル

PostgreSQL は 4 つの分離レベルをサポートしています。

1. Read Uncommitted：PostgreSQL では Read Committed として扱われます
2. Read Committed（デフォルト）：ステートメントの開始時点でコミット済みのデータを参照します
3. Repeatable Read：トランザクションの開始時点のスナップショットを参照します
4. Serializable：SSI による真の直列化可能分離です

分離レベルの選択：

• Read Committed：ほとんどのアプリケーションに適し、最高のパフォーマンスを発揮します
• Repeatable Read：一貫性が必要なレポートや分析に適しています
• Serializable：金融取引など、厳密な一貫性が必要な場合に適しています

インデックスの種類

PostgreSQL には、用途に応じて複数のインデックスの種類があります。

1. B-Tree（デフォルト）

• 用途：等価検索、範囲検索、ソート
• サポート対象：<、<=、=、>=、>、BETWEEN、IN、IS NULL
• 最適な用途：汎用的なインデックスの大半
• 例：主キー、外部キー、タイムスタンプ

2. Hash

• 用途：等価比較のみ
• サポート対象：= 演算子
• 最適な用途：等価検索を行う大規模テーブル
• 制限事項：PG 10 より前は WAL に記録されず、範囲検索もできません

3. GiST（Generalized Search Tree）

• 用途：幾何データ、全文検索、カスタム型
• サポート対象：重複、包含、近傍検索
• 最適な用途：空間データ、範囲、全文検索
• 例：PostGIS のジオメトリ、tsvector、範囲

4. GIN（Generalized Inverted Index）

• 用途：複数の値を持つ列（配列、JSONB、全文検索）
• 対応：包含演算子、存在演算子
• 最適な用途：JSONB クエリ、配列操作、全文検索
• トレードオフ：更新は遅く、クエリは速くなります

5. BRIN（Block Range Index）

• 用途：自然な順序を持つ非常に大きなテーブル
• 対応：ソート済みデータに対する範囲クエリ
• 最適な用途：時系列データ、追記専用テーブル
• 利点：インデックスサイズが非常に小さく、数十億行までスケールします

6. SP-GiST（Space-Partitioned GiST）

• 用途：平衡でないデータ構造
• 対応：点、範囲、IP アドレス
• 最適な用途：四分木、k-d 木、基数木

クエリ計画と最適化

PostgreSQL のクエリプランナーは、実行戦略を決定します。

プランナーのコンポーネント：

• 統計情報：カーディナリティ推定のためのテーブルと列の統計情報
• コストモデル：CPU、I/O、メモリのコスト推定
• プランの種類：シーケンシャルスキャン、インデックススキャン、ビットマップスキャン、結合
• 結合方式：ネステッドループ、ハッシュ結合、マージ結合
• 最適化：クエリ書き換え、述語プッシュダウン、結合順序の変更

主な統計情報：

• n_distinct：個別値の数（選択性の推定用）
• correlation：物理的な行順序の相関
• most_common_vals：偏りのある分布に対する MCV リスト
• histogram_bounds：値の分布を示すヒストグラム

EXPLAIN の理解：

• コスト：起動コスト .. 合計コスト（任意単位）
• 行数：推定行数
• 幅：平均行サイズ（バイト単位）
• 実際の時間：実際の実行時間（ANALYZE を使用した場合）
• ループ：ノードの実行回数

パーティショニング戦略

大規模なデータセットを管理するためのテーブルパーティショニング：

範囲パーティショニング

• 用途：時系列データ、連続値
• 例：日付範囲（日次、月次、年次）でパーティション分割
• 利点：データライフサイクルの管理が容易になり、クエリが高速化します

リストパーティショニング

• 用途：離散的なカテゴリ値
• 例：国、地域、ステータスでパーティション分割
• 利点：論理的にデータを分離でき、パーティションプルーニングが可能です

ハッシュパーティショニング

• 用途：データを均等に分散
• 例：hash(user_id) でパーティション分割
• 利点：パーティションサイズの均衡化、並列クエリ

パーティションプルーニング：

• プランナーが無関係なパーティションを除外します
• クエリの対象範囲を大幅に削減します
• パーティションのパフォーマンスに不可欠です

パーティション単位の操作：

• パーティション単位の結合：一致するパーティションを直接結合します
• パーティション単位の集約：パーティション内で集約します
• パーティションの並列処理

レプリケーションと高可用性

PostgreSQL のレプリケーションオプション：

ストリーミングレプリケーション（物理）

• 種類：スタンバイサーバーへのバイナリ WAL ストリーミング
• モード：非同期、同期、クォーラムベース
• 用途：高可用性、読み取りのスケーラビリティ
• フェイルオーバー：Patroni、repmgr などのツールで自動化

同期と非同期の比較：

• 同期：データ損失ゼロ、レイテンシは高くなります
• 非同期：レイテンシは低く、データ損失の可能性があります
• クォーラム：安全性とパフォーマンスのバランス

論理レプリケーション

• 種類：行レベルの変更ストリーム
• 用途：選択的なレプリケーション、アップグレード、マルチマスター
• 利点：特定のテーブルのレプリケーション、バージョンをまたいだレプリケーション
• 制限：DDL はレプリケーションされず、オーバーヘッドがあります

カスケードレプリケーション

• スタンバイが他のスタンバイからレプリケーションします
• プライマリの負荷を軽減します
• 地理的に分散できます

コネクションプーリング

データベース接続を効率的に管理します。

pgBouncer

• 種類：軽量なコネクションプーラー
• モード：セッション、トランザクション、ステートメントのプーリング
• 用途：接続数の多いアプリケーション
• 利点：接続のオーバーヘッドを軽減し、リソースを制限できます

プーリングモード：

• セッション：クライアントはセッション全体を通じて接続します
• トランザクション：トランザクションごとに接続します
• ステートメント：ステートメントごとに接続します（ほとんど使用されません）

PgPool-II

• 種類：機能豊富なミドルウェア
• 機能：コネクションプーリング、負荷分散、クエリキャッシュ
• 用途：読み取り／書き込みの分割、コネクション管理
• 利点：高度なルーティング、インメモリキャッシュ

VACUUM とメンテナンス

重要なメンテナンス操作：

VACUUM

• 目的：不要なタプルの領域を再利用し、統計情報を更新します
• 種類：通常の VACUUM、VACUUM FULL
• 実行タイミング：大規模な更新／削除の後、および autovacuum による定期的な実行
• 影響：通常の VACUUM は処理をブロックしません

ANALYZE

• 目的：プランナーの統計情報を更新します
• 実行タイミング：データ変更後、スキーマ変更後
• 影響：ほとんどのテーブルで影響は最小限で、処理も高速です

REINDEX

• 目的：インデックスを再構築し、肥大化を解消します
• 実行タイミング：インデックスの破損、著しい肥大化が発生した場合
• 影響：テーブルをロックします。REINDEX CONCURRENTLY（PG 12+）を使用してください

Autovacuum

• 目的：VACUUM と ANALYZE を自動実行します
• 設定：しきい値に基づいて起動します
• 調整：リソース使用量と応答性のバランスを取ります
• 監視：autovacuum の実行状況を追跡し、トランザクション ID の周回を防ぎます

パフォーマンスのチューニング

主要な設定パラメーター：

メモリー設定

shared_buffers: 25% of RAM (start point)
effective_cache_size: 50-75% of RAM
work_mem: Per-operation memory (sort, hash)
maintenance_work_mem: VACUUM, CREATE INDEX memory

チェックポイントと WAL

checkpoint_timeout: How often to checkpoint
max_wal_size: WAL size before checkpoint
checkpoint_completion_target: Spread checkpoint I/O
wal_buffers: WAL write buffer size

クエリプランナー

random_page_cost: Relative cost of random I/O
effective_io_concurrency: Concurrent I/O operations
default_statistics_target: Histogram detail level

コネクション設定

max_connections: Maximum client connections
connection_limit: Per-database/user limits

インデックス戦略

適切なインデックスの選択

判断マトリックス：

クエリパターン	インデックスの種類	理由
WHERE id = 5	B-tree	等価検索
WHERE created_at > '2024-01-01'	B-tree	範囲検索
ORDER BY name	B-tree	ソートをサポート
WHERE tags @> ARRAY['sql']	GIN	配列の包含検索
WHERE data->>'status' = 'active'	GIN (jsonb_path_ops)	JSONB クエリ
WHERE to_tsvector(content) @@ query	GIN	全文検索
WHERE location <-> point(0,0)	GiST	最近傍検索
WHERE timestamp BETWEEN ... (large table)	BRIN	時系列データの順次検索
WHERE ip_address << '192.168.0.0/16'	GiST or SP-GiST	IP 範囲検索

複合インデックス

複雑なクエリ向けの複数列インデックス：

列の順序に関するルール：

1. 等価条件の列を先に配置します
2. ソート／範囲条件の列を最後に配置します
3. 選択性の高い列を先に配置します
4. クエリパターンに正確に合わせます

例：

-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY created_at
-- Optimal index: (status, created_at)
CREATE INDEX idx_users_status_created ON users(status, created_at);

部分インデックス

行の一部をインデックス化します：

利点：

• インデックスサイズが小さくなります
• インデックス化されていない行の更新が高速になります
• クエリを対象に絞って最適化できます

使用例：

• アクティブなレコードのみをインデックス化：WHERE deleted_at IS NULL
• 最近のデータをインデックス化：WHERE created_at > NOW() - INTERVAL '90 days'
• 特定の状態をインデックス化：WHERE status IN ('pending', 'processing')

式インデックス

計算値をインデックス化します：

例：

-- Case-insensitive search
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Date truncation
CREATE INDEX idx_events_date ON events(DATE(created_at));

-- JSONB field
CREATE INDEX idx_data_status ON documents((data->>'status'));

カバリングインデックス（INCLUDE）

キー以外の列を含め、インデックスのみのスキャンを可能にします：

CREATE INDEX idx_users_email_include
ON users(email)
INCLUDE (first_name, last_name, created_at);

利点： クエリをインデックスだけで処理でき、テーブルを参照する必要がありません

インデックスのメンテナンス

インデックスの使用状況の監視：

-- Unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

肥大化の検出：

-- Index bloat estimation
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

クエリの最適化

EXPLAIN ANALYZE の使用

クエリの実行を理解します：

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- EXPLAIN ANALYZE (actually runs query)
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at > '2024-01-01';

-- Detailed output
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

主要な指標：

• 計画時間：実行計画の生成にかかる時間
• 実行時間：クエリの実際の実行時間
• 共有ヒットと読み取り：バッファーキャッシュのヒット数とディスク読み取り数
• 行数：推定行数と実際の行数
• Filter と Index Cond：スキャン後のフィルタリングとインデックスの使用状況

よくあるクエリのアンチパターン

1. N+1 クエリ

問題： ループ内で各行に対してクエリを1回ずつ実行します 解決策： JOIN またはバッチクエリを使用します

2. SELECT *

問題： 不要な列を取得します 解決策： 必要な列だけを選択します

3. 暗黙的な型変換

問題： 型が一致しないため、インデックスが使用されません 解決策： クエリの型を列の型に合わせます

4. インデックス対象列への関数の適用

問題： WHERE UPPER(email) = '[email protected]' 解決策： 式インデックスを使用するか、適切な方法で比較します

5. OR 条件

問題： WHERE status = 'A' OR status = 'B' 解決策： IN を使用します：WHERE status IN ('A', 'B')

結合の最適化

結合の種類：

1. ネステッドループ

   • 最適な条件：小さな外側のテーブルと、インデックス付きの内側のテーブル
   • 方法：外側の各行について、内側のテーブルをスキャンします
   • 使用する状況：結果セットが小さく、適切なインデックスがある場合

2. ハッシュ結合

   • 最適な条件：大きなテーブルで、適切なインデックスがない場合
   • 方法：小さい方のテーブルのハッシュテーブルを構築します
   • 使用する状況：等価結合で、十分なメモリがある場合

3. マージ結合

   • 最適な条件：事前にソートされたデータと、等価結合
   • 方法：両方の入力をソートし、マージスキャンします
   • 使用する状況：両方の入力がソート済み、または低コストでソートできる場合

結合順序は重要です：

• プランナーは最適化のために結合順序を並べ替えます
• 統計情報が結合順序の決定に役立ちます
• SET join_collapse_limit で順序を指定できます

集約の最適化

手法：

• 部分集約：パーティション単位の集約
• ハッシュ集約：メモリ内でのグループ化
• ソート済み集約：事前にソートされた入力
• 並列集約：複数のワーカー

マテリアライズドビュー：

• コストの高い集約を事前計算します
• スケジュールまたはトリガーに応じて更新します
• データの鮮度とクエリ速度を引き換えます

クエリキャッシュ

レベル：

1. 共有バッファー：PostgreSQL のページキャッシュ
2. OS ページキャッシュ：オペレーティングシステムのキャッシュ
3. アプリケーションキャッシュ：Redis、Memcached
4. プリペアドステートメント：クエリプランを再利用します

パーティショニング

範囲パーティショニングの実装

時系列の例：

-- Create partitioned table
CREATE TABLE events (
    id BIGSERIAL,
    event_type TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    data JSONB,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Default partition for data outside ranges
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Indexes on partitions
CREATE INDEX idx_events_2024_01_user ON events_2024_01(user_id);
CREATE INDEX idx_events_2024_02_user ON events_2024_02(user_id);

パーティションの自動化

パーティション管理の自動化：

-- Function to create monthly partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(
    base_table TEXT,
    partition_date DATE
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := base_table || '_' || TO_CHAR(partition_date, 'YYYY_MM');
    start_date := DATE_TRUNC('month', partition_date);
    end_date := start_date + INTERVAL '1 month';

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, base_table, start_date, end_date
    );

    -- Create indexes
    EXECUTE format(
        'CREATE INDEX IF NOT EXISTS %I ON %I(user_id)',
        'idx_' || partition_name || '_user', partition_name
    );
END;
$$ LANGUAGE plpgsql;

パーティションのメンテナンス

古いパーティションの削除：

-- Detach partition (fast, non-blocking)
ALTER TABLE events DETACH PARTITION events_2023_01;

-- Drop detached partition
DROP TABLE events_2023_01;

-- Or archive before dropping
CREATE TABLE archive.events_2023_01 AS SELECT * FROM events_2023_01;
DROP TABLE events_2023_01;

高可用性とレプリケーション

ストリーミングレプリケーションの設定

プライマリサーバーの設定（postgresql.conf）：

# Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
synchronous_commit = on  # or off for async
synchronous_standby_names = 'standby1,standby2'  # for sync replication

レプリケーションユーザーの作成：

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secure_password';

プライマリ上の pg_hba.conf：

# Allow replication connections
host replication replicator standby_ip/32 md5

スタンバイサーバーの設定：

# Stop standby PostgreSQL
systemctl stop postgresql

# Remove old data directory
rm -rf /var/lib/postgresql/14/main

# Base backup from primary
pg_basebackup -h primary_host -D /var/lib/postgresql/14/main \
              -U replicator -P -v -R -X stream -C -S standby1

# Start standby
systemctl start postgresql

スタンバイの設定（-R フラグで作成）：

# standby.signal file created automatically
# postgresql.auto.conf contains:
primary_conninfo = 'host=primary_host port=5432 user=replicator password=secure_password'
primary_slot_name = 'standby1'

レプリケーションの監視

プライマリ上：

-- Check replication status
SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;

-- Check replication slots
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

スタンバイ上：

-- Check replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- Check recovery status
SELECT pg_is_in_recovery();

フェイルオーバーとスイッチオーバー

スタンバイをプライマリに昇格：

# Trigger failover
pg_ctl promote -D /var/lib/postgresql/14/main

# Or using SQL
SELECT pg_promote();

計画的なスイッチオーバー：

# 1. Stop writes on primary
# 2. Wait for standby to catch up
# 3. Promote standby
# 4. Reconfigure old primary as new standby

論理レプリケーションの設定

パブリッシャー（ソース）上：

-- Create publication
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Or all tables
CREATE PUBLICATION all_tables FOR ALL TABLES;

サブスクライバー（宛先）上：

-- Create subscription
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher_host dbname=mydb user=replicator password=pass'
    PUBLICATION my_publication;

-- Monitor subscription
SELECT * FROM pg_stat_subscription;

バックアップとリカバリー

物理バックアップ

pg_basebackup：

# Full physical backup
pg_basebackup -h localhost -U postgres -D /backup/base \
              -F tar -z -P -v

# With WAL files for point-in-time recovery
pg_basebackup -h localhost -U postgres -D /backup/base \
              -X stream -F tar -z -P

継続的アーカイブ（WAL アーカイブ）：

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'

論理バックアップ

pg_dump：

# Single database
pg_dump -h localhost -U postgres -F c -b -v -f mydb.dump mydb

# All databases
pg_dumpall -h localhost -U postgres -f all_databases.sql

# Specific tables
pg_dump -h localhost -U postgres -t users -t orders -F c -f tables.dump mydb

# Schema only
pg_dump -h localhost -U postgres --schema-only -F c -f schema.dump mydb

pg_restore：

# Restore database
pg_restore -h localhost -U postgres -d mydb -v mydb.dump

# Parallel restore
pg_restore -h localhost -U postgres -d mydb -j 4 -v mydb.dump

# Restore specific tables
pg_restore -h localhost -U postgres -d mydb -t users -v mydb.dump

ポイントインタイムリカバリー（PITR）

設定：

1. ベースバックアップを取得します
2. WAL アーカイブを設定します
3. WAL ファイルを安全に保管します

リカバリー：

# 1. Restore base backup
tar -xzf base.tar.gz -C /var/lib/postgresql/14/main

# 2. Create recovery.signal file
touch /var/lib/postgresql/14/main/recovery.signal

# 3. Configure recovery target (postgresql.conf or postgresql.auto.conf)
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
# Or: recovery_target_name = 'before_disaster'
# Or: recovery_target_lsn = '0/3000000'

# 4. Start PostgreSQL
systemctl start postgresql

バックアップ戦略

3-2-1 ルール：

• データのコピーを 3 つ作成します
• 異なる種類のメディアを 2 つ使用します
• オフサイトバックアップを 1 つ用意します

バックアップスケジュール：

• 毎日：WAL の増分アーカイブ
• 毎週：pg_basebackup によるフルバックアップ
• 毎月：長期保存

バックアップのテスト：

• テスト環境に定期的にリストアします
• データの整合性を確認します
• リストア時間を測定します

パフォーマンスの監視

監視する主なメトリクス

データベースの健全性：

• アクティブな接続数
• トランザクション率
• キャッシュヒット率
• デッドロック
• チェックポイントの頻度
• 自動バキュームの実行回数

クエリのパフォーマンス：

• スロークエリログ
• クエリの実行時間
• ロック待ち
• シーケンシャルスキャン

システムリソース：

• CPU 使用率
• メモリ使用量
• ディスク I/O
• ネットワーク帯域幅

重要な監視クエリ

接続統計：

SELECT count(*) as total_connections,
       count(*) FILTER (WHERE state = 'active') as active,
       count(*) FILTER (WHERE state = 'idle') as idle,
       count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
FROM pg_stat_activity;

キャッシュヒット率：

SELECT sum(heap_blks_read) as heap_read,
       sum(heap_blks_hit) as heap_hit,
       sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

テーブルの肥大化：

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

長時間実行中のクエリ：

SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;

ロックの監視：

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

pg_stat_statements

インストール：

CREATE EXTENSION pg_stat_statements;

設定（postgresql.conf）：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

合計時間が長いクエリの上位：

SELECT query,
       calls,
       total_exec_time,
       mean_exec_time,
       max_exec_time,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

平均時間が長いクエリの上位：

SELECT query,
       calls,
       mean_exec_time,
       total_exec_time
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

ベストプラクティス

スキーマ設計

正規化：

• トランザクションシステムでは第3正規形（3NF）に正規化します
• 読み取り負荷の高いワークロードでは、必要に応じて非正規化します
• データの整合性を保つために外部キーを使用します
• 非常に大きなテーブルではパーティショニングを検討します

データ型：

• 適切な範囲で最小のデータ型を使用します
• 大きな ID には BIGINT、小さな範囲には INTEGER を使用します
• 正確な小数値には NUMERIC を使用します
• タイムスタンプには TIMESTAMP WITH TIME ZONE を使用します
• 長さの制約が必要な場合を除き、VARCHAR より TEXT を使用します
• 分散 ID の生成には UUID を使用します
• 半構造化データには JSONB を使用します

制約：

• すべてのテーブルに主キーを設定します
• 参照整合性のために外部キーを設定します
• ビジネスルールには CHECK 制約を使用します
• 適切な箇所に NOT NULL を設定します
• 一意性を保証するには UNIQUE 制約を使用します
• 保守性を高めるために制約に名前を付けます

マイグレーション戦略

ダウンタイムゼロのマイグレーション：

1. 新しいカラムを追加

   ALTER TABLE users ADD COLUMN email_verified BOOLEAN;
   

2. データをバックフィル（複数のバッチに分けて）

   UPDATE users SET email_verified = false
   WHERE email_verified IS NULL
   LIMIT 10000;
   

3. NOT NULL 制約を追加

   ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
   

インデックスの作成：

• 本番環境では CREATE INDEX CONCURRENTLY を使用します
• テーブルをロックせず、読み取りと書き込みが可能です
• 時間はかかりますが、処理をブロックしません
• pg_stat_progress_create_index で進行状況を監視します

大規模テーブルの変更：

• テーブルの再構築には pg_repack を使用します
• 変更前に大規模テーブルをパーティション分割します
• メンテナンス時間帯に実施します
• 本番環境に近いデータセットでテストします

セキュリティのベストプラクティス

認証：

• 強力なパスワードまたは証明書認証を使用します
• パスワードの暗号化には SCRAM-SHA-256 を使用します
• アプリケーションごとにユーザーを分けます
• アプリケーションからの接続にスーパーユーザーを使用しないでください

認可：

• 必要最小限の権限を付与します
• ロールベースのアクセス制御を使用します
• PUBLIC のアクセス権を取り消します
• マルチテナント環境では行レベルセキュリティを使用します

ネットワークセキュリティ：

• pg_hba.conf を厳格に設定します
• 接続には SSL/TLS を使用します
• データベースのポートをファイアウォールで保護します
• レプリケーションには VPN またはプライベートネットワークを使用します

監査ログ：

• 接続ログを有効にします
• DDL ステートメントを記録します
• 詳細な監査には pgAudit 拡張機能を使用します
• 不審なアクティビティを監視します

メンテナンススケジュール

毎日：

• 遅いクエリを監視します
• レプリケーションの遅延を確認します
• autovacuum の動作を確認します
• ディスク容量を監視します

毎週：

• 上位のクエリを分析します
• インデックスの使用状況を確認します
• テーブルの肥大化を確認します
• バックアップを検証します

毎月：

• 重要なテーブルに対して VACUUM を完全実行します
• 肥大化したインデックスを REINDEX します
• 設定パラメーターを確認します
• キャパシティプランニングを行います

四半期ごと：

• インデックスを確認して最適化します
• スキーマ最適化の機会を検討します
• アップグレードを計画します
• パフォーマンスのベースラインを更新します

高度なトピック

並列クエリ実行

設定：

max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
min_parallel_table_scan_size = 8MB

並列実行を強制する：

SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

並列処理が効果を発揮するケース：

• 大規模なシーケンシャルスキャン
• 大規模な集計
• 大規模テーブルに対するハッシュ結合
• ビットマップヒープスキャン

カスタム関数とプロシージャ

ストアドプロシージャ：

CREATE OR REPLACE PROCEDURE update_user_statistics()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET
        order_count = (SELECT COUNT(*) FROM orders WHERE user_id = users.id),
        last_order_date = (SELECT MAX(created_at) FROM orders WHERE user_id = users.id);

    COMMIT;
END;
$$;

適切なエラー処理を備えた関数：

CREATE OR REPLACE FUNCTION create_user(
    p_email TEXT,
    p_name TEXT
) RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id INTEGER;
BEGIN
    INSERT INTO users (email, name)
    VALUES (p_email, p_name)
    RETURNING id INTO v_user_id;

    RETURN v_user_id;
EXCEPTION
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Email already exists: %', p_email;
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error creating user: %', SQLERRM;
END;
$$;

外部データラッパー

外部データソースへのアクセス：

-- Install postgres_fdw
CREATE EXTENSION postgres_fdw;

-- Create server
CREATE SERVER remote_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', dbname 'remote_database', port '5432');

-- Create user mapping
CREATE USER MAPPING FOR current_user
SERVER remote_db
OPTIONS (user 'remote_user', password 'remote_password');

-- Import foreign schema
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_db
INTO local_schema;

-- Query foreign table
SELECT * FROM local_schema.remote_table;

JSON と JSONB の操作

JSONB のインデックス作成：

-- GIN index for containment queries
CREATE INDEX idx_data_gin ON documents USING GIN (data);

-- Expression index for specific field
CREATE INDEX idx_data_status ON documents ((data->>'status'));

-- GIN index with jsonb_path_ops (smaller, faster for @> queries)
CREATE INDEX idx_data_path_ops ON documents USING GIN (data jsonb_path_ops);

効率的な JSONB クエリ：

-- Containment query (uses GIN index)
SELECT * FROM documents WHERE data @> '{"status": "active"}';

-- Existence query
SELECT * FROM documents WHERE data ? 'email';

-- Path query
SELECT * FROM documents WHERE data->'user'->>'email' = '[email protected]';

-- Array operations
SELECT * FROM documents WHERE data->'tags' @> '["sql", "postgres"]';

全文検索

基本的な設定：

-- Add tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Generate search vector
UPDATE articles SET search_vector =
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));

-- Create GIN index
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Trigger for automatic updates
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

検索クエリ：

-- Basic search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Phrase search
SELECT title FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'database engineering');

-- Search with highlighting
SELECT title,
       ts_headline('english', content, query) AS snippet
FROM articles, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query;

トラブルシューティング

よくある問題

問題：クエリが遅い

• EXPLAIN ANALYZE の出力を確認します
• インデックスが存在し、使用されていることを確認します
• テーブル統計情報を更新します：ANALYZE table_name
• 外部キーにインデックスが不足していないか確認します
• インデックス付きカラムに対する関数呼び出しを確認します

問題：CPU 使用率が高い

• pg_stat_statements を使って負荷の高いクエリを特定します
• シーケンシャルスキャンの原因となるインデックス不足がないか確認します
• 並列クエリの設定を見直します
• 効率の悪い結合や集計がないか確認します

問題：接続の枯渇

• max_connections を増やします（再起動が必要です）
• 接続プーリングを導入します（pgBouncer）
• アプリケーションで接続リークがないか特定します
• pg_stat_activity を使って監視します

問題：オートバキュームが追いつかない

• autovacuum_max_workers を増やします
• autovacuum のしきい値を調整します
• autovacuum_naptime を短縮します
• autovacuum_work_mem を増やします
• VACUUM を妨げている長時間実行中のトランザクションがないか確認します

問題：レプリケーション遅延

• プライマリとスタンバイ間のネットワーク帯域幅を確認します
• スタンバイのハードウェアリソースを確認します
• スタンバイ上で長時間実行中のクエリがないか確認します
• WAL の生成率を監視します
• wal_sender_timeout の増加を検討します

問題：トランザクション ID の周回

• 最も古いトランザクションの経過時間を監視します
• 古いテーブルで VACUUM FREEZE を実行します
• autovacuum_freeze_max_age を確認します
• オートバキュームの実行頻度を高めます
• 必要に応じて手動で VACUUM FREEZE を実行します

診断クエリ

外部キーに不足しているインデックスを見つける：

SELECT c.conrelid::regclass AS table,
       c.confrelid::regclass AS referenced_table,
       string_agg(a.attname, ', ') AS foreign_key_columns
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND c.conkey[1:array_length(c.conkey, 1)]
          OPERATOR(pg_catalog.@>) i.indkey[0:array_length(c.conkey, 1) - 1]
  )
GROUP BY c.conrelid, c.confrelid, c.conname;

ブロックしているクエリを特定する：

SELECT activity.pid,
       activity.usename,
       activity.query,
       blocking.pid AS blocking_id,
       blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

---

スキルのバージョン: 1.0.0 最終更新日: 2025年10月 スキルのカテゴリ: データベースエンジニアリング、パフォーマンス最適化、データアーキテクチャ 対応バージョン: PostgreSQL 12+、13、14、15、16 前提条件: SQL の知識、データベースの基本概念、Linux コマンドライン