Compartir vía

Vacío completo mediante pg_repack en Azure Database for PostgreSQL: servidor flexible

  • Artículo

SE APLICA A: Azure Database for PostgreSQL con servidor flexible

En este artículo, aprenderá cómo utilizar pg_repack para eliminar la sobrecarga y mejorar el rendimiento del servidor flexible Azure Database for PostgreSQL. El sobredimensionamiento es la acumulación de datos innecesarios en tablas e índices debido a actualizaciones y eliminaciones frecuentes. La sobrecarga puede hacer que el tamaño de la base de datos aumente más de lo esperado y puede afectar gravemente al rendimiento de algunas consultas. Utilice pg_repack para recuperar el espacio desaprovechado y reorganizar los datos de forma más eficiente.

¿Qué es pg_repack?

pg_repack es una extensión de PostgreSQL que elimina la sobrecarga de tablas e índices y los reorganiza de forma más eficiente. pg_repack funciona creando una nueva copia de la tabla o índice de destino, aplicando los cambios que se hayan producido durante el proceso, y luego intercambiando las versiones antigua y nueva de forma atómica. pg_repack no requiere ningún tiempo de inactividad ni bloqueos de acceso exclusivos en la tabla o el índice procesados, excepto durante un breve periodo al principio y al final de la operación. Puede utilizar pg_repack para optimizar cualquier tabla o índice de datos de servidores flexibles de Azure Database for PostgreSQL.

¿Cómo se usa pg_repack?

Para utilizar pg_repack, debe instalar la extensión en su base de datos del servidor flexible de Azure Database for PostgreSQL, y luego ejecutar el comando pg_repack, especificando el nombre de la tabla o el índice que desea optimizar. La extensión adquiere bloqueos en la tabla o índice para evitar que se realicen otras operaciones mientras la optimización está en curso. Elimina la hinchazón y reorganiza los datos de forma más eficiente.

Cómo funciona el reempaquetado completo de tablas

Para realizar un reempaquetado completo de la tabla, la extensión sigue estos pasos:

  1. Crea una tabla de registro para registrar los cambios realizados en la tabla original.
  2. Agrega un desencadenador a la tabla original, registrando INSERT, UPDATE y DELETE en la tabla de registro.
  3. Crea una nueva tabla que contiene todas las filas de la tabla original.
  4. Crea índices en la nueva tabla.
  5. Aplica todos los cambios registrados en la tabla de registro a la nueva tabla.
  6. Intercambia las tablas originales y las nuevas, incluidos los índices y las tablas de mensaje emergente.
  7. Suelta la tabla original.

Durante estos pasos, pg_repack solo mantiene un bloqueo de acceso exclusivo durante un breve periodo, durante la configuración inicial (pasos 1 y 2) y de nuevo durante la fase final de intercambio y entrega (pasos 6 y 7). Durante el resto del tiempo, pg_repack solo necesita mantener un bloqueo de acceso compartido en la tabla original, permitiendo que los INSERT, UPDATE y DELETE procedan como de costumbre.

Limitaciones

pg_repack tiene algunas limitaciones que debes conocer antes de utilizarlo:

  • La tabla de destino debe tener un índice PRIMARY KEY o UNIQUE en una columna NOT NULL para que la operación se realice correctamente.
  • Mientras pg_repack se esté ejecutando, no podrá ejecutar ningún comando del lenguaje de definición de datos (DDL) en las tablas de destino, excepto VACUUM o ANALYZE. Para garantizar el cumplimiento de estas restricciones, pg_repack mantiene un bloqueo de acceso compartido en la tabla de destino durante el reempaquetado completo de la tabla.

Configurar

Requisitos previos

  1. Configure la extensión pg_repack permitiendo la lista y creando la extensión.

Crear la aplicación cliente pg_repack

El uso de esta extensión requiere una aplicación cliente que puede crear e instalar en una instancia de Ubuntu.

Para instalar la versión 1.4.7 de pg_repack, ejecute el siguiente script bash en una máquina 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

Uso de pg_repack

Ejemplo de cómo ejecutar pg_repack en una tabla llamada información en un esquema público dentro de la instancia de servidor flexible de Azure Database for PostgreSQL con punto de conexión pgserver.postgres.database.azure.com, nombre de usuario azureuser y base de datos foo utilizando el siguiente comando.

  1. Utilizando el cliente de su preferencia, conéctese a la instancia de servidor flexible de Azure Database for PostgreSQL. En este ejemplo utilizamos psql.

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

  2. Encuentre la versión de la extensión pg_repack instalada en la base de datos.

    SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_repack';

  3. La versión de la extensión debe coincidir con la versión de la aplicación cliente, que puede comprobar ejecutando este comando:

    azureuser@azureuser:~$ pg_repack --version

  4. Ejecute el cliente pg_repack contra una tabla llamada información que existe en la base de datos foo.

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

Opciones de pg_repack

Opciones pg_repack útiles para cargas de trabajo de producción:

  • -k, --no-superuser-check: Omite las comprobaciones de súper usuario en el cliente. Esta configuración es útil para el uso de pg_repack en plataformas que admiten su ejecución como no súper usuarios, como las instancias de servidor flexible de Azure Database for PostgreSQL.

  • -j, --jobs: Crear el número especificado de conexiones adicionales al servidor flexible de Azure Database for PostgreSQL y utilizar estas conexiones adicionales para paralelizar la reconstrucción de índices en cada tabla. Las compilaciones de índices paralelas solo se admiten para volver a empaquetar tablas completas.

  • --index o --only de índices: si su instancia de servidor flexible de Azure Database for PostgreSQL tiene núcleos y E/S de disco adicionales disponibles, el uso de esta opción puede ser una forma útil de acelerar pg_repack.

  • -D, --no-kill-backend: en lugar de ejecutar clientes backend que estén ejecutando consultas de bloqueo, omite el reempaquetado de una tabla si no se puede adquirir el bloqueo tras esperar el tiempo especificado en --wait-timeout. De forma predeterminada, --wait-timeout está establecido en 60 horas. El valor predeterminado para este parámetro es false.

  • -E LEVEL, --elevel=LEVEL: elija el nivel de mensaje de salida de DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL y PANIC. El valor predeterminado es INFO.

Para comprender todas las opciones, consulte la documentación de pg_repack.

Preguntas más frecuentes

¿Es pg_repack una extensión o un ejecutable del lado del cliente como psql o pg_dump?

pg_repack es en realidad ambas cosas. pg_repack/lib contiene el código de la extensión, incluidos los artefactos de esquema y SQL que crea, y la biblioteca C que implementa el código de varias de esas funciones.

Por otro lado, pg_repack/bin tiene el código para la aplicación cliente, que sabe cómo interactuar con los elementos de programación implementados en la extensión. El objetivo de esta aplicación cliente es facilitar la interacción con las distintas interfaces de la extensión del servidor. Ofrece al usuario algunas opciones de línea de comandos más fáciles de entender. La aplicación cliente es inútil sin la extensión creada en la base de datos a la que apunta. La extensión del lado del servidor por sí sola sería totalmente funcional, pero requeriría que el usuario entendiera un complicado patrón de interacción. Ese patrón consistiría en ejecutar consultas para recuperar datos que se utilizan como entrada para funciones implementadas por la extensión, etc.

Contenido relacionado