Condividi tramite

Vuoto completo con pg_repack in Database di Azure per PostgreSQL - Server flessibile

  • Articolo

SI APPLICA A: Database di Azure per PostgreSQL - Server flessibile

Questo articolo illustra come usare pg_repack per rimuovere bloat e migliorare le prestazioni del server flessibile Database di Azure per PostgreSQL. Il bloat è l'accumulo di dati non necessari nelle tabelle e negli indici a causa di aggiornamenti e cancellazioni frequenti. Bloat può causare un aumento delle dimensioni del database superiore al previsto e può influire gravemente sulle prestazioni di alcune query. Usare pg_repack per recuperare lo spazio sprecato e riorganizzare i dati in modo più efficiente.

Che cos'è pg_repack?

pg_repack è un'estensione PostgreSQL che rimuove bloat dalle tabelle e dagli indici e li riorganizza in modo più efficiente. pg_repack funziona creando una nuova copia della tabella o dell'indice di destinazione, applicando eventuali modifiche apportate durante il processo e quindi scambiando le versioni precedenti e nuove in modo atomico. pg_repack non richiede tempi di inattività o blocchi di accesso esclusivi per la tabella o l'indice elaborati, ad eccezione di un breve periodo all'inizio e alla fine dell'operazione. È possibile usare pg_repack per ottimizzare qualsiasi tabella o indice nei database del server flessibile Database di Azure per PostgreSQL.

Come usare pg_repack?

Per usare pg_repack, è necessario installare l'estensione nel database del server flessibile Database di Azure per PostgreSQL e quindi eseguire il pg_repack comando specificando il nome o l'indice della tabella da ottimizzare. L'estensione acquisisce blocchi sulla tabella o sull'indice per impedire l'esecuzione di altre operazioni mentre l'ottimizzazione è in corso. Rimuove il bloat e riorganizza i dati in modo più efficiente.

Funzionamento della ricompressione completa della tabella

Per eseguire una ricompressione completa della tabella, l'estensione segue questa procedura:

  1. Crea una tabella di log per registrare le modifiche apportate alla tabella originale.
  2. Aggiunge un trigger alla tabella originale, la registrazione di INSERT, UPDATEs e DELETEs nella tabella dei log.
  3. Crea una nuova tabella contenente tutte le righe della tabella originale.
  4. Compila gli indici nella nuova tabella.
  5. Applica tutte le modifiche registrate nella tabella di log alla nuova tabella.
  6. Scambia le tabelle originali e nuove, inclusi gli indici e le tabelle di tipo avviso popup.
  7. Elimina la tabella originale.

Durante questi passaggi, pg_repack mantiene solo un blocco di accesso esclusivo per un breve periodo, durante la configurazione iniziale (passaggi 1 e 2) e di nuovo durante la fase finale di scambio e rilascio (passaggi 6 e 7). Per il resto del tempo, pg_repack deve contenere solo un blocco di accesso condiviso sulla tabella originale, consentendo agli INSERT, agli UPDATEs e ai DELETes di procedere come di consueto.

Limiti

pg_repack presenta alcune limitazioni da tenere presenti prima di usarlo:

  • La tabella di destinazione deve avere una chiave PRIMARIA o un indice UNIQUE in una colonna NOT NULL affinché l'operazione abbia esito positivo.
  • Durante pg_repack l'esecuzione, non è possibile eseguire comandi DDL (Data Definition Language) nelle tabelle di destinazione, ad eccezione di VACUUM o ANALYZE. Per garantire l'applicazione di queste restrizioni, pg_repack mantiene un blocco di accesso condiviso nella tabella di destinazione durante una ricompressione completa della tabella.

Impostazione

Prerequisiti

  1. Configurare l'estensione consentendo l'elenco pg_repack e creando l'estensione.

Compilare pg_repack'applicazione client

L'uso di questa estensione richiede un'applicazione client che è possibile compilare e installare in un'istanza di Ubuntu.

Per installare la versione 1.4.7 di pg_repack, eseguire lo script bash seguente in un computer Ubuntu.

# Create the file repository configuration
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists
sudo apt-get update
# Install required packages to build the code
sudo apt-get install -y postgresql-server-dev-14 unzip make gcc libssl-dev liblz4-dev zlib1g-dev libreadline-dev libzstd-dev
# Download compressed version of build tree for version 1.4.7 of pg_repack
wget 'https://api.pgxn.org/dist/pg_repack/1.4.7/pg_repack-1.4.7.zip'
# Uncompress build tree
unzip pg_repack-1.4.7.zip
# Set current directory to where build tree was uncompressed
cd pg_repack-1.4.7
# Build code
sudo make
# Copy resulting binaries to /usr/local/bin
sudo cp bin/pg_repack /usr/local/bin
# Run pg_repack to check its version
pg_repack --version

Usare pg_repack

Esempio di come eseguire pg_repack in una tabella denominata info in uno schema pubblico all'interno dell'istanza del server flessibile Database di Azure per PostgreSQL con endpoint pgserver.postgres.database.azure.com, nome utente azureuser e database foo usando il comando seguente.

  1. Usando il client delle preferenze, connettersi all'istanza del server flessibile Database di Azure per PostgreSQL. In questo esempio viene usato psql.

        psql "host=<server>.postgres.database.azure.com port=5432 dbname=<database> user=<user> password=<password> sslmode=require"

  2. Trovare la versione dell'estensione pg_repack installata nel database.

    SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_repack';

  3. La versione dell'estensione deve corrispondere alla versione dell'applicazione client, che è possibile controllare eseguendo questo comando:

    azureuser@azureuser:~$ pg_repack --version

  4. Eseguire pg_repack il client su una tabella denominata info esistente nel database foo.

    pg_repack --host=<server>.postgres.database.azure.com --username=<user> --dbname=<database> --table=info --jobs=2 --no-kill-backend --no-superuser-check

opzioni di pg_repack

Opzioni utili pg_repack per i carichi di lavoro di produzione:

  • -k, --no-superuser-check: ignorare i controlli dell'utente con privilegi avanzati nel client. Questa impostazione è utile per l'uso pg_repack su piattaforme che supportano l'esecuzione come utenti non con privilegi avanzati, ad esempio Database di Azure per PostgreSQL istanze di server flessibili.

  • -j, --jobs: creare il numero specificato di connessioni aggiuntive a Database di Azure per PostgreSQL server flessibile e usare queste connessioni aggiuntive per parallelizzare la ricompilazione degli indici in ogni tabella. Le compilazioni di indici paralleli sono supportate solo per i repack full-table.

  • --indexo --only opzioni di indicizzazione: se l'istanza del server flessibile Database di Azure per PostgreSQL dispone di core aggiuntivi e I/O su disco disponibili, l'uso di questa opzione può essere un modo utile per velocizzare pg_repack.

  • -D, --no-kill-backend: invece di eliminare i client back-end che eseguono query di blocco, ignorare il repacking di una tabella se il blocco non può essere acquisito dopo l'attesa del tempo specificato in --wait-timeout. Per impostazione predefinita, --wait-timeout è impostato su 60 secondi. Il valore predefinito per questo parametro è false.

  • -E LEVEL, --elevel=LEVEL: scegliere il livello di messaggio di output da DEBUG, INFO, NOTICEWARNING, ERROR, LOG, FATAL, e PANIC. Il valore predefinito è INFO.

Per comprendere tutte le opzioni, vedere la documentazione di pg_repack.

Domande frequenti

È pg_repack un'estensione o un eseguibile lato client come psql o pg_dump?

pg_repack in realtà è entrambi. pg_repack/lib include il codice per l'estensione, inclusi gli elementi dello schema e SQL creati e la libreria C che implementa il codice di diverse di queste funzioni.

D'altra parte, pg_repack/bin ha il codice per l'applicazione client, che sa come interagire con gli elementi programmabilità implementati nell'estensione. Questa applicazione client mira a semplificare la complessità dell'interazione con le diverse interfacce rilevate dall'estensione lato server. Offre all'utente alcune opzioni della riga di comando che sono più facili da comprendere. L'applicazione client è inutile senza l'estensione creata nel database a cui punta. L'estensione lato server autonomamente sarebbe completamente funzionale, ma richiederebbe all'utente di comprendere un modello di interazione complicato. Tale modello consiste nell'esecuzione di query per recuperare i dati usati come input per le funzioni implementate dall'estensione e così via.

Contenuto correlato