VACUUM et ANALYZE
Explication
VACUUM récupère l'espace des lignes mortes (après UPDATE/DELETE). VACUUM FULL réécrit la table entièrement (bloquant). ANALYZE met à jour les statistiques du planificateur. VACUUM ANALYZE fait les deux. L'autovacuum gère cela automatiquement en production.
Exemple concret
Maintenance de table
-- Nettoyer et mettre à jour les statistiques
VACUUM ANALYZE orders;
-- Vérifier le besoin de vacuum
SELECT relname, n_dead_tup, n_live_tup,
last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';Résultat
| relname | n_dead_tup | n_live_tup | last_autovacuum |
|---|---|---|---|
| orders | 342 | 12500 | 2024-01-15 03:00:00 |
Notions SQL
Explorez, apprenez et maîtrisez les concepts essentiels pour progresser à votre rythme.
ABS – valeur absolue
ABS retourne la valeur absolue d'un nombre (supprime le signe).
absence de SELECT *
Éviter SELECT * améliore la clarté. Vous savez exactement ce que vous renvoyez. Moins de risques si le schéma change.
alias explicites
Des alias explicites clarifient les colonnes. Préférer order_total à t. Évite les collisions de noms.
BETWEEN / NOT BETWEEN
BETWEEN vérifie qu'une valeur est dans un intervalle (inclus aux deux bornes). Fonctionne avec les nombres et les dates.
clés primaires et étrangères
Une clé primaire identifie une ligne de façon unique. Une clé étrangère référence une autre table. orders. customer_id → customers. idproducts...
colonnes explicitement listées
Lister les colonnes rend la requête stable. Évite des colonnes inutiles. Facilite la review.
CONCAT et assemblage de chaînes
CONCAT assemble plusieurs chaînes. On peut aussi utiliser l'opérateur || en PostgreSQL.
CREATE TABLE
CREATE TABLE crée une table. Vous définissez colonnes, types et contraintes. À exécuter une seule fois.
DATE_PART – extraire une composante de date
DATE_PART extrait une composante d'une date (année, mois, jour, heure…). Syntaxe alternative à EXTRACT.
DISTINCT
DISTINCT supprime les doublons. Il s’applique aux colonnes sélectionnées. Attention : ça peut coûter plus cher en performance.
DROP TABLE
DROP TABLE supprime la table. Action destructive. À éviter en prod.
Fonctions d'arrondi : ROUND, CEIL, FLOOR
ROUND arrondit un nombre. CEIL arrondit au supérieur. FLOOR arrondit à l'inférieur.
FOREIGN KEY
Une FOREIGN KEY lie deux tables. Elle assure la cohérence. Elle empêche des références invalides.
formatage SQL standardisé
Un SQL standardisé est plus simple à relire. Même style partout. Moins d’ambiguïté.
FROM
FROM indique la table source. Une requête lit d’abord les données depuis cette table. Vous pourrez ensuite filtrer avec WHERE.
gestion des NULL
Un NULL signifie « valeur inconnue / absente ». Écrire = NULL ne marche pas : utilisez IS NULL ou IS NOT NULL.
gestion explicite des NULL
Gérer les NULL évite des résultats inattendus. Utilisez COALESCE si besoin. Utilisez IS NULL pour filtrer.
granularité maîtrisée
La granularité = le niveau de détail d’une ligne. Commande ≠ ligne de commande. Un JOIN peut changer la granularité.
GREATEST et LEAST
GREATEST retourne la plus grande valeur parmi une liste d'arguments. LEAST retourne la plus petite. Ce sont des fonctions scalaires, contrairement aux agrégats MAX/MIN.
IN / NOT IN
IN teste si une valeur est dans une liste. NOT IN exclut. Attention avec NOT IN et NULL.
indentation cohérente
Une indentation cohérente rend les blocs évidents. Utile surtout avec JOIN/CTE. Réduit les erreurs de lecture.
LENGTH
LENGTH retourne le nombre de caractères d'une chaîne.
LIKE et ILIKE
LIKE filtre par motif avec % (n'importe quoi) et _ (un caractère). ILIKE est la variante insensible à la casse.
LIMIT
LIMIT limite le nombre de lignes renvoyées. Utile pour tester une requête. Souvent combiné avec ORDER BY.
LTRIM, RTRIM, BTRIM
Supprime les espaces ou caractères en début/fin de chaîne.
MAKE_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
MAKE_DATE construit une date depuis ses composantes. CURRENT_TIME retourne l'heure courante. CURRENT_TIMESTAMP retourne la date et l'heure actuelles avec timezone.
MOD, POWER, SQRT, TRUNC
MOD retourne le reste d'une division, POWER calcule une puissance, SQRT la racine carrée, et TRUNC tronque un nombre sans arrondir.
mots-clés SQL en majuscules
Mettre les mots-clés en majuscules améliore la lecture. SELECT, FROM, WHERELes identifiants restent en minuscules.
noms de CTE descriptifs
Une CTE doit avoir un nom qui décrit son rôle. Ex : order_totals, active_customers.
opérateurs logiques et de comparaison
Les opérateurs comparent et combinent des conditions. Comparaison : =, <, >=Logique : AND, OR, NOT
ORDER BY
ORDER BY trie le résultat. ASC = croissant. DESC = décroissant.
PRIMARY KEY
Une PRIMARY KEY identifie une ligne. Unique. Non NULL.
SELECT
SELECT sert à choisir les colonnes à afficher. Vous pouvez afficher une ou plusieurs colonnes. Évitez SELECT * si possible.
TRIM
TRIM supprime les espaces en début et fin d'une chaîne.
types de données
Le type définit le format stocké. VARCHAR : texteINT : entierDECIMAL : montantTIMESTAMP : date/heure
une intention par CTE
Une CTE = une intention. Une étape claire. Pas un « fourre-tout ».
UPPER et LOWER
UPPER met une chaîne en majuscules, LOWER en minuscules — pour normaliser avant comparaison.
WHERE
WHERE filtre les lignes. Sans WHERE, vous récupérez tout. Avec WHERE, vous limitez à une condition.
ALTER TABLE
ALTER TABLE modifie la structure. Ajouter une colonne. Ajouter une contrainte.
ANY, ALL avec tableaux
ANY teste si une valeur correspond à au moins un élément d'un tableau (comme IN). ALL teste si elle correspond à tous. @>, <@ et && sont les opérateurs de containment.
Arithmétique de dates et intervalles
On ajoute une durée à une date avec INTERVAL. On soustrait deux dates pour obtenir un nombre de jours. AGE() calcule l'écart entre deux dates.
ARRAY_AGG
ARRAY_AGG agrège des valeurs d'un groupe en un tableau PostgreSQL. Utile pour regrouper des identifiants ou valeurs sans les concaténer en texte.
array_append, array_prepend, array_cat, array_remove
Ajoute, supprime ou concatène des éléments dans un tableau PostgreSQL.
array_to_string, array_position, cardinality
Convertit un tableau en chaîne, cherche un élément, ou compte les éléments.
ARRAY[], UNNEST, array_length
ARRAY[] crée un tableau littéral. UNNEST décompose un tableau en lignes. array_length retourne la longueur d'un tableau selon sa dimension.
AT TIME ZONE
Convertit un timestamp vers un fuseau horaire différent.
Auto-jointure (self-join)
Une auto-jointure joint une table avec elle-même. Utile pour comparer des lignes de la même table.
AVG
AVG calcule une moyenne. Ignore les NULL. Souvent pertinent sur price ou des paniers.
BOOL_AND et BOOL_OR
BOOL_AND retourne TRUE si toutes les lignes d'un groupe satisfont une condition. BOOL_OR retourne TRUE si au moins une ligne la satisfait.
Calcul de différence entre dates
Pour calculer l'écart entre deux dates : date1 - date2 donne des jours, AGE() donne un intervalle lisible. PostgreSQL n'a pas de DATE_DIFF.
CAST et conversion de types
CAST convertit une valeur d'un type à un autre. PostgreSQL supporte aussi la syntaxe :: (ex: '2024-01-01'::date).
CHECK
CHECK impose une règle sur une colonne. Ex : prix > 0. Ex : quantité > 0.
choix approprié WHERE vs HAVING
WHERE filtre avant l’agrégation, HAVING après. WHERE = lignes brutes. HAVING = résultats agrégés.
COALESCE et NULLIF
COALESCE retourne le premier argument non NULL. NULLIF retourne NULL si les deux arguments sont égaux.
COUNT
COUNT compte des lignes. COUNT(*) compte toutes les lignes. COUNT(col) ignore les NULL.
CREATE INDEX et types d'index
Crée un index pour accélérer les requêtes, avec différents types selon le cas d'usage.
CREATE ROLE et ALTER ROLE
Crée et configure des rôles de base de données avec attributs et appartenances.
CREATE SEQUENCE
Crée un générateur de nombres séquentiels indépendant ou lié à une colonne.
CREATE TYPE ENUM
Crée un type énuméré avec une liste fixe de valeurs autorisées.
CREATE VIEW
Une VIEW est une requête sauvegardée comme une table virtuelle. Elle simplifie les requêtes complexes et abstrait la logique métier.
DATE_TRUNC – tronquer une date
DATE_TRUNC tronque une date à la précision voulue (mois, semaine, année…). Indispensable pour les agrégations temporelles.
DELETE
DELETE supprime des lignes. Sans WHERE, vous supprimez tout. Attention aux cascades (ex: order_items).
EXCEPT et INTERSECT
EXCEPT retourne les lignes du premier SELECT absentes du second. INTERSECT retourne les lignes communes aux deux SELECT.
EXISTS / NOT EXISTS
EXISTS vérifie qu'une sous-requête retourne au moins une ligne. Plus efficace que IN pour les grandes tables.
EXPLAIN et EXPLAIN ANALYZE
Affiche le plan d'exécution d'une requête pour identifier les goulots d'étranglement.
FILTER (WHERE) dans les agrégations
Filtre conditionnel appliqué à une agrégation, sans affecter les autres agrégats.
filtres appliqués le plus tôt possible
Filtrer tôt réduit le volume de données. WHERE avant JOIN quand possible. Sous-requête filtrée avant jointure.
Fonctions de date : NOW, CURRENT_DATE, EXTRACT
NOW() retourne la date et heure courante. CURRENT_DATE retourne la date du jour. EXTRACT extrait une partie d'une date.
FULL OUTER JOIN
FULL OUTER JOIN retourne toutes les lignes des deux tables, avec NULL quand il n'y a pas de correspondance.
granularité maîtrisée
La granularité = le niveau de détail d’une ligne. Commande ≠ ligne de commande. Un JOIN peut changer la granularité.
GROUP BY
GROUP BY regroupe les lignes pour agréger. Toute colonne non agrégée doit être dans le GROUP BY. Chaque groupe produit une ligne.
HAVING
HAVING filtre les groupes (après agrégation). WHERE filtre les lignes. HAVING filtre les résultats agrégés.
Index et performance des requêtes
Stratégies d'indexation pour optimiser les performances des requêtes PostgreSQL.
INNER JOIN
INNER JOIN garde uniquement les correspondances. Si ça ne match pas, la ligne disparaît. Utile pour relier des faits et des dimensions.
INSERT
INSERT ajoute des lignes dans une table. Respectez les contraintes (NOT NULL, CHECK). Faites attention aux clés étrangères.
intégrité des données
L’intégrité des données garantit la cohérence. Contraintes NOT NULL, UNIQUE, CHECK, FK. Évite les orphelins et valeurs incohérentes.
jointures justifiées
Chaque JOIN doit apporter une information utilisée. Un JOIN inutile alourdit la requête et peut créer des doublons.
LEFT JOIN
LEFT JOIN garde toutes les lignes de gauche. Si pas de match, les colonnes de droite sont NULL. Parfait pour trouver des « manquants ».
logique conditionnelle simple (CASE WHEN)
CASE WHEN renvoie une valeur différente selon des conditions, dans le SELECT.
maîtrise des doublons
Les doublons faussent les agrégations. Un JOIN peut multiplier les lignes. Vérifier avec COUNT(*) vs COUNT(DISTINCT).
MAX
MAX renvoie la plus grande valeur. Marche avec des nombres et des dates. Souvent couplé à un filtre.
MIN
MIN renvoie la plus petite valeur. Marche avec des nombres et des dates. Utile pour trouver un premier achat.
Opérateurs -> et ->>
-> retourne un sous-document JSON, ->> retourne le texte. #> et #>> permettent l'accès par chemin. Utilisés sur des colonnes de type JSONB.
Opérateurs de containment JSONB : @>, <@, ?
Teste si un JSON contient, est contenu dans, ou possède certaines clés.
protection contre les divisions par zéro
Une division par zéro provoque une erreur. Utiliser NULLIF(x, 0) ou CASE WHEN. Fréquent dans les calculs de ratios.
RANDOM et GENERATE_SERIES
RANDOM() génère un nombre aléatoire entre 0 et 1. GENERATE_SERIES produit une série de valeurs numériques ou de dates avec un pas défini.
REPLACE et SPLIT_PART
REPLACE remplace toutes les occurrences d'une sous-chaîne. SPLIT_PART extrait la n-ième partie d'une chaîne découpée par un délimiteur.
RETURNING
Retourne les lignes insérées, modifiées ou supprimées sans SELECT supplémentaire.
RIGHT JOIN
RIGHT JOIN garde toutes les lignes de droite. Souvent évitable : un LEFT JOIN inversé suffit. À connaître pour lire du SQL existant.
row_to_json et jsonb_each
Convertit une ligne en JSON ou décompose un objet JSON en paires clé-valeur.
STRING_AGG
STRING_AGG concatène des valeurs d'un groupe en une chaîne séparée par un délimiteur. Supporte ORDER BY à l'intérieur de l'agrégat.
SUBSTRING et extraction de texte
SUBSTRING extrait une partie d'une chaîne. LEFT/RIGHT extraient depuis les extrémités. STRPOS trouve la position d'un sous-texte.
SUM
SUM additionne des valeurs numériques. Souvent utilisé avec GROUP BY. Attention aux types et aux NULL.
TO_CHAR – formater une date en texte
TO_CHAR formate une date (ou un nombre) en chaîne de caractères selon un masque de format.
TO_DATE et TO_TIMESTAMP – parser une date
TO_DATE et TO_TIMESTAMP convertissent un texte en date ou timestamp selon un format donné.
to_jsonb, jsonb_build_object, jsonb_agg
to_jsonb convertit une valeur en JSONB. jsonb_build_object construit un objet JSON depuis des paires clé/valeur. jsonb_agg agrège des lignes en tableau JSON.
TRUNCATE TABLE
Vide une table entière rapidement, avec option de reset des séquences.
UNIQUE
UNIQUE empêche les doublons. Souvent utilisé sur email. Peut être multi-colonnes.
UPDATE
UPDATE modifie des lignes existantes. Sans WHERE, vous modifiez toute la table. Testez d’abord avec un SELECT.
absence de logique dupliquée
La logique dupliquée est source de bugs. Utiliser des CTE pour factoriser. Un calcul = un seul endroit.
agrégations conditionnelles
Une agrégation conditionnelle agrège selon une condition. Souvent via SUM(CASE WHEN ... THEN ... END). Permet plusieurs métriques en une requête.
Agrégats glissants : SUM() OVER, AVG() OVER
SUM, AVG, COUNT, MIN, MAX peuvent s'utiliser comme fonctions de fenêtrage pour calculer des cumuls progressifs ou des moyennes glissantes sur N lignes.
anti-join
Un anti-join sert à trouver les lignes sans correspondance. Souvent : LEFT JOIN + WHERE ... IS NULL. Utile pour identifier des clients inactifs.
cardinalités
La cardinalité décrit la relation entre tables.1-N : categories → products1-N : orders → order_itemsComprendre ça évite les duplications.
CREATE FUNCTION et CREATE PROCEDURE
Crée des fonctions et procédures stockées réutilisables dans la base de données.
CTE (WITH)
Une CTE (WITH) est une table temporaire lisible. Améliore la clarté. Permet de découper en étapes.
DENSE_RANK
DENSE_RANK classe avec ex-aequo sans trous. Deux valeurs égales → même rang. Le rang suivant ne saute pas.
Expressions régulières : ~, REGEXP_REPLACE, REGEXP_MATCHES
~ et ~* filtrent via des expressions régulières. REGEXP_REPLACE remplace des occurrences correspondant à un pattern. REGEXP_MATCHES retourne les groupes capturés.
FIRST_VALUE, LAST_VALUE, NTH_VALUE
FIRST_VALUE, LAST_VALUE et NTH_VALUE accèdent à des positions spécifiques dans la fenêtre. Attention au cadre par défaut de LAST_VALUE.
Frames de fenêtre : ROWS BETWEEN / RANGE BETWEEN
Définit précisément le sous-ensemble de lignes pour une fonction de fenêtrage.
GRANT
GRANT donne des droits. Dépend de la base (PostgreSQL, etc.). Important en production.
jointures multiples
Une jointure multiple chaîne plusieurs tables. Utilisez des alias courts. Vérifiez la granularité (risque de doublons).
jsonb_array_elements, jsonb_object_keys
jsonb_array_elements décompose un tableau JSON en lignes. jsonb_object_keys liste les clés d'un objet JSON. Fonctions set-returning très utiles pour explorer du JSON.
jsonb_set et jsonb_insert
jsonb_set modifie une valeur dans un document JSONB. jsonb_insert insère dans un tableau JSONB. Le chemin est spécifié sous forme de tableau text[].
LAG
LAG lit la valeur précédente dans un ordre. Très utile pour comparer dans le temps. Fonctionne avec OVER (PARTITION BY ... ORDER BY ...).
LEAD
LEAD lit la valeur suivante dans un ordre. Utile pour anticiper un événement suivant. Même logique que LAG, mais vers l’avant.
logique conditionnelle avancée
La logique conditionnelle se fait souvent avec CASE. Permet de créer des catégories. Utile pour du reporting.
MERGE / UPSERT
Un UPSERT insère ou met à jour selon l’existence. Très pratique sur des clés uniques (ex: customers. email). Syntaxe dépend du SGBD.
normalisation
La normalisation réduit la redondance. On sépare les entités (clients, commandes, items). On relie via des clés.
NTILE
NTILE(n) répartit les lignes en n groupes égaux selon un ordre. Idéal pour créer des quartiles, déciles ou centiles.
ORDER BY (fenêtrage)
Dans une window function, ORDER BY définit l’ordre du calcul. Ce n’est pas le tri final (même si lié). Impacte LAG, LEAD, ranking.
OVER
OVER définit la fenêtre d’une fonction analytique. Sans OVER, pas de window function. Vous définissez l’ordre et/ou le partitionnement.
PARTITION BY
PARTITION BY découpe le résultat en groupes dans une fenêtre. Chaque groupe est traité séparément. Très utile par customer_id ou category_id.
PERCENTILE_CONT et PERCENTILE_DISC
PERCENTILE_CONT calcule un percentile interpolé, PERCENTILE_DISC retourne la valeur existante la plus proche. La fraction 0.5 donne la médiane.
permissions
Les permissions contrôlent les actions autorisées. SELECT, INSERT, UPDATE, DELETEPeuvent être par table.
PRIMARY KEY / FOREIGN KEY (rappel)
PK/FK définissent le modèle relationnel. PK = identifiant. FK = lien. Sans ça, les JOIN deviennent risqués.
principe du moindre privilège
Le moindre privilège = donner le minimum nécessaire. Réduit le risque. Limite les erreurs humaines.
RANK
RANK classe avec des ex-aequo (trous possibles). Deux valeurs égales → même rang. Le rang suivant peut sauter un numéro.
requête robuste face aux données imparfaites
Les données réelles sont imparfaites : NULL, doublons, valeurs aberrantes. Anticiper les cas limites avec COALESCE, NULLIF.
requêtes imbriquées
Une requête imbriquée empile des niveaux. Utile pour : calculer puis filtrer puis classer. Une CTE est souvent plus lisible.
REVOKE
REVOKE retire des droits. Complément de GRANT. Utile quand un accès n’est plus nécessaire.
rôles
Un rôle regroupe des permissions. Plus simple à gérer. Moins d’erreurs.
rollback
ROLLBACK annule les changements d’une transaction non validée. Utile en test. Évite des modifications accidentelles.
Row Level Security (RLS)
Restreint l'accès aux lignes d'une table selon l'utilisateur, sans changer les requêtes.
ROW_NUMBER
ROW_NUMBER numérote les lignes dans une fenêtre. Utile pour prendre le « premier » élément d’un groupe. Nécessite OVER(...).
séparation des environnements
Séparer dev / staging / prod évite les accidents. Pas d’écriture en prod pour tout le monde. Accès contrôlés par rôle.
simplicité et sobriété de la solution
La simplicité est une qualité. Pas de sous-requête quand un JOIN suffit. Le code le plus simple est le plus maintenable.
sous-requêtes
Une sous-requête est une requête dans une autre. Elle peut être dans WHERE, FROM ou SELECT. Utile si vous voulez filtrer par une agrégation.
STDDEV et VARIANCE
STDDEV mesure l'écart-type et VARIANCE la variance d'un ensemble de valeurs. Les variantes _POP calculent sur la population complète, les variantes _SAMP sur un échantillon.
top N par groupe
Le top N par groupe consiste à garder les N meilleures lignes par partition. Solution classique : ROW_NUMBER puis filtre rn <= N.
traçabilité de la logique métier
La traçabilité permet de comprendre le pourquoi. Noms de CTE explicites. Commentaires orientés métier.
transactions
Une transaction regroupe plusieurs actions en une unité. Soit tout passe, soit rien. Utile pour maintenir la cohérence.
UNION / UNION ALL
UNION combine deux résultats en supprimant les doublons. UNION ALL combine sans supprimer les doublons. Les colonnes doivent être compatibles.
VACUUM et ANALYZE
Récupère l'espace et met à jour les statistiques pour maintenir les performances.
Vues matérialisées
Une MATERIALIZED VIEW stocke physiquement le résultat d'une requête. Plus rapide mais nécessite REFRESH pour mettre à jour.
WITH dans les DML (CTE modifiantes)
Utilisez des CTE avec INSERT/UPDATE/DELETE pour des modifications complexes atomiques.
CTE récursif (WITH RECURSIVE)
WITH RECURSIVE permet de faire des requêtes récursives, utiles pour traverser des hiérarchies.