Buenas prácticas de SQL: consultas correctas, rápidas y mantenibles

SQL es un lenguaje declarativo: describes el resultado que quieres y el optimizador de la base de datos decide cómo obtenerlo. Eso es liberador, y también es la razón de que tanto SQL lento parezca perfectamente razonable. El optimizador solo puede elegir un buen plan si la consulta le da la oportunidad, el esquema le proporciona los índices y la aplicación no lo echa todo a perder llamando a la consulta mil veces dentro de un bucle. Las prácticas que siguen se aplican a cualquier motor relacional; los ejemplos usan la sintaxis de PostgreSQL 16 y se señalan las diferencias con Oracle cuando importan.

Pide exactamente las filas y columnas que necesitas

La fila más barata es la que nunca llegas a traer. De ahí se derivan directamente dos hábitos.

Nombra tus columnas. SELECT * transfiere columnas que no vas a usar, impide los index-only scans (el índice no puede resolver una consulta que toca columnas que no contiene) y acopla tu código al orden de las columnas, algo que te pasa factura el día en que alguien añade una columna en mitad de la tabla.

Filtra y agrega en la base de datos, no en la aplicación. Cargar 200.000 filas en memoria para sumar una columna en Java es más lento, consume más memoria y retiene una conexión durante más tiempo que SELECT SUM(amount) FROM payment WHERE .... La misma lógica vale para ordenar y contar: ORDER BY y COUNT(*) pertenecen a SQL, donde pueden apoyarse en índices y en workers paralelos.

Los ORM hacen que este error sea fácil de cometer. Con una colección lazy, recorrer 1.000 pedidos y llamar a order.getLines() en cada uno lanza 1.001 consultas. Es el problema N+1, y la solución es siempre la misma: una única consulta que haga el join o traiga las filas hijas como un conjunto.

-- One round trip instead of 1,001
SELECT o.id, o.placed_at, l.sku, l.quantity, l.unit_price
FROM   customer_order o
JOIN   order_line l ON l.order_id = o.id
WHERE  o.customer_id = $1
ORDER  BY o.placed_at DESC, o.id, l.line_no;

En JPA eso significa un JOIN FETCH o un entity graph; en Spring Data, @EntityGraph o una proyección. La guía de Spring Data JPA muestra ambas opciones.

Escribe predicados que el optimizador pueda aprovechar

Un índice sobre email no sirve de nada para la consulta WHERE LOWER(email) = '[email protected]'. La función transforma la columna, así que los valores almacenados en el índice ya no coinciden con lo que necesita la comparación. Un predicado que puede resolverse buscando en un índice se llama sargable (de «search argument»). La regla es sencilla: deja la columna tal cual a un lado del operador y pon el cálculo al otro.

La conversión implícita de tipos es la versión traicionera del mismo problema. Si customer_ref es una columna VARCHAR y escribes WHERE customer_ref = 12345, PostgreSQL lanza un error, pero Oracle convierte en silencio el customer_ref de cada fila con TO_NUMBER y recorre la tabla entera. Enlaza los parámetros con el tipo JDBC correcto y compara fechas con literales de fecha, no con cadenas.

Los predicados de rango merecen un último apunte: un índice sobre (status, placed_at) resuelve de forma eficiente WHERE status = 'PAID' AND placed_at > :since, pero un índice sobre (placed_at, status) no puede aprovechar la igualdad sobre status después del rango sobre placed_at. El orden de las columnas en un índice compuesto es una decisión de diseño; la guía de índices y planes de ejecución explica cómo elegirlo.

Trata NULL como lo que es: un valor desconocido

SQL usa lógica de tres valores. Cualquier comparación con NULL no es ni verdadera ni falsa, sino UNKNOWN, y WHERE solo conserva las filas cuya condición es TRUE. Todas las sorpresas siguientes vienen de esa regla:

• WHERE discount <> 0 descarta las filas en las que discount IS NULL. Si lo que quieres decir es «sin descuento o con descuento cero», escribe WHERE discount IS NULL OR discount = 0, o define la columna como NOT NULL DEFAULT 0 para que la duda no llegue a plantearse.
• NULL = NULL es UNKNOWN, y por eso WHERE a.col = b.col nunca une dos NULL. PostgreSQL ofrece IS NOT DISTINCT FROM para cuando de verdad quieres que NULL sea igual a NULL.
• x NOT IN (SELECT y FROM t) no devuelve absolutamente nada si algún y es NULL. Usa NOT EXISTS.
• COUNT(col) ignora los NULL; COUNT(*) cuenta filas. AVG(col) promedia solo los valores no nulos, lo que puede coincidir o no con lo que espera el informe.
• Oracle trata la cadena vacía '' como NULL en las columnas VARCHAR2. PostgreSQL no. El código que se porte de uno a otro no debe depender de ninguno de los dos comportamientos.

COALESCE(col, fallback) es la forma estándar y portable de sustituir un valor; NVL y DECODE de Oracle o IFNULL de MySQL hacen lo mismo con peor portabilidad. Prefiere las expresiones CASE para cualquier cosa más compleja que un único valor por defecto.

Joins, EXISTS e IN

Usa la sintaxis explícita de join ANSI (JOIN ... ON) en lugar de listar las tablas en FROM y hacer el join en WHERE. Separa las condiciones de join de los filtros, elimina la ambigüedad de los outer joins y es lo que esperan todos los optimizadores modernos y todos los revisores de código. La notación (+) de Oracle para outer joins sigue funcionando, pero es legado.

Elige el tipo de join según el resultado que quieres, no el que tecleas siempre:

• INNER JOIN cuando una fila sin coincidencia debe desaparecer.
• LEFT JOIN cuando la fila de la izquierda debe sobrevivir aunque no tenga coincidencia. Un filtro sobre la tabla de la derecha en WHERE (en lugar de en ON) vuelve a convertir el left join en un inner join, porque las filas sin coincidencia tienen NULL ahí y no pasan el filtro.
• EXISTS cuando haces una pregunta de sí o no sobre filas relacionadas y no necesitas sus columnas. Se detiene en la primera coincidencia y nunca duplica filas, algo que sí haría un join si la tabla hija tiene varias coincidencias.
• IN (subquery) para conjuntos de valores pequeños y sin NULL. Los optimizadores reescriben de todos modos la mayoría de las subconsultas IN como semi-joins, así que la diferencia de rendimiento con EXISTS suele ser nula; la diferencia semántica en torno a NULL, no.

-- Customers with at least one unpaid invoice: no duplicates, no NULL trap
SELECT c.id, c.name
FROM   customer c
WHERE  EXISTS (
  SELECT 1
  FROM   invoice i
  WHERE  i.customer_id = c.id
  AND    i.status = 'UNPAID'
);

Añadir DISTINCT para que desaparezcan los duplicados es una mala señal: los duplicados indican que el join multiplica filas, y DISTINCT oculta la causa tras una ordenación adicional. Del mismo modo, UNION elimina duplicados y ordena; UNION ALL no, y es lo que necesitas cuando los dos lados no pueden solaparse.

Parámetros enlazados, siempre

Concatenar la entrada del usuario en el SQL es la causa de la inyección SQL, que sigue figurando entre los riesgos de inyección del OWASP Top 10 décadas después de que las sentencias preparadas hicieran innecesaria esa concatenación. Los parámetros enlazados (? en JDBC, $1 en el protocolo de PostgreSQL, :name en Oracle y en el NamedParameterJdbcTemplate de Spring) envían la sentencia y los valores por separado, de modo que un valor nunca puede alterar la estructura de la sentencia.

También hay un motivo de rendimiento. Oracle guarda en caché las sentencias analizadas en el shared pool, indexadas por su texto; mil variantes de la misma consulta plagadas de literales provocan mil hard parses y contención de latches. Las sentencias preparadas del lado del servidor de PostgreSQL y el prepareThreshold del driver JDBC ofrecen la misma ventaja para las consultas repetidas.

// JDBC: the value is a parameter, never part of the SQL text
try (PreparedStatement ps = conn.prepareStatement(
        "SELECT id, total FROM customer_order WHERE customer_id = ? AND placed_at >= ?")) {
    ps.setLong(1, customerId);
    ps.setObject(2, since);           // java.time.LocalDate maps to DATE
    try (ResultSet rs = ps.executeQuery()) { /* ... */ }
}

Lo único que no puedes enlazar es un identificador: los nombres de tablas y columnas deben salir de una lista blanca definida en tu código, nunca de la entrada del usuario.

Paginación y ordenación

Un ORDER BY sobre un resultado grande sin un índice que ya proporcione ese orden obliga a ordenar, posiblemente volcando a disco. Ordena solo cuando quien consume los datos necesita un orden y, cuando pagines, asegúrate de que el orden es total: ORDER BY placed_at a secas es ambiguo para filas con la misma marca de tiempo y puede mostrar la misma fila en dos páginas. Añade la clave primaria para desempatar.

La paginación con OFFSET lee y descarta todas las filas anteriores a la página; la página 1.000 cuesta mil veces lo que la página 1. La paginación keyset recuerda la última fila vista y pide las filas posteriores:

-- First page
SELECT id, placed_at, total
FROM   customer_order
WHERE  customer_id = $1
ORDER  BY placed_at DESC, id DESC
LIMIT  20;

-- Next page: pass the last row's (placed_at, id) back in
SELECT id, placed_at, total
FROM   customer_order
WHERE  customer_id = $1
AND    (placed_at, id) < ($2, $3)      -- row-value comparison
ORDER  BY placed_at DESC, id DESC
LIMIT  20;

Con un índice sobre (customer_id, placed_at DESC, id DESC), cada página es un breve recorrido de rango del índice, sea cual sea la profundidad. Oracle usa FETCH FIRST 20 ROWS ONLY (desde la 12c) en lugar de LIMIT; la idea del keyset es idéntica.

Transacciones y lotes desde el código de la aplicación

Toda sentencia se ejecuta dentro de una transacción; la cuestión es si tú controlas sus límites. El auto-commit convierte un bucle de 10.000 inserciones en 10.000 transacciones, cada una esperando a que se vuelque el log. Envuelve el lote en una sola transacción y usa la API de batch del driver para que las sentencias viajen en bloque:

conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO event (id, occurred_at, payload) VALUES (?, ?, ?::jsonb)")) {
    for (Event e : events) {
        ps.setObject(1, e.id());           // UUID
        ps.setObject(2, e.occurredAt());   // OffsetDateTime -> timestamptz
        ps.setString(3, e.payloadJson());
        ps.addBatch();
        if (++n % 500 == 0) ps.executeBatch();   // keep memory bounded
    }
    ps.executeBatch();
    conn.commit();
}

El driver JDBC de PostgreSQL reescribe las inserciones por lotes como sentencias INSERT de varias filas cuando reWriteBatchedInserts=true; el de Oracle procesa los lotes de forma nativa. En ambos casos, mantén las transacciones cortas: una transacción que sigue abierta mientras la aplicación habla con una API remota retiene bloqueos y, en PostgreSQL, impide que vacuum haga limpieza.

Para actualizaciones que afectan a una gran parte de una tabla, un único UPDATE ... WHERE orientado a conjuntos supera a cualquier bucle; para cambios realmente enormes, plantéate trocearlos por rangos de clave para que cada transacción sea lo bastante pequeña como para no inflar el undo log ni el WAL.

Decisiones de esquema que condicionan cada consulta

Algunos problemas «de consulta» son en realidad problemas del modelo. Tres reglas evitan la mayoría:

1. Declara las claves foráneas e indexa la columna que referencia. Sin la restricción se cuelan registros huérfanos y el optimizador pierde información que podría usar para eliminar joins. Sin el índice, cada borrado en la tabla padre recorre la tabla hija para comprobar si hay referencias.
2. Usa el tipo adecuado. Fechas en columnas DATE/TIMESTAMP, no en cadenas; importes en NUMERIC, no en coma flotante; identificadores del mismo tipo a ambos lados de cada join para que no haga falta ninguna conversión. Si generas los identificadores en la aplicación, las columnas UUID los almacenan en 16 bytes en lugar de 36 caracteres (un generador de UUID resulta práctico para cargar datos de prueba).
3. Planifica el crecimiento y la purga. Las tablas que no paran de crecer necesitan una política de retención desde el primer día; borrar 100 millones de filas más adelante es mucho más difícil que eliminar una partición.

La guía de normalización trata a fondo la parte de diseño.

Errores comunes

• Una función sobre la columna indexada. WHERE UPPER(name) = 'SMITH' recorre la tabla. Guarda los datos en la forma en que los buscas o crea un índice de expresión sobre UPPER(name).
• Filtrar el lado externo de un LEFT JOIN en el WHERE. LEFT JOIN address a ON ... WHERE a.country = 'FR' se convierte sin avisar en un inner join. Mueve la condición a la cláusula ON.
• NOT IN con una subconsulta que admite NULL. No devuelve nada. Usa NOT EXISTS.
• Traerlo todo y filtrar en el código. Un findAll() seguido de un filtro sobre un stream mueve la tabla entera por la red. Lleva el predicado a la consulta.
• ORDER BY sin criterio de desempate en consultas paginadas. Las filas se repiten o desaparecen entre páginas. Añade la clave primaria.
• Los hints como primera respuesta a la lentitud. Un /*+ PARALLEL */ de Oracle o un índice forzado ocultan el mal predicado que causó el problema y dejan de funcionar el día en que cambia la distribución de los datos. Corrige la consulta y luego mide con el plan.
• Hacer joins de vistas sobre vistas. Las vistas genéricas arrastran columnas y joins que la consulta no necesita, y el optimizador no siempre consigue podarlos. Consulta las tablas base o escribe una vista específica.

Buenas prácticas

• Formatea el SQL de manera coherente y usa alias de tabla cortos y con significado (customer_order o, no t1); una consulta se lee muchas más veces de las que se escribe.
• Prefiere el SQL estándar (COALESCE, CASE, FETCH FIRST, joins ANSI) a las funciones propietarias, salvo que la característica del fabricante aporte una mejora real de rendimiento.
• Revisa el plan de ejecución de cada consulta que toque una tabla grande antes de ponerla en producción, y de nuevo cuando los datos se multipliquen por diez.
• Mantén las transacciones cortas y con límites explícitos; nunca dejes una abierta durante una interacción con el usuario o una llamada externa.
• Deja que la base de datos garantice la integridad: NOT NULL, CHECK, UNIQUE y las claves foráneas detectan bugs que la validación en la aplicación deja pasar cuando hay concurrencia.
• Guarda JSON en una columna jsonb solo para datos realmente variables; las columnas relacionales siguen siendo más rápidas de indexar, restringir y consultar. Si lo haces, valida los documentos con un formateador de JSON antes de depurar una inserción mal formada.

Puntos clave

• Trae solo las columnas y filas que necesitas, y haz el filtrado, la ordenación y la agregación en la base de datos.
• Un predicado es sargable cuando la columna indexada está sola en un lado de la comparación; las funciones y las conversiones implícitas sobre la columna obligan a recorrer la tabla.
• NULL significa desconocido: las comparaciones con él dan UNKNOWN, NOT IN con una subconsulta que contiene un NULL no devuelve nada y COUNT(col) lo ignora.
• Usa EXISTS para saber «si hay alguna fila relacionada», JOIN cuando necesitas las columnas relacionadas y UNION ALL salvo que de verdad necesites eliminar duplicados.
• Enlaza cada valor; la concatenación es a la vez un riesgo de inyección y la ruina de la caché de análisis.
• Pagina con keysets y un orden total, no con un OFFSET profundo.
• Agrupa las escrituras en lotes dentro de transacciones explícitas y cortas, e indexa cada clave foránea.