SQL en el mundo real

Un taller de PostgreSQL de profesional: cada consulta parte de un síntoma real —un informe vacío, un join lento, un índice fantasma— y muestra la consulta exacta junto a su salida psql comentada. Gran densidad de trampas resueltas (NOT IN/NULL, predicados non-sargable, OFFSET profundo) y de consultas de diagnóstico sobre los catálogos del sistema que los DBA tienen realmente a mano.

19 snippets destacados

• →FIRST_VALUE / LAST_VALUE: la trampa del frame por defectoLAST_VALUE devuelve la fila actual si no cambias el frame por defecto (hasta CURRENT ROW). La solución: extender el frame a UNBOUNDED FOLLOWING.
• →Detectar ciclos en un grafo: la cláusula CYCLEUn bucle en los datos (A padre de B, B padre de A) hace que una CTE recursiva gire infinitamente. La cláusula CYCLE (PostgreSQL 14+) detiene la recursión y marca las filas culpables.
• →Fusionar intervalos de fechas que se solapanReducir periodos solapados a rangos continuos: se detecta un nuevo grupo cuando el inicio supera el máximo de los finales anteriores, y luego se agrega por grupo.
• →Indexar jsonb: GIN + el operador de containment @>Un índice GIN jsonb_path_ops acelera drásticamente las consultas de containment @> sobre grandes volúmenes de documentos. Verifica su uso con EXPLAIN.
• →Detectar las claves foráneas sin índicePostgreSQL no indexa las FK automáticamente: cada DELETE en el padre desencadena entonces un seq scan del hijo. Esta consulta al catálogo lista las FK cuya primera columna no está indexada en ningún sitio.
• →Antipatrón: NOT IN frente a los NULLUn solo NULL en la subconsulta y NOT IN devuelve cero filas, de forma silenciosa (lógica ternaria de SQL). NOT EXISTS es NULL-safe y, por lo general, mejor planificado.
• →Antipatrón: función sobre columna indexada (non-sargable)DATE_TRUNC o EXTRACT aplicado a la columna impide el uso del índice. Reescribir el filtro como un rango de valores lo hace sargable, y el índice B-tree vuelve a ser utilizable.
• →Antipatrón: OFFSET profundo — pasar a keysetOFFSET 20000 lee y descarta 20 000 filas en cada página. La paginación keyset (seek) reanuda desde el último elemento visto: coste constante sea cual sea la profundidad.
• →DISTINCT ON: última fila por clave en una sola pasadaEspecífico de PostgreSQL: DISTINCT ON conserva la primera fila de cada grupo según el ORDER BY — a menudo más rápido que ROW_NUMBER con el índice adecuado.
• →Cola de jobs concurrente: FOR UPDATE SKIP LOCKEDCada worker reserva un lote de jobs saltándose las filas ya bloqueadas por los demás: una cola de trabajo fiable en SQL puro, sin broker externo.
• →Embudo de conversión: tasa por etapa en una sola consultaContar los usuarios distintos en cada etapa con FILTER, y luego desplegar el embudo en filas mediante LATERAL VALUES para calcular la tasa global y la tasa etapa por etapa.
• →Secuencias cerca del desbordamiento (overflow int4)Mide el porcentaje de consumo de cada secuencia respecto a su max_value: una secuencia int4 que se desborda bloquea de la noche a la mañana todas las inserciones.
• →Restricciones NOT VALID y triggers desactivados: las barreras dormidasInventario de las restricciones CHECK/FK creadas NOT VALID (nunca re-validadas) y de los triggers desactivados: otras tantas reglas de negocio que la base ya no aplica.
• →Waterfall de MRR: new, expansion, contraction y churn en un solo informeDescomposición mensual del ingreso recurrente en cuatro movimientos mediante un FULL JOIN del MRR de cada cliente con su mes anterior — el informe estándar de cualquier SaaS, en SQL puro.
• →Scoring RFM completo: recencia, frecuencia e importe en quintilesPipeline de dos CTE: agregación de los tres ejes RFM por cliente, scoring en quintiles con NTILE y, después, una segmentación legible (Champion, VIP a reactivar, Perdido) lista para el CRM.
• →Detección de fraude: transacciones multipaís muy próximas en la misma tarjetaUna ventana LAG por tarjeta compara cada transacción con la anterior: un cambio de país en menos de una hora es físicamente imposible — señal clásica de tarjeta comprometida.
• →Detectar el patrón N+1 en pg_stat_statementsLas consultas con un tiempo unitario irrisorio pero llamadas cientos de miles de veces delatan un ORM en bucle N+1: individualmente invisibles, colectivamente dominantes.
• →Correlación física de las columnas: candidatos a BRIN y CLUSTERLectura de pg_stats.correlation para las columnas indexadas: cerca de 1, basta con un BRIN diminuto; cerca de 0 en una columna de ordenación frecuente, un CLUSTER reordena la tabla y divide las I/O.
• →Bloat real de los índices B-tree con pgstatindexMedida exacta (no estimada) de la densidad de las páginas hoja de cada índice: por debajo del 50 % de densidad, el índice ha duplicado su volumen para nada y merece un REINDEX CONCURRENTLY.