Administración de objetos grandes con el módulo lo - Amazon Aurora
Instalación de la extensión loUso de la función de desencadenador lo_manageEliminación de objetos grandes huérfanos mediante vacuumloFuncionamiento de vacuumloMejora en el rendimiento de vacuumloConsideraciones para objetos grandes

Administración de objetos grandes con el módulo lo

El módulo lo (extensión) es para usuarios de base de datos y desarrolladores que trabajan con bases de datos PostgreSQL mediante controladores JDBC u ODBC. Tanto JDBC como ODBC esperan que la base de datos se encargue de eliminar los objetos grandes cuando cambian las referencias a ellos. No obstante, PostgreSQL no funciona así. PostgreSQL no asume que un objeto se deba eliminar cuando cambie su referencia. El resultado es que los objetos permanecen en el disco, sin referencia. La extensión lo incluye una función que se utiliza para desencadenarse en los cambios de referencia con el fin de eliminar objetos si es necesario.

sugerencia

Para determinar si su base de datos se puede beneficiar de la extensión lo, utilice la utilidad vacuumlo para comprobar si hay objetos grandes huérfanos. Para obtener el recuento de los objetos grandes huérfanos sin realizar ninguna acción, ejecute la utilidad con la opción -n (sin operación). Para saber cómo, consulte vacuumlo utility a continuación.

El módulo lo está disponible para Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 y versiones secundarias posteriores.

Para instalar el módulo (extensión), necesita privilegios de rds_superuser. La instalación de la extensión lo agrega a su base de datos:

  • lo: es un tipo de datos de objeto grande (lo) que puede utilizar para objetos binarios grandes (BLOB) y otros objetos de gran tamaño. El tipo de datos lo es un dominio del tipo de datos oid. Es decir, se trata de un identificador de objeto con restricciones opcionales. Para obtener más información, consulte Object identifiers (Identificadores de objetos) en la documentación de PostgreSQL. Dicho de forma sencilla, puede utilizar el tipo de datos lo para distinguir las columnas de base de datos que contienen referencias a objetos grandes de otros identificadores de objetos (OID).

  • lo_manage: es una función que puede utilizar en los desencadenadores de las columnas de tabla que contienen referencias a objetos grandes. Siempre que elimine o modifique un valor que haga referencia a un objeto grande, el desencadenador desvincula el objeto (lo_unlink) de su referencia. Utilice el desencadenador en una columna solo si esta es la única referencia de base de datos al objeto grande.

Para obtener más información sobre el módulo de objetos grandes, consulte lo en la documentación de PostgreSQL.

Instalación de la extensión lo

Antes de instalar la extensión lo, asegúrese de que dispone de privilegios de rds_superuser.

Para instalar la extensión lo

  1. Use psql para conectarse a la instancia de base de datos principal de su clúster de base de datos de Aurora PostgreSQL.

    psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

    Escriba la contraseña cuando se le solicite. El cliente psql se conecta y muestra la base de datos de conexión administrativa predeterminada, postgres=>, como el símbolo del sistema.

  2. Instale la extensión de la siguiente manera:

    postgres=> CREATE EXTENSION lo;
CREATE EXTENSION

Ahora puede utilizar el tipo de datos lo para definir columnas en las tablas. Por ejemplo, puede crear una tabla (images) que contenga datos de imágenes rasterizadas. Puede utilizar el tipo de datos lo para una columna raster, como se muestra en el siguiente ejemplo, que crea una tabla.

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

Uso de la función de desencadenador lo_manage para eliminar objetos

Puede utilizar la función lo_manage en un desencadenador en lo o en otras columnas de objetos grandes para limpiar (y evitar los objetos huérfanos) cuando lo se actualiza o se elimina.

Para configurar desencadenadores en columnas que hacen referencia a objetos grandes

  • Realice una de las siguientes acciones:

    • Cree un desencadenador BEFORE UPDATE OR DELETE en cada columna para que contenga referencias únicas a objetos grandes, con el nombre de columna como argumento.

      postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

    • Aplique un desencadenador solo cuando la columna se esté actualizando.

      postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

La función de desencadenador lo_manage solo funciona en el contexto de la inserción o eliminación de datos de columna, en función de cómo se defina el desencadenador. No tiene ningún efecto cuando se realiza una operación DROP o TRUNCATE en una base de datos. Esto significa que debe eliminar las columnas de objeto de cualquier tabla antes de suprimirla, para evitar que se creen objetos huérfanos.

Por ejemplo, suponga que desea eliminar la base de datos que contiene la tabla images. Elimina la columna de la siguiente manera. 

postgres=> DELETE FROM images COLUMN raster

Si se supone que la función lo_manage está definida en esa columna para gestionar las eliminaciones, ahora puede eliminar la tabla con seguridad.

Eliminación de objetos grandes huérfanos mediante vacuumlo

La utilidad vacuumlo identifica y puede eliminar los objetos grandes huérfanos de las bases de datos. Esta utilidad está disponible desde PostgreSQL 9.1.24. Si los usuarios de la base de datos trabajan habitualmente con objetos grandes, le recomendamos que ejecute vacuumlo de vez en cuando para limpiar los objetos grandes huérfanos.

Antes de instalar la extensión lo, puede utilizar vacuumlo para evaluar si se puede beneficiar su clúster de bases de datos Aurora PostgreSQL. Para ello, ejecute vacuumlo con la opción -n (sin operación) para mostrar lo que se eliminaría, como se presenta a continuación: 

$ 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".

Como muestra la salida, los objetos grandes huérfanos no son un problema para esta base de datos concreta.

Para obtener más información sobre esta utilidad, consulte vacuumlo en la documentación de PostgreSQL.

Funcionamiento de vacuumlo

El comando vacuumlo elimina los objetos grandes (LO) huérfanos de la base de datos de PostgreSQL sin que ello afecte ni entre en conflicto con las tablas de usuario.

El comando funciona así:

  1. vacuumlo comienza creando una tabla temporal que contiene todos los ID de objeto (OID) de los objetos grandes de la base de datos.

  2. A continuación, vacuumlo escanea todas las columnas de la base de datos que utilizan los tipos de datos oid o lo. Si vacuumlo encuentra un OID coincidente en estas columnas, lo elimina de la tabla temporal. vacuumlocomprueba solo y específicamente las columnas con los nombres oid o lo, no los dominios basados en estos tipos.

  3. El resto de las entradas de la tabla temporal representan LO huérfanos, que posteriormente elimina vacuumlo de forma segura.

Mejora en el rendimiento de vacuumlo

Puede mejorar el rendimiento de vacuumlo aumentando el tamaño del lote mediante la opción -l. Esto permite a vacuumlo procesar más LO a la vez.

Si el sistema tiene memoria suficiente y puede alojar la tabla temporal completamente en la memoria, aumentar la configuración de temp_buffers para la base de datos puede mejorar el rendimiento. Esto permite que la tabla resida completamente en la memoria, lo que puede mejorar el rendimiento general.

La siguiente consulta estima el tamaño de la tabla temporal:

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

Consideraciones para objetos grandes

A continuación, encontrará algunas consideraciones importantes que debe tener en cuenta al trabajar con objetos grandes:

  • Vacuumlo es la única solución, ya que actualmente no existe otro método para eliminar los LO huérfanos.

  • Las herramientas como pglogical, la replicación lógica nativa y AWS DMS, que utilizan tecnologías de replicación, no admiten la replicación de objetos de gran tamaño.

  • Al diseñar el esquema de la base de datos, evite utilizar objetos grandes siempre que sea posible y, en su lugar, considere la posibilidad de utilizar tipos de datos alternativos como bytea.

  • Ejecute vacuumlo con regularidad, al menos una vez por semana, para evitar problemas con los LO huérfanos.

  • Utilice un activador con la función lo_manage en las tablas que almacenen objetos grandes para evitar que se creen LO huérfanos.