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   |

• Fase finale di swap-and-drop.

I consigli indicato di seguito si applicano quando rimuovi le dimensioni dalle tabelle e dagli indici utilizzando l’estensione pg_repack:

• Per minimizzarne l’impatto sulle prestazioni di altre attività del database, esegui la nuova compressione durante le ore non lavorative o durante una finestra di manutenzione.

• Monitora attentamente le sessioni di blocco durante l’attività di ricostruzione e assicurati che sulla tabella originale non vi siano attività che potrebbero bloccare pg_repack, in particolare durante la fase finale di swap-and-drop, quando è necessario un blocco esclusivo sulla tabella originale. Per ulteriori informazioni, consulta l’articolo su come identificare gli elementi che bloccano una query.

  Quando viene visualizzata una sessione di blocco, puoi interromperla utilizzando il seguente comando dopo un’attenta valutazione. Ciò consente di continuare pg_repack per completare la ricostruzione:

  SELECT pg_terminate_backend(pid);

• Durante l’applicazione delle modifiche accumulate dalla tabella di log di pg_repack's sui 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, pg_repack potrebbe non essere in grado di completare la procedura di applicazione. Per ulteriori informazioni, consulta Monitoraggio della nuova tabella durante la nuova compressione. Se gli indici sono aumentati molto in dimensioni, una soluzione alternativa consiste nell’eseguire una nuova compressione solo dell’indice. Ciò aiuta anche a completare più velocemente i cicli di pulizia degli indici di VACUUM.

  Puoi saltare la fase di pulizia dell’indice utilizzando VACUUM manuale dalla versione 12 di PostgreSQL. In questo modo, la fase di pulizia viene saltata automaticamente durante l’autovacuum di emergenza da PostgreSQL versione 14. Ciò consente al comando VACUUM di completare più rapidamente il processo senza rimuovere le dimensioni dell’indice. Tale soluzione è pensata solo per le situazioni di emergenza, ad esempio per evitare che si verifichi un processo di vacuum per wraparound. Per ulteriori informazioni, consulta Impedimento dell’aumento delle dimensioni negli indici nella Guida per l’utente di Amazon Aurora.

• La tabella deve avere un vincolo CHIAVE PRIMARIA o un vincolo UNIVOCO non nullo.

• La versione dell’estensione deve essere la stessa per il client e per il server.

• Assicurati che l’istanza RDS abbia una dimensione FreeStorageSpace superiore alla dimensione totale della tabella senza aumento delle dimensioni. Ad esempio, se la dimensione totale della tabella, compresi TOAST e indici, è pari a 2 TB e l’aumento delle dimensioni totali nella tabella è pari a 1 TB, la dimensione di FreeStorageSpace richiesto deve essere superiore al valore risultante dal seguente calcolo:

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

  Puoi utilizzare la seguente query per verificare la dimensione totale della tabella e puoi utilizzare pgstattuple per individuare l’aumento delle dimensioni. Per ulteriori informazioni, consulta la sezione Diagnosi delle dimensioni della tabella e dell’indice 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’istanza RDS abbia una capacità di elaborazione e IO sufficiente per gestire l’operazione di nuova compressione. Potresti prendere in considerazione l’idea di aumentare verticalmente la classe di istanze per raggiungere un equilibrio ottimale delle prestazioni.

• Le dimensioni del database vengono aumentate della dimensione totale della tabella meno l’aumento di dimensione, fino alla fase di swap-and-drop della nuova compressione. È possibile monitorare il tasso di crescita delle dimensioni del database, calcolare la velocità della nuova compressione 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 nuova compressione è il seguente:

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

  Puoi stimare approssimativamente la velocità dell’operazione di nuova compressione 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 1.000 minuti o 16,6 ore circa per completare l’operazione iniziale di creazione della tabella. Oltre alla creazione iniziale della tabella, pg_repack deve applicare anche le modifiche accumulate. Il tempo necessario per tale operazione dipende dalla velocità di applicazione delle modifiche che sono in corso e di quelle accumulate.

  Nota

  È possibile utilizzare l’estensione pgstattuple per calcolare l’aumento della dimensione nella tabella. Per ulteriori informazioni, consulta pgstattuple.

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

  Puoi controllare la tabella di log pg_repack's in pg_stat_all_tables per monitorare le modifiche applicate alla nuova tabella. pg_stat_all_tables.n_live_tupindica il numero di record che attendono di essere applicati alla nuova tabella. Per ulteriori informazioni, vedi 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’estensione pg_stat_statements per scoprire il tempo impiegato da ogni fase dell’operazione di nuova compressione. Ciò è utile in preparazione all’applicazione della stessa operazione di nuova compressione in un ambiente di produzione. Puoi modificare la clausola LIMIT 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)