Dan Levy

Quiz : Postgres en profondeur, partie 2

SQL d'acier ou SQL en mousse ?

Partie 2 sur 2. Retour à la partie 1

Bienvenue dans la deuxième partie de mon quiz PostgreSQL approfondi !

Ce quiz explore un mélange de fonctionnalités familières et méconnues de PostgreSQL, ainsi que ses pièges classiques : JOIN, NULL, SQL ANSI, transactions, fonctionnement interne, indexation, échantillonnage de données… et plus encore !

Bonne chance ! 🍀

1. 

Échauffement : Jointures

Laquelle de ces expressions n’est pas ❌ une expression JOIN valide dans PostgreSQL ?

Vous connaissiez le CROSS JOIN, n’est-ce pas ?

Est-ce que le JOIN ALL vous a fait penser à une autre opération, UNION ALL ?

La réponse ici est JOIN ALL ❌.

Les types de JOIN corrects sont :

  • INNER JOIN (le JOIN par défaut)
  • LEFT JOIN (ou LEFT OUTER JOIN)
  • RIGHT JOIN (ou RIGHT OUTER JOIN)
  • FULL JOIN (ou FULL OUTER JOIN)
  • CROSS JOIN (le produit cartésien, à moins qu’une clause WHERE ultérieure ne le filtre)

2. 

Colonnes d'identité

Quelle est la syntaxe (norme SQL Standard) pour une colonne IDENTITY à auto-incrémentation dans PostgreSQL (v10+) ?

Bien que SERIAL soit encore couramment utilisé, la méthode conforme au SQL Standard est :

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • id INT IDENTITY(1,1) est la syntaxe SQL Server.
  • id INT AUTO_INCREMENT est la syntaxe MySQL.
  • id INTEGER AUTO_INCREMENT PRIMARY KEY est la syntaxe MySQL.

Cela présente plusieurs avantages par rapport à SERIAL :

  • C’est conforme au standard SQL
  • Meilleure gestion de la propriété des séquences
  • Sémantique plus claire concernant les insertions manuelles
  • Fonctionne mieux avec la réplication

SERIAL n’est en réalité qu’un raccourci pour créer une séquence et définir une valeur par défaut, ce qui peut entraîner des problèmes lors des sauvegardes et restaurations.

3. 

ANSI SQL

Quelle clause PostgreSQL documente-t-il comme une extension au standard SQL ?

PostgreSQL documente sa clause RETURNING comme une extension au standard SQL. ILIKE est une autre extension PostgreSQL.

Ne classez pas toute la prise en charge des tableaux et de JSON comme non standard : SQL normalise des fonctionnalités dans ces domaines, tandis que PostgreSQL fournit aussi ses propres types et opérateurs, comme jsonb.

Différentes éditions successives du standard SQL ont introduit notamment :

  • WITH (expressions de table communes).
  • LIKE pour la recherche de motifs.
  • Les jointures LATERAL.
  • Les fonctions de fenêtrage.

4. 

Transactions

À part l’option “Dépend des effets de bord”, que se passe-t-il après une erreur ?

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

Lorsqu’une erreur survient ici :

  • La transaction entière est annulée (rollback)
  • Aucune modification n’est validée
  • La transaction est marquée comme échouée

Pour effectuer une validation partielle :

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

5. 

Internes SQL

Quelle condition ON PostgreSQL peut-il utiliser pour un HASH JOIN ?

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

Une jointure par hachage (hash join) est possible avec une condition d’égalité (=) dans la clause ON.

  • = est la forme de prédicat clé pour les hash joins
  • > et >= ne sont pas des prédicats de hash join
  • HASH JOIN n’est pas une syntaxe valide dans PostgreSQL

Le planificateur peut tout de même choisir une boucle imbriquée (nested loop) ou une jointure par fusion (merge join) selon la taille des tables, les statistiques, les index et les paramètres de mémoire.

6. 

Indexation

Quel index est le plus adapté pour cette requête ?

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

Ce qui est surprenant ici, c’est qu’un index multi-colonnes peut être utilisé même si l’ordre des colonnes dans le texte de la requête ne correspond pas à la définition de l’index.

Note : Avec les index B-tree, la colonne de tête est cruciale. Comme cette requête filtre par grade_level et last_name, un index commençant par grade_level suivi de last_name est un excellent choix.

Plus SQL peut sélectionner rapidement un nombre restreint de lignes, meilleure est la performance.

Par exemple, si vous avez une table task et que vous savez que status est plus sélectif que created_at, vous pourriez choisir un index commençant par status pour une requête comme status = 'done' AND created_at > '2024-01-01'. L’ordre des conditions dans la clause WHERE n’est pas ce qui importe ; c’est l’ordre des colonnes de l’index et la forme des prédicats qui comptent.

Un index B+Tree peut être utilisé pour :

  • La colonne de tête, ou un préfixe de colonnes situé le plus à gauche.
  • Des opérateurs tels que =, >, <, BETWEEN, et IN, selon la structure de la requête.
  • La correspondance de préfixe, LIKE 'prefix%'.

Merci à u/mwdb2 pour avoir signalé une erreur précédente dans cette explication !

7. 

Guillemets 1

Laquelle de ces syntaxes est correcte pour les noms de colonnes sensibles à la casse ?

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

Dans PostgreSQL :

  • Les guillemets doubles (") sont destinés aux identifiants (noms de tables, noms de colonnes)
  • Les guillemets simples (') sont destinés aux chaînes de caractères (littéraux)

Bonnes pratiques :

  • Évitez de mélanger majuscules et minuscules ou d’utiliser des espaces dans les noms
  • Utilisez le snake_case pour les identifiants
  • N’utilisez des identifiants entre guillemets que lorsque c’est absolument nécessaire

8. 

Guillemets 2

Comment spécifier un nom de colonne contenant un point ?

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

PostgreSQL utilise des doubles guillemets pour délimiter les identifiants :

  • Doubles guillemets "..." pour les identifiants (noms de colonnes, noms de tables)
  • Guillemets simples '...' pour les chaînes de caractères littérales
  • La forme first.name est interprétée comme nom_table.nom_colonne

Sans guillemets, un point dans un nom de colonne serait interprété comme un séparateur schéma/table ! De plus, tous les identifiants non entourés de guillemets sont convertis en minuscules par défaut.

Afin de limiter les mauvaises surprises, il est préférable d’utiliser le snake_case et d’éviter les caractères spéciaux.

9. 

Échantillonnage de données

Quelle est la syntaxe valide pour échantillonner aléatoirement 10 % des étudiants ?

Celui-ci est délicat ! Il comporte environ 6 pièges, dont certains incluent :

  1. Description de haut niveau + fragments de syntaxe. Teste comment vous adaptez et assemblez des informations incomplètes.
  2. Nécessite une connaissance familière de la syntaxe TABLESAMPLE, car aucune des options TABLESAMPLE n’utilise la syntaxe correcte ! (Sinistre ! Je sais ! Continuez, je crois en vous !!! ❤️)
  3. ROW_NUMBER() OVER (ORDER BY RAND()) et ORDER BY RANDOM() > 10 sont d’autres fausses pistes. Ils ont l’air corrects, mais ne le sont pas. RAND() n’existe pas. Et RANDOM() > 10 est une expression booléenne qui sera toujours fausse puisque RANDOM() est toujours dans la plage 0.0-1.0. Autre problème, ORDER BY RANDOM() > 10 trierait également sur une valeur booléenne, pas sur un nombre aléatoire. Mais ce > 10 donnait l’impression qu’il pouvait s’agir d’un pourcentage, n’est-ce pas ?
  4. BERNOULLI est valide, cependant SAMPLETABLE ne l’est pas.
  5. WHERE RANDOM() >= 0.1 est un piège ! RANDOM() renvoie une valeur dans la plage 0.0 <= x < 1.0, donc >= 0.1 renverrait environ 90 % des lignes, pas 10 %.

En ce qui concerne la méthode TABLESAMPLE, PostgreSQL prend en charge deux méthodes d’échantillonnage :

  • BERNOULLI : Chaque ligne a une probabilité égale
  • SYSTEM : Échantillonnage au niveau des blocs (plus rapide mais moins aléatoire)

Pour en savoir plus sur l’échantillonnage, consultez cet article de Render.

Exemple :

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);


-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

Il convient également de mentionner que TABLESAMPLE a été signalé comme n’étant pas très aléatoire. Si vous avez besoin d’un échantillon de taille fixe plus aléatoire, envisagez d’utiliser ORDER BY RANDOM() LIMIT 10. Mais attention, cela renvoie 10 lignes, pas 10 %, et peut être lent sur de grandes tables.

Il existe également des extensions tierces qui fournissent de meilleures méthodes d’échantillonnage aléatoire, mais cela sort du cadre de ce quiz ! 😅

10. 

Indexation

Que fait cette incantation ?

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

Cette astuce s’appelle un Partial Index (index partiel).

Ils sont utiles pour réduire l’utilisation du disque et accélérer les requêtes lorsqu’un seul sous-ensemble de lignes est généralement interrogé.

Le planificateur peut envisager cet index s’il peut prouver que les conditions de la requête impliquent status = 'active'. Une correspondance textuelle exacte n’est pas toujours nécessaire, mais ces vérifications sont limitées : une condition paramétrée peut ne pas établir le prédicat lors de la planification. Être éligible ne garantit pas que l’index sera choisi.

Rappel : Postgres ne supporte pas les ‘query hints’ (indices de requête) comme d’autres RDBMS. Le planificateur de requête décidera quand utiliser le ou les index appropriés.

11. 

Comportement de NULL

Combien de lignes ce code retournera-t-il ?

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

D’accord, c’était un peu piégeux. Le piège est que x = NULL ne correspondra jamais à aucune ligne, et retournera TOUJOURS une seule ligne avec un compte de 0.

Puisque NULL est une valeur inconnue, elle ne peut être comparée à rien, pas même à elle-même.

La bonne façon de vérifier les valeurs NULL est d’utiliser IS NULL.

Exemple :

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)


postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

12. 

Performance

Que fait EXPLAIN ANALYZE pour les instructions UPDATE ?

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

EXPLAIN ANALYZE exécute réellement la requête ! Soyez prudent lors de l’analyse d’une opération d’écriture, plus précisément les instructions UPDATE, DELETE et INSERT. Vous devrez peut-être effectuer des opérations de nettoyage après chaque EXPLAIN ANALYZE pour obtenir des résultats reproductibles.

Meilleures pratiques :

  • Utilisez EXPLAIN (sans ANALYZE) pour UPDATE/DELETE/INSERT
  • Enveloppez les modifications dans des transactions :
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Bien joué ! Vous avez exploré en profondeur plusieurs facettes de PostgreSQL ! 🐘

J’espère que vous avez appris quelque chose de nouveau, ou au moins que vous avez obtenu un score digne de ce nom ! 🏆

Envie de plus de sensations fortes ? Allez voir ma Collection de Quiz pour un plaisir* sans fin !

Loading comments...