Gestione di oggetti di grandi dimensioni con il modulo lo - Amazon Aurora
Installazione dell'estensione loUtilizzo della funzione di trigger lo_manageRimozione di oggetti orfani di grandi dimensioni orfani con vacuumloIntroduzione al funzionamento di vacuumloMiglioramento delle prestazioni di vacuumloConsiderazioni per oggetti di grandi dimensioni

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Gestione di oggetti di grandi dimensioni con il modulo lo

Il modulo lo (estensione) è per utenti di database e sviluppatori che utilizzano database PostgreSQL tramite driver JDBC o ODBC. JDBC e ODBC presumono entrambi che il database gestisca l'eliminazione di oggetti di grandi dimensioni quando i riferimenti ad essi cambiano. Tuttavia, PostgreSQL non funziona in questo modo. PostgreSQL non ipotizza che un oggetto venga eliminato quando il suo riferimento cambia. Il risultato è che gli oggetti rimangono su disco, senza riferimenti. L'estensione lo include una funzione utilizzata per attivare le modifiche dei riferimenti per eliminare gli oggetti se necessario.

Suggerimento

Per determinare se il database può sfruttare l’estensione lo, utilizzare l’utilità vacuumlo per verificare la presenza di oggetti orfani di grandi dimensioni. Per ottenere il conteggio di oggetti orfani di grandi dimensioni senza eseguire alcuna azione, esegui l'utilità con l'opzione -n(no-op). Per scoprire come, consulta vacuumlo utility seguente.

Il modulo lo è disponibile per Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 e versioni secondarie successive.

Per installare il modulo (estensione), sono necessari privilegi rds_superuser. L'installazione dell'estensione lo aggiunge quanto segue al database:

  • lo – Questo è un tipo di dati (lo) oggetto di grandi dimensioni che puoi utilizzare per oggetti binari di grandi dimensioni (BLOB) e altri oggetti di grandi dimensioni. Il tipo di dati lo è un dominio del tipo di dati oid. In altre parole, è un identificatore di oggetti con vincoli opzionali. Per ulteriori informazioni, consulta Identificatori di oggetti nella documentazione di PostgreSQL. In poche parole, puoi utilizzare il tipo di dati lo per distinguere le colonne del database che contengono riferimenti di oggetti di grandi dimensioni da altri identificatori di oggetti (OID).

  • lo_manage – Questa è una funzione che puoi utilizzare nei trigger sulle colonne di tabella contenenti riferimenti di oggetti di grandi dimensioni. Ogni volta che elimini o modifichi un valore che fa riferimento a un oggetto di grandi dimensioni, il trigger scollega l'oggetto (lo_unlink) dal suo riferimento. Utilizza il trigger su una colonna solo se la colonna è l'unico riferimento del database all'oggetto di grandi dimensioni.

Per ulteriori informazioni sul modulo di oggetti di grandi dimensioni, consulta lo nella documentazione di PostgreSQL.

Installazione dell'estensione lo

Prima di installare l'estensione lo, assicurati di disporre dei privilegi rds_superuser.

Installare l'estensione

  1. Utilizza psql per connetterti all'istanza database principale del cluster database Aurora PostgreSQL.

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

    Specifica la password, quando richiesto. Il client psql si connette e visualizza il database di connessione amministrativa predefinito postgres=> come prompt.

  2. Installa l'estensione come segue:

    postgres=> CREATE EXTENSION lo;
CREATE EXTENSION

Ora puoi utilizzare il tipo di dati lo per definire le colonne nelle tabelle. Ad esempio, puoi creare una tabella (images) contenente dati immagine raster. Puoi utilizzare il tipo di dati lo per una colonna raster, come mostrato nell'esempio seguente, che crea una tabella.

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

Utilizzo della funzione di trigger lo_manage per eliminare gli oggetti

Puoi utilizzare la funzione lo_manage in un trigger su un lo o altre colonne di oggetti di grandi dimensioni per rimuovere (e prevenire oggetti orfani) quando lo viene aggiornato o eliminato.

Impostare trigger su colonne che fanno riferimento a oggetti di grandi dimensioni

  • Esegui una di queste operazioni:

    • Crea un trigger BEFORE UPDATE OR DELETE su ogni colonna per contenere riferimenti univoci a oggetti di grandi dimensioni, utilizzando il nome della colonna come argomento.

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

    • Applica un trigger solo quando la colonna viene aggiornata.

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

La funzione di trigger lo_manage funziona solo nel contesto dell'inserimento o dell'eliminazione dei dati di colonna, a seconda di come definisci il trigger. Non ha alcun effetto quando esegui un'operazione DROP o TRUNCATE su un database. Ciò significa che devi eliminare le colonne di oggetti da qualsiasi tabella prima del rilascio, per evitare la creazione di oggetti orfani.

Ad esempio, supponi di voler rilasciare il database contenente la tabella images. Per eliminare la colonna procedi come segue. 

postgres=> DELETE FROM images COLUMN raster

Ipotizzando che la funzione lo_manage sia definita su tale colonna per gestire le eliminazioni, ora puoi rilasciare la tabella in modo sicuro.

Rimozione di oggetti orfani di grandi dimensioni orfani con vacuumlo

L’utilità vacuumlo identifica e rimuove oggetti orfani di grandi dimensioni dai database. Questa utilità è disponibile a partire da PostgreSQL 9.1.24. Se gli utenti del database utilizzano regolarmente oggetti di grandi dimensioni, si consiglia di eseguire vacuumlo occasionalmente per rimuovere gli oggetti orfani di grandi dimensioni.

Prima di installare l’estensione lo, è possibile utilizzare vacuumlo per valutare se il cluster di database Aurora PostgreSQL può trarne vantaggio. A questo scopo, esegui vacuumlo con l'opzione -n (no-op) per mostrare cosa viene rimosso, come illustrato di seguito: 

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

Come mostra l'output, gli oggetti orfani di grandi dimensioni non sono un problema per questo particolare database.

Per ulteriori informazioni su questa utilità, consulta vacuumlo nella documentazione di PostgreSQL.

Introduzione al funzionamento di vacuumlo

Il comando vacuumlo rimuove gli oggetti orfani di grandi dimensioni (LO) dal database PostgreSQL senza influire o entrare in conflitto con le tabelle utente.

Il comando funziona come segue:

  1. vacuumlo inizia creando una tabella temporanea contenente tutti gli ID oggetto (OID) degli oggetti di grandi dimensioni presenti nel database.

  2. vacuumlo analizza quindi ogni colonna del database che utilizza i tipi di dati oid o lo. Se vacuumlo trova un OID corrispondente in queste colonne, rimuove l’OID dalla tabella temporanea. vacuumlo controlla solo le colonne denominate specificatamente oid o lo, non i domini basati su questi tipi.

  3. Le voci rimanenti nella tabella temporanea rappresentano gli oggetti orfani di grandi dimensioni, che vengono quindi rimossi in modo sicuro da vacuumlo.

Miglioramento delle prestazioni di vacuumlo

È possibile migliorare le prestazioni di vacuumlo aumentando la dimensione del batch con l’opzione -l. Ciò consente a vacuumlo di elaborare più oggetti di grandi dimensioni contemporaneamente.

Se il sistema dispone di memoria sufficiente ed è possibile inserire completamente la tabella temporanea in memoria, l’aumento dell’impostazione temp_buffers a livello di database può migliorare le prestazioni. Ciò consente alla tabella di risiedere interamente in memoria, il che può migliorare le prestazioni complessive.

La seguente query stima la dimensione della tabella temporanea:

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

Considerazioni per oggetti di grandi dimensioni

Di seguito sono riportate alcune considerazioni importanti da tenere presenti quando si utilizza oggetti di grandi dimensioni:

  • Vacuumlo è l’unica soluzione in quanto attualmente non esiste un altro metodo per rimuovere gli oggetti orfani di grandi dimensioni.

  • Strumenti come pglogical, replica logica nativa, e AWS DMS che utilizzano tecnologie di replica non supportano la replica di oggetti di grandi dimensioni.

  • Durante la progettazione dello schema del database, evitare di utilizzare oggetti di grandi dimensioni quando possibile e prendere in considerazione l’utilizzo di tipi di dati alternativi come bytea.

  • Eseguire vacuumlo regolarmente, almeno settimanalmente, per evitare problemi con gli oggetti orfani di grandi dimensioni.

  • Utilizzare un trigger con la funzione lo_manage sulle tabelle che archiviano oggetti di grandi dimensioni per impedire la creazione di oggetti orfani di grandi dimensioni.