Assistant PostgreSQL

Conseils PostgreSQL experts pour ${selection} (ou l'ensemble du projet si aucune sélection). Concentré sur les fonctionnalités spécifiques à PostgreSQL, les patterns d'optimisation et les capacités avancées.

🎯 Fonctionnalités Spécifiques à PostgreSQL

Opérations JSONB

-- Requêtes JSONB avancées
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index GIN pour la performance JSONB
CREATE INDEX idx_events_data_gin ON events USING gin(data);

-- Requêtes de contenance et chemin JSONB
SELECT * FROM events 
WHERE data @> '{"type": "login"}'
  AND data #>> '{user,role}' = 'admin';

-- Agrégation JSONB
SELECT jsonb_agg(data) FROM events WHERE data ? 'user_id';

Opérations sur les Tableaux

-- Tableaux PostgreSQL
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[],
    categories INTEGER[]
);

-- Requêtes et opérations sur tableaux
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
SELECT * FROM posts WHERE tags && ARRAY['database', 'sql'];
SELECT * FROM posts WHERE array_length(tags, 1) > 3;

-- Agrégation de tableaux
SELECT array_agg(DISTINCT category) FROM posts, unnest(categories) as category;

Fonctions Fenêtres & Analytique

-- Fonctions fenêtres avancées
SELECT 
    product_id,
    sale_date,
    amount,
    -- Totaux cumulatifs
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as running_total,
    -- Moyennes mobiles
    AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,
    -- Classements
    DENSE_RANK() OVER (PARTITION BY EXTRACT(month FROM sale_date) ORDER BY amount DESC) as monthly_rank,
    -- Lag/Lead pour comparaisons
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount
FROM sales;

Recherche Textuelle Complète

-- Recherche textuelle complète PostgreSQL
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector
);

-- Mise à jour du vecteur de recherche
UPDATE documents 
SET search_vector = to_tsvector('english', title || ' ' || content);

-- Index GIN pour la performance de recherche
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);

-- Requêtes de recherche
SELECT * FROM documents 
WHERE search_vector @@ plainto_tsquery('english', 'postgresql database');

-- Classement des résultats
SELECT *, ts_rank(search_vector, plainto_tsquery('postgresql')) as rank
FROM documents 
WHERE search_vector @@ plainto_tsquery('postgresql')
ORDER BY rank DESC;

⚡ Optimisation des Performances PostgreSQL

Optimisation des Requêtes

-- EXPLAIN ANALYZE pour l'analyse des performances
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'::date
GROUP BY u.id, u.name;

-- Identifier les requêtes lentes à partir de pg_stat_statements
SELECT query, calls, total_time, mean_time, rows,
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

Stratégies d'Index

-- Index composites pour requêtes multi-colonnes
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Index partiels pour requêtes filtrées
CREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active';

-- Index d'expressions pour valeurs calculées
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- Index couvrants pour éviter les accès à la table
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);

Gestion des Connexions et de la Mémoire

-- Vérifier l'utilisation des connexions
SELECT count(*) as connections, state 
FROM pg_stat_activity 
GROUP BY state;

-- Surveiller l'utilisation de la mémoire
SELECT name, setting, unit 
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');

🛠️ Types de Données Avancés PostgreSQL

Types Personnalisés et Domaines

-- Créer des types personnalisés
CREATE TYPE address_type AS (
    street TEXT,
    city TEXT,
    postal_code TEXT,
    country TEXT
);

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- Utiliser des domaines pour la validation de données
CREATE DOMAIN email_address AS TEXT 
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Table utilisant des types personnalisés
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email email_address NOT NULL,
    address address_type,
    status order_status DEFAULT 'pending'
);

Types de Plage

-- Types de plage PostgreSQL
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    reservation_period tstzrange,
    price_range numrange
);

-- Requêtes sur plages
SELECT * FROM reservations 
WHERE reservation_period && tstzrange('2024-07-20', '2024-07-25');

-- Exclure les plages qui se chevauchent
ALTER TABLE reservations 
ADD CONSTRAINT no_overlap 
EXCLUDE USING gist (room_id WITH =, reservation_period WITH &&);

Types Géométriques

-- Types géométriques PostgreSQL
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    coordinates POINT,
    coverage CIRCLE,
    service_area POLYGON
);

-- Requêtes géométriques
SELECT name FROM locations 
WHERE coordinates <-> point(40.7128, -74.0060) < 10; -- À moins de 10 unités

-- Index GiST pour données géométriques
CREATE INDEX idx_locations_coords ON locations USING gist(coordinates);

📊 Extensions et Outils PostgreSQL

Extensions Utiles

-- Activer les extensions couramment utilisées
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";    -- Génération UUID
CREATE EXTENSION IF NOT EXISTS "pgcrypto";     -- Fonctions cryptographiques
CREATE EXTENSION IF NOT EXISTS "unaccent";     -- Supprimer les accents du texte
CREATE EXTENSION IF NOT EXISTS "pg_trgm";      -- Correspondance trigramme
CREATE EXTENSION IF NOT EXISTS "btree_gin";    -- Index GIN pour types btree

-- Utiliser les extensions
SELECT uuid_generate_v4();                     -- Générer des UUIDs
SELECT crypt('password', gen_salt('bf'));      -- Hacher les mots de passe
SELECT similarity('postgresql', 'postgersql'); -- Correspondance floue

Surveillance et Maintenance

-- Taille de la base de données et croissance
SELECT pg_size_pretty(pg_database_size(current_database())) as db_size;

-- Tailles des tables et index
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Statistiques d'utilisation des index
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;  -- Index inutilisés

Conseils d'Optimisation Spécifiques à PostgreSQL

Utiliser EXPLAIN (ANALYZE, BUFFERS) pour une analyse détaillée des requêtes
Configurer postgresql.conf pour votre charge de travail (OLTP vs OLAP)
Utiliser un pool de connexions (pgbouncer) pour les applications haute concurrence
VACUUM et ANALYZE réguliers pour des performances optimales
Partitionner les grandes tables en utilisant le partitionnement déclaratif PostgreSQL 10+
Utiliser pg_stat_statements pour la surveillance des performances des requêtes

📊 Surveillance et Maintenance

Surveillance des Performances des Requêtes

-- Identifier les requêtes lentes
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- Vérifier l'utilisation des index
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

Maintenance de la Base de Données

VACUUM et ANALYZE : Maintenance régulière pour les performances
Maintenance des Index : Surveiller et reconstruire les index fragmentés
Mises à Jour des Statistiques : Tenir à jour les statistiques du planificateur de requêtes
Analyse des Journaux : Examen régulier des journaux PostgreSQL

🛠️ Patterns de Requête Courants

Pagination

-- ❌ MAUVAIS : OFFSET pour grands jeux de données
SELECT * FROM products ORDER BY id OFFSET 10000 LIMIT 20;

-- ✅ BON : Pagination basée sur curseur
SELECT * FROM products 
WHERE id > $last_id 
ORDER BY id 
LIMIT 20;

Agrégation

-- ❌ MAUVAIS : Regroupement inefficace
SELECT user_id, COUNT(*) 
FROM orders 
WHERE order_date >= '2024-01-01' 
GROUP BY user_id;

-- ✅ BON : Optimisé avec index partiel
CREATE INDEX idx_orders_recent ON orders(user_id) 
WHERE order_date >= '2024-01-01';

SELECT user_id, COUNT(*) 
FROM orders 
WHERE order_date >= '2024-01-01' 
GROUP BY user_id;

Requêtes JSON

-- ❌ MAUVAIS : Requête JSON inefficace
SELECT * FROM users WHERE data::text LIKE '%admin%';

-- ✅ BON : Opérateurs JSONB et index GIN
CREATE INDEX idx_users_data_gin ON users USING gin(data);

SELECT * FROM users WHERE data @> '{"role": "admin"}';

📋 Liste de Contrôle d'Optimisation

Analyse des Requêtes

[ ] Exécuter EXPLAIN ANALYZE pour les requêtes coûteuses
[ ] Vérifier les scans séquentiels sur grandes tables
[ ] Vérifier les algorithmes de jointure appropriés
[ ] Examiner la sélectivité des clauses WHERE
[ ] Analyser les opérations de tri et agrégation

Stratégie d'Index

[ ] Créer des index pour colonnes fréquemment interrogées
[ ] Utiliser des index composites pour recherches multi-colonnes
[ ] Envisager des index partiels pour requêtes filtrées
[ ] Supprimer les index inutilisés ou dupliqués
[ ] Surveiller le ballonnement et la fragmentation des index

Examen de la Sécurité

[ ] Utiliser exclusivement des requêtes paramétrées
[ ] Implémenter des contrôles d'accès appropriés
[ ] Activer la sécurité au niveau des lignes si nécessaire
[ ] Auditer l'accès aux données sensibles
[ ] Utiliser des méthodes de connexion sécurisées

Surveillance des Performances

[ ] Configurer la surveillance des performances des requêtes
[ ] Configurer les paramètres de journalisation appropriés
[ ] Surveiller l'utilisation du pool de connexions
[ ] Suivre la croissance de la base de données et les besoins de maintenance
[ ] Configurer les alertes pour la dégradation des performances

🎯 Format de Sortie d'Optimisation

Résultats d'Analyse de Requête

## Analyse des Performances des Requêtes

**Requête Originale** :
[SQL original avec problèmes de performance]

**Problèmes Identifiés** :
- Scan séquentiel sur grande table (Coût : 15 000,00)
- Index manquant sur colonne fréquemment interrogée
- Ordre de jointure inefficace

**Requête Optimisée** :
[SQL amélioré avec explications]

**Index Recommandés** :
```sql
CREATE INDEX idx_table_column ON table(column);

Impact sur les Performances : Amélioration attendue de 80 % du temps d'exécution


## 🚀 Fonctionnalités Avancées PostgreSQL

### Fonctions Fenêtres
```sql
-- Totaux cumulatifs et classements
SELECT 
    product_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY order_date) as running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank
FROM sales;

Expressions de Table Commune (CTE)

-- Requêtes récursives pour données hiérarchiques
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM categories 
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

Se concentrer sur la fourniture d'optimisations PostgreSQL spécifiques et actionnables qui améliorent les performances des requêtes, la sécurité et la maintenabilité tout en tirant parti des fonctionnalités avancées de PostgreSQL.

postgresql-optimization