Aplicações mais rápidas com desnormalização

Quando trabalhamos com bancos de dados relacionais, é comum nos depararmos com situações onde precisamos contar registros relacionados em uma tabela.

Primeiramente vamos considerar as seguintes tabelas:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY, 
    order_date DATE,
    customer VARCHAR(100)
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT,
    product VARCHAR(100),
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Temos uma tabela de orders e queremos contar quantos itens cada pedido possui. Uma abordagem comum seria usar uma subquery com COUNT:

SELECT 
    o.order_id,(SELECT 
          COUNT(*) FROM order_items oi 
     WHERE oi.order_id = o.order_id
) AS total_items
FROM 
    orders o;

Essa query funciona, mas pode se tornar ineficiente em cenários com grandes volumes de dados, pois a subquery é executada para cada linha da tabela orders.

Aqui entra a desnormalização!

A desnormalização em bancos de dados é o processo de introduzir redundância em um banco de dados previamente normalizado, com o objetivo de melhorar o desempenho em operações de leitura (consultas). Envolve a adição de dados duplicados ou a combinação de tabelas para reduzir o número de operações necessárias (joins, counts...), o que pode acelerar a recuperação de informações em sistemas onde a leitura é mais frequente que a escrita.

Agora sabemos o que é desnormalização como podemos simplificar nossa query?
Em vez de contar os itens toda vez que executamos a query, podemos adicionar um campo total_items diretamente na tabela orders. E sempre que um item for adicionado ou removido, incrementamos ou decrementamos esse campo.

Como fazer isso?

1. Adicione o campo total_items na tabela orders:

ALTER TABLE orders ADD COLUMN total_items INT DEFAULT 0;

1. Atualize o campo total_items sempre que um item for adicionado ou removido

2. Sua query fica muito mais simples e eficiente:
   

SELECT 
    order_id,
    total_items
FROM 
    orders;

Vamos inserir dados para podermos validar nossa proposta, podemos inserir uma massa de dados com a seguinte função:

CREATE OR REPLACE FUNCTION generate_sample_data()
RETURNS VOID AS $$
DECLARE
    order_count INT := 200; 
    items_per_order INT := 10;
    current_order_id INT;
    random_customer VARCHAR(100);
    random_product VARCHAR(100);
    random_quantity INT;
BEGIN
    FOR i IN 1..order_count LOOP
        random_customer := 'Customer ' || (floor(random() * 1000)::INT);
        INSERT INTO orders (order_date, customer, total_items)
        VALUES (current_date - (floor(random() * 365)::INT), random_customer, 10)
        RETURNING order_id INTO current_order_id;

        FOR j IN 1..items_per_order LOOP
            random_product := 'Product ' || (floor(random() * 100)::INT);

            random_quantity := (floor(random() * 10)::INT + 1;

            INSERT INTO order_items (order_id, product, quantity)
            VALUES (current_order_id, random_product, random_quantity);
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT generate_sample_data();

No script anterior basicamente criamos 200 registros na tabela orders e 10 registros na tabela order_items para cada order.

Para compararmos e validarmos nossa hipótese vamos executar nossas queries com explain analyse e teremos o seguinte resultado:

Utilizando a subquery:

Podemos ver que executando a consulta com a subquery o execution time é igual a 28.983ms

Utilizando o campo criado na desnormalização:

Sem a subquery nosso resultado é 0.073ms. Sendo assim 397 vezes mais rápido e vale ressaltar que criamos apenas 200 registros na tabela orders, quanto mais registros criarmos mais a query com subquery será lenta e a query desnormalizada irá se manter na mesma média.

Vantagens:

Performance: Evita a execução de subqueries repetidas.
Simplicidade: A query principal fica mais limpa e fácil de entender.
Controle: Você tem um controle direto sobre o valor do contador.

Cuidados:

Consistência: É preciso garantir que o campo total_items seja sempre atualizado corretamente.
Concorrência: Em sistemas com alta concorrência, é importante garantir que as atualizações sejam atômicas para evitar inconsistências.

Espero que esse conteúdo tenha sido útil para você!