Compartilhar via

Vácuo completo com o pg_repack no Banco de Dados do Azure para PostgreSQL – Servidor Flexível

  • Artigo

APLICA-SE A: Banco de dados do Azure para PostgreSQL – Servidor Flexível

Neste artigo, você aprenderá a usar o pg_repack para remover a sobrecarga e melhorar o desempenho do seu servidor flexível do Banco de Dados do Azure para PostgreSQL. A sobrecarga são os dados desnecessários acumulados em tabelas e índices devido a atualizações e exclusões frequentes. O bloat pode fazer com que o tamanho do banco de dados cresça maior do que o esperado e pode afetar severamente o desempenho de algumas consultas. Use o pg_repack, você pode recuperar o espaço desperdiçado e reorganizar os dados com mais eficiência.

O que é pg_repack?

pg_repack é uma extensão postgreSQL que remove a sobrecarga de tabelas e índices e os reorganiza com mais eficiência. pg_repack funciona criando uma nova cópia da tabela ou índice de destino, aplicando todas as alterações que ocorreram durante o processo e trocando as versões antigas e novas atomicamente. pg_repack não requer nenhum tempo de inatividade ou bloqueios de acesso exclusivos na tabela ou índice processado, exceto por um breve período no início e no final da operação. Agora você pode usar o pg_repack para otimizar qualquer tabela ou índice em seu servidor flexível do Banco de Dados do Azure para PostgreSQL.

Como usar pg_repack?

Para usar o pg_repack, você precisa instalar a extensão no banco de dados do servidor flexível do Banco de Dados do Azure para PostgreSQL e, em seguida, executar o comando pg_repack, especificando o nome da tabela ou o índice que deseja otimizar. A extensão aplica bloqueios na tabela ou no índice para impedir que outras operações sejam executadas enquanto a otimização está em andamento. Ela remove a sobrecarga e reorganiza os dados com mais eficiência.

Como funciona a recompactação de tabela completa

Para executar um reempacotar uma tabela completa, a extensão segue estas etapas:

  1. Cria uma tabela de log para registrar as alterações feitas na tabela original.
  2. Adiciona um gatilho à tabela original, registrando INSERTs, UPDATEs e DELETEs na tabela de log.
  3. Cria uma nova tabela que contém todas as linhas na tabela original.
  4. Cria índices na nova tabela.
  5. Aplica todas as alterações registradas na tabela de log à nova tabela.
  6. Troca as tabelas originais e novas, incluindo índices e tabelas do sistema.
  7. Descarta a tabela original.

Durante essas etapas, pg_repack mantém apenas um bloqueio de acesso exclusivo por um curto período, durante a configuração inicial (etapas 1 e 2) e novamente durante a fase final de troca e queda (etapas 6 e 7). Pelo resto do tempo, pg_repack só precisa manter um bloqueio de acesso compartilhado na tabela original, permitindo que INSERTs, UPDATEs e DELETEs prossiga como de costume.

Limitações

pg_repack tem algumas limitações sobre as quais você deve estar ciente antes de usá-lo:

  • A tabela de destino deve ter uma CHAVE PRIMÁRIA ou um índice UNIQUE em uma coluna NOT NULL para que a operação seja bem-sucedida.
  • Enquanto pg_repack estiver em execução, você não poderá executar nenhum comando DDL (Linguagem de Definição de Dados) nas tabelas de destino, exceto para VACUUM ou ANALYZE. Para garantir que essas restrições sejam impostas, pg_repack mantém um bloqueio de acesso compartilhado na tabela de destino durante uma reempacote de tabela completa.

Instalação

Pré-requisitos

  1. Configure a extensão pg_repack adicionando a extensão à lista de permissões e criando-a.

Criar pg_repack aplicativo cliente

O uso dessa extensão requer um aplicativo cliente que você pode criar e instalar em uma instância do Ubuntu.

Para instalar a versão 1.4.7 do pg_repack, execute o seguinte script bash em um computador 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

Use pg_repack

Exemplo de como executar o pg_repack em uma tabela chamada info em um esquema público dentro da instância do servidor flexível do Banco de Dados do Azure para PostgreSQL com o ponto de extremidade pgserver.postgres.database.azure.com, nome de usuário azureuser e banco de dados foo usando o seguinte comando.

  1. Usando o cliente de sua preferência, conecte-se à instância de servidor flexível do Banco de Dados do Azure para PostgreSQL. Neste exemplo, usamos o ZRS.

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

  2. Localize a versão da extensão pg_repack instalada no banco de dados.

    SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_repack';

  3. A versão da extensão deve corresponder à versão do aplicativo cliente, que você pode verificar executando este comando:

    azureuser@azureuser:~$ pg_repack --version

  4. Execute o cliente pg_repack em uma tabela chamada info que existe no banco de dados foo.

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

Opções de pg_repack

Opções de pg_repack úteis para cargas de trabalho de produção:

  • -k, --no-superuser-check: ignorar as verificações do superusuário no cliente. Essa configuração é útil para usar o pg_repack em plataformas que dão suporte para sua execução como não superusuários, como instâncias de servidor flexível do Banco de Dados do Azure para PostgreSQL.

  • -j, --jobs: cria o número especificado de conexões extras para o servidor flexível do Banco de Dados do Azure para PostgreSQL e usa essas conexões extras para paralelizar a reconstrução de índices em cada tabela. Os builds de índice paralelo só têm suporte para recompactação de tabela completa.

  • opções de índices--index ou --only: se a instância do servidor flexível do Banco de Dados do Azure para PostgreSQL tiver núcleos extras e E/S de disco disponíveis, o uso dessa opção poderá ser uma maneira útil de acelerar pg_repack.

  • -D, --no-kill-backend: em vez de encerrar clientes de back-end que estão executando consultas de bloqueio, ignore o reempacotamento de uma tabela se o bloqueio não puder ser adquirido depois de aguardar o tempo especificado no --wait-timeout. Por padrão, --wait-timeout é definido como 60 segundos. O valor padrão para esse parâmetro é false.

  • -E LEVEL, --elevel=LEVEL: escolha o nível da mensagem de saída de DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATALe PANIC. O padrão é INFO.

Para entender todas as opções, confira a documentação de pg_repack.

Perguntas frequentes

A pg_repack uma extensão ou um executável do lado do cliente, como psql ou pg_dump?

pg_repack é, na verdade, ambos. pg_repack/lib tem o código da extensão, incluindo o esquema e os artefatos SQL que ele cria, e a biblioteca C que implementa o código de várias dessas funções.

Por outro lado, pg_repack/bin tem o código do aplicativo cliente, que sabe como interagir com os elementos de programação implementados na extensão. Esse aplicativo cliente tem como objetivo facilitar a complexidade da interação com as diferentes interfaces apresentadas pela extensão do lado do servidor. Ele oferece ao usuário algumas opções de linha de comando que são mais fáceis de entender. O aplicativo cliente é inútil sem a extensão criada no banco de dados para o qual ele está apontado. A extensão do lado do servidor por conta própria seria totalmente funcional, mas exigiria que o usuário entendesse um padrão de interação complicado. Esse padrão consistiria na execução de consultas para recuperar dados usados como entrada para funções implementadas pela extensão etc.

Conteúdo relacionado