Gestione di oggetti di grandi dimensioni con il modulo lo - Amazon Aurora
Installazione dell'estensione loUtilizzo della funzione di trigger lo_manageRimozione di oggetti di grandi dimensioni orfani utilizzando vacuumloIntroduzione al funzionamento di vacuumloMigliorare vacuumlo le prestazioniConsiderazioni relative agli 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) è destinato agli utenti e agli sviluppatori di database che utilizzano i database Postgre tramite i nostri driver. SQL JDBC ODBC Entrambi JDBC ODBC si aspettano che il database gestisca la cancellazione di oggetti di grandi dimensioni quando i riferimenti ad essi cambiano. Tuttavia, Postgre SQL non funziona in questo modo. Postgre SQL non presume che un oggetto debba essere 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ò trarre vantaggio dall'estensione lo, utilizzate l'vacuumloutilità per verificare la presenza di oggetti di grandi dimensioni orfani. 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 Postgree SQL 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— Si tratta di un tipo di dati di grandi dimensioni (lo) che è possibile utilizzare per oggetti binari di grandi dimensioni () e altri oggetti di grandi dimensioni. BLOBs 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 SQL Postgre. In termini semplici, è possibile utilizzare il tipo di lo dati per distinguere le colonne del database che contengono riferimenti a oggetti di grandi dimensioni da altri identificatori di oggetti (). OIDs

  • 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 large objects, consultate lo nella documentazione di SQL Postgree.

Installazione dell'estensione lo

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

Installare l'estensione

  1. psqlUtilizzalo per connetterti all'istanza DB principale del tuo cluster DB Aurora SQL Postgre.

    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 DELETE trigger BEFORE UPDATE OR su ogni colonna per contenere riferimenti univoci a oggetti di grandi dimensioni, utilizzando il nome della colonna per l'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 di grandi dimensioni orfani utilizzando vacuumlo

L'vacuumloutilità identifica e rimuove oggetti di grandi dimensioni orfani dai database. Questa utilità è disponibile a partire da SQL Postgree 9.1.24. Se gli utenti del database lavorano abitualmente con oggetti di grandi dimensioni, consigliamo di eseguirli di tanto in vacuumlo tanto per ripulire oggetti di grandi dimensioni orfani.

Prima di installare l'estensione log, puoi utilizzarla vacuumlo per valutare se il tuo cluster Aurora Postgree SQL DB 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 la documentazione di vacuumloPostgre. SQL

Introduzione al funzionamento di vacuumlo

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

Il comando funziona nel modo seguente:

  1. vacuumloinizia creando una tabella temporanea contenente tutti gli Object IDs (OIDs) degli oggetti di grandi dimensioni presenti nel database.

  2. vacuumloquindi analizza ogni colonna del database che utilizza i tipi di dati oid olo. Se vacuumlo trova una corrispondenza OID in queste colonne, la rimuove OID dalla tabella temporanea. vacuumlocontrolla solo le colonne con nome specifico oid olo, non i domini basati su questi tipi.

  3. Le voci rimanenti nella tabella temporanea sono quelle orfaneLOs, che vengono vacuumlo quindi rimosse in modo sicuro.

Miglioramento delle prestazioni vacuumlo

È possibile migliorare le prestazioni di vacuumlo aumentando la dimensione del batch utilizzando l'-lopzione. Ciò consente vacuumlo di elaborarne più LOs contemporaneamente.

Se il sistema dispone di memoria sufficiente ed è possibile inserire completamente la tabella temporanea in memoria, l'aumento dell'temp_buffersimpostazione 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 relative agli oggetti di grandi dimensioni

Di seguito sono riportate alcune considerazioni importanti da tenere presente quando si lavora con oggetti di grandi dimensioni:

  • Vacuumloè l'unica soluzione in quanto attualmente non esiste un altro metodo per rimuovere gli orfaniLOs.

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

  • Durante la progettazione dello schema del database, evita di utilizzare oggetti di grandi dimensioni quando possibile e prendi in considerazione l'utilizzo di tipi di dati alternativi come invece. bytea

  • Esegui vacuumlo regolarmente, almeno una volta alla settimana, per evitare problemi con gli orfaniLOs.

  • Utilizza un trigger con la lo_manage funzione sulle tabelle che memorizzano oggetti di grandi dimensioni per evitare che vengano creati oggetti orfaniLOs.