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? Adicione o campo total_items na tabela orders: ALTER TABLE orders ADD COLUMN total_items INT DEFAULT 0; Atualize o campo total_items sempre que um item for adicionado ou removido 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ê!

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)
);

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

ALTER TABLE orders ADD COLUMN total_items INT DEFAULT 0;

SELECT 
    order_id,
    total_items
FROM 
    orders;

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();

Related Posts

Exploring Tronpy: A Beacon of Open Source Innovation in...

Mar 11, 2025  0

Why can deleted files be recovered?

Mar 20, 2025  0

MPS 2024.3 Is Out!

Jan 26, 2025  0