- A partir de una alerta por falta de espacio de almacenamiento, se hizo una limpieza en PostgreSQL y se recuperaron más de 70 GB de espacio no optimizado o sin usar sin borrar índices ni datos
- Al principio se revisaron técnicas habituales de recuperación como índices sin uso, bloat de tablas e índices,
REINDEX CONCURRENTLY, pg_repack y la deduplicación de B-Tree de PostgreSQL 13
- La diferencia decisiva fue que, a diferencia de Oracle, PostgreSQL también indexa valores NULL; al cambiar el índice completo de
cancelled_by_user_id, donde casi todo era NULL, por un partial index, pasó de 769 MB a menos de 5 MB
- Se buscaron índices de una sola columna con
pg_stats.null_frac alto para seleccionar candidatos a partial index con la condición WHERE column IS NOT NULL, y después de reiniciar los contadores estadísticos se verificó el uso real con pg_stat_all_indexes
- En Django, un
ForeignKey nullable puede crear por defecto un índice B-Tree, así que hay que indicar db_index=False y definir manualmente un índice parcial con models.Index(..., condition=Q(...__isnull=False))
Limpieza iniciada por una alerta de falta de espacio
- Cada pocos meses aparecía una alerta de falta de espacio en el monitoreo de la base de datos, y normalmente se resolvía agregando más almacenamiento
- En ese momento, por un período de menor carga del sistema, era una buena oportunidad para intentar una tarea de limpieza más pesada de lo habitual
- Al final, se recuperaron más de 70 GB de espacio no optimizado o sin usar sin eliminar índices ni borrar datos
- Solo con la reconstrucción habitual de índices y tablas ya se redujo bastante espacio, y luego un hallazgo inesperado permitió eliminar unos 20 GB adicionales de valores indexados sin uso
Las primeras causas sospechosas
-
Índices sin uso
- Los índices sin uso se crean para mejorar el rendimiento de consultas, pero ocupan espacio y pueden volver más lentos
INSERT y UPDATE
- En
pg_stat_all_indexes se buscaron índices con idx_scan = 0, idx_tup_read = 0, idx_tup_fetch = 0 para identificar índices que no habían sido escaneados ni usados para fetch desde el último reinicio de estadísticas
- Hay casos en los que parecen no usarse, pero no deben eliminarse
- Cuando el optimizador de PostgreSQL usa metadatos y no el índice en sí
- Índices que garantizan restricciones unique o primary key en tablas que no se actualizan con frecuencia
- Hay que revisar uno por uno si realmente pueden borrarse, y los contadores estadísticos se administran reiniciándolos después de la inspección
- En este caso, como ese trabajo ya se hacía periódicamente, no había índices sin uso para eliminar
-
Bloat de índices y tablas
- PostgreSQL, al actualizar filas, marca el tuple anterior como dead y agrega uno nuevo en otro espacio, por lo que puede generarse bloat
- El bloat afecta no solo a las tablas, sino también a los índices, haciendo que usen más almacenamiento del realmente necesario
- Como estimar el bloat no es trivial, se usaron consultas de la wiki de PostgreSQL y de
pgsql-bloat-estimation para estimar el bloat de tablas e índices B-Tree
Cómo reducir el bloat de índices
-
Recrear y reconstruir índices
- Para eliminar el bloat de un índice, hay que volver a crearlo
- Si se hace
drop y luego create directamente, puede crearse un índice nuevo ya optimizado
REINDEX de PostgreSQL reconstruye el índice existente en su lugar- Como la recreación normal bloquea la tabla e impide cambios, en producción es más adecuado usar
REINDEX INDEX CONCURRENTLY index_name
REINDEX CONCURRENTLY crea un índice nuevo con el sufijo _ccnew, sincroniza los cambios y, al terminar, lo intercambia por el índice viejo y elimina el anterior- Si la reconstrucción se interrumpe a mitad de camino, el nuevo índice puede quedar en estado invalid y ocupar espacio, así que hay que encontrar y eliminar índices
_ccnew cuyo indisvalid no sea verdadero
-
Deduplicación de B-Tree en PostgreSQL 13
- PostgreSQL 13 introdujo B-Tree Deduplication, que permite almacenar valores duplicados de forma más eficiente en índices B-Tree
- Hasta PostgreSQL 12, aunque hubiera muchos valores repetidos, cada uno se almacenaba repetidamente en las hojas del índice, lo que podía consumir mucho espacio
- Cuando la deduplicación está habilitada en PostgreSQL 13, los valores duplicados se almacenan una sola vez, lo que puede impactar mucho el tamaño de índices con muchas repeticiones
- En PostgreSQL 13,
deduplicate_items = ON es el valor por defecto; si se viene de una versión anterior, para obtener el beneficio completo hay que reconstruir los índices con REINDEX
- En un ejemplo de 1 millón de filas, el índice de una columna no única con muchos duplicados se redujo de 21 MB a 6840 kB con deduplicación, mientras que el índice de una columna única siguió en 21 MB
- En este caso, PostgreSQL 13 todavía era una versión nueva y el proveedor cloud no lo soportaba, así que no se pudo reducir espacio con deduplicación
Bloat de tablas y pg_repack
-
Limitaciones para eliminar bloat en tablas
- Las tablas también pueden sufrir bloat y fragmentación por los dead tuples
- A diferencia de los índices, las tablas no son fáciles de recrear: hay que crear una tabla nueva, mover datos, sincronizar, y recrear índices, restricciones y claves referenciales
VACUUM FULL table_name puede recuperar el espacio ocupado por bloat y dead tuples, pero requiere bloquear la tabla- Tanto la recreación de tablas como
VACUUM FULL pueden implicar mucho trabajo de desarrollo o downtime
-
Uso de pg_repack
- pg_repack es una extensión de PostgreSQL usada para reconstruir tablas e índices sin downtime
- Para usarla, se crea la extensión con
CREATE EXTENSION pg_repack; y se ejecuta desde consola con algo como pg_repack -k --table table_name db_name
pg_repack crea una tabla nueva, carga los datos originales mientras sincroniza los nuevos cambios, reconstruye también los índices y al final intercambia ambas tablas- Al aplicarlo en producción, hay varias restricciones
- Se necesita aproximadamente espacio de almacenamiento adicional equivalente al tamaño de la tabla y los índices a reconstruir
- Si el repack falla o se interrumpe manualmente, pueden quedar objetos intermedios y hacer falta una limpieza manual
- Si ya hay poco espacio disponible, el requerimiento de espacio adicional puede volverlo poco apropiado, así que conviene monitorear el espacio libre y planificarlo de antemano
Hallazgo inesperado: PostgreSQL también indexa NULL
- Incluso después de eliminar índices sin uso y ordenar el bloat de tablas e índices con técnicas habituales, todavía quedaba margen para reducir espacio
- Una de las tablas más grandes almacenaba datos de transacciones, incluyendo casos en que el usuario cancelaba después del pago y recibía reembolso
- La tabla de transacciones tenía foreign keys tanto para el usuario comprador como para el usuario que canceló, y ambos campos tenían índices B-Tree definidos
- El usuario comprador tenía restricción
NOT NULL, así que todas las filas tenían valor
- El usuario que canceló era nullable y, como las cancelaciones eran raras, la mayoría de los valores eran NULL
- Se esperaba que el índice del usuario que canceló fuera mucho más pequeño que el del comprador, pero en la práctica tenían el mismo tamaño
- En Oracle se aprende que NULLs are not indexed, pero en PostgreSQL los valores NULL también se indexan
- El índice completo original era el siguiente
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
- Se probó la hipótesis cambiándolo por un partial index que excluye los valores NULL
DROP INDEX transaction_cancelled_by_ix;
CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
- Después de reindexar, el tamaño del índice completo era de 769 MB y más del 99% de los valores eran NULL
- El partial index que excluía NULL quedó en menos de 5 MB, una reducción de más del 99% del índice
- Tras reiniciar las estadísticas de la tabla y observar durante un tiempo, se confirmó que el nuevo índice se usaba igual que el anterior, y se eliminaron más de 760 MB de indexed tuples sin uso sin degradación de rendimiento
Cómo encontrar candidatos a partial index
- Después del buen resultado con un partial index, se buscaron índices parecidos consultando columnas con
pg_stats.null_frac alto
null_frac es la proporción estimada por PostgreSQL de valores NULL en esa columna- La consulta de búsqueda de candidatos aplicó estas condiciones
- Se excluyen primary keys porque no pueden convertirse en partial index
- Se excluyen índices que ya son partial
- Se excluyen índices compuestos
- Solo se incluyen índices de más de 10 MB
- En el ejemplo de resultados,
tx_cancelled_by_ix tenía 1418 MB y null_frac de 96.15%, por lo que aparecía como candidato con un ahorro potencial de 1363 MB
- Excluir todos los NULL del índice no siempre es la mejor opción
- Las consultas con condición
IS NULL pueden beneficiarse de tener indexados los valores NULL
- Además de valores no NULL, también puede convenir excluir del partial index otros valores que casi nunca se consultan
- Los 20 GB del título fueron posibles porque, al reducir unos 10 GB en la base de datos primaria, en la réplica también se liberaba una cantidad parecida de almacenamiento
Migrar a partial index en Django ORM
-
ForeignKey nullable e índice implícito
- Django, si no se especifica
db_index=False, crea implícitamente un índice B-Tree para campos models.ForeignKey
- Si no se configura nada en un
ForeignKey nullable que guarda el usuario que canceló, Django crea un índice completo
- Para crear un partial index, hay que poner
db_index=False en el campo FK y agregar un models.Index condicional en Meta.indexes
class Transaction(models.Model):
cancelled_by_user = models.ForeignKey(
to=User,
null=True,
on_delete=models.CASCADE,
db_index=False,
)
class Meta:
indexes = (
models.Index(
fields=('cancelled_by_user_id', ),
name='%(class_name)s_cancelled_by_part_ix',
condition=Q(cancelled_by_user_id__isnull=False),
),
)
- Un foreign key nullable es un buen candidato a partial index
- Para evitar que una funcionalidad implícita cree índices sin que nadie lo note, se creó un check de Django para obligar a especificar siempre
db_index en foreign keys
-
Cómo cambiar un índice completo por un partial index sin downtime
- Para reemplazar un índice completo por un partial index sin downtime ni degradación de rendimiento, conviene hacerlo por etapas
- Se modifica el modelo Django relacionado para cambiar el índice completo por el partial index, pero no se ejecuta tal cual la migración generada por Django
- Esa migración podría desactivar la restricción FK, hacer
drop del índice completo existente y luego crear el partial index nuevo, causando downtime y degradación de rendimiento
- Se genera el SQL con
./manage.py sqlmigrate, se extrae solo el CREATE INDEX, se cambia a CONCURRENTLY y luego se ejecuta manualmente en la base de datos
- Como el índice completo viejo sigue existiendo, las consultas pueden seguir usándolo mientras se crea el partial index
- Para verificar si el nuevo partial index se usa, se reinician los contadores estadísticos del índice completo con
pg_stat_reset_single_table_counters(<full index oid>)
- Luego se observan
idx_scan, idx_tup_read, idx_tup_fetch en pg_stat_all_indexes para comparar el uso del índice completo y del partial index
- Si se confirma que el partial index se usa, se elimina el índice completo y se comprueba cuánto espacio se recuperó realmente
- Una vez que el estado de la base de datos coincide con el estado del modelo, se registra la migración de Django como ejecutada con
./manage.py migrate --fake
- En entornos como dev, QA o staging, donde el downtime preocupa menos, la migración de Django sí se ejecutó normalmente y reemplazó el índice completo por el partial index
Resumen final
- Mejorar solo discos, parámetros de almacenamiento o configuraciones tiene un límite para optimizar rendimiento y espacio
- El último margen de mejora estaba en los propios objetos de la base de datos; en este caso, en la definición de los índices
- El flujo aplicado puede resumirse en tres pasos
- Eliminar índices sin uso
- Repack de tablas e índices, y si es posible activar B-Tree deduplication
- Usar partial indexes para indexar solo los valores necesarios
- Este tipo de limpieza puede ser una opción práctica para ganar algunos días más antes de tener que aprovisionar almacenamiento adicional
1 comentarios
Opiniones de Hacker News
Aunque haya mucho almacenamiento, hacer que los índices sean pequeños es bueno. Así más datos pueden entrar en el hot set.
Dicho eso, desde la perspectiva de operar bases de datos de varios TB, “simplemente agregar más almacenamiento” también es siempre una opción válida. Sobre todo si estás fuera de la nube; en hardware propio, los SSD NVMe empresariales cuestan alrededor de USD 80/TB, y la RAM DDR4, cerca de USD 1.20/GB. Si estimamos 4 horas de ingeniería en unos USD 1000, eso alcanza para comprar 800 GB de RAM o 12 TB de almacenamiento.
Solo con backups semanales de un año, 1 TB se convierte en 50 TB, incluso si es almacenamiento offline. En nuestra empresa también era fácil agregar más almacenamiento a la DB en vivo, y lo hicimos durante años, pero mover backups de varios TB se fue volviendo cada vez más engorroso; ahora estamos en una situación en la que tenemos que reducir los datos de producción aunque implique costo de ingeniería.
Si es algo puntual, la infraestructura existente quizá pueda absorber costos como espacio libre en rack o slots PCIe de sobra, pero esa amortización solo funciona bien a pequeña escala. Además, a medida que el sistema crece, el costo de administración y el aumento de latencia hacen que el rendimiento por unidad de equipo inevitablemente baje. Si sigues resolviendo todo agrandando el sistema, el sistema completo puede volverse cada vez más lento hasta ser imposible de operar. En cambio, una solución que reduce los recursos necesarios aumenta el rendimiento por unidad de recurso y, por sí misma, crea un activo de mayor calidad.
Una optimización que hoy ahorra 1 TB puede ahorrar 2 TB dentro de unos años, y con solo algunas decisiones de este tipo puede aparecer una diferencia de más de un orden de magnitud. Eso es especialmente cierto a mayor escala y, al final, siempre hay que buscar un equilibrio.
A veces la opción de “tirarle hardware encima” puede ser sensata, pero en la mayoría de los casos no creo que puedas convertir hoy un servidor con 128 GB de RAM en uno con 1 TB de RAM por USD 1K mañana.
Pero como no es un gasto único sino mensual, el cálculo se vuelve un poco más complejo. Claro que, si la base de datos crece a un ritmo constante, el aprovisionamiento único on-premises en realidad también se parece bastante a un costo mensual.
El artículo menciona la deduplicación de B-tree, que existe en PostgreSQL 13 pero no en la versión 12 que estaban usando, y dice que la gran mayoría de los valores de algunos índices de claves foráneas son NULL.
Me pregunto si la deduplicación de B-tree habría ayudado en este caso. Según la sección 63.4.2 de la documentación de PostgreSQL 13, parece que sí: “B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...”. Por supuesto, probablemente no habría sido tan efectiva como el índice parcial que aplicaron en el artículo, pero es interesante.
https://www.postgresql.org/docs/13/btree-implementation.html
https://news.ycombinator.com/item?id=25989467
Este artículo fue útil no solo por el “descubrimiento” central de los índices parciales, sino también porque recopila en general buenas técnicas que conviene conocer cuando te preocupa que PostgreSQL esté usando espacio de forma ineficiente.
Dicho eso, si eres una startup o una empresa en etapa temprana, vale la pena recordar que ante este tipo de problemas de almacenamiento casi siempre conviene agregar más espacio en disco en lugar de preocuparse por optimizar el tamaño. Los desarrolladores son caros y el disco es barato.
Hace tiempo trabajé en una startup que crecía rápido y uno de los fundadores insistía en seguir mejorando un solo servidor. Teníamos redundancia y backups, pero la arquitectura era simple, así que era fácil de administrar, depurar y recuperar. Durante los primeros 5 años de la empresa, todo el sistema corrió en un solo servidor mientras crecía exponencialmente y atendía a millones de usuarios en todo el mundo. Después de ver eso, quedé convencido de que hay que actualizar solo cuando haga falta, y de la forma más simple y directa posible.
Usar un índice parcial que encaja claramente con el caso de uso, como cuando la mayoría de los valores son NULL, se parece más a un modelado correcto y no debería verse como optimización prematura ni como una pérdida de tiempo de desarrollo.
En un contexto parecido, estos scripts me resultaron bastante útiles:
https://github.com/NikolayS/postgres_dba
Reordenando las columnas de tablas grandes pudimos recuperar alrededor del 10% del almacenamiento, es decir, unos 100 GB.
La semana pasada, solo con
reindexypg_repack, recuperé 200 GB en una DB de 600 GB. La tabla más grave tenía 17 GB de datos y 142 GB de índices; después de reindexar, bajó a 21 GB.Esa tabla estaba sobreindexada, con varios índices para distintas combinaciones de columnas. Creo que una de las causas de esos índices enormes era la distribución de los datos. Desde 2015 hubo inserciones, actualizaciones y eliminaciones constantes, y cuanto más antiguos eran los datos, más probable era que se eliminaran, así que hay más datos de años recientes; aun así, alrededor del 0.1% sigue siendo de 2015. Supongo que esta distribución sesgada de cola larga pudo haber hecho más difícil que vacuum manejara la hinchazón de los índices.
También se comentó en su momento: An unexpected find that freed 20GB of unused index space in PostgreSQL - https://news.ycombinator.com/item?id=25988871 - febrero de 2021, 78 comentarios
Recomiendo muchísimo pganalyze.com para encontrar índices sin usar, oportunidades de optimización y consultas con alta latencia.
De verdad no era una exageración decir “un truco raro para ahorrar 20 GB”. En entornos antiguos de Oracle / SQL Server casi nunca escuché que se pidiera usar esta forma peculiar de índice de PostgreSQL, pero no sabía que también indexaba valores NULL.
En el primer proyecto grande en el que participé, el equipo no lograba entender por qué las operaciones se volvieron más lentas a medida que crecía el dataset.
En los índices, el tiempo de inserción por registro es log(n). Si la base de datos de pruebas pasa de 1000 registros a casi 65k, el tiempo de inserción aumenta 60% (2^10 frente a 2^16). El éxito vuelve todo más lento, y las actualizaciones de servidor tienen un límite. Si por alguna funcionalidad ambigua que pidió el negocio agregas algunos índices más, ahora se duplica.
También administro muchas DB con cientos de millones de registros y más de 40 índices por tabla/colección.
Una búsqueda binaria dentro de una página de B-tree toma menos de 100 ciclos. Incluso una búsqueda en un B-tree sobre 100 millones de registros debería seguir estando en el orden de microsegundos, y la búsqueda binaria encima de eso también debería estar en microsegundos, aunque no en cientos de nanosegundos.
¿Este principio también aplica cuando uno o unos pocos valores son muy comunes? Por ejemplo, si el 90% de una columna entera es 0.
where val = 0deja el 90% de las filas, puede elegir un escaneo de tabla en lugar del índice.