postgresql-code-review

PostgreSQL コードレビューアシスタント

${selection} に対する専門的な PostgreSQL コードレビュー（選択がない場合はプロジェクト全体）。PostgreSQL 固有のベストプラクティス、アンチパターン、そして PostgreSQL ならではの品質基準に焦点を当てます。

🎯 PostgreSQL 固有のレビュー領域

JSONB のベストプラクティス

-- ❌ BAD: Inefficient JSONB usage
SELECT * FROM orders WHERE data->>'status' = 'shipped';  -- No index support

-- ✅ GOOD: Indexable JSONB queries
CREATE INDEX idx_orders_status ON orders USING gin((data->'status'));
SELECT * FROM orders WHERE data @> '{"status": "shipped"}';

-- ❌ BAD: Deep nesting without consideration
UPDATE orders SET data = data || '{"shipping":{"tracking":{"number":"123"}}}';

-- ✅ GOOD: Structured JSONB with validation
ALTER TABLE orders ADD CONSTRAINT valid_status 
CHECK (data->>'status' IN ('pending', 'shipped', 'delivered'));

配列操作のレビュー

-- ❌ BAD: Inefficient array operations
SELECT * FROM products WHERE 'electronics' = ANY(categories);  -- No index

-- ✅ GOOD: GIN indexed array queries
CREATE INDEX idx_products_categories ON products USING gin(categories);
SELECT * FROM products WHERE categories @> ARRAY['electronics'];

-- ❌ BAD: Array concatenation in loops
-- This would be inefficient in a function/procedure

-- ✅ GOOD: Bulk array operations
UPDATE products SET categories = categories || ARRAY['new_category']
WHERE id IN (SELECT id FROM products WHERE condition);

PostgreSQL スキーマ設計のレビュー

-- ❌ BAD: Not using PostgreSQL features
CREATE TABLE users (
    id INTEGER,
    email VARCHAR(255),
    created_at TIMESTAMP
);

-- ✅ GOOD: PostgreSQL-optimized schema
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email CITEXT UNIQUE NOT NULL,  -- Case-insensitive email
    created_at TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- Add JSONB GIN index for metadata queries
CREATE INDEX idx_users_metadata ON users USING gin(metadata);

カスタム型とドメイン

-- ❌ BAD: Using generic types for specific data
CREATE TABLE transactions (
    amount DECIMAL(10,2),
    currency VARCHAR(3),
    status VARCHAR(20)
);

-- ✅ GOOD: PostgreSQL custom types
CREATE TYPE currency_code AS ENUM ('USD', 'EUR', 'GBP', 'JPY');
CREATE TYPE transaction_status AS ENUM ('pending', 'completed', 'failed', 'cancelled');
CREATE DOMAIN positive_amount AS DECIMAL(10,2) CHECK (VALUE > 0);

CREATE TABLE transactions (
    amount positive_amount NOT NULL,
    currency currency_code NOT NULL,
    status transaction_status DEFAULT 'pending'
);

🔍 PostgreSQL 固有のアンチパターン

パフォーマンスのアンチパターン

• PostgreSQL 固有のインデックスの回避: 適切なデータ型に対して GIN/GiST を使用していない
• JSONB の誤用: JSONB を単純な文字列フィールドのように扱っている
• 配列演算子の無視: 非効率な配列操作を使用している
• 不適切なパーティションキーの選択: PostgreSQL のパーティショニングを効果的に活用していない

スキーマ設計の問題

• ENUM 型を使用していない: 値の種類が限られているのに VARCHAR を使用している
• 制約の無視: データ検証のための CHECK 制約が欠けている
• 誤ったデータ型: TEXT や CITEXT ではなく VARCHAR を使用している
• JSONB 構造の欠如: 検証のない非構造化 JSONB

関数とトリガーの問題

-- ❌ BAD: Inefficient trigger function
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();  -- Should use TIMESTAMPTZ
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ✅ GOOD: Optimized trigger function
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Set trigger to fire only when needed
CREATE TRIGGER update_modified_time_trigger
    BEFORE UPDATE ON table_name
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION update_modified_time();

📊 PostgreSQL 拡張機能の使用レビュー

拡張機能のベストプラクティス

-- ✅ Check if extension exists before creating
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ✅ Use extensions appropriately
-- UUID generation
SELECT uuid_generate_v4();

-- Password hashing
SELECT crypt('password', gen_salt('bf'));

-- Fuzzy text matching
SELECT word_similarity('postgres', 'postgre');

🛡️ PostgreSQL セキュリティレビュー

行レベルセキュリティ（RLS）

-- ✅ GOOD: Implementing RLS
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_data_policy ON sensitive_data
    FOR ALL TO application_role
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

権限管理

-- ❌ BAD: Overly broad permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

-- ✅ GOOD: Granular permissions
GRANT SELECT, INSERT, UPDATE ON specific_table TO app_user;
GRANT USAGE ON SEQUENCE specific_table_id_seq TO app_user;

🎯 PostgreSQL コード品質チェックリスト

スキーマ設計

• 適切な PostgreSQL データ型（CITEXT、JSONB、配列）を使用している
• 値が制約された項目に ENUM 型を活用している
• 適切な CHECK 制約を実装している
• TIMESTAMP ではなく TIMESTAMPTZ を使用している
• 再利用可能な制約のためにカスタムドメインを定義している

パフォーマンスに関する考慮事項

• 適切なインデックス型（JSONB/配列には GIN、範囲には GiST）
• 包含演算子（@>、?）を使用した JSONB クエリ
• PostgreSQL 固有の演算子を使用した配列操作
• ウィンドウ関数と CTE の適切な使用
• PostgreSQL 固有の関数の効率的な使用

PostgreSQL 機能の活用

• 適切な場面で拡張機能を使用している
• 有益な場合は PL/pgSQL でストアドプロシージャを実装している
• PostgreSQL の高度な SQL 機能を活用している
• PostgreSQL 固有の最適化手法を使用している
• 関数内で適切なエラーハンドリングを実装している

セキュリティとコンプライアンス

• 必要に応じて行レベルセキュリティ（RLS）を実装している
• 適切なロールと権限の管理
• PostgreSQL 組み込みの暗号化関数の使用
• PostgreSQL の機能を使用した監査証跡の実装

📝 PostgreSQL 固有のレビューガイドライン

1. データ型の最適化: PostgreSQL 固有の型が適切に使用されていることを確認する
2. インデックス戦略: インデックス型を確認し、PostgreSQL 固有のインデックスが活用されていることを確かめる
3. JSONB 構造: JSONB のスキーマ設計とクエリパターンを検証する
4. 関数の品質: PL/pgSQL 関数の効率性とベストプラクティスを確認する
5. 拡張機能の使用: PostgreSQL 拡張機能の適切な使用を検証する
6. パフォーマンス機能: PostgreSQL の高度な機能の活用状況を確認する
7. セキュリティ実装: PostgreSQL 固有のセキュリティ機能を確認する

PostgreSQL の独自の機能に焦点を当て、単なる汎用 SQL データベースとして扱うのではなく、PostgreSQL ならではの特長をコードが活用していることを確認してください。