Eliminación de consultas N+1 y subconsultas correlacionadas en una API Postgres de Node.js
Análisis detallado de dos endpoints Express lentos en Postgres: cómo la falta de índices, los bucles anidados N+1 y las subconsultas por fila contribuyen a la lentitud, y cómo reducir su tiempo de respuesta a menos de 200 ms.
A menudo se culpa a la infraestructura por los endpoints lentos, pero muchos de ellos son lentos por razones que se encuentran claramente en el código del manejador. Este análisis examina dos endpoints de Express respaldados por Postgres: uno que devuelve los pedidos de un usuario en 2,24 segundos y otro de generación de informes que necesita aproximadamente 29 segundos. Ninguna solución implica el uso de Redis, servidores adicionales o una nueva arquitectura; cinco errores comunes explican casi todo el retraso, y corregirlos hace que ambas respuestas estén por debajo de los 200 ms. Al final, deberías poder identificar los mismos patrones en tu propio código y saber qué formas de consulta utilizar en su lugar.
Si desea un método general para identificar el cuello de botella antes de modificar cualquier código, nuestra guía sobre cómo encontrar el verdadero cuello de botella en un endpoint lento de Node.js aborda el aspecto del perfilado. Aquí el enfoque está en los patrones de consulta específicos.
Los dos endpoints bajo el microscopio
El primer manejador, GET /orders/:userId, carga los pedidos de un usuario, luego recorre cada pedido para obtener sus elementos individuales y, a continuación, recorre cada elemento para buscar el producto correspondiente. Para cada elemento también calcula un hash SHA-256 y lo adjunta como campo etag.
// GET /orders/:userId
app.get('/orders/:userId', async (req, res) => {
const { userId } = req.params;
const ordersResult = await pool.query(
`SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
);
const orders = ordersResult.rows;
const enrichedOrders = [];
for (const order of orders) {
const itemsResult = await pool.query(
`SELECT * FROM order_items WHERE order_id = ${order.id}`
);
const items = itemsResult.rows;
const enrichedItems = [];
for (const item of items) {
const productResult = await pool.query(
`SELECT * FROM products WHERE id = ${item.product_id}`
);
const product = productResult.rows[0];
const etag = crypto
.createHash('sha256')
.update(JSON.stringify({ item, product }))
.digest('hex');
enrichedItems.push({ ...item, product, etag });
}
enrichedOrders.push({ ...order, items: enrichedItems });
}
res.json(enrichedOrders);
});
El segundo manejador crea un resumen del estado de los pedidos. Selecciona cada pedido junto con una tasa de cancelación por usuario, calculada mediante una subconsulta, y luego recorre las filas resultantes en JavaScript para contar los estados y almacenar la tasa por usuario. Aunque el fragmento contiene SQL, el propio manejador está escrito en JavaScript.
app.get('/reports/order-status-summary', async (req, res) => {
const result = await pool.query(`
SELECT
o.id AS order_id,
o.user_id,
o.status,
(
SELECT round(
count(*) FILTER (WHERE o2.status = 'cancelled')::numeric
/ NULLIF(count(*), 0),
4
)
FROM orders o2
WHERE o2.user_id = o.user_id
) AS cancellation_rate
FROM orders o
`);
const rows = result.rows;
const totalsByUser = {};
const cancellationRateByUser = {};
for (const row of rows) {
totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
totalsByUser[row.user_id][row.status] =
(totalsByUser[row.user_id][row.status] || 0) + 1;
cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
}
res.json({
usersProcessed: Object.keys(totalsByUser).length,
totalsByUser,
cancellationRateByUser,
});
});
Al leerlo rápidamente, ambos parecen razonables. Los problemas solo se vuelven evidentes con volúmenes de datos reales. La base de datos de pruebas contiene:
- usuarios: 5,000
- productos: 2,000
- pedidos: 500,000
- elementos_de_pedido: 1,500,000
A esa escala, el endpoint de pedidos tarda 2.24 segundos, lo cual es demasiado tiempo para una pantalla sencilla de “Mis pedidos”, y el endpoint de resúmenes necesita 29.26 segundos.
Por qué GET /orders/:userId tarda 2.24 segundos
Un usuario que toque en “Mis pedidos” no debería tener que esperar dos segundos frente a un indicador de carga. La demora se debe a tres capas separadas de ineficiencias que se acumulan una sobre la otra.
La búsqueda inicial escanea y ordena toda la tabla
La primera consulta ya presenta varios problemas en solo un par de líneas.
const ordersResult = await pool.query(
`SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
);
- No existe índice en
orders.user_id, por lo que Postgres realiza un escaneo secuencial de las 500,000 filas para encontrar aproximadamente 102 que pertenecen al usuario. - Tampoco hay nada que respalde la cláusula
ORDER BY created_at DESC; por eso, después del escaneo, las filas coincidentes se ordenan sin la ayuda de ningún índice. - El valor de
userIdse inserta directamente en la cadena SQL. Esto representa una vulnerabilidad de inyección SQL, ya que el valor proviene directamente de la URL, y además convierte cada solicitud en una consulta textualmente distinta.
N+1 anidado dos veces
Los bucles son donde se pierde la mayor parte del tiempo.
for (const order of orders) {
const itemsResult = await pool.query(`SELECT * FROM order_items WHERE order_id = ${order.id}`);
...
for (const item of items) {
const productResult = await pool.query(`SELECT * FROM products WHERE id = ${item.product_id}`);
- Este es el patrón clásico N+1, anidado dos veces. Aproximadamente 102 órdenes generan 102 consultas por artículo, y los unos 500 artículos producen otras ~500 consultas por producto. Eso supone más de 600 idas y venidas a Postgres, ejecutadas una tras otra porque cada
awaitespera al anterior, pagando así toda la latencia de red. Los mismos datos pueden obtenerse con una a tres consultas utilizandoJOINoWHERE id = ANY(...). order_items.order_idno tiene índice, por lo que cada una de las 102 consultas por artículo debe leer por sí sola 1,5 millones de filas.
Hashing que bloquea el bucle de eventos sin motivo
El último problema es el trabajo del procesador y no las operaciones de E/S.
const etag = crypto.createHash('sha256').update(JSON.stringify({ item, product })).digest('hex');
El hash se calcula de forma síncrona para cada elemento, unas 500 veces por solicitud. Dado que Node.js ejecuta JavaScript en un único hilo, cada uno de esos cálculos bloquea el bucle de eventos y retrasa todas las demás solicitudes que está procesando. Peor aún, el valor nunca se utiliza para el caché ni en solicitudes condicionales, por lo que todo ese trabajo no sirve para nada. Nuestro artículo sobre cómo process.nextTick puede agotar el bucle de eventos explica por qué bloquear ese hilo es tan costoso.
En conjunto, la latencia es la suma en secuencia de más de 600 idas y venidas, varias exploraciones completas de la tabla y cientos de cálculos de hash.
Por qué el informe resumido tarda unos 29 segundos
Un usuario que abra el resumen de su pedido y espere cerca de medio minuto razonablemente asumirá que la página está rota. La causa se concentra en una única sentencia SQL.
SELECT
o.id AS order_id, o.user_id, o.status,
(
SELECT round(
count(*) FILTER (WHERE o2.status = 'cancelled')::numeric / NULLIF(count(*), 0),
4
)
FROM orders o2
WHERE o2.user_id = o.user_id
) AS cancellation_rate
FROM orders o
- Una subconsulta correlacionada se ejecuta una vez por fila exterior. La consulta exterior devuelve los 500,000 pedidos, y para cada uno Postgres vuelve a ejecutar la subconsulta sobre
orders o2filtrada por eluser_idde esa fila. Con unos 100 pedidos por usuario, la misma tasa de cancelación se recalcula aproximadamente 100 veces para cada usuario, lo que suma un total de unas 500,000 ejecuciones de subconsulta. - Ningún índice sirve para las búsquedas repetidas. Sin un índice en
orders.user_id, cada una de esas ejecuciones realiza un escaneo completo. Un índice haría que cada ejecución fuera más rápida, pero no eliminaría el desperdicio inherente de calcular la misma respuesta cien veces.
totalsByUser y cancellationRateByUser. Eso es una agregación que Postgres podría haber realizado de una sola vez con GROUP BY.Paso uno: agregar índices que se ajusten al patrón de acceso
Los índices suelen ser la solución más económica y eficaz al principio. Aquí se necesitan dos: un índice compuesto en orders (user_id, created_at DESC) para que tanto el filtrado como el ordenamiento se realicen mediante el índice, y un índice en order_items (order_id) para las búsquedas de los artículos. La orden de comandos a continuación crea ambos dentro del contenedor de Postgres; IF NOT EXISTS permite ejecutarlo nuevamente sin problemas.
docker exec slow-api-postgres psql -U postgres -d shop -c "
CREATE INDEX IF NOT EXISTS idx_orders_user_id_created_at ON orders (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items (order_id);
"
El orden de las columnas en el índice compuesto es importante. Al colocar user_id primero, Postgres puede ir directamente a las filas de un usuario, y como esas filas ya están almacenadas en orden created_at DESC dentro del índice, el paso de ordenamiento desaparece por completo. En una tabla de producción con mucho tráfico, considere usar CREATE INDEX CONCURRENTLY para que su creación no bloquee las escrituras.
Con los índices en su lugar, ambos controladores pueden reescribirse para obtener datos en bloque y dejar que la base de datos realice la agregación.
Reescribiendo GET /orders/:userId con consultas por lotes
La nueva versión realiza exactamente tres consultas. Obtiene los pedidos del usuario mediante una consulta parametrizada, recopila sus IDs y carga todos los artículos relacionados con order_id = ANY($1); luego elimina las duplicadas de los IDs de los productos con un Set y carga esos productos en otra consulta más. Los resultados se combinan en memoria mediante dos búsquedas en Map, y un retorno anticipado gestiona a los usuarios que no tienen pedidos.
app.get('/orders/:userId', async (req, res) => {
const { userId } = req.params;
const ordersResult = await pool.query(
'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
[userId]
);
const orders = ordersResult.rows;
if (orders.length === 0) {
return res.json([]);
}
const orderIds = orders.map((o) => o.id);
const itemsResult = await pool.query(
'SELECT * FROM order_items WHERE order_id = ANY($1)',
[orderIds]
);
const items = itemsResult.rows;
const productIds = [...new Set(items.map((i) => i.product_id))];
const productsResult = await pool.query(
'SELECT * FROM products WHERE id = ANY($1)',
[productIds]
);
const productsById = new Map(productsResult.rows.map((p) => [p.id, p]));
const itemsByOrderId = new Map();
for (const item of items) {
const enrichedItem = { ...item, product: productsById.get(item.product_id) };
if (!itemsByOrderId.has(item.order_id)) {
itemsByOrderId.set(item.order_id, []);
}
itemsByOrderId.get(item.order_id).push(enrichedItem);
}
const enrichedOrders = orders.map((order) => ({
...order,
items: itemsByOrderId.get(order.id) || [],
}));
res.json(enrichedOrders);
});
Los cambios, uno por uno:
- El índice compuesto en
orders (user_id, created_at DESC)convierte la cláusulaWHEREjunto conORDER BYen un escaneo de índice en lugar de un escaneo secuencial y ordenamiento de más de 500,000 filas.
order_items (order_id) implica que las búsquedas de artículos ya no escanean 1,5 millones de filas.$1 reemplazan la interpolación de cadenas, lo que cierra la vulnerabilidad a inyecciones SQL. Tenga en cuenta que con node-postgres aún se planifica una consulta parametrizada sin nombre por ejecución; si desea que Postgres reutilice un plan, utilice una sentencia preparada con nombre.Una precaución práctica: eliminar el campo etag cambia la estructura de la respuesta. Asegúrese de que ningún cliente dependa de él antes de implementar este cambio.
Reescribiendo el informe con GROUP BY
El informe optimizado ejecuta dos consultas de agregación específicas: una agrupa por user_id y status para obtener conteos, mientras que la otra agrupa por user_id para calcular la tasa de cancelaciones. El JavaScript solo reorganiza las filas ya agregadas en el formato de respuesta. Como antes, el procesador es JavaScript que incorpora SQL.
// GET /reports/order-status-summary (OPTIMIZED / "after")
app.get('/reports/order-status-summary-optimized', async (req, res) => {
const statusResult = await pool.query(`
SELECT user_id, status, count(*)::int AS count
FROM orders
GROUP BY user_id, status
`);
const rateResult = await pool.query(`
SELECT
user_id,
round(
count(*) FILTER (WHERE status = 'cancelled')::numeric
/ NULLIF(count(*), 0),
4
) AS cancellation_rate
FROM orders
GROUP BY user_id
`);
const totalsByUser = {};
for (const row of statusResult.rows) {
totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
totalsByUser[row.user_id][row.status] = row.count;
}
const cancellationRateByUser = {};
for (const row of rateResult.rows) {
cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
}
res.json({
usersProcessed: Object.keys(totalsByUser).length,
totalsByUser,
cancellationRateByUser,
});
});
Qué cambió y por qué es importante:
- La subconsulta correlacionada se convierte en un
GROUP BY user_id. Postgres calcula la tasa una vez por usuario en un único proceso de agregación: 5,000 evaluaciones en lugar de unas 500,000. - El conjunto de resultados pasa de 500,000 filas a aproximadamente 5,000 a 20,000. Postgres devuelve una fila por cada par
(user_id, status)para los conteos y una fila por usuario para la tasa, sin columnas calculadas duplicadas.
orders, en lugar de una consulta anidada dentro de otra para cada fila.El tiempo de ejecución pasa de aproximadamente 26 a 29 segundos a unos 0.1 a 0.19 segundos, es decir, entre 150 y 260 veces más rápido. Además, se verificó que el nuevo endpoint devolvía los mismos valores de totalsByUser y cancellationRateByUser que el original para los 5,000 usuarios. Esa verificación de equivalencia merece ser copiada: siempre que reescriba una consulta para mejorar su velocidad, compare los resultados con la versión lenta antes de reemplazarla.
Ambas formas de agregación siguen leyendo toda la tabla orders en cada solicitud. Eso está bien a esta escala, pero si la tabla sigue creciendo, una única consulta que utilice cláusulas FILTER, una vista materializada o una tabla de resumen actualizada periódicamente puede mejorar aún más el rendimiento del informe.
Puntos clave
- Indexe las columnas que se filtran y se ordenan, y ajuste el orden del índice compuesto a la consulta: primero las columnas de igualdad, luego la columna de ordenación.
- Considere cualquier llamada a
await db.query()dentro de un bucle como una señal de alerta; cámbielo por consultas por lotes conJOINoANY($1). - Siempre utilice consultas parametrizadas para los valores que provienen de la solicitud.
- Tenga cuidado con las subconsultas correlacionadas que vuelven a calcular por fila lo que podría agruparse una sola vez por entidad.
- Deje que la base de datos realice el agregado y devuelva solo las filas que realmente necesita la respuesta.
Lecturas relacionadas
- Encontrar el verdadero cuello de botella en un punto final lento de Node.js — Aprenda un método sistemático para rastrear la latencia del backend a lo largo de la ruta de solicitud, desde el código de Node.js hasta las consultas a la base de datos, utilizando medición de tiempos y EXPLAIN ANALYZE.
- Registro estructurado en Node.js: Conviertiendo el caos de la depuración en producción en claridad — Aprenda por qué console.log falla en las aplicaciones Node.js en producción y cómo el registro estructurado, los niveles de registro y los IDs de correlación convierten los errores difíciles en soluciones rápidas.