postgresql

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

次のようなことをユーザーが希望する場合は、このスキルを使用してください。

• テーブル、インデックス、制約、トリガー、PL/pgSQL 関数を設計する
• SQL クエリ（結合、CTE、ウィンドウ関数、集計）を作成または最適化する
• PostgreSQL 固有の機能（JSONB、全文検索、配列型、pgvector）を使用する
• psql でユーザー、ロール、権限を管理する
• バックアップ（pg_dump）、レプリケーション、パフォーマンスチューニング（EXPLAIN ANALYZE）を設定する

このスキルの使用方法

ワークフロー

1. タスクを特定する - スキーマ設計、クエリ作成、最適化、または管理
2. SQL を記述する - 以下のパターンと例を使用する
3. パフォーマンスを分析する - 低速なクエリに対して EXPLAIN ANALYZE を実行する
4. ベストプラクティスを適用する - 必要に応じてインデックス戦略、VACUUM、パーティショニングを行う

クイックスタートの例：インデックスとクエリを含むテーブル

-- Create a table with constraints
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    status      TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','shipped','delivered')),
    total       NUMERIC(10,2) NOT NULL,
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Create an index for common queries
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

-- Query with CTE and window function
WITH monthly_totals AS (
    SELECT customer_id,
           date_trunc('month', created_at) AS month,
           SUM(total) AS month_total
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id, date_trunc('month', created_at)
)
SELECT customer_id, month, month_total,
       LAG(month_total) OVER (PARTITION BY customer_id ORDER BY month) AS prev_month
FROM monthly_totals;

パフォーマンス分析

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

ベストプラクティス

1. 戦略的にインデックスを作成する - WHERE/JOIN 句で使用する列にインデックスを作成し、フィルター条件のあるクエリには部分インデックスを使用する
2. 定期的に VACUUM を実行する - テーブルの肥大化を防ぎ、書き込みの多いテーブルでは autovacuum のしきい値を設定する
3. 大きなテーブルをパーティショニングする - 1 億行を超えるテーブルでは、タイムスタンプ列を使った範囲パーティショニングを使用する
4. ROLE/GRANT を使用する - 最小権限を付与し、アプリケーションの接続にスーパーユーザーを使用しない
5. バックアップを作成して検証する - pg_dump または WAL アーカイブを使用し、復元手順を定期的にテストする

キーワード

postgresql, postgres, psql, SQL, JSONB, 全文検索, CTE, ウィンドウ関数, リレーショナルデータベース, インデックス, レプリケーション, EXPLAIN ANALYZE, pg_dump, パーティショニング