Riduzione della dimensione nelle tabelle e negli indici con l’estensione pg_repack

• Creazione iniziale della tabella di log: viene creata una tabella di log per registrare le modifiche che si verificano durante la copia iniziale dei dati, come illustrato nell'esempio seguente:

  postgres=>\dt+ repack.log_*
  List of relations
  -[ RECORD 1 ]-+----------
  Schema        | repack
  Name          | log_16490
  Type          | table
  Owner         | postgres
  Persistence   | permanent
  Access method | heap
  Size          | 65 MB
  Description   |

• swap-and-dropFase finale.

I seguenti consigli si applicano quando si rimuove bloat dalle tabelle e dagli indici utilizzando l'estensione: pg_repack

• Esegui il repack durante le ore non lavorative o durante una finestra di manutenzione per minimizzarne l'impatto sulle prestazioni di altre attività del database.

• Monitora attentamente le sessioni di blocco durante l'attività di ricostruzione e assicurati che sulla tabella originale non vi siano attività potenzialmente bloccabilipg_repack, in particolare durante la swap-and-drop fase finale, quando è necessario un blocco esclusivo sulla tabella originale. Per ulteriori informazioni, vedere Identificazione degli elementi che bloccano una query.

  Quando viene visualizzata una sessione di blocco, è possibile interromperla utilizzando il seguente comando dopo un'attenta valutazione. Questo aiuta a continuare pg_repack a completare la ricostruzione:

  SELECT pg_terminate_backend(pid);

• Durante l'applicazione delle modifiche accumulate dalla tabella di pg_repack's registro su sistemi con un tasso di transazione molto elevato, il processo di applicazione potrebbe non essere in grado di tenere il passo con la frequenza delle modifiche. In questi casi, non pg_repack sarebbe in grado di completare la procedura di candidatura. Per ulteriori informazioni, consulta Monitoraggio della nuova tabella durante il repack. Se gli indici sono molto gonfi, una soluzione alternativa consiste nell'eseguire un reimpacchettamento del solo indice. Ciò consente inoltre di completare più rapidamente VACUUM i cicli di pulizia degli indici.

  È possibile saltare la fase di pulizia dell'indice utilizzando il manuale disponibile nella SQL versione 12 di Postgre, che viene saltata automaticamente durante l'autovacuum di emergenza a VACUUM partire dalla versione 14 di Postgre. SQL Ciò consente di VACUUM completare il processo più rapidamente senza rimuovere il gonfiore dell'indice ed è pensato solo per situazioni di emergenza, ad esempio per impedire il wraparound. VACUUM Per ulteriori informazioni, consulta Avoiding Bloat negli indici nella Guida per l'utente di Amazon Aurora.

• La tabella deve avere PRIMARY KEY un vincolo o non nullo. UNIQUE

• La versione dell'estensione deve essere la stessa sia per il client che per il server.

• Assicurati che l'RDSistanza abbia una dimensione FreeStorageSpace superiore alla dimensione totale della tabella senza il problema. Ad esempio, considera la dimensione totale della tabella, compresi gli indici, pari a 2 TB TOAST e il gonfiore totale nella tabella pari a 1 TB. Il valore richiesto FreeStorageSpace deve essere superiore al valore restituito dal seguente calcolo:

  2TB (Table size) - 1TB (Table bloat) = 1TB

  È possibile utilizzare la seguente query per verificare la dimensione totale della tabella e utilizzarla pgstattuple per ricavare bloat. Per ulteriori informazioni, consulta Diagnosticare il gonfiamento di tabelle e indici nella Guida per l'utente di Amazon Aurora

  SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;

  Questo spazio viene recuperato dopo il completamento dell'attività.

• Assicurati che l'RDSistanza abbia una capacità di elaborazione e IO sufficiente per gestire l'operazione di repack. Potresti prendere in considerazione l'idea di scalare la classe dell'istanza per un equilibrio ottimale delle prestazioni.

• La dimensione del database viene aumentata della dimensione totale della tabella meno il bloat, fino alla swap-and-drop fase di repack. È possibile monitorare il tasso di crescita delle dimensioni del database, calcolare la velocità del repack e stimare approssimativamente il tempo necessario per completare il trasferimento iniziale dei dati.

  Ad esempio, considera la dimensione totale della tabella di 2 TB, la dimensione del database di 4 TB e il volume totale della tabella di 1 TB. Il valore della dimensione totale del database restituito dal calcolo al termine dell'operazione di repack è il seguente:

  2TB (Table size) + 4 TB (Database size) - 1TB (Table bloat) = 5TB

  È possibile stimare approssimativamente la velocità dell'operazione di repack campionando il tasso di crescita in byte tra due momenti nel tempo. Se il tasso di crescita è di 1 GB al minuto, possono essere necessari 1000 minuti o 16,6 ore circa per completare l'operazione iniziale di creazione della tabella. Oltre alla creazione iniziale della tabella, deve applicare pg_repack anche le modifiche maturate. Il tempo necessario dipende dalla velocità di applicazione delle modifiche in corso e delle modifiche maturate.

  Nota

  È possibile utilizzare l'pgstattupleestensione per calcolare il rigonfiamento nella tabella. Per ulteriori informazioni, consulta pgstattuple.

• Il numero di righe nella tabella di pg_repack's log, secondo lo schema repack, rappresenta il volume delle modifiche in sospeso da applicare alla nuova tabella dopo il caricamento iniziale.

  È possibile controllare la tabella di pg_repack's registro pg_stat_all_tables per monitorare le modifiche applicate alla nuova tabella. pg_stat_all_tables.n_live_tupindica il numero di record in attesa di applicazione alla nuova tabella. Per ulteriori informazioni, vedere pg_stat_all_tables.

  postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%';
          
  -[ RECORD 1 ]---------
  relname    | log_16490
  n_live_tup | 2000000
          

• Puoi utilizzare l'pg_stat_statementsestensione per scoprire il tempo impiegato da ogni fase dell'operazione di repack. Ciò è utile in preparazione all'applicazione della stessa operazione di reimballaggio in un ambiente di produzione. È possibile modificare la LIMIT clausola per estendere ulteriormente l'output.

  postgres=>SELECT
       SUBSTR(query, 1, 100) query,
       round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes
   FROM
       pg_stat_statements
   WHERE
       query ILIKE '%repack%'
   ORDER BY
       total_exec_time DESC LIMIT 5;
          
   query                                                                 | total_exec_time_in_minutes
  -----------------------------------------------------------------------+----------------------------
   CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) |                     6.8627
   INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1           |                     6.4150
   SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)                    |                     0.5395
   SELECT repack.repack_drop($1, $2)                                     |                     0.0004
   SELECT repack.repack_swap($1)                                         |                     0.0004
  (5 rows)