Administración de autovacuum con índices de gran tamaño
Como parte de su funcionamiento, autovacuum realiza varias fases de vaciado
Para este proceso, compruebe primero el tamaño general del índice. A continuación, determine si hay índices que es posible que no se utilicen y que se puedan eliminar, tal y como se muestra en los siguientes ejemplos.
Para comprobar el tamaño de la tabla y sus índices
postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));pg_size_pretty 6404 MB (1 row)
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));pg_size_pretty 11 GB (1 row)
En este ejemplo, el tamaño de los índices es mayor que el de la tabla. Esta diferencia puede provocar problemas de rendimiento, ya que los índices están sobrecargados o no se utilizan, lo que afecta a las operaciones de autovacuum y de inserción.
Para comprobar si hay índices no utilizados
En la vista pg_stat_user_indexesidx_scan. En el siguiente ejemplo, los índices no utilizados tienen el valor 0 en idx_scan.
postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+------------------+-----------------------+----------+--------------+--------------- 16433 | 16454 | public | pgbench_accounts | index_f | 6 | 6 | 0 16433 | 16450 | public | pgbench_accounts | index_b | 3 | 199999 | 0 16433 | 16447 | public | pgbench_accounts | pgbench_accounts_pkey | 0 | 0 | 0 16433 | 16452 | public | pgbench_accounts | index_d | 0 | 0 | 0 16433 | 16453 | public | pgbench_accounts | index_e | 0 | 0 | 0 16433 | 16451 | public | pgbench_accounts | index_c | 0 | 0 | 0 16433 | 16449 | public | pgbench_accounts | index_a | 0 | 0 | 0 (7 rows)
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;schemaname | relname | indexrelname | idx_scan ------------+------------------+-----------------------+---------- public | pgbench_accounts | index_f | 6 public | pgbench_accounts | index_b | 3 public | pgbench_accounts | pgbench_accounts_pkey | 0 public | pgbench_accounts | index_d | 0 public | pgbench_accounts | index_e | 0 public | pgbench_accounts | index_c | 0 public | pgbench_accounts | index_a | 0 (7 rows)
nota
Estas estadísticas son incrementales desde el momento en que se restablecen las estadísticas. Supongamos que tiene un índice que solo se usa al final de un trimestre empresarial o solo para un informe específico. Es posible que este índice no se haya utilizado desde que se restablecieron las estadísticas. Para obtener más información, consulte Statistics Functions
Para comprobar cuándo se restablecieron por última vez las estadísticas de una base de datos, utilice pg_stat_database
postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';datname | stats_reset ----------+------------------------------- postgres | 2022-11-17 08:58:11.427224+00 (1 row)
Vaciado de una tabla lo más rápido posible
RDS para PostgreSQL 12 y versiones posteriores
Si tiene demasiados índices en una tabla grande, la instancia de base de datos podría estar a punto de reiniciar el identificador de transacción (XID), que es cuando el contador de XID vuelve a ponerse en cero. Si esta casilla no se marca, esta situación podría provocar la pérdida de datos. Sin embargo, puede vaciar rápidamente la tabla sin limpiar los índices. En RDS para PostgreSQL 12 y versiones posteriores, puede usar VACUUM con la cláusula INDEX_CLEANUP
postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;INFO: vacuuming "public.pgbench_accounts" INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517 Skipped 0 pages due to buffer pins, 0 frozen pages. CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
Si ya se está ejecutando una sesión de autovacuum, debe finalizarla para iniciar VACUUM manualmente. Para obtener información acerca de la ejecución de una inmovilización de vacío manual, consulte Realización de una inmovilización de vacío manual.
nota
Omitir la limpieza del índice con regularidad puede provocar una sobrecarga del índice, lo que repercute en el rendimiento general del análisis. Como práctica recomendada, use el procedimiento anterior solo para impedir que el identificador se reinicie.
RDS para PostgreSQL 11 y versiones anteriores
Sin embargo, en RDS para PostgreSQL 11 y versiones anteriores, la única forma de hacer que el vacío se realice más rápidamente es reducir el número de índices de una tabla. La eliminación de un índice puede afectar a los planes de consulta. Le recomendamos que primero borre los índices no utilizados y, a continuación, los índices cuando el reinicio de XID sea inminente. Una vez finalizado el proceso de vaciado, puede volver a crear estos índices.
