Siete mecanismos de SQL donde la gente tropieza en producción: filtrado de filas, lectura rápida y deadlocksArtículo · digest del 22 de noviembre
Bodegón de ingeniería del escritorio de un ingeniero de datos con planos de mecanismos de PostgreSQL, un libro de SQL y una terminal de optimización de consultas
Artículo4 secciones≈ 12 min

Siete mecanismos de SQL donde la gente tropieza en producción: filtrado de filas, lectura rápida y deadlocks

En SQL es fácil caer en errores raros por un detalle: escribes SELECT en la primera línea, pero la base de datos lo evalúa casi al final. Intentas escribir WHERE SUM(sales) > 4000 y salta un error de sintaxis. Pones GROUP BY y desaparecen los recibos individuales que aún necesitabas. Y más adelante la tabla crece a decenas de millones de filas, las consultas se arrastran y dos transacciones se bloquean mutuamente de golpe.

Tomemos una tabla sencilla de ventas store_sales(id, store, sales, sale_date) para repasar siete mecanismos esenciales de SQL. Los ejemplos están probados en PostgreSQL, aunque la lógica aplica en la mayoría de bases relacionales.

Orden de filtrado y funciones de ventana: cuándo colapsan las filas y cuándo no

Empecemos por la confusión entre WHERE y HAVING. La diferencia está en el momento en que actúan. Si necesitas descartar filas originales antes de cualquier cálculo, el encargado es WHERE. Se ejecuta antes de GROUP BY, así que aprovecha índices normales y reduce el volumen de datos desde el arranque.

HAVING entra en juego después de GROUP BY, cuando las filas ya están agrupadas y las sumas calculadas. Allí se evalúan condiciones sobre agregados como SUM() o COUNT(). Si metes un filtro por tienda dentro de HAVING, la base sumará primero todas las tiendas del mundo y luego tirará lo que sobra. Tiempo desperdiciado.

-- Filtramos tiendas con WHERE,
-- luego agrupamos y dejamos totales mayores a 4000
SELECT store, SUM(sales) AS total_sales
FROM store_sales
WHERE store IN ('store1', 'store2')
GROUP BY store
HAVING SUM(sales) > 4000;

Aquí primero quedan solo los registros de store1 y store2, luego la base suma las ventas y conserva las tiendas con total superior a 4000. En WHERE no puedes escribirlo así porque en ese paso las sumas todavía no existen.

Ahora otro problema: mostrar cada venta individual, pero junto a ella ver los ingresos totales de la tienda y el puesto del recibo en el ranking. Un GROUP BY normal no sirve porque colapsa todas las filas de la tienda en un único registro resumen. Aquí salvan el día las funciones de ventana con OVER(). Calculan un total o ranking sobre la «ventana» de filas vinculada al registro actual, pero sin fusionar las filas.

-- Calculamos ingresos de la tienda y puesto del recibo,
-- manteniendo cada venta en su propia fila
SELECT
  store,
  sale_date,
  sales,
  SUM(sales) OVER (PARTITION BY store) AS store_total,
  ROW_NUMBER() OVER (
    PARTITION BY store
    ORDER BY sales DESC
  ) AS rn
FROM store_sales;

La cláusula PARTITION BY store divide la tabla en ventanas independientes por tienda, y ORDER BY sales DESC dentro de ROW_NUMBER() numera los recibos de mayor a menor. Haz clic por los pasos en el ejemplo interactivo de abajo para ver la diferencia en vivo.

Cómo cambia store_sales en WHERE, HAVING y OVER()

Al alternar entre el tercer y el cuarto paso todo queda claro al instante: GROUP BY reduce cuatro filas a dos sumas, y HAVING deja solo una. La función de ventana no esconde nada y simplemente añade las columnas calculadas a la derecha.

CTE frente a subconsultas anidadas: legibilidad y materialización oculta

Cuando una consulta acumula tres niveles de SELECT anidados, toca leerla de adentro hacia afuera. Para estos casos SQL ofrece las CTE (Common Table Expression, expresión de tabla común), declaradas con WITH. Definen un conjunto temporal con nombre antes de la consulta principal: el código se lee de arriba hacia abajo, el bloque se puede reutilizar varias veces y con WITH RECURSIVE puedes recorrer árboles de categorías.

-- Extraemos la agregación a un paso llamado top_stores
WITH top_stores AS (
  SELECT store, SUM(sales) AS total_sales
  FROM store_sales
  GROUP BY store
)
SELECT *
FROM top_stores
WHERE total_sales > 4000;

En las revisiones de código suele aparecer un mito: pasas una subconsulta a CTE y todo irá cinco veces más rápido. Lamentablemente, no. Según la documentación de PostgreSQL sobre consultas WITH, desde la versión 12 el planificador por defecto no materializa una CTE de un solo uso sin efectos secundarios. Simplemente la integra en la consulta principal para empujar el WHERE hacia los índices. Una CTE usada varias veces sí se evalúa una vez por defecto y se guarda en caché. Puedes controlar este comportamiento de forma explícita con AS MATERIALIZED o AS NOT MATERIALIZED si el planificador eligió un mal plan.

Tres formas de acelerar la lectura: particiones, vistas materializadas e índices de cobertura

Cuando store_sales crece hasta decenas de millones de filas, entran en escena tres herramientas distintas para acelerar las consultas.

La primera es el particionamiento (Partitioned Tables). Una tabla gigante se divide físicamente en fragmentos, por ejemplo por años de sale_date: hasta finales de 2022, 2023, 2024 y 2025+. Los detalles de sintaxis están en la sección de particionamiento de PostgreSQL.

-- La tabla padre define la regla de partición por fecha
CREATE TABLE store_sales (
  id BIGINT,
  store TEXT,
  sales NUMERIC,
  sale_date DATE NOT NULL
) PARTITION BY RANGE (sale_date);

-- Partición física para el año 2024
CREATE TABLE store_sales_2024 PARTITION OF store_sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Si la consulta incluye WHERE sale_date >= '2024-01-01', se activa el partition pruning (poda de particiones): la base ni siquiera abre los archivos de 2022 y 2023. Además, un año viejo se borra al instante con DROP TABLE, sin un pesado DELETE. En tablas pequeñas no conviene montar particiones: solo complican el esquema.

La segunda opción son las vistas materializadas (MATERIALIZED VIEW). Un VIEW normal no guarda nada en disco: es una consulta guardada que se recalcula cada vez. Los datos siempre están frescos, pero un reporte pesado carga la base de datos. Una vista materializada guarda el resultado listo en disco: la lectura es instantánea, aunque las cifras quedan congeladas hasta que la actualices explícitamente.

-- Guardamos en disco el cálculo ya hecho de ingresos
CREATE MATERIALIZED VIEW mv_store_totals AS
SELECT store, SUM(sales) AS total_sales
FROM store_sales
GROUP BY store;

-- Índice único para actualizar sin bloqueos
CREATE UNIQUE INDEX idx_mv_store_totals_store
  ON mv_store_totals (store);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_store_totals;

Un REFRESH normal bloquea a los lectores durante todo el recálculo. La bandera CONCURRENTLY elimina el bloqueo, pero exige tener un UNIQUE INDEX obligatorio sobre la vista.

La tercera vía son los índices de cobertura (Covering Indexes) con INCLUDE. Un índice común sobre store encuentra las filas necesarias, pero aun así la base tiene que ir a la tabla principal (heap) a buscar la columna sales. Si agregas sales dentro de INCLUDE, PostgreSQL la guarda en las páginas hoja del índice B-tree sin meterla en el ordenamiento de la clave.

-- Guardamos sales en las hojas del índice para leer sin tocar la tabla
CREATE INDEX idx_store_sales_cov
  ON store_sales (store)
  INCLUDE (sales);

Así el planificador puede elegir Index-Only Scan y leer la respuesta directo desde el índice. Según la documentación de Index-Only Scans, bajo el capó PostgreSQL depende del visibility map (mapa de visibilidad de páginas) que actualiza autovacuum. Si el mapa queda desactualizado, volverán las lecturas a la tabla. Además, meter demasiadas columnas en INCLUDE infla el índice y frena los INSERT y UPDATE. Siempre puedes comprobar si funcionó la poda de particiones o el Index-Only Scan con EXPLAIN (ANALYZE, BUFFERS).

Deadlocks: cómo dos transacciones se cuelgan entre sí y tres reglas de defensa

Un deadlock (interbloqueo o bloqueo mutuo) ocurre cuando hay una espera circular. La transacción A bloqueó la fila 1 y espera la fila 2, mientras la transacción B retiene la fila 2 y espera la fila 1. Ninguna puede avanzar.

El caso típico en producción son transferencias cruzadas entre cuentas (1 -> 2 y 2 -> 1), cuando el código bloquea las filas en el orden en que llegaron los parámetros. PostgreSQL detecta el ciclo por sí solo y revierte a la fuerza una de las transacciones con el error deadlock detected (código SQLSTATE 40P01).

-- Bloqueamos las filas en el mismo orden ascendente por id
BEGIN;

SELECT id, sales
FROM store_sales
WHERE id IN (101, 205)
ORDER BY id
FOR UPDATE;

UPDATE store_sales
SET sales = sales + 500
WHERE id = 101;

COMMIT;

La protección se sostiene en tres reglas simples. Primera: las transacciones deben ser cortas, sin llamadas de red ni APIs externas dentro de BEGIN ... COMMIT. Segunda: bloquea siempre tablas y filas en un mismo orden predecible, por ejemplo por id ascendente; así las transferencias cruzadas simplemente hacen fila una detrás de otra. Tercera: implementa reintentos (retry) de la transacción en el código de la aplicación ante el error 40P01, para que un conflicto puntual bajo carga pase inadvertido para el usuario.

Quiero un análisis detalladoLos votos ayudan a elegir qué analizar a fondo. Este material tiene 0 votos.
Fuentes · 4