Análisis de errores típicos al crear procedimientos analíticos

Hoy, para cerrar este épico viaje por PL/pgSQL, vamos a dejarlo claro: los errores en los procedimientos analíticos son inevitables. ¿Por qué? Porque en analítica se trabaja con datos enormes, cálculos complejos y a veces condiciones bastante rebuscadas. Cuanto más complicado es el query o el procedimiento, más se parece a un laberinto, donde un par de pasos en falso pueden llevarte a resultados incorrectos.

Por suerte, la mayoría de los errores son típicos y se pueden predecir (y evitar). Vamos a repasarlos uno por uno.

1. Falta de índices en campos clave

Los índices son como el GPS en el mundo de las bases de datos. Si no los tienes, la base de datos tiene que recorrer a pie todas las filas de la tabla. En tablas pequeñas se aguanta, pero cuando los datos crecen a millones de filas, tus consultas van más lentas que Windows XP en un Pentium III.

Supón que tienes una tabla de pedidos y quieres calcular las ventas del último mes:

SELECT SUM(order_total)
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month';

Si el campo order_date no tiene índice, PostgreSQL hace un escaneo completo de la tabla (Seq Scan). Y eso casi siempre es lento.

Solución: ¡usa índices! Solo necesitas este comando:

CREATE INDEX idx_order_date ON orders (order_date);

Ahora PostgreSQL podrá buscar en la tabla por order_date mucho más rápido.

Uso de consultas ineficientes

Algunas consultas se ven bonitas, pero funcionan como un ladrillo de cemento en vez de una llave. Por ejemplo, usar subconsultas que podrías reemplazar por un JOIN, o filtrar de más.

En vez de esto:

SELECT product_id, SUM(order_total)
FROM orders
WHERE product_id IN (SELECT id FROM products WHERE category = 'electronics')
GROUP BY product_id;

Mejor hazlo así:

SELECT o.product_id, SUM(o.order_total)
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE p.category = 'electronics'
GROUP BY o.product_id;

Esto evita que PostgreSQL tenga que hacer la subconsulta para cada fila y acelera mucho la ejecución.

Estructura incorrecta de tablas temporales

Las tablas temporales pueden ser una herramienta potente si las usas con cabeza. Pero si olvidas añadir las columnas o índices necesarios, la tabla temporal se convierte en un cuello de botella que ralentiza todo el procedimiento.

Por ejemplo. Creamos una tabla temporal para cálculos intermedios:

CREATE TEMP TABLE temp_sales AS
SELECT region, SUM(order_total) AS total_sales
FROM orders
GROUP BY region;

Pero luego necesitas filtrar por la columna total_sales, y no hay índice en ese campo.

Antes de usar una tabla temporal, piensa cómo vas a trabajar con ella. Si necesitas filtrar por una columna, añade un índice:

CREATE INDEX idx_temp_sales_total_sales ON temp_sales (total_sales);

Errores en los cálculos (por ejemplo, división por cero)

Dividir por cero es el clásico problema de la analítica. SQL no va a mirar para otro lado, simplemente va a romper la ejecución de la consulta.

Supón que quieres calcular el valor medio de los pedidos:

SELECT SUM(order_total) / COUNT(*) AS avg_order_value
FROM orders;

Si la tabla orders está vacía, vas a dividir por cero y la consulta fallará.

Para evitarlo, usa un control para cuando el contador sea cero:

SELECT
    CASE 
        WHEN COUNT(*) = 0 THEN 0
        ELSE SUM(order_total) / COUNT(*)
    END AS avg_order_value
FROM orders;

Falta de logging y control de ejecución

Los procedimientos en PL/pgSQL pueden ser complejos y tener varias etapas: desde cálculos intermedios hasta informes finales. Si algo falla en esa cadena y no tienes logging, nunca sabrás en qué paso y por qué todo se fue al garete.

Supón que creamos un procedimiento para calcular métricas, pero olvidamos comprobar los datos esperados en cada etapa. Al final, todo el procedimiento peta cuando se encuentra con datos inesperados (por ejemplo, tablas vacías).

Para evitarlo, puedes añadir logging en cada paso importante del procedimiento. Por ejemplo:

RAISE NOTICE 'Inicio del cálculo de ventas';
-- Tu código aquí...

RAISE NOTICE 'El módulo % terminó correctamente', módulo;

Para procedimientos más complejos, mejor guarda los logs en una tabla especial:

CREATE TABLE log_analytics (
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_message TEXT
);

En el procedimiento añade:

INSERT INTO log_analytics (log_message)
VALUES ('El procedimiento terminó correctamente');

Problemas de rendimiento por falta de optimización

La optimización es importante no solo para las consultas, sino también para los procedimientos. Si muchos usuarios usan el procedimiento, su ejecución puede convertirse en un cuello de botella del sistema.

Por ejemplo, aquí hay un procedimiento que recalcula métricas para todas las regiones, aunque solo necesitas los datos de una región:

CREATE OR REPLACE FUNCTION calculate_sales()
RETURNS VOID AS $$
BEGIN
    -- Recalcular para todas las regiones
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

Esto genera carga innecesaria.

¿Cómo evitarlo? Añade la opción de filtrar los datos pasando la región como parámetro:

CREATE OR REPLACE FUNCTION calculate_sales(p_region TEXT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    WHERE region = p_region
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

Ahora el procedimiento no procesará datos innecesarios y la consulta irá más rápido.

Ignorar herramientas de análisis de rendimiento

Herramientas como EXPLAIN ANALYZE son tus colegas que te muestran dónde se atascan las consultas y cómo arreglarlo. Si escribes un procedimiento pero no analizas su rendimiento, eres como un programador de computadoras cuánticas sin osciloscopio: parece que funciona, pero nadie sabe qué está pasando.

Por ejemplo. El problema en esta consulta se ve con EXPLAIN ANALYZE:

SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

Esta consulta es ineficiente porque la función EXTRACT() desactiva el uso de índices.

Puedes arreglarlo así. Analiza la consulta con:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE order_date >= DATE '2023-01-01' AND order_date < DATE '2024-01-01';

¿Cómo evitar los errores típicos?

Para prevenir errores, sigue estas prácticas:

1. Usa índices en los campos que se usan para filtrar o hacer joins.
2. Optimiza tus consultas: elimina subconsultas innecesarias, usa JOIN.
3. Haz logging de la ejecución. Así depurarás más fácil si algo falla.
4. Siempre revisa tus procedimientos con herramientas como EXPLAIN ANALYZE.
5. ¿Ves un problema de rendimiento? Piensa en usar particionamiento o en rehacer la lógica de la consulta.

Ahora tienes el conocimiento para anticipar y evitar errores que podrían dejar a tus analistas sin cafetera y sin Wi-Fi por culpa de consultas lentas.