Installation de l’extension lo Utilisation de la fonction de déclenchement lo_manage Suppression d’objets volumineux orphelins à l’aide de vacuumlo Comprendre le fonctionnement d’vacuumlo Amélioration des performances de vacuumlo Considérations relatives aux objets volumineux

Gestion des objets volumineux avec le module lo

Le module lo (extension) est destiné aux utilisateurs et aux développeurs de base de données qui travaillent avec des bases de données PostgreSQL via des pilotes JDBC ou ODBC. JDBC et ODBC s’attendent à ce que la base de données gère la suppression des objets volumineux lorsque les références à ces derniers changent. Cependant, PostgreSQL ne fonctionne pas de cette façon. PostgreSQL ne suppose pas qu’un objet doit être supprimé lorsque sa référence change. Les objets restent donc sur le disque, sans référence. L’extension lo inclut une fonction qui se déclenche en cas de changement de référence afin de supprimer des objets si nécessaire.

Astuce

Pour déterminer si votre base de données peut bénéficier de l’extension lo, utilisez l’utilitaire vacuumlo pour vérifier la présence d’objets volumineux orphelins. Pour obtenir le nombre d’objets volumineux orphelins sans effectuer aucune action, exécutez l’utilitaire avec l’option -n (no-op). Pour savoir comment procéder, consultez vacuumlo utility.

Le module lo est disponible pour Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 et versions mineures ultérieures.

Pour installer le module (extension), vous avez besoin de privilèges rds_superuser. L’installation de l’extension lo ajoute ce qui suit à votre base de données :

lo : type de données d’objet volumineux (lo) que vous pouvez utiliser pour les objets volumineux binaires (BLOB) et d’autres objets volumineux. Le type de données lo est un domaine du type de données oid. En d’autres termes, il s’agit d’un identifiant d’objet avec des contraintes facultatives. Pour en savoir plus, consultez Identifiants d’objet dans la documentation PostgreSQL. En termes simples, vous pouvez utiliser le type de données lo pour distinguer les colonnes de votre base de données contenant des références d’objets volumineux des autres identifiants d’objet (OID).
lo_manage : fonction que vous pouvez utiliser dans les déclencheurs sur les colonnes de la table contenant des références d’objets volumineux. Lorsque vous supprimez ou modifiez une valeur qui fait référence à un objet volumineux, le déclencheur dissocie l’objet (lo_unlink) de sa référence. Utilisez le déclencheur sur une colonne uniquement si la colonne est la seule référence de base de données à l’objet volumineux.

Pour en savoir plus sur le module d’objets volumineux, consultez lo dans la documentation PostgreSQL.

Installation de l’extension lo

Avant d’installer l’extension lo, assurez-vous que vous disposez de privilèges rds_superuser.

Pour installer l’extension Io

Utilisez psql pour vous connecter à l’instance de base de données principale de votre cluster de bases de données Aurora PostgreSQL.
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
Lorsque vous y êtes invité, saisissez votre mot de passe. Le client psql se connecte à la base de données de connexions administratives par défaut, postgres=>, et l’affiche sous forme d’invite.

Installez l’extension comme suit.


postgres=> CREATE EXTENSION lo;
CREATE EXTENSION

Vous pouvez désormais utiliser le type de données lo pour définir des colonnes dans vos tables. Vous pouvez, par exemple, créer une table (images) qui contient des données d’image tramée. Vous pouvez utiliser le type de données lo pour une colonne raster, comme illustré dans l’exemple suivant, qui crée une table.


postgres=> CREATE TABLE images (image_name text, raster lo);

Utilisation de la fonction de déclenchement lo_manage pour supprimer des objets

Vous pouvez utiliser la fonction lo_manage dans un déclencheur sur une colonne lo ou d’autres colonnes d’objets volumineux pour les nettoyer (et éviter les objets orphelins) lorsque lo est mis à jour ou supprimé.

Pour configurer des déclencheurs sur les colonnes qui font référence à des objets volumineux

Effectuez l’une des actions suivantes :
- Créez un déclencheur BEFORE UPDATE OR DELETE sur chaque colonne de sorte qu’il contienne des références uniques à des objets volumineux, en utilisant le nom de colonne comme argument.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
- Appliquez un déclencheur uniquement lorsque la colonne est en cours de mise à jour.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```

La fonction de déclenchement lo_manage fonctionne uniquement dans le contexte de l’insertion ou de la suppression de données de colonne, en fonction de la façon dont vous définissez le déclencheur. Elle n’a aucun effet lorsque vous effectuez une opération DROP ou TRUNCATE sur une base de données. Cela signifie que vous devriez supprimer les colonnes d’objets de n’importe quelle table avant de procéder à la suppression de la base de données, pour éviter la création d’objets orphelins.

Par exemple, supposons que vous souhaitiez supprimer la base de données contenant la table images. Vous supprimez la colonne comme suit.


postgres=> DELETE FROM images COLUMN raster

En supposant que la fonction lo_manage est définie sur cette colonne pour gérer les suppressions, vous pouvez maintenant supprimer la table en toute sécurité.

Suppression d’objets volumineux orphelins à l’aide de `vacuumlo`

L’utilitaire vacuumlo identifie les objets volumineux orphelins et peut les supprimer des bases de données. Cet utilitaire est disponible depuis PostgreSQL 9.1.24. Si les utilisateurs de base de données travaillent régulièrement avec des objets volumineux, nous vous recommandons d’exécuter vacuumlo occasionnellement pour nettoyer les objets volumineux orphelins.

Avant d’installer l’extension lo, vous pouvez utiliser vacuumlo pour déterminer si votre cluster de bases de données Aurora PostgreSQL peut en bénéficier. Pour ce faire, exécutez vacuumlo avec l’option -n (no-op) pour afficher les objets qui seraient supprimés, comme illustré dans l’exemple suivant :


$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".

Comme indiqué dans la sortie, les objets volumineux orphelins ne posent aucun problème pour cette base de données.

Pour plus d’informations sur cet utilitaire, consultez vacuumlo dans la documentation PostgreSQL.

Comprendre le fonctionnement d’`vacuumlo`

La commande vacuumlo supprime les objets volumineux orphelins (LO) de votre base de données PostgreSQL sans affecter ni entrer en conflit avec vos tables utilisateur.

La commande fonctionne comme suit :

vacuumlo commence par créer une table temporaire contenant tous les ID d’objet (OID) des objets volumineux de votre base de données.
vacuumlo parcourt ensuite chaque colonne de la base de données qui utilise les types de données oid ou lo. Si vacuumlo identifie un OID correspondant dans ces colonnes, il le supprime de la table temporaire. vacuumlo ne vérifie que les colonnes portant spécifiquement le nom oid ou lo, et non les domaines définis à partir de ces types.
Les autres entrées de la table temporaire représentent des LO orphelins, que vacuumlo peut alors supprimer sans risque.

Amélioration des performances de `vacuumlo`

Vous pouvez potentiellement améliorer les performances de vacuumlo en augmentant la taille du lot à l’aide de l’option -l. vacuumlo peut ainsi traiter plus de LO à la fois.

Si la mémoire disponible sur votre système est suffisante pour maintenir la table temporaire en mémoire, augmenter le paramètre temp_buffers au niveau de la base de données peut contribuer à de meilleures performances. La table peut ainsi être conservée entièrement en mémoire, ce qui améliore les performances globales.

La requête suivante estime la taille de la table temporaire :


SELECT
    pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
    pg_largeobject_metadata;

Considérations relatives aux objets volumineux

Voici quelques points importants à garder à l’esprit lors de l’utilisation d’objets volumineux.

Vacuumlo est la seule solution, car il n’existe actuellement aucune autre méthode pour supprimer les LO orphelins.
Des outils tels que pglogical, la réplication logique native et AWS DMS qui utilisent des technologies de réplication ne prennent pas en charge la réplication d’objets volumineux.
Lorsque vous concevez le schéma de votre base de données, évitez d’utiliser des objets volumineux dans la mesure du possible et envisagez d’utiliser d’autres types de données, comme bytea.
Exécutez vacuumlo régulièrement, au moins une fois par semaine, pour éviter les problèmes liés aux LO orphelins.
Utilisez un déclencheur avec la fonction lo_manage sur les tables qui stockent des objets volumineux pour empêcher la création de LO orphelins.

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Utilisation de la prise en charge de la délégation des extensions Amazon Aurora pour PostgreSQL

Gestion des données spatiales avec PostGIS