Reducción de la sobrecarga en tablas e índices con la extensión pg_repack

• Creación inicial de la tabla de registro: se crea una tabla de registro para registrar los cambios que se producen durante la copia inicial de los datos, como se muestra en el siguiente ejemplo:

  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 final de intercambio y eliminación.

Las siguientes recomendaciones se aplican al eliminar el sobredimensionamiento de las tablas e índices mediante la extensión pg_repack:

• Realice el reempaquetado fuera del horario laboral o durante un período de mantenimiento para minimizar su impacto en el rendimiento de otras actividades de la base de datos.

• Monitorice de cerca las sesiones de bloqueo durante la actividad de reconstrucción y asegúrese de que no haya ninguna actividad en la tabla original que pueda bloquear pg_repack, especialmente durante la fase final de intercambio y eliminación, cuando es necesario bloquear exclusivamente la tabla original. Para obtener más información, consulte Identificar qué bloquea una consulta.

  Si ve una sesión que bloquee, puede finalizarla mediante el siguiente comando tras estudiarla detenidamente. Esto ayuda a continuar con pg_repack para terminar la reconstrucción:

  SELECT pg_terminate_backend(pid);

• Al aplicar los cambios acumulados de la tabla de registro pg_repack's en sistemas con una tasa de transacciones muy alta, es posible que el proceso de solicitud no pueda mantener la tasa de cambios. En esos casos, pg_repack no podría completar el proceso de aplicación. Para obtener más información, consulte Monitorización de la nueva tabla durante el reempaquetado. Si los índices están muy sobredimensionados, una solución alternativa es volver a empaquetar únicamente los índices. Esto también ayuda a que los ciclos de limpieza de índices de VACUUM finalicen más rápido.

  Puede omitir la fase de limpieza de índices mediante el VACUUM manual de la versión 12 de PostgreSQL, y se omite automáticamente durante el autovacuum de emergencia de la versión 14 de PostgreSQL. Esto ayuda a que VACUUM se complete más rápido sin eliminar el sobredimensionamiento del índice y solo está diseñado para situaciones de emergencia, como evitar que el VACUUM se acumule. Para obtener más información, consulte Evitar la sobrecarga en los índices en la Guía del usuario de Amazon Aurora.

• La tabla debe tener una restricción de PRIMARY KEY o una UNIQUE que no sea null.

• La versión de la extensión debe ser la misma tanto para el cliente como para el servidor.

• Asegúrese de que la instancia de RDS tenga más FreeStorageSpace que el tamaño total de la tabla sin la sobrecarga. Como ejemplo, considere el tamaño total de la tabla, incluidos el TOAST y los índices, como de 2 TB, y el tamaño total de la tabla como 1 TB. El FreeStorageSpace requerido debe ser superior al valor devuelto por el siguiente cálculo:

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

  Puede utilizar la siguiente consulta para comprobar el tamaño total de la tabla y utilizar pgstattuple para derivar la sobrecarga. Para obtener más información, consulte Diagnóstico de sobrecarga de tablas e índices en la Guía del usuario de Amazon Aurora.

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

  Este espacio se recupera una vez finalizada la actividad.

• Asegúrese de que la instancia de RDS tenga suficiente capacidad de procesamiento y E/S para gestionar la operación de reempaquetado. Podría considerar la posibilidad de escalar verticalmente la clase de instancia para lograr un equilibrio óptimo del rendimiento.

• El tamaño de la base de datos se incrementa en función del tamaño total de la tabla, menos la sobrecarga, hasta la fase de intercambio y eliminación del reempaquetado. Puede monitorizar la tasa de crecimiento del tamaño de la base de datos, calcular la velocidad de reempaquetado y estimar aproximadamente el tiempo que se tarda en completar la transferencia inicial de datos.

  Como ejemplo, considere que el tamaño total de la tabla es de 2 TB, el tamaño de la base de datos es de 4 TB y la sobrecarga total de la tabla es de 1 TB. El valor del tamaño total de la base de datos devuelto por el cálculo al final de la operación de reempaquetado es el siguiente:

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

  Puede estimar aproximadamente la velocidad de la operación de reempaquetado muestreando la tasa de crecimiento en bytes entre dos puntos en el tiempo. Si la tasa de crecimiento es de 1 GB por minuto, la operación inicial de creación de la tabla puede tardar 1000 minutos o 16,6 horas aproximadamente en completarse. Además de la construcción inicial de la tabla, pg_repack también necesita aplicar los cambios acumulados. El tiempo que tarda depende del ritmo de aplicación de los cambios continuos más los acumulados.

  nota

  Puede usar la extensión pgstattuple para calcular la sobrecarga en la tabla. Para obtener más información, consulte pgstattuple.

• El número de filas de la tabla de registro pg_repack's, según el esquema de reempaquetado, representa el volumen de cambios pendientes de aplicarse a la nueva tabla tras la carga inicial.

  Puede consultar la tabla de registro pg_repack's en pg_stat_all_tables para supervisar los cambios aplicados a la nueva tabla. pg_stat_all_tables.n_live_tup indica el número de registros pendientes de ser aplicados a la nueva tabla. Para obtener más información, consulte 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
          

• Puede utilizar la extensión pg_stat_statements para determinar el tiempo que tarda cada paso de la operación de reempaquetado. Esto es útil para prepararse para aplicar la misma operación de reempaque en un entorno de producción. Puede ajustar la cláusula LIMIT para ampliar aún más la salida.

  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)