El hinchamiento de la tabla y el índice se produce cuando la operación de VACUUM no mantiene el ritmo del lenguaje de manipulación de datos (DML) en una tabla. Parte del aumento de tamaño de la tabla y el índice es inevitable y, por lo general, aceptable, ya que las transacciones pueden reutilizar ese espacio. El bloat grave causa problemas de rendimiento, como tiempos de consulta más lentos, mayor E/S, uso menos eficiente del búfer compartido y la caché, y mayores costos de almacenamiento.
La mejor manera de estimar el aumento es usar el módulo pgstattuple. Sin embargo, usar pgstattuple es menos eficiente cuando la extensión debe leer todas las páginas de una relación. Si no puedes usar pgstattuple, puedes usar otros secuencias de comandos para estimar el bloat, aunque este enfoque proporciona menos precisión.
Cómo leer el resultado de pgstattuple
Cuando usas pgstattuple para informar las características de almacenamiento físico de una tabla o un índice, el resultado se muestra en el siguiente formato:
> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len | 7431888896
tuple_count | 66666666
tuple_len | 2796296271
tuple_percent | 37.63
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 3873153652
free_percent | 52.12
A continuación, se describe el resultado:
table_len(7431888896)- Es el tamaño total de la tabla en bytes. Incluye todas las tuplas activas, las tuplas inactivas y el espacio libre. En este caso,
large_tableocupa aproximadamente 7,088 GB. tuple_count(66666666)- Representa la cantidad de tuplas activas (filas visibles y activas) que hay en la tabla. La tabla contiene aproximadamente 66 millones de filas activas.
tuple_len(2796296271)- La longitud total en bytes de todas las tuplas activas combinadas, que es de aproximadamente 2.6 GB.
tuple_percent(37.63)Es el porcentaje del
table_lenque ocupan las tuplas activas. Se calcula de la siguiente manera:(
tuple_len/table_len) * 100 = (2796296271/7431888896) * 100 ≈37.63porcentajeEste resultado indica que los datos activos usan menos del 40% del espacio en disco de la tabla.
dead_tuple_count(0)Es la cantidad de tuplas inactivas, es decir, filas que las operaciones de VACUUM aún no quitaron después de las actualizaciones o eliminaciones. Un valor de
0significa que la tabla está limpia y no tiene tuplas inactivas que esperan ser recuperadas. Esto suele sugerir queVACUUM, o autovacuum, se ejecuta de manera eficiente en la tabla.dead_tuple_len(0)Es la longitud total en bytes de todas las tuplas inactivas. De acuerdo con
dead_tuple_count, un valor de0significa que las filas inactivas no consumen espacio.dead_tuple_percent(0)Es el porcentaje del
table_lenque ocupan las tuplas muertas. Se calcula de la siguiente manera:(
dead_tuple_len/table_len) * 100 = (0/7431888896) * 100 =0porcentajefree_space(3873153652)Este valor representa la cantidad de espacio libre en las páginas de datos de la tabla. Puedes reutilizar este espacio para nuevas inserciones o actualizaciones sin necesidad de expandir la tabla. Esto indica una cantidad significativa de espacio libre, aproximadamente 3.7 GB.
free_percent(52.12)Este valor representa el porcentaje del
table_lenque es espacio libre. Puedes calcularlo de la siguiente manera:(
free_space/table_len) * 100 = (3873153652/7431888896) * 100 ≈52.12porcentaje
Estima el aumento del tamaño con consultas
Puedes estimar el aumento de tamaño con consultas en las tablas del catálogo del sistema, aunque este método es menos preciso que el uso de extensiones como pgstattuple. Si usas secuencias de comandos como PostgreSQL table bloat check, las estadísticas de tu tabla deben estar actualizadas. De lo contrario, la estimación será imprecisa.
Cuando comparas el resultado de una búsqueda con el módulo pgstattuple, las estimaciones son comparables, pero no son mediciones exactas de la expansión. El módulo sigue siendo el indicador más preciso de la expansión, ya que la consulta en SQL se basa en el motor de estadísticas acumulativas de PostgreSQL, mientras que el módulo lee las páginas de datos reales.
En el siguiente ejemplo, se compara el resultado de dos métodos diferentes para analizar la expansión de la tabla en PostgreSQL para una tabla llamada large_table.
> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len | 2475892736
tuple_count | 22000000
tuple_len | 922925931
tuple_percent | 37.28
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 1293696900
free_percent | 52.25
Vs.
-[ RECORD 1 ]+------------
databasename | postgres
schemaname | public
tablename | large_table
can_estimate | t
est_rows | 21965400
pct_bloat | 54
mb_bloat | 1269.24
table_mb | 2361.195
Exceso de direcciones
Puedes administrar el bloat de las siguientes maneras, cada una de las cuales tiene ventajas y desventajas, lo que requiere una planificación cuidadosa:
- Realiza una aspiración completa. Esta operación requiere un bloqueo de tabla completo durante la duración del proceso de limpieza y requiere tiempo de inactividad.
- Mueve los datos a una tabla nueva. Para usar este método, debes detener los cambios en la tabla para asegurarte de que no se transfieran a la nueva relación.
- Usa
pg_squeeze. Esta extensión mitiga el exceso de contenido en línea. La extensión crea una tabla sombra, inserta todos los datos y, luego, mantiene los cambios en la tabla con una ranura de replicación hasta el último paso, que consiste en reemplazar la tabla inflada por la tabla no inflada recién creada. Luego, solicita un bloqueo breve de la tabla para mover la tabla no inflada y reemplazar la tabla inflada. Este método requiere el doble de espacio del objeto durante la operación, pero es el menos intrusivo para el funcionamiento normal.