PostgreSQL Antipatterns : combattre les hordes de « morts »

Les particularités des mécanismes internes de PostgreSQL lui permettent d'être très rapide dans certaines situations et « pas très rapide » dans d'autres. Aujourd'hui, nous allons nous concentrer sur un exemple classique de conflit entre le fonctionnement d'un SGBD et ce que le développeur en fait - Principes UPDATE et MVCC.

Brève histoire de excellent article:

Lorsqu'une ligne est modifiée par une commande UPDATE, deux opérations sont en réalité effectuées : DELETE et INSERT. DANS version actuelle de la chaîne xmax est égal au numéro de la transaction qui a effectué la MISE À JOUR. Puis il est créé nouvelle version la même ligne ; sa valeur xmin coïncide avec la valeur xmax de la version précédente.

Quelque temps après que cette transaction soit terminée, l'ancienne ou la nouvelle version, selon COMMIT/ROOLBACK, sera reconnu "mort" (tuples morts) en passant VACUUM selon le tableau et dégagé.

Mais cela n'arrivera pas tout de suite, mais les problèmes avec les « morts » peuvent être acquis très rapidement - avec des problèmes répétés ou répétés. mise à jour massive des dossiers dans une grande table, et un peu plus tard vous rencontrerez la même situation VACUUM ne pourra pas aider.

#1 : J’aime le déplacer

Supposons que votre méthode travaille sur la logique métier et qu'elle réalise soudain qu'il serait nécessaire de mettre à jour le champ X dans un enregistrement :

UPDATE tbl SET X = <newX> WHERE pk = $1;

Puis, au fur et à mesure de l’exécution, il s’avère que le champ Y doit également être mis à jour :

UPDATE tbl SET Y = <newY> WHERE pk = $1;

... et puis aussi Z - pourquoi perdre du temps avec des bagatelles ?

UPDATE tbl SET Z = <newZ> WHERE pk = $1;

Combien de versions de cet enregistrement avons-nous maintenant dans la base de données ? Oui, 4 pièces ! Parmi ceux-ci, un est pertinent et trois devront être nettoyés après vous par [auto]VACUUM.

Ne procédez pas de cette façon ! Utiliser mise à jour de tous les champs en une seule requête — presque toujours, la logique de la méthode peut être modifiée comme ceci :

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

#2 : L'utilisation EST DISTINCTE DE, Luke !

Alors tu voulais toujours mettre à jour de très nombreux enregistrements dans une table (lors de l'utilisation d'un script ou d'un convertisseur par exemple). Et quelque chose comme ceci apparaît dans le script :

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

Une demande sous cette forme se produit assez souvent et presque toujours non pas pour remplir un nouveau champ vide, mais pour corriger certaines erreurs dans les données. En même temps, elle-même l'exactitude des données existantes n'est pas du tout prise en compte - mais en vain! Autrement dit, le disque est réécrit, même s'il contenait exactement ce qui était recherché - mais pourquoi ? Réparons-le :

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

Beaucoup de gens ne connaissent pas l'existence d'un opérateur aussi merveilleux, voici donc un aide-mémoire sur IS DISTINCT FROM et d'autres opérateurs logiques pour aider :

... et un peu sur les opérations sur complexes ROW()-expressions:

#3 : Je reconnais ma chérie en... bloquant

Courir deux processus parallèles identiques, dont chacun essaie de marquer l'entrée comme étant « en cours » :

UPDATE tbl SET processing = TRUE WHERE pk = $1;

Même si ces processus font réellement des choses indépendamment les uns des autres, mais au sein du même identifiant, le deuxième client sera « verrouillé » sur cette requête jusqu'à ce que la première transaction soit terminée.

Décision n 1: la tâche est réduite à la précédente

Ajoutons-le à nouveau IS DISTINCT FROM:

UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

Sous cette forme, la deuxième demande ne changera tout simplement rien dans la base de données, tout est déjà comme il se doit - par conséquent, le blocage ne se produira pas. Ensuite, nous traitons le fait de « ne pas trouver » l'enregistrement dans l'algorithme appliqué.

Décision n 2: écluses consultatives

Un grand sujet pour un article séparé, dans lequel vous pouvez lire sur modalités d'application et « râteau » du blocage des recommandations.

Décision n 3: appels stupides

Mais c'est exactement ce qui devrait t'arriver travail simultané avec le même disque? Ou avez-vous gâché les algorithmes permettant d'appeler la logique métier côté client, par exemple ? Et si vous y réfléchissiez ?..

Source: habr.com