Agent skill

postgres-patterns

PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices.

View SKILL.md on GitHub Repository
Stars 132,726
Forks 19,206

Install this agent skill to your Project

npx add-skill https://github.com/affaan-m/everything-claude-code/tree/main/docs/ja-JP/skills/postgres-patterns

SKILL.md

PostgreSQL パターン

PostgreSQLベストプラクティスのクイックリファレンス。詳細なガイダンスについては、database-reviewer エージェントを使用してください。

起動タイミング

  • SQLクエリまたはマイグレーションの作成時
  • データベーススキーマの設計時
  • 低速クエリのトラブルシューティング時
  • Row Level Securityの実装時
  • コネクションプーリングの設定時

クイックリファレンス

インデックスチートシート

クエリパターン インデックスタイプ 例
WHERE col = value B-tree（デフォルト） CREATE INDEX idx ON t (col)
WHERE col > value B-tree CREATE INDEX idx ON t (col)
WHERE a = x AND b > y 複合 CREATE INDEX idx ON t (a, b)
WHERE jsonb @> '{}' GIN CREATE INDEX idx ON t USING gin (col)
WHERE tsv @@ query GIN CREATE INDEX idx ON t USING gin (col)
時系列範囲 BRIN CREATE INDEX idx ON t USING brin (col)

データタイプクイックリファレンス

用途 正しいタイプ 避けるべき
ID bigint int、ランダムUUID
文字列 text varchar(255)
タイムスタンプ timestamptz timestamp
金額 numeric(10,2) float
フラグ boolean varchar、int

一般的なパターン

複合インデックスの順序:

sql
-- 等価列を最初に、次に範囲列
CREATE INDEX idx ON orders (status, created_at);
-- 次の場合に機能: WHERE status = 'pending' AND created_at > '2024-01-01'

カバリングインデックス:

sql
CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- SELECT email, name, created_at のテーブル検索を回避

部分インデックス:

sql
CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- より小さなインデックス、アクティブユーザーのみを含む

RLSポリシー（最適化）:

sql
CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- SELECTでラップ！

UPSERT:

sql
INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

カーソルページネーション:

sql
SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) vs OFFSET は O(n)

キュー処理:

sql
UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

アンチパターン検出

sql
-- インデックスのない外部キーを検索
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- 低速クエリを検索
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- テーブル肥大化をチェック
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

設定テンプレート

sql
-- 接続制限（RAMに応じて調整）
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- タイムアウト
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- モニタリング
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- セキュリティデフォルト
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

関連

  • Agent: database-reviewer - 完全なデータベースレビューワークフロー
  • Skill: clickhouse-io - ClickHouse分析パターン
  • Skill: backend-patterns - APIとバックエンドパターン

[Supabase Agent Skills](Supabase Agent Skills (credit: Supabase team))（MITライセンス）に基づく

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

affaan-m/everything-claude-code

python-testing

Python testing best practices using pytest including fixtures, parametrization, mocking, coverage analysis, async testing, and test organization. Use when writing or improving Python tests.

132,726 19,206
Explore
affaan-m/everything-claude-code

golang-patterns

Go-specific design patterns and best practices including functional options, small interfaces, dependency injection, concurrency patterns, error handling, and package organization. Use when working with Go code to apply idiomatic Go patterns.

132,726 19,206
Explore
affaan-m/everything-claude-code

e2e-testing

Playwright E2E testing patterns, Page Object Model, configuration, CI/CD integration, artifact management, and flaky test strategies.

132,726 19,206
Explore
affaan-m/everything-claude-code

agentic-engineering

Operate as an agentic engineer using eval-first execution, decomposition, and cost-aware model routing. Use when AI agents perform most implementation work and humans enforce quality and risk controls.

132,726 19,206
Explore
affaan-m/everything-claude-code

api-design

REST API design patterns including resource naming, status codes, pagination, filtering, error responses, versioning, and rate limiting for production APIs.

132,726 19,206
Explore
affaan-m/everything-claude-code

python-patterns

Python-specific design patterns and best practices including protocols, dataclasses, context managers, decorators, async/await, type hints, and package organization. Use when working with Python code to apply Pythonic patterns.

132,726 19,206
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results