Assistant pour la Revue de Code PostgreSQL

Revue de code PostgreSQL expert pour ${selection} (ou projet entier si pas de sélection). Concentrez-vous sur les bonnes pratiques PostgreSQL-spécifiques, les anti-patterns et les standards de qualité uniques à PostgreSQL.

🎯 Domaines de Revue Spécifiques à PostgreSQL

Bonnes Pratiques JSONB

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

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

-- ❌ BAD: Imbrication profonde sans considération
UPDATE orders SET data = data || '{"shipping":{"tracking":{"number":"123"}}}';

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

Revue des Opérations sur Tableaux

-- ❌ BAD: Opérations sur tableaux inefficaces
SELECT * FROM products WHERE 'electronics' = ANY(categories);  -- No index

-- ✅ GOOD: Requêtes sur tableaux indexées GIN
CREATE INDEX idx_products_categories ON products USING gin(categories);
SELECT * FROM products WHERE categories @> ARRAY['electronics'];

-- ❌ BAD: Concaténation de tableaux en boucles
-- This would be inefficient in a function/procedure

-- ✅ GOOD: Opérations sur tableaux en masse
UPDATE products SET categories = categories || ARRAY['new_category']
WHERE id IN (SELECT id FROM products WHERE condition);

Revue de la Conception du Schéma PostgreSQL

-- ❌ BAD: Ne pas utiliser les fonctionnalités PostgreSQL
CREATE TABLE users (
    id INTEGER,
    email VARCHAR(255),
    created_at TIMESTAMP
);

-- ✅ GOOD: Schéma optimisé pour PostgreSQL
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);

Types Personnalisés et Domaines

-- ❌ BAD: Utiliser des types génériques pour des données spécifiques
CREATE TABLE transactions (
    amount DECIMAL(10,2),
    currency VARCHAR(3),
    status VARCHAR(20)
);

-- ✅ GOOD: Types personnalisés PostgreSQL
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'
);

🔍 Anti-Patterns Spécifiques à PostgreSQL

Anti-Patterns de Performance

• Éviter les index spécifiques à PostgreSQL : Ne pas utiliser GIN/GiST pour les types de données appropriés
• Mauvaise utilisation de JSONB : Traiter JSONB comme un simple champ de chaîne
• Ignorer les opérateurs sur tableaux : Utiliser des opérations sur tableaux inefficaces
• Mauvaise sélection de clé de partition : Ne pas exploiter efficacement le partitioning PostgreSQL

Problèmes de Conception du Schéma

• Ne pas utiliser les types ENUM : Utiliser VARCHAR pour des ensembles de valeurs limités
• Ignorer les contraintes : Manquer de contraintes CHECK pour la validation des données
• Mauvais types de données : Utiliser VARCHAR au lieu de TEXT ou CITEXT
• Absence de structure JSONB : JSONB non structuré sans validation

Problèmes de Fonction et Déclencheur

-- ❌ BAD: Fonction de déclencheur inefficace
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();  -- Should use TIMESTAMPTZ
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ✅ GOOD: Fonction de déclencheur optimisée
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();

📊 Revue de l'Utilisation des Extensions PostgreSQL

Bonnes Pratiques des Extensions

-- ✅ Vérifier si l'extension existe avant de la créer
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ✅ Utiliser les extensions de manière appropriée
-- Génération d'UUID
SELECT uuid_generate_v4();

-- Hachage de mot de passe
SELECT crypt('password', gen_salt('bf'));

-- Correspondance de texte approximatif
SELECT word_similarity('postgres', 'postgre');

🛡️ Revue de Sécurité PostgreSQL

Row Level Security (RLS)

-- ✅ GOOD: Implémentation de 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);

Gestion des Privilèges

-- ❌ BAD: Permissions trop larges
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

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

🎯 Liste de Contrôle de Qualité du Code PostgreSQL

Conception du Schéma

• [ ] Utilisation des types de données PostgreSQL appropriés (CITEXT, JSONB, tableaux)
• [ ] Exploitation des types ENUM pour les valeurs contraintes
• [ ] Implémentation de contraintes CHECK correctes
• [ ] Utilisation de TIMESTAMPTZ au lieu de TIMESTAMP
• [ ] Définition de domaines personnalisés pour les contraintes réutilisables

Considérations de Performance

• [ ] Types d'index appropriés (GIN pour JSONB/tableaux, GiST pour les plages)
• [ ] Requêtes JSONB utilisant les opérateurs de contenance (@>, ?)
• [ ] Opérations sur tableaux utilisant les opérateurs spécifiques à PostgreSQL
• [ ] Utilisation appropriée des window functions et CTEs
• [ ] Utilisation efficace des fonctions spécifiques à PostgreSQL

Utilisation des Fonctionnalités PostgreSQL

• [ ] Utilisation des extensions où approprié
• [ ] Implémentation de procédures stockées en PL/pgSQL quand bénéfique
• [ ] Exploitation des fonctionnalités SQL avancées de PostgreSQL
• [ ] Utilisation des techniques d'optimisation spécifiques à PostgreSQL
• [ ] Implémentation de la gestion d'erreurs appropriée dans les fonctions

Sécurité et Conformité

• [ ] Implémentation de Row Level Security (RLS) si nécessaire
• [ ] Gestion appropriée des rôles et privilèges
• [ ] Utilisation des fonctions de chiffrement intégrées à PostgreSQL
• [ ] Implémentation de pistes d'audit avec les fonctionnalités PostgreSQL

📝 Directives de Revue Spécifiques à PostgreSQL

1. Optimisation des Types de Données : S'assurer que les types spécifiques à PostgreSQL sont utilisés de manière appropriée
2. Stratégie d'Index : Examiner les types d'index et s'assurer que les index spécifiques à PostgreSQL sont utilisés
3. Structure JSONB : Valider la conception du schéma JSONB et les motifs de requête
4. Qualité de la Fonction : Examiner les fonctions PL/pgSQL pour l'efficacité et les bonnes pratiques
5. Utilisation des Extensions : Vérifier l'utilisation appropriée des extensions PostgreSQL
6. Fonctionnalités de Performance : Vérifier l'utilisation des fonctionnalités avancées de PostgreSQL
7. Implémentation de la Sécurité : Examiner les fonctionnalités de sécurité spécifiques à PostgreSQL

Concentrez-vous sur les capacités uniques de PostgreSQL et assurez-vous que le code exploite ce qui rend PostgreSQL spécial plutôt que de le traiter comme une base de données SQL générique.