Optimización de funciones para trabajar con grandes volúmenes de datos

Cuando hablamos de optimizar funciones en PostgreSQL, normalmente nos referimos a dos cosas clave: indexación y particionado. Estas dos técnicas ayudan a procesar grandes volúmenes de datos más rápido, eliminando cálculos innecesarios y permitiendo acceder a los datos "al grano". Vamos a verlo en detalle.

Los índices en el mundo de las bases de datos funcionan igual que los índices en los libros. Cuando buscas info en un libro, no lees todas las páginas una por una. Vas al índice, encuentras el tema que te interesa y vas directo a la página. Más o menos lo mismo hacen los índices en PostgreSQL.

Creación de índices

Los índices se crean con el comando CREATE INDEX. Aquí tienes un ejemplo sencillo:

-- Creamos un índice en la columna id de la tabla users para acelerar las búsquedas
CREATE INDEX idx_users_id ON users (id);

Ahora, si ejecutas una consulta como:

SELECT * FROM users WHERE id = 42;

PostgreSQL va a usar el índice creado para encontrar la fila que buscas mucho más rápido.

Ejemplo: Optimización de una función usando índices

Supón que tienes una función que selecciona datos de pedidos de la tabla orders por usuario:

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE user_id = user_id;
END; 
$$ LANGUAGE plpgsql;

Si la tabla orders tiene millones de filas, la función va a ser lenta. ¿La solución? Creamos un índice en user_id:

CREATE INDEX idx_orders_user_id ON orders (user_id);

Ahora la consulta dentro de la función será mucho más rápida porque PostgreSQL usará el índice para buscar las filas.

Tipos de índices

PostgreSQL soporta varios tipos de índices, pero los más populares son B-TREE y GIN. Aquí tienes una comparación rápida:

Si quieres profundizar más en los índices, échale un ojo a la documentación oficial de PostgreSQL.

Particionado de datos

Si los índices son para acelerar las búsquedas, el particionado es una técnica que ayuda a dividir una tabla en "trozos" más pequeños (particiones). Esto es útil cuando tienes una cantidad enorme de datos en una sola tabla.

Imagina que tienes una tabla orders y guarda pedidos de los últimos 10 años. Si haces una consulta para buscar pedidos del último mes, PostgreSQL igual va a mirar toda la tabla, lo cual es costoso. El particionado resuelve esto dividiendo los datos, por ejemplo, por años.

Creación de una tabla particionada

Así puedes crear una tabla particionada:

-- Creamos la tabla orders como partición padre
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- Creamos tablas hijas para cada año
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Ahora, cuando ejecutes una consulta como:

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

PostgreSQL va a saber al instante que solo tiene que buscar en la tabla orders_2023, en vez de revisar toda la tabla.

Uso de particionado en funciones

Imagina que tienes una función que selecciona pedidos de un año concreto. Gracias al particionado, las consultas dentro de la función serán más rápidas porque PostgreSQL trabajará solo con la tabla hija correspondiente.

CREATE OR REPLACE FUNCTION get_orders_by_year(year INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE order_date >= make_date(year, 1, 1) 
      AND order_date < make_date(year + 1, 1, 1);
END;
$$ LANGUAGE plpgsql;

Casos prácticos

1. Casos de indexación

Búsqueda por cadenas: si tienes una tabla de productos y buscas mucho por el nombre, crea un índice en el campo name:

CREATE INDEX idx_products_name ON products (name);

Acelerar ordenación: si en tus consultas sueles ordenar por fecha, crea un índice:

CREATE INDEX idx_orders_date ON orders (order_date);

2. Casos de particionado

Datos históricos: si la tabla tiene datos con marca de tiempo, particionar por días, meses o años acelera mucho las consultas.

Datos geográficos: si la tabla tiene datos por países, crea particiones para cada país.

Errores potenciales y cómo solucionarlos

Mucha gente comete el error de crear demasiados índices. Esto hace que las inserciones y actualizaciones sean más lentas porque PostgreSQL tiene que actualizar los índices cada vez que cambias la tabla. Consejo: crea índices solo en los campos que usas mucho en condiciones o en ordenaciones.

Otro error típico es particionar mal. Si creas demasiadas particiones pequeñas (por ejemplo, por días en vez de por meses), puedes acabar con mucho overhead gestionando todas esas tablas.