Al implementar un patrón de borrado suave utilizando una bandera booleana, ¿por qué agregar esta bandera a un índice compuesto a veces degrada el rendimiento de las consultas de registros activos en comparación con una estrategia de índice parcial, y cómo difiere la estimación de selectividad del planificador de consultas entre estos enfoques?

Respuesta a la pregunta

Historia de la pregunta

Los patrones de borrado suave surgieron como una alternativa a la eliminación dura para auditorías y recuperación de datos. Las primeras implementaciones utilizaron simples banderas booleanas o columnas de marcas de tiempo, pero pronto los desarrolladores se encontraron con degradaciones de rendimiento cuando estas banderas se incorporaron a los índices B-tree estándar. El problema se hizo prominente con la adopción generalizada de PostgreSQL índices parciales y SQL Server índices filtrados a mediados de la década de 2000, que permitieron indexar solo registros activos. Comprender la estimación de selectividad—cómo el planificador de consultas predice el porcentaje de filas que coinciden con una condición—se volvió crucial al comparar índices compuestos completos con estrategias de indexación parcial.

El problema

Cuando se agrega una bandera de borrado suave (por ejemplo, is_deleted) a un índice compuesto como (is_deleted, user_id, created_at), el optimizador de la base de datos puede calcular incorrectamente la selectividad de fila para las consultas que filtran WHERE is_deleted = false. Si el 90% de las filas están activas, el optimizador podría optar por un escaneo secuencial en lugar de un escaneo de índice, o, por el contrario, si la distribución está sesgada, podría favorecer inapropiadamente el índice. Los índices parciales (WHERE is_deleted = false) almacenan únicamente filas activas, garantizando una alta selectividad, pero los índices compuestos estándar almacenan todas las filas, lo que lleva a la inflación del índice y estimaciones de cardinalidad ambiguas cuando las estadísticas no reflejan con precisión la distribución de borrados suaves.

La solución

Implementar índices parciales (en PostgreSQL) o índices filtrados (en SQL Server) que excluyan completamente las filas borradas suavemente, junto con índices separados para datos borrados si es necesario. Para MySQL o bases de datos que carecen de indexación parcial, use un índice compuesto con la bandera de borrado suave como la columna principal solo si el conjunto de datos activo es pequeño; de lo contrario, particione la tabla por estado de eliminación. Analice explícitamente las estadísticas de la tabla después de eliminaciones masivas para evitar histogramas obsoletos. Al consultar registros activos, utilice el predicado exacto de la definición del índice parcial (WHERE is_deleted = false) para asegurar que el optimizador reconozca la aplicabilidad del índice.

Ejemplo de código

-- PostgreSQL: Índice parcial solo para registros activos
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server: Índice filtrado equivalente
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- Consulta que aprovecha el índice parcial
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

Situación de la vida real

Una plataforma SaaS que gestiona 10 millones de registros de usuarios experimentó graves ralentizaciones en su panel de administración al filtrar usuarios activos por fecha de creación. Inicialmente, utilizaron un índice compuesto (is_deleted, created_at) en PostgreSQL, asumiendo que aceleraría las consultas WHERE is_deleted = false ORDER BY created_at. Sin embargo, a medida que el conjunto de datos creció hasta un 80% de cuentas históricas borradas suavemente, las consultas comenzaron a tardar de 8 a 12 segundos porque el planificador subestimaba el costo de escanear el índice hinchado.

Solución A: Mantener el índice compuesto y forzar el uso del índice con sugerencias. Este enfoque utilizó SET enable_seqscan = off o sugerencias de planes de consulta para forzar la utilización del índice. Si bien mejoró temporalmente consultas específicas, creó deudas de mantenimiento y a menudo forzó planes subóptimos para otros patrones de acceso cuando la distribución de datos cambió. La solución también falló en abordar la inflación del índice subyacente que causaba un aumento en el almacenamiento y en el costo de VACUUM.

Solución B: Crear índices parciales separados para registros activos y borrados. Implementar CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false redujo el tamaño del índice en un 80% y permitió que el planificador estimara correctamente 2 millones de filas activas frente a 8 millones borradas. Las consultas bajaron a 40 ms, pero requerían reestructurar todas las consultas de la aplicación para asegurar que el predicado is_deleted = false permaneciera explícito y no envuelto en funciones o abstraído en vistas que ofuscaran la condición.

El equipo eligió la Solución B porque proporcionó un rendimiento sostenible sin mantenimiento de sugerencias de consultas. El resultado fue una reducción del 95% en la latencia de consultas y eliminación de problemas periódicos de inflación de VACUUM causados por el índice compuesto sobredimensionado anterior. El monitoreo confirmó tiempos de respuesta consistentes de menos de un segundo para los casos de uso primarios del panel.

Lo que a menudo se pierde de vista por los candidatos

¿Cómo afecta la presencia de valores NULL en una columna de marca de tiempo de borrado suave (usando NULL para activo, marca de tiempo para borrado) el uso de índices parciales frente a enfoques de bandera booleana?

Al usar una columna de marca de tiempo deleted_at nullable, los índices parciales como WHERE deleted_at IS NULL enfrentan desafíos con la gestión de NULL indexabilidad de PostgreSQL. A diferencia de las banderas booleanas donde = false es explícito y sargable, las condiciones IS NULL requieren que el planificador reconozca la aplicabilidad del índice, lo que puede fallar si la consulta utiliza declaraciones parametrizadas donde el planificador no puede probar que el parámetro será NULL. Además, las actualizaciones que establecen deleted_at = CURRENT_TIMESTAMP causan inflación del índice en el índice parcial para registros activos a medida que las filas se eliminan de él, mientras que las actualizaciones de la bandera booleana cambian el bit pero permanecen dentro de un índice compuesto completo. El enfoque nullable requiere llamadas ANALYZE más frecuentes y una cuidadosa consideración de los factores de llenado del índice para manejar la alta rotación de cambios en el estado de eliminación.

¿Por qué un índice cubriente que incluye columnas de borrado suave podría causar escrituras más lentas de lo esperado incluso con frecuencia de eliminación baja?

Los índices cubrientes (usando la cláusula INCLUDE en PostgreSQL 11+ o SQL Server) que agregan is_deleted para evitar búsquedas en tablas, en realidad degradan el rendimiento de escritura porque cada operación de borrado suave (una ACTUALIZACIÓN) debe modificar múltiples estructuras de índice. Cuando un usuario es borrado suavemente, la base de datos debe marcar la antigua entrada del índice como muerta en el índice parcial activo, insertar una nueva entrada en cualquier índice de registros borrados y actualizar los punteros del montón del índice cubriente. Los candidatos a menudo pierden de vista que los índices parciales aíslan esta rotación—solo se modifican los índices parciales específicos para estados activos o borrados—mientras que los índices cubrientes en la tabla principal requieren la actualización de la estructura del índice principal independientemente del estado de borrado suave, creando una amplificación de escrituras que impacta en el rendimiento de las transacciones.

¿Cuándo ignora el optimizador de consultas un índice parcial para datos borrados suavemente incluso cuando la consulta filtra explícitamente por registros borrados?

Si el índice parcial se define como WHERE is_deleted = true para consultas de auditoría, pero la aplicación utiliza una declaración preparada con un parámetro $1 para consultas activas y borradas, es posible que PostgreSQL almacene en caché un plan genérico que no reconozca el índice parcial para el caso específico de true. Esto ocurre porque las declaraciones preparadas generan planes antes de que se vinculen los valores de los parámetros, y el optimizador no puede probar que $1 = true coincidirá siempre con el predicado de índice. Los candidatos no se dan cuenta de que SQL dinámico o sugerencias de recompilación (OPTION (RECOMPILE) en SQL Server, ejecutando con valores literales en PostgreSQL) son necesarios para asegurarse de que el planificador vea el valor concreto y lo empareje con el predicado del índice parcial, en lugar de depender de planes genéricos que se predeterminan para escaneos secuenciales debido a la incertidumbre del valor del parámetro.