PostgreSQL ベストプラクティス

コア原則

• PostgreSQLの高度な機能を活用して堅牢なデータモデリングを実現する
• EXPLAIN ANALYZEと適切なインデックス戦略を使用してクエリを最適化する
• ネイティブPostgreSQLデータ型を適切に使用する
• 適切な接続プーリングとリソース管理を実装する
• PostgreSQL固有のセキュリティベストプラクティスに従う

スキーマ設計

データ型

• 適切なネイティブ型を使用する: UUID, JSONB, ARRAY, INET, CIDR
• タイムゾーン対応アプリケーションでは TIMESTAMP より TIMESTAMPTZ を推奨
• 長さ制限が不要な場合は VARCHAR の代わりに TEXT を使用
• 正確な小数計算（金融データ）には NUMERIC を検討
• 自動増分IDには SERIAL または BIGSERIAL を、分散システムには UUID を使用

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(customer_id),
    order_data JSONB NOT NULL DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    total_amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

テーブル設計

• 常に主キーを定義する
• 適切なON DELETE/UPDATEアクションを持つ外部キーを使用
• 必要に応じてNOT NULL制約を追加
• CHECK制約でデータ検証を実施
• 大規模テーブルのパーティショニングを検討

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'inactive', 'discontinued')),
    metadata JSONB DEFAULT '{}'
);

パーティショニング

• 大規模テーブル（数百万行）には宣言型パーティショニングを使用
• 適切なパーティション戦略を選択: RANGE、LIST、またはHASH
• パーティショニング後、パーティショニングされたテーブルにインデックスを作成

CREATE TABLE events (
    event_id BIGSERIAL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

インデックス戦略

インデックスタイプ

• 等号クエリと範囲クエリにはB-treeインデックス（デフォルト）を使用
• JSONB、配列、全文検索にはGINインデックスを使用
• 幾何学的データと範囲型にはGiSTインデックスを使用
• 大規模で自然に順序付けられたデータにはBRINインデックスを使用
• フィルター処理されたクエリには部分インデックスを検討

-- 一般的なルックアップ用B-treeインデックス
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- JSONBクエリ用GINインデックス
CREATE INDEX idx_orders_data ON orders USING GIN (order_data);

-- アクティブなレコードのみの部分インデックス
CREATE INDEX idx_active_products ON products(name) WHERE status = 'active';

-- テーブルルックアップを回避するカバリングインデックス
CREATE INDEX idx_orders_covering ON orders(customer_id)
    INCLUDE (order_date, total_amount);

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

• 定期的にANALYZEを実行して統計情報を更新
• 膨張したインデックスにはREINDEXを使用
• pg_stat_user_indexesでインデックス使用状況を監視
• 未使用のインデックスを削除して書き込みオーバーヘッドを削減

-- インデックス使用状況を確認
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

クエリ最適化

EXPLAIN ANALYZE

• 遅いクエリに対して常にクエリプランを分析
• 大規模テーブルのシーケンシャルスキャンを探す
• クエリプランから欠落しているインデックスを特定
• 行推定値と実際の行数の乖離を監視

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.name, COUNT(o.order_id)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.created_at > '2024-01-01'
GROUP BY c.customer_id, c.name;

共通テーブル式（CTE）

• 複雑なクエリ編成にはCTEを使用
• 注意: 古いPostgreSQLバージョンではCTEは最適化フェンス
• PostgreSQL 12以降では MATERIALIZED/NOT MATERIALIZED ヒントを使用

WITH recent_orders AS MATERIALIZED (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders
    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY customer_id
)
SELECT c.name, ro.order_count, ro.total_spent
FROM customers c
JOIN recent_orders ro ON c.customer_id = ro.customer_id
WHERE ro.total_spent > 1000;

ウィンドウ関数

• 分析クエリにはウィンドウ関数を使用
• PARTITION BYとORDER BYを活用した複雑な計算

SELECT
    order_id,
    customer_id,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rank
FROM orders;

JSONBベストプラクティス

• パフォーマンスとインデックス機能の点でJSONではなくJSONBを使用
• クエリ対象のJSONB列にはGINインデックスを作成
• 効率的なクエリには包含演算子（@>、<@）を使用
• 頻繁にクエリされるフィールドは通常列に抽出

-- GINインデックスを使用した効率的なJSONBクエリ
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- 特定フィールドを抽出
SELECT
    product_id,
    metadata->>'brand' AS brand,
    (metadata->>'rating')::numeric AS rating
FROM products
WHERE metadata ? 'rating';

接続管理

接続プーリング

• 接続プーリングにはPgBounceまたはpgpool-IIを使用
• ワークロードに基づいて適切なプールサイズを設定
• 短時間の接続にはトランザクションプーリングモードを使用

接続設定

-- 推奨されるセッション設定
SET statement_timeout = '30s';
SET lock_timeout = '10s';
SET idle_in_transaction_session_timeout = '60s';

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

• 適切なトランザクション分離レベルを使用
• ロック競合を減らすためトランザクションを短く保つ
• アプリケーションレベルのロックにはアドバイザリロックを使用
• ロック競合を監視して解決

-- アプリケーション調整用のアドバイザリロックを使用
SELECT pg_advisory_lock(hashtext('resource_name'));
-- 処理を実行
SELECT pg_advisory_unlock(hashtext('resource_name'));

-- ブロックしているクエリを確認
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid;

メンテナンス

VACUUMとANALYZE

• autovacuumを有効にしてワークロードに合わせてチューニング
• バルク操作後は手動でVACUUM ANALYZEを実行
• テーブルブロートを監視

-- テーブルブロートを確認
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

バックアップ戦略

• 論理バックアップにはpg_dumpを使用
• 物理バックアップにはpg_basebackupを使用
• WALアーカイビングを使用してポイントインタイムリカバリ（PITR）を実装
• バックアップ復元を定期的にテスト

セキュリティ

• 接続にはSSL/TLSを使用
• マルチテナントアプリケーションには行レベルセキュリティ（RLS）を実装
• アクセス制御にはロールとGRANT/REVOKEを使用
• pgAudit拡張で機密操作を監査

-- 行レベルセキュリティを有効化
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY documents_tenant_policy ON documents
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- 最小限の権限を付与
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT USAGE ON SEQUENCE orders_order_id_seq TO app_user;

モニタリング

• pg_stat_statements拡張でモニタリング
• 遅いクエリを追跡して定期的に最適化
• レプリケーションラグ、接続数、ディスク使用量のアラートを設定
• pg_stat_activityで有効なクエリを監視

-- pg_stat_statementsを有効化
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 遅いクエリを検索
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;