Quiz : Postgres en profondeur, partie 2

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)

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.

Exemples d’extensions PostgreSQL au standard ANSI SQL :

• La clause RETURNING.
• ILIKE pour la correspondance insensible à la casse.
• Les types de tableaux (arrays) et leurs opérations.
• Les types JSON/JSONB.

Le standard ANSI SQL original inclut :

• WITH (Common Table Expressions).
• LIKE pour la recherche de motifs.
• Les jointures LATERAL.
• Les fonctions de fenêtrage (Window functions).

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;

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.

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 !

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

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.

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 ! 😅

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é.

Postgres n’utilisera l’index que si la condition status = 'active' est présente dans la requête.

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.

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)

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;