Un Common Table Expression (CTE) recursivo en SQL permite recorrer datos jerárquicos utilizando una consulta autorreferencial que se ejecuta de forma basada en conjuntos. La estructura consiste en un miembro ancla (el caso base, típicamente nodos raíz donde manager_id ES NULL) y un miembro recursivo (la parte iterativa que une el CTE consigo mismo en la relación padre-hijo). El motor de la base de datos ejecuta repetidamente el miembro recursivo hasta que no se devuelven nuevas filas, construyendo el conjunto de resultados de manera incremental sin constructos de iteración explícitos.
Este enfoque aprovecha la naturaleza declarativa de SQL, permitiendo al optimizador elegir algoritmos de unión eficientes (típicamente uniones hash o de combinación) en lugar del procesamiento fila por fila inherente a CURSOR o bucles WHILE. La sintaxis utiliza WITH RECURSIVE en PostgreSQL y MySQL, o simplemente WITH en SQL Server (donde la recursión es implícita), seguido por el nombre del CTE y la lista de columnas.
Una corporación multinacional con 12,000 empleados necesitaba generar cadenas de informes completas para auditorías de cumplimiento de SOX. El sistema existente utilizaba un CURSOR de T-SQL que iteraba a través de cada empleado, llamaba recursivamente a una función escalar para encontrar su gerente y construía la jerarquía cadena por cadena. Este proceso tardaba 47 minutos en completarse, mantenía bloqueos en la tabla Employees que impedían las actualizaciones de RRHH durante las horas laborales y frecuentemente fallaba con errores de desbordamiento de pila al procesar jerarquías profundas que superaban los 100 niveles (común en la estructura matricial del departamento de ingeniería).
Solución A: CURSOR optimizado con tablas temporales. El equipo consideró reescribir el cursor para volcar resultados en una tabla temporal primero, y luego insertar en bloque al final. Esto reduciría el tiempo de bloqueo de 47 minutos a aproximadamente 40 minutos. Pros: Cambios mínimos en el código, patrón familiar para el equipo legado. Contras: Aún procesamiento fila por fila, aún vulnerable a desbordamientos de pila recursivos profundos, solo mitiga en lugar de resolver el problema de rendimiento.
Solución B: Tipo de dato HierarchyID. Migrar la tabla para usar el tipo nativo HierarchyID de SQL Server, que almacena posiciones de árbol como rutas codificadas optimizadas para el recorrido. Pros: Recuperación de subárbol O(1), métodos integrados como GetAncestor() y GetDescendant(), extremadamente rápido para cargas de trabajo de lectura intensiva. Contras: Requiere una migración masiva de esquema (12,000 filas más datos históricos), complejo de mantener durante reorganizaciones (actualizar un gerente requiere recalcular todos los caminos descendientes), bloqueado a SQL Server mientras la compañía consideraba una migración a PostgreSQL.
Solución C: CTE recursivo con detección de ciclos. Implementar un CTE recursivo que une la tabla de empleados consigo misma en manager_id, utilizando un arreglo de ruta para rastrear nodos visitados y prevenir bucles infinitos en caso de referencias circulares (que habían ocurrido dos veces debido a errores de entrada de datos). Pros: Estándar puro de ANSI SQL (portable a PostgreSQL durante la migración), la ejecución basada en conjuntos redujo el tiempo de ejecución a 4 minutos y 12 segundos, no se mantienen bloqueos de tabla durante la ejecución (utiliza aislamiento de instantáneas), maneja profundidad arbitraria sin desbordamiento de pila, detecta automáticamente problemas de calidad de datos (ciclos).
El equipo eligió Solución C. La implementación utilizó un CTE recursivo con una columna path acumulando IDs de empleados utilizando la agregación de arreglos de PostgreSQL (o concatenación de cadenas en SQL Server), con una cláusula WHERE verificando que el nuevo manager_id no existiera en la ruta acumulada. El resultado fue una mejora del 91% en el rendimiento, eliminación de bloqueos de producción y detección temprana de relaciones de informes circulares que anteriormente causaban fallos en la aplicación.
¿Por qué termina un CTE recursivo y qué sucede si los datos contienen una referencia circular?
Los candidatos a menudo creen que los CTE recursivos tienen detección de ciclos incorporada, pero la recursión estándar de SQL termina solo cuando el miembro recursivo devuelve cero nuevas filas. Si el Empleado A reporta a B, B a C, y C de vuelta a A, el CTE se ejecuta indefinidamente (o hasta alcanzar límites de implementación como los 100 niveles de recursión predeterminados de SQL Server). La solución requiere detección manual de ciclos acumulando IDs de nodos visitados en una columna de ruta (usando arreglos o cadenas delimitadas) y filtrando WHERE new_id != ALL(path_array). Las modernas PostgreSQL (14+) y SQL Server (2022+) admiten la cláusula estándar SQL:1999 CYCLE: WITH RECURSIVE cte AS (...) CYCLE id SET is_cycle USING path, que maneja esto automáticamente.
¿Cómo difiere el plan de ejecución entre un CTE recursivo y un enfoque basado en cursor, y por qué es importante para la concurrencia?
Los candidatos junior a menudo afirman que los CTE son "más rápidos" sin entender el modelo de ejecución. Un CURSOR en SQL Server o PostgreSQL obliga al motor a materializar un conjunto de resultados e iterar fila por fila, utilizando típicamente un tipo de cursor Keyset o Static que requiere bloqueos o recursos de tempdb durante la duración de la iteración. Esto crea Bloqueos Compartidos (o Bloqueos de Actualización) en las tablas subyacentes durante toda la duración de 47 minutos en el ejemplo anterior. En cambio, un CTE recursivo es una única declaración SELECT. Bajo Read Committed Snapshot Isolation (RCSI) o Snapshot Isolation, lee una vista consistente del punto en el tiempo de los datos sin mantener bloqueos, utilizando el almacén de versiones en su lugar. El optimizador generalmente elige Nested Loop Joins para el miembro recursivo con operaciones de Index Seek en el índice padre-hijo, haciéndolo O(n log n) en lugar de O(n²) para los enfoques de cursor.
¿Cuál es la diferencia entre un CTE recursivo y el Nested Sets Model para datos jerárquicos, y cuándo elegirías uno sobre el otro?
Los candidatos frecuentemente confunden los métodos de recorrido con los modelos de almacenamiento. Un CTE recursivo es una técnica de recorrido en tiempo de consulta que trabaja en Listas de Adyacencia (claves foráneas parent_id). El Nested Sets Model (valores izquierdo/derecho) es un patrón de diseño de almacenamiento que pre-calcula las rutas de recorrido del árbol. Para cargas de trabajo con muchas escrituras (cambios frecuentes de gerentes), los CTE recursivos en listas de adyacencia son superiores porque actualizar un solo parent_id es O(1), mientras que los conjuntos anidados requieren O(n) actualizaciones a todos los valores derecho a la derecha del nodo movido. Para jerarquías estáticas y pesadas en lectura (organigramas que cambian mensualmente), los conjuntos anidados proporcionan recuperación de subárboles O(1) (WHERE left BETWEEN parent.left AND parent.right) frente a O(n) para los CTE recursivos. Un enfoque híbrido utiliza Closure Tables (tabla separada que almacena todos los pares ancestro-descendiente), lo que ofrece O(1) tanto para el recorrido como para encontrar todos los hijos, a costa de O(n²) de almacenamiento y un mantenimiento más complejo. La elección depende de la relación de lectura/escritura: utiliza CTE recursivos cuando las escrituras > 5% de las operaciones; utiliza conjuntos anidados o tablas de cierre para árboles predominantemente estáticos.