Succión y análisis manuales de las tablas

Si la base de datos se succiona mediante el proceso de autovacuum, se recomienda evitar realizar succiones manuales en toda la base de datos con demasiada frecuencia. Una succión manual puede provocar cargas de E/S innecesarias o picos de CPU y, además, es posible que no se eliminen las tuplas inactivas. Realice succiones manuales tabla por tabla solo si es realmente necesario; por ejemplo, cuando la proporción de tuplas activas e inactivas sea baja o cuando haya espacios largos entre cada autovacuum. Además, debe realizar succiones manuales cuando la actividad del usuario sea mínima.

Autovacuum también mantiene actualizadas las estadísticas de una tabla. Al ejecutar el comando ANALYZE manualmente, vuelve a crear estas estadísticas en lugar de actualizarlas. La nueva creación de las estadísticas mediante el proceso habitual de autovacuum cuando ya están actualizadas puede provocar la utilización de los recursos del sistema.

Se recomienda que ejecute los comandos VACUUM y ANALYZE manualmente en las siguientes situaciones:

Para reducir el impacto de E/S al ejecutar los comandos VACUUM y ANALYZE en las actividades simultáneas de la base de datos, puede utilizar el parámetro vacuum_cost_delay. En muchas situaciones, los comandos de mantenimiento como VACUUM y ANALYZE no tienen por qué finalizarse rápidamente. Sin embargo, estos comandos no deberían interferir con la capacidad del sistema para realizar otras operaciones en la base de datos. Para evitarlo, puede activar los retardos de succión basados en los costos mediante el parámetro vacuum_cost_delay. Este parámetro está desactivado de forma predeterminada para los comandos VACUUM que se ejecutan manualmente. Para activarlo, configúrelo en un valor distinto de cero.

Ejecución de operaciones de succión y limpieza en paralelo

La opción PARALLEL del comando VACUUM utiliza trabajos paralelos para las fases de succión y limpieza de índices y está desactivada de forma predeterminada. El número de trabajos paralelos (el grado de paralelismo) viene determinado por el número de índices de la tabla, y el usuario puede especificar este número. Si ejecuta operaciones VACUUM paralelas sin un argumento entero, el grado de paralelismo se calcula en función del número de índices de la tabla.

Los siguientes parámetros lo ayudan a configurar la succión paralela en Amazon RDS para PostgreSQL y Aurora compatible con PostgreSQL:

En el siguiente ejemplo se ilustra el comportamiento de la base de datos cuando se usa VACUUM y ANALYZE manualmente en una base de datos.

Esta es una tabla de ejemplo en la que se ha desactivado autovacuum (solo con fines ilustrativos; no se recomienda desactivarlo):

create table t1 ( a int, b int, c int );
alter table t1 set (autovacuum_enabled=false);

apgl=> \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | integer | | | | plain | |
Access method: heap
Options: autovacuum_enabled=false

Agregue 1 millón de filas a la tabla t1:

apgl=> select count(*) from t1;
count
1000000
(1 row)

Estadísticas de la tabla t1:

select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+--------
relid         | 914744
schemaname    | public
relname       | t1
seq_scan      | 0
seq_tup_read  | 0
idx_scan      |
idx_tup_fetch |
n_tup_ins     | 1000000
n_tup_upd     | 0
n_tup_del     | 0
n_tup_hot_upd | 0
n_live_tup    | 1000000
n_dead_tup    | 0
n_mod_since_analyze | 1000000
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |
vacuum_count     | 0
autovacuum_count | 0
analyze_count    | 0
autoanalyze_count | 0

Agregue un índice:

create index i2 on t1 (b,a);

Ejecute el comando EXPLAIN (plan 1):

Bitmap Heap Scan on t1 (cost=10521.17..14072.67 rows=5000 width=4)
Recheck Cond: (a = 5)
→ Bitmap Index Scan on i2 (cost=0.00..10519.92 rows=5000 width=0)
Index Cond: (a = 5)
(4 rows)

Ejecute el comando EXPLAIN ANALYZE (plan 2):

explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.023..0.024 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=4
→ Bitmap Index Scan on i2 (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.054 ms
Execution Time: 0.076 ms
(9 rows)

Los comandos EXPLAIN y EXPLAIN ANALYZE muestran planes diferentes, ya que autovacuum estaba desactivado en la tabla y el comando ANALYZE no se ejecutó manualmente. Ahora vamos a actualizar un valor de la tabla y volveremos a crear el plan EXPLAIN ANALYZE:

update t1 set a=8 where b=5;
explain (analyze,buffers,costs off) select a from t1 where b = 5;

El comando EXPLAIN ANALYZE (plan 3) ahora muestra:

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.075..0.076 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=5
→ Bitmap Index Scan on i2 (actual time=0.017..0.017 rows=2 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.053 ms
Execution Time: 0.125 ms

Si compara los costos entre el plan 2 y el plan 3, verá las diferencias en el tiempo de planificación y ejecución, ya que aún no hemos recopilado estadísticas.

Ahora ejecutemos ANALYZE manualmente en la tabla y, a continuación, comprobemos las estadísticas y volvamos a crear el plan:

apgl=> analyze t1
apgl→ ;
ANALYZE
Time: 212.223 ms

apgl=> select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+------------------------------
relid          | 914744
schemaname     | public
relname        | t1
seq_scan       | 3
seq_tup_read   | 1000000
idx_scan       | 3
idx_tup_fetch  | 3
n_tup_ins      | 1000000
n_tup_upd      | 1
n_tup_del      | 0
n_tup_hot_upd  | 0
n_live_tup     | 1000000
n_dead_tup     | 1
n_mod_since_analyze | 0
last_vacuum       |
last_autovacuum   |
last_analyze      | 2023-04-15 11:39:02.075089+00
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 1
autoanalyze_count | 0

Time: 148.347 ms

Ejecute el comando EXPLAIN ANALYZE (plan 4):

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Index Only Scan using i2 on t1 (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: (b = 5)
Heap Fetches: 1
Buffers: shared hit=4
Planning Time: 0.056 ms
Execution Time: 0.068 ms
(6 rows)

Time: 138.462 ms

Si compara todos los resultados del plan después de analizar manualmente la tabla y recopilar estadísticas, observará que el plan 4 del optimizador es mejor que los demás y, además, reduce el tiempo de ejecución de las consultas. En este ejemplo se muestra la importancia de realizar actividades de mantenimiento en la base de datos.

Reescritura de una tabla completa con VACUUM FULL

Al ejecutar el comando VACUUM con el parámetro FULL, se reescribe todo el contenido de una tabla en un nuevo archivo de disco sin espacio adicional y el espacio no utilizado se devuelve al sistema operativo. Esta operación es mucho más lenta y requiere un bloqueo ACCESS EXCLUSIVE en cada tabla. También requiere espacio adicional en el disco, ya que escribe una nueva copia de la tabla y no libera la copia anterior hasta que se completa la operación.

VACUUM FULL puede ser de utilidad en los siguientes casos:

Si la base de datos puede tolerar el tiempo de inactividad, le recomendamos que utilice VACUUM FULL cuando tenga tablas sin claves principales.

Como VACUUM FULL requiere más bloqueos que otras operaciones, es más caro ejecutarlo en las bases de datos cruciales. Para reemplazar este método, puede usar la extensión pg_repack , que se describe en la siguiente sección. Esta opción es similar a VACUUM FULL, pero requiere un bloqueo mínimo, y es compatible tanto con Amazon RDS para PostgreSQL como con Aurora compatible con PostgreSQL.