Dans un billet précédent, je mentionnais les différences entre objets larges et colonnes bytea pour stocker des données binaires, via un tableau d’une quinzaine de points de comparaison. Ici, détaillons un peu une de ces différences: les verrouillages induits par les opérations.

Effacement en masse

Pour le bytea, ça se passe comme avec les autres types de données de base. Les verrous au niveau ligne sont matérialisés dans les entêtes de ces lignes sur disque, et non pas dans une structure à part (c’est pourquoi ils ne sont pas visibles dans la vue pg_locks). En conséquence, il n’y pas de limite au nombre de lignes pouvant être effacées dans une transaction.

Mais du côté des objets larges, c’est différent. Une suppression d’objet via lo_unlink() est conceptuellement équivalent à un DROP, et prend un verrou en mémoire partagée, dans une zone qui est pré-allouée au démarrage du serveur. De ce fait, ces verrous sont limités en nombre, et si on dépasse la limite, on obtient une erreur de ce type:

ERROR: out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

La documentation nous dit à propos de cette limite:

La table des verrous partagés trace les verrous sur max_locks_per_transaction * (max_connections + max_prepared_transactions) objets (c’est-à-dire des tables) ; de ce fait, au maximum ce nombre d’objets distincts peuvent être verrouillés simultanément.

les valeurs par défaut de ces paramètres étant (pour la version 10):

Ca nous donne 4096 verrous partagés par défaut. Même si on peut booster ces valeurs dans postgresql.conf, le maximum sera donc d’un ordre de grandeur peu élevé, disons quelques dizaines de milliers, ce qui peut être faible pour des opérations en masse sur ces objets. Et n’oublions pas qu’un verrou n’est libérable qu’à la fin de la transaction, et que la consommation de ces ressources affecte toutes les autres transactions de l’instance qui pourraient en avoir besoin pour autre chose.

Une parenthèse au passage, ce nombre d’objets verrouillables n’est pas un maximum strict, dans le sens où c’est une estimation basse. Dans cette question de l’an dernier à la mailing-liste: Maximum number of exclusive locks, j’avais demandé pourquoi en supprimant N objets larges, le maximum constaté pouvait être plus de deux fois plus grand que celui de la formule, avec un exemple à 37132 verrous prenables au lieu des 17920=(512*30+5) attendus. L’explication est qu’à l’intérieur de cette structure en mémoire partagée, il y a des sous-ensembles différents. Le nombre de verrous exclusifs réellement disponibles à tout moment dépend de l’attente ou non des verrous par d’autres transactions.

Pour être complet, disons aussi que le changement de propriétaire, via ALTER LARGE OBJECT nécessite aussi ce type de verrou en mémoire partagée, et qu’en revanche le GRANT … ON LARGE OBJECT … pour attribuer des permissions n’en a pas besoin.

Ecriture en simultané

Ce n’est pas ce qu’il y a de plus commun, mais on peut imaginer que deux transactions concurrentes veulent mettre à jour le même contenu binaire.

Dans le cas du bytea, la transaction arrivant en second est bloquée dans tous les cas sur le verrou au niveau ligne posé par un UPDATE qui précède. Et physiquement, l’intégralité du contenu va être remplacé, y compris si certains segments TOAST sont identiques entre l’ancien et le nouveau contenu.

Dans le cas de l’objet large, c’est l’inverse. Seul les segments concernés par le changement de valeur vont être remplacés par la fonction lowrite(). Rappelons la structure de pg_largeobject:

=# \d pg_largeobject
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 loid    | oid     |                 | not null  | 
 pageno  | integer |                 | not null  | 
 data    | bytea   |                 | not null  | 
Index :
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

Chaque entrée de cette table représente le segment numéro pageno de l’objet large loid avec son contenu data d’une longueur maximale de 2048 octets (LOBLKSIZE).

Deux écritures concurrentes dans le même objet large vont se gêner seulement si elles modifient les mêmes segments.

Conclusion

Même si le stockage TOAST des colonnes bytea et les objets larges ont des structures très similaires, en pratique leurs stratégies de verrouillage sont quasiment opposées. L’effacement d’un bytea (DELETE) est une opération à verrouillage léger alors que l’effacement d’un objet large est plutôt comparable à un DROP TABLE. En revanche, la nature segmentée de l’objet large permet des modifications plus ciblées et légères en verrouillage, alors qu’une modification de colonne bytea induit un verrouillage (et remplacement) intégral de toute la ligne qui la porte.