Sujet : sql
-
Recherche plein texte dans PostgreSQL avec tsvector
PostgreSQL transforme le texte en un tsvector de lexèmes normalisés selon une configuration linguistique, le confronte à un tsquery, classe les correspondances avec ts_rank et l'indexe avec GIN ; il gère la racinisation et les mots vides, mais pas les fautes de frappe ni les synonymes sans configuration supplémentaire.
-
Stocker des données dérivées dans PostgreSQL : colonnes générées contre vues matérialisées
Une colonne générée dérive une valeur par ligne à partir de cette seule ligne, soit virtuelle (calculée à la lecture), soit stockée (calculée à l'écriture), et ne peut utiliser que des expressions immuables ; une vue matérialisée stocke le résultat d'une requête quelconque et n'est fraîche qu'à hauteur de son dernier REFRESH. Utiliser des colonnes générées stockées pour la normalisation par ligne qui doit être indexée, des vues matérialisées pour les agrégats coûteux qui peuvent accuser un retard, et une table de synthèse maintenue lorsque ni l'une ni l'autre ne convient.
-
Upsert, row limits and identifier quoting across PostgreSQL, MySQL, SQLite and SQL Server
PostgreSQL's ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, SQLite's PostgreSQL-style ON CONFLICT and SQL Server's MERGE are three families of upsert syntax, LIMIT/OFFSET has at least three shapes, and a quoted identifier means double quotes, backticks or square brackets depending on the engine.
-
NULL en SQL : logique à trois valeurs et ses pièges
NULL signifie inconnu, si bien que les comparaisons avec NULL donnent inconnu plutôt que vrai ou faux ; les filtres WHERE écartent les lignes inconnues, NOT IN avec un NULL ne trouve plus aucune correspondance, et les agrégats ignorent les NULL. Utiliser IS NULL, IS DISTINCT FROM et COALESCE délibérément.
-
Expressions de table commune et requêtes récursives avec WITH
WITH nomme une sous-requête pour le reste d'une instruction ; WITH RECURSIVE évalue un terme non récursif, puis répète un terme récursif jusqu'à ce qu'il ne produise plus de nouvelle ligne, ce qui permet de parcourir des arbres et des graphes de profondeur quelconque en une seule requête. Les CTE à usage unique et sans effet de bord sont repliées dans la requête externe sauf si MATERIALIZED est écrit, et les clauses SEARCH et CYCLE gèrent l'ordre et les boucles.
-
Statistiques du planificateur dans PostgreSQL : cibles de statistiques, colonnes corrélées et erreurs d'estimation
Le planificateur estime le nombre de lignes à partir de statistiques par colonne échantillonnées par ANALYZE (valeurs les plus fréquentes, histogrammes, nombres de valeurs distinctes) et suppose les colonnes indépendantes ; les erreurs d'estimation viennent de statistiques périmées, de colonnes asymétriques avec trop peu d'entrées, de colonnes corrélées et d'expressions. Relever la cible de statistiques sur des colonnes précises, créer des statistiques étendues pour les colonnes corrélées ou les expressions, relancer ANALYZE, et comparer les lignes estimées aux lignes réelles.
-
Lire un plan de requête PostgreSQL avec EXPLAIN ANALYZE
EXPLAIN affiche l'arbre choisi par le planificateur avec des coûts estimés ; EXPLAIN ANALYZE exécute la requête et ajoute les temps et les nombres de lignes réels. Comparer les lignes estimées aux lignes réelles, repérer le nœud au temps réel le plus élevé, et vérifier la présence de parcours séquentiels sur de grandes tables ainsi que de jointures mal estimées.
-
NULL en SQL : la logique à trois valeurs et ses pièges
NULL signifie inconnu ; c'est pourquoi toute comparaison avec NULL ne donne ni vrai ni faux, mais inconnu. WHERE rejette les lignes dont le résultat est inconnu, NOT IN avec un NULL ne sélectionne rien, les fonctions d'agrégation ignorent NULL, et une contrainte d'unicité autorise plusieurs NULL. Utiliser IS NULL, IS DISTINCT FROM et COALESCE de façon délibérée.
-
Les points de sauvegarde et l'état de transaction avortée dans PostgreSQL
Après toute erreur survenue à l'intérieur d'un bloc de transaction, PostgreSQL rejette toute commande ultérieure tant que le bloc n'a pas été annulé (rollback) ; un point de sauvegarde posé avant une instruction risquée permet à ROLLBACK TO SAVEPOINT de n'annuler que cette partie et de poursuivre. Utiliser les points de sauvegarde délibérément et avec parcimonie, et faire en sorte que les gestionnaires d'erreur effectuent un rollback plutôt que de relancer la même instruction sur la même connexion.
-
Pagination par curseur plutôt que par décalage : des pages stables face aux modifications
La pagination par décalage (offset) oblige quand même la base de données à calculer toutes les lignes sautées et déplace les pages dès qu'une insertion ou une suppression survient entre-temps ; la pagination par curseur ou par ensemble de clés (keyset) demande « les 20 suivants après la clé X », exploite l'index et fournit chaque ligne exactement une fois. Elle exige un tri unique en contrepartie de l'abandon des numéros de page.
-
Conventions de schéma pour une nouvelle base PostgreSQL : noms, identifiants, horodatages et texte
Décider d'une poignée de conventions avant la première migration : des noms en snake_case minuscule qui n'ont jamais besoin d'être mis entre guillemets, une seule stratégie d'identifiant appliquée partout, timestamptz pour chaque instant avec created_at sur chaque table, text au lieu de varchar(n), et NOT NULL et clés étrangères explicites ; les consigner par écrit afin que chaque migration ultérieure les respecte.
-
Opérations en masse plutôt que des boucles ligne par ligne : allers-retours, transactions et COPY
Une boucle qui envoie une instruction par ligne paie un aller-retour, une analyse syntaxique et souvent une validation (commit) par ligne ; une opération en masse envoie l'ensemble en une seule instruction, un seul flux ou une seule transaction. La documentation de PostgreSQL elle-même recommande de valider une seule fois, d'utiliser COPY pour les chargements et de construire les index après le chargement ; les bibliothèques clientes proposent executemany et copy pour la même raison.
-
Vérifications de qualité des données : fraîcheur, volume, valeurs nulles et unicité comme socle minimal
Quatre vérifications peu coûteuses détectent la plupart des chargements défaillants : la source est-elle assez fraîche, un nombre de lignes plausible est-il arrivé, les clés et les indicateurs sont-ils renseignés, la granularité déclarée est-elle unique ? Formuler chaque vérification comme une requête qui renvoie les lignes fautives, l'exécuter après le chargement et avant la publication, et séparer l'avertissement du blocage.
-
Quelle taille de pool de connexions par rapport au nombre de cœurs les équipes ont-elles retenue pour un serveur PostgreSQL, et quelle mesure les a poussées à la changer ?
Question ouverte : le wiki de PostgreSQL propose une formule de dimensionnement des connexions actives fondée sur le nombre de cœurs et le nombre effectif de disques (spindles), et la valeur par défaut de max_connections sur le serveur est typiquement de 100 ; quelles tailles de pool les équipes ont-elles réellement obtenues après réglage, à quelle distance étaient-elles de la formule, et quelles preuves (attentes de verrous, mise en file d'attente au niveau du pooler, saturation du CPU, latence) ont motivé chaque changement ?
-
Suppression logique (soft delete) contre tables d'archivage
Une suppression logique conserve la ligne avec un marqueur deleted_at, si bien que chaque requête doit la filtrer et chaque contrainte d'unicité doit devenir un index partiel ; une table d'archivage déplace la ligne hors de la table active, de sorte que les requêtes actives restent simples et que l'historique réside en un seul endroit. Le choix dépend de qui lit les données supprimées et à quelle fréquence, et doit être porté par les contraintes et les vues plutôt que par chaque requête.
-
Écrire un upsert avec INSERT ... ON CONFLICT
INSERT ... ON CONFLICT (colonnes) DO UPDATE SET ... transforme l'insertion-ou-mise-à-jour en une seule instruction atomique pilotée par un index unique : nommer la cible du conflit, ne mettre à jour que les colonnes qui doivent changer, utiliser EXCLUDED pour la ligne proposée, et ajouter un WHERE qui évite les mises à jour sans effet. MERGE couvre les cas à branches multiples qu'ON CONFLICT ne peut pas exprimer.
-
Requêtes N+1 : les détecter en les comptant et les corriger en les regroupant
Charger N lignes parentes puis accéder à une relation chargée paresseusement sur chacune émet N+1 requêtes ; le coût croît avec les données, non avec le code, si bien que cela passe les tests sur de petits jeux de données. Le détecter en vérifiant le nombre de requêtes par appel, faire lever une exception sur les chargements paresseux non voulus, et le corriger avec des jointures pour les relations vers-un et des secondes requêtes groupées par IN pour les collections.
-
Normaliser jusqu'à la troisième forme normale et choisir quand dénormaliser
Les formes normales éliminent les groupes répétitifs et les faits stockés à plus d'un endroit ; la troisième forme normale signifie que chaque colonne non clé dépend de la clé et de rien d'autre. Normaliser par défaut les données transactionnelles, et ne dénormaliser que dans des colonnes dérivées, nommées comme telles, dont la source de vérité reste normalisée.
-
Contraintes déclaratives dans PostgreSQL : CHECK, UNIQUE et clés étrangères avec ON DELETE
Les contraintes font en sorte que la base de données rejette les états invalides pour tout auteur d'écriture, pas seulement pour l'application : CHECK pour des règles par ligne, NOT NULL pour les valeurs requises, UNIQUE pour l'identité, et des clés étrangères avec une action ON DELETE explicite. Choisir l'action référentielle et indexer la colonne référençante sont les deux décisions le plus souvent omises.
-
Repérer les requêtes les plus coûteuses avec pg_stat_statements
pg_stat_statements agrège le nombre d'appels, le temps d'exécution et le nombre de blocs par requête normalisée, pour l'ensemble du serveur. Le charger via shared_preload_libraries, classer par total_exec_time puis séparément par calls, comparer la moyenne au maximum, lire les colonnes de blocs, puis soumettre les requêtes en tête de liste à EXPLAIN ANALYZE et comparer l'avant et l'après par queryid.
Lisible par machine : JSON