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à.

Diagnosi delle dimensioni della tabella e dell'indice

Puoi usare PostgreSQL Multiversion Concurrency Control (MVCC) per preservare l'integrità dei dati. PostgreSQL MVCC funziona salvando una copia interna delle righe aggiornate o eliminate (chiamate anche tuple) fino al commit o al rollback di una transazione. Questa copia interna salvata è invisibile agli utenti. Tuttavia, le dimensioni della tabella possono aumentare quando le copie invisibili non vengono pulite regolarmente dalle utilità VACUUM o AUTOVACUUM. Se non controllato, l'aumento delle dimensioni della tabella può comportare un aumento dei costi di archiviazione e rallentare la velocità di elaborazione.

In molti casi, le impostazioni predefinite per VACUUM o AUTOVACUUM su Aurora sono sufficienti per gestire l'aumento indesiderato delle dimensioni della tabella. Tuttavia, verifica la presenza di un aumento delle dimensioni se l'applicazione presenta le seguenti condizioni:

Per iniziare, raccogli le informazioni accurate su quanto spazio viene utilizzato dalle tuple inattive e su quanto puoi recuperare ripulendo le dimensioni della tabella e dell'indice. Per farlo, usa l'estensione pgstattuple per raccogliere statistiche sul cluster Aurora. Per ulteriori informazioni, consulta pgstattuple. I privilegi di utilizzo dell'estensione pgstattuple sono limitati al ruolo pg_stat_scan_tables e ai superuser del database.

Per creare l'estensione pgstattuple su Aurora, connetti una sessione client al cluster, ad esempio psql o pgAdmin, e usa il seguente comando:

CREATE EXTENSION pgstattuple;

Crea l'estensione in ogni database da profilare. Dopo aver creato l'estensione, usa l'interfaccia della linea di comando (CLI) per misurare la quantità di spazio inutilizzato che puoi recuperare. Prima di raccogliere le statistiche, modifica il gruppo di parametri del cluster impostando AUTOVACUUM su 0. Un'impostazione pari a 0 impedisce ad Aurora di eliminare automaticamente tutte le tuple inattive dell'applicazione, il che può influire sulla precisione dei risultati. Immetti il seguente comando per creare una tabella semplice:

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

Nell'esempio seguente, eseguiamo la query con AUTOVACUUM attivato per il cluster di database. Il valore di dead_tuple_count è pari a 0, che indica che AUTOVACUUM ha cancellato i dati obsoleti o le tuple dal database PostgreSQL.

Per utilizzare pgstattuple per raccogliere informazioni sulla tabella, specifica il nome di una tabella o un identificatore di oggetto (OID) nella query:

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

Nella seguente query, disattiviamo AUTOVACUUM ed eseguiamo un comando che elimina 25.000 righe dalla tabella. Di conseguenza, il valore di dead_tuple_count aumenta a 25000.

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

Per recuperare le tuple inattive, avvia un processo VACUUM.

Osservazione delle dimensioni senza interrompere l'applicazione

Le impostazioni su un cluster Aurora sono ottimizzate per fornire le best practice per la maggior parte dei carichi di lavoro. Tuttavia, potresti voler ottimizzare un cluster per adattarlo meglio alle tue applicazioni e ai tuoi modelli di utilizzo. In questo caso, puoi utilizzare l'estensione pgstattuple senza interrompere un'applicazione in esecuzione. A tale scopo, esegui i seguenti passaggi:

Dopo aver eseguito le applicazioni e visualizzato il risultato, usa pg_repack o VACUUM FULL sulla copia ripristinata e confronta le differenze. Se noti un calo significativo dei valori di dead_tuple_count, dead_tuple_len o dead_tuple_percent, modifica il programma di vacuum sul cluster di produzione per ridurre al minimo l'aumento delle dimensioni.

Impedimento dell'aumento delle dimensioni nelle tabelle temporanee

Se l'applicazione crea tabelle temporanee, assicurati che vengano rimosse quando non sono più necessarie. I processi Autovacuum non individuano le tabelle temporanee. Se non selezionate, le tabelle temporanee possono creare rapidamente un aumento delle dimensioni del database. Inoltre, l'aumento delle dimensioni può estendersi alle tabelle di sistema, che sono le tabelle interne che tracciano gli oggetti e gli attributi di PostgreSQL, come pg_attribute e pg_depend.

Quando una tabella temporanea non è più necessaria, è possibile utilizzare un'istruzione TRUNCATE per svuotarla e liberare spazio. Quindi, esegui manualmente il vacuum delle tabelle pg_attribute e pg_depend. Il vacuum di queste tabelle garantisce che la creazione e il troncamento o l'eliminazione delle tabelle temporanee in modo continuo non comporti l'aggiunta di tuple né contribuisca all'aumento delle dimensioni del sistema.

Puoi evitare questo problema durante la creazione di una tabella temporanea includendo la seguente sintassi che elimina le nuove righe quando il contenuto viene sottoposto a commit:

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

La clausola ON COMMIT DELETE ROWS tronca la tabella temporanea quando la transazione viene sottoposta a commit.

Impedimento dell'aumento delle dimensioni negli indici

Quando si modifica un campo indicizzato in una tabella, l'aggiornamento dell'indice genera una o più tuple inattive in quell'indice. Per impostazione predefinita, il processo autovacuum elimina l'aumento delle dimensioni negli indici, ma tale pulizia richiede una notevole quantità di tempo e risorse. Per specificare le preferenze di pulizia dell'indice quando crei una tabella, includi la clausola vacuum_index_cleanup. Per impostazione predefinita, al momento della creazione della tabella, la clausola è impostata su AUTO, il che significa che il server decide se l'indice deve essere pulito quando esegue il vacuum della tabella. È possibile impostare la clausola su ON per attivare la pulizia dell'indice per una tabella specifica o su OFF per disattivare la pulizia dell'indice per la tabella. Tieni presente che la disattivazione della pulizia dell'indice può far risparmiare tempo, ma può potenzialmente portare a un aumento delle dimensioni dell'indice.

È possibile controllare manualmente la pulizia dell'indice quando esegui il VACUUM di una tabella dalla linea di comando. Per eseguire il vacuum di una tabella e rimuovere le tuple inattive dagli indici, includi la clausola INDEX_CLEANUP con il valore ON e il nome della tabella:

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

Per eseguire il vacuum di una tabella senza pulire gli indici, specifica il valore OFF:

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM