Buenos días. Estaba leyendo la documentación oficial de Postgres relacionada con el proceso de vacío y la rutina Reindex. Algunas oraciones no me quedaron claras, así que quiero aclararlas. (Documentación de Postgres para la versión 12)
En primer lugar. Entendí que autovacuum verifica la tabla en busca de tuplas muertas, almacena sus ubicaciones en una memoria especial llamada "maintenance_work_mem" y luego, cuando esta memoria está llena, elimina las páginas correspondientes en todos los índices que tienen referencias a esas ubicaciones. La documentación sobre reindex dice
Las páginas de índice de árbol B que se han quedado completamente vacías se recuperan para su reutilización. Sin embargo, todavía existe la posibilidad de un uso ineficiente del espacio: si se han eliminado todas las claves de índice de una página, excepto unas pocas, la página permanece asignada
La pregunta es. Si "la página permanece asignada", ¿significa que el vacío automático no devuelve el espacio físico de las páginas eliminadas dentro del índice al sistema operativo? Por ejemplo, el índice ocupa 1 GB de memoria. Eliminé todas menos una fila de la tabla y ejecuté el vacío. En este caso, el índice seguirá ocupando 1 Gb de memoria. ¿Tengo razón?
Sí para VACUUM (pero no para VACUUM FULL):
select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit (1 row) create table t(s text); CREATE TABLE insert into t select generate_series(1,300000)::text; INSERT 0 300000 select pg_size_pretty(pg_table_size('t')); pg_size_pretty ---------------- 10 MB (1 row) create index on t(s); CREATE INDEX select pg_size_pretty(pg_indexes_size('t')); pg_size_pretty ---------------- 6600 kB (1 row) delete from t where s <> '1'; DELETE 299999 select count(*) from t; count ------- 1 (1 row) select pg_size_pretty(pg_table_size('t')); pg_size_pretty ---------------- 10 MB (1 row) select pg_size_pretty(pg_indexes_size('t')); pg_size_pretty ---------------- 6600 kB (1 row) vacuum t; VACUUM select pg_size_pretty(pg_table_size('t')); pg_size_pretty ---------------- 48 kB (1 row) select pg_size_pretty(pg_indexes_size('t')); pg_size_pretty ---------------- 6600 kB (1 row) vacuum full t; VACUUM select pg_size_pretty(pg_table_size('t')); pg_size_pretty ---------------- 16 kB (1 row) select pg_size_pretty(pg_indexes_size('t')); pg_size_pretty ---------------- 16 kB (1 row)Y no para REINDEX:
select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit (1 row) create table t(s text); CREATE TABLE insert into t select generate_series(1,300000)::text; INSERT 0 300000 select pg_size_pretty(pg_table_size('t')); pg_size_pretty ---------------- 10 MB (1 row) create index on t(s); CREATE INDEX select pg_size_pretty(pg_indexes_size('t')); pg_size_pretty ---------------- 6600 kB (1 row) delete from t where s <> '1'; DELETE 299999 select count(*) from t; count ------- 1 (1 row) select pg_size_pretty(pg_table_size('t')); pg_size_pretty ---------------- 10 MB (1 row) select pg_size_pretty(pg_indexes_size('t')); pg_size_pretty ---------------- 6600 kB (1 row) reindex table t; REINDEX select pg_size_pretty(pg_table_size('t')); pg_size_pretty ---------------- 10 MB (1 row) select pg_size_pretty(pg_indexes_size('t')); pg_size_pretty ---------------- 16 kB (1 row)El README en src/backend/access/nbtree tiene mucha información detallada sobre esto. Las citas en esta respuesta son de allí.
Si realmente elimina todas las filas de la tabla menos una, se eliminarán casi todas las páginas del índice.
Consideramos eliminar una página completa del btree solo cuando se haya quedado completamente vacío de elementos. (Combinar páginas parcialmente llenas permitiría una mejor reutilización del espacio, pero parece poco práctico mover los elementos de datos existentes hacia la izquierda o hacia la derecha para que esto suceda; de ser así, un escaneo que se mueva en la dirección opuesta podría perder los elementos). Además, nunca elimine la página más a la derecha en un nivel de árbol (esta restricción simplifica los algoritmos transversales, como se explica a continuación). La eliminación de página siempre comienza desde una página de hoja vacía. Una página interna solo se puede eliminar como parte de la eliminación de un subárbol completo. Este es siempre un subárbol "delgado" que consta de una "cadena" de páginas internas más una página de una sola hoja. Hay una página en cada nivel del subárbol y cada nivel/página cubre el mismo espacio clave.
Sin embargo, el espacio no se libera para el sistema operativo:
Reclamar una página en realidad no cambia su estado en el disco; simplemente la registramos en el mapa de espacio libre de memoria compartida, desde el cual se entregará la próxima vez que se necesite una nueva página para una división de página.
El árbol se volverá "delgado", porque la profundidad de un índice nunca se reduce. PostgreSQL tiene una optimización para eso:
Debido a que nunca eliminamos la página más a la derecha de ningún nivel (y en particular, nunca eliminamos la raíz), es imposible que la altura del árbol disminuya. Después de eliminaciones masivas, podríamos tener un escenario en el que el árbol es "delgado", con varios niveles de una sola página debajo de la raíz. Las operaciones seguirán siendo correctas en este caso, pero desperdiciaríamos ciclos descendiendo a través de los niveles de una sola página. Para manejar esto, usamos una idea de Lanin y Shasha: hacemos un seguimiento del nivel de "raíz rápida", que es el nivel más bajo de una sola página. La página de metadatos mantiene un puntero a este nivel, así como a la verdadera raíz. Todas las operaciones ordinarias inician sus búsquedas en la raíz rápida, no en la raíz verdadera.
Si ejecuta REINDEX INDEX en el índice o VACUUM (FULL) en la tabla, se reconstruirá el índice y se liberará el espacio.