DEV Community: Guilherme Rodrigues

Supercharge Read Performance: 397x Faster Database Queries Through Denormalization

Guilherme Rodrigues — Mon, 17 Mar 2025 14:37:49 +0000

When working with relational databases, we often encounter situations where we need to count related records in a table.

First, let's consider the following tables:

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

We have an orders table and want to count how many items each order contains. A common approach would be to use a COUNT subquery:

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

This query works, but can become inefficient in scenarios with large data volumes as the subquery executes for each row in the orders table.

Enter Denormalization!

Denormalization in databases is the process of introducing redundancy to a previously normalized database, aiming to improve performance for read operations (queries). It involves adding duplicated data or combining tables to reduce the number of required operations (joins, counts...), which can speed up information retrieval in systems where reads are more frequent than writes.

Now that we understand denormalization, how can we simplify our query?

Instead of counting items every time we execute the query, we can add a total_items field directly to the orders table. Whenever an item is added or removed, we increment or decrement this field.

Add the total_items column to the orders table:

ALTER TABLE orders ADD COLUMN total_items INT DEFAULT 0;

Update the total_items field whenever items are added/removed
Your query becomes simpler and more efficient:

SELECT 
    order_id,
    total_items
FROM 
    orders;

Let's insert sample data to validate our proposal using this function:

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

This script creates 200 records in orders and 10 corresponding records in order_items for each order.

To compare performance, we'll execute both queries with EXPLAIN ANALYZE:

Using subquery:

Execution time: 28.983ms

Using denormalized field:

Execution time: 0.073ms (397x faster). Note this test used only 200 order records - performance gains become more significant as data volume increases.

Advantages:

Performance: Avoids repetitive subquery execution
Simplicity: Cleaner main query structure
Control: Direct management of counter values

Considerations:

Consistency: Must ensure total_items always reflects accurate counts
Concurrency: Requires atomic updates in high-concurrency systems

Hope you found this content useful!

Aplicação 397x mais rápida com desnormalização

Guilherme Rodrigues — Fri, 14 Mar 2025 22:20:32 +0000

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ê!

Singleflight: Reduce Costs and Improve Performance in Go Applications

Guilherme Rodrigues — Tue, 04 Mar 2025 21:18:48 +0000

As software engineers, we should always consider how to improve the performance of our applications while also reducing costs. Achieving both of these goals simultaneously is not always easy; often, we are forced to choose between being cheaper or faster.

In this article, I want to show you how we can achieve both goals by using singleflight.

What is Singleflight?

Singleflight is a standard Go library (golang.org/x/sync/singleflight) that prevents redundant calls to the same function. It achieves this by grouping concurrent requests to the same resource and returning the first result to all subsequent requests.

Use cases examples

Queries to external APIs charged per request.
Read operations in databases.
Prevention of rate limiting in APIs with request limits.

Example: Product Price Search System

An e-commerce platform where users search for product prices.
The price is obtained from an external API that charges per request.
Multiple users can search for the price of the same product simultaneously.

Code without singleflight

package main

import (
    "encoding/json"
    "log"
    "net/http"
    "time"
)

var cost float64


func fetchProductPrice(productID string) (float64, error) {
    log.Printf("[COST: $0.01] Calling external API for product: %s\n", productID)
    time.Sleep(2 * time.Second) // Simulates latency
    cost += 0.01
    return 99.99, nil 
}


func getProductPriceHandler(w http.ResponseWriter, r *http.Request) {
    productID := r.URL.Query().Get("id")

    price, err := fetchProductPrice(productID)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    response := map[string]interface{}{
        "product_id": productID,
        "price":      price,
    }


    w.Header().Set("Content-Type", "application/json")
    if err := json.NewEncoder(w).Encode(response); err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
}

func getCost(w http.ResponseWriter, r *http.Request) {

    response := map[string]interface{}{
        "total_cost": fmt.Sprintf("%.2f", cost),
    }


    w.Header().Set("Content-Type", "application/json")
    if err := json.NewEncoder(w).Encode(response); err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
}

func clearCosts(w http.ResponseWriter, r *http.Request) {
    cost = 0
}

func main() {

    mux := http.NewServeMux()

    mux.HandleFunc("/products/{id}/price", getProductPriceHandler)
    mux.HandleFunc("/costs", getCost)
    mux.HandleFunc("/clear-costs", clearCosts)


    log.Println("API running without singleflight on port :8080...")
    if err := http.ListenAndServe(":8080", mux); err != nil {
        log.Fatalf("Could not start server: %s\n", err.Error())
    }
}

Problem: If 100 users search for the price of the same product at the same time, the system will make 100 calls to the external API, generating a cost of $1.00 and increasing latency.

Code with singleflight

package main

import (
    "encoding/json"
    "log"
    "net/http"
    "time"

    "golang.org/x/sync/singleflight"
)

var (
    group singleflight.Group
    cost  float64
)


func fetchProductPrice(productID string) (float64, error) {
    log.Printf("[COST: $0.01] Calling external API for product: %s\n", productID)
    time.Sleep(2 * time.Second) 
    cost += 0.01
    return 99.99, nil 
}

func getProductPriceHandler(w http.ResponseWriter, r *http.Request) {
    productID := r.URL.Query().Get("id")

    // Uses singleflight to avoid redundant calls
    result, err, _ := group.Do(productID, func() (interface{}, error) {
        return fetchProductPrice(productID)
    })
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }


    price := result.(float64)


    response := map[string]interface{}{
        "product_id": productID,
        "price":      price,
    }


    w.Header().Set("Content-Type", "application/json")
    if err := json.NewEncoder(w).Encode(response); err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
}

func getCost(w http.ResponseWriter, r *http.Request) {

    response := map[string]interface{}{
        "total_cost": fmt.Sprintf("%.2f", cost),
    }
    w.Header().Set("Content-Type", "application/json")
    if err := json.NewEncoder(w).Encode(response); err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
}

func clearCosts(w http.ResponseWriter, r *http.Request) {
    cost = 0
}

func main() {
    mux := http.NewServeMux()
    mux.HandleFunc("/products/{id}/price", getProductPriceHandler)
    mux.HandleFunc("/costs", getCost)
    mux.HandleFunc("/clear-costs", clearCosts)

    log.Println("API running with singleflight on port :8081...")
    if err := http.ListenAndServe(":8081", mux); err != nil {
        log.Fatalf("Could not start server: %s\n", err.Error())
    }
}

Improvement: Only one call is made to the external API, regardless of how many users are searching for the price of the same product. This reduces the cost to $0.01 and decreases latency.

Tests using Vegeta

Without Singleflight

vegeta attack -duration=2s -rate=200 -workers=200 -targets=targets_without_singleflight.txt | vegeta report

Requests      [total, rate, throughput]         400, 200.47, 100.08
Duration      [total, attack, wait]             3.997s, 1.995s, 2.001s
Latencies     [min, mean, 50, 90, 95, 99, max]  2.001s, 2.002s, 2.002s, 2.002s, 2.002s, 2.002s, 2.003s
Bytes In      [total, mean]                     12800, 32.00
Bytes Out     [total, mean]                     0, 0.00
Success       [ratio]                           100.00%
Status Codes  [code:count]                      200:400  
Error Set:

Latency:
- All requests took ~2 seconds to complete.
- This happens because each request made an independent call to the external API, which has a simulated latency of 2 seconds.
Throughput:
- The throughput was 100.08 requests per second.
- This indicates that the system was able to process about 100 requests per second, but with high latency.
Cost:
- Since each request made a call to the external API, the cost would be 4.00(400 requests × 0.01 per call).
- GET http://localhost:8080/costs
```
{"total_cost":"4.00"}
```

With singleflight

vegeta attack -duration=2s -rate=200 -workers=200 -targets=targets_with_singleflight.txt | vegeta report  
Requests      [total, rate, throughput]         400, 200.46, 198.32
Duration      [total, attack, wait]             2.017s, 1.995s, 21.452ms
Latencies     [min, mean, 50, 90, 95, 99, max]  15.337ms, 1.014s, 1.019s, 1.811s, 1.911s, 1.991s, 2.009s
Bytes In      [total, mean]                     12800, 32.00
Bytes Out     [total, mean]                     0, 0.00
Success       [ratio]                           100.00%
Status Codes  [code:count]                      200:400  
Error Set:

Latency:
- The minimum latency was 15.337ms, and the average was 1.014s.
- This happens because only one call was made to the external API (taking ~2 seconds), and the other requests received the result in less than 22ms.
Throughput:
- The throughput increased to 198.32 requests per second.
- This indicates that the system was able to process almost double the number of requests per second, thanks to the reduced latency for most requests.
Cost:
- Since only one call was made to the external API, the cost would be 0.01 (1 request × 0.01 per call).
- GET http://localhost:8081/costs
```
{"total_cost":"0.01"}
```

Direct Comparison

Metric	Without `singleflight`	With `singleflight`	Gain
Average Latency	2.002s	1.014s	~49%
Throughput	100.08 req/s	198.32 req/s	~98%
Cost	$4.00	$0.01	99.75%

Latency Reduction:
- Singleflight reduced the average latency from 2.002s to 1.014s, a gain of ~49%.
- This happens because most requests were resolved in less than 22ms, while only the first request took ~2 seconds.
Throughput Increase:
- The throughput increased from 100.08 req/s to 198.32 req/s, a gain of ~98%.
- This indicates that the system was able to process almost double the number of requests per second, thanks to the reduced latency.
Cost Reduction:
- The cost dropped from 4.00 to 0.01, a savings of 99.75%.
- This happens because only one call was made to the external API, instead of 400.

When to Use (and When Not to Use)

Use:
- For read operations (queries to APIs or databases).
- When the resource is idempotent (does not change the system state).
Do not use:
- For write operations (creation, update, deletion).
- When the result may vary between calls.
- If you are working with multiple pods is good to use a cache tool as Redis with singleflight

Conclusion

Singleflight is a powerful tool to optimize Go applications, reducing costs and improving performance in high-concurrency scenarios. Try implementing it in your projects and see the difference!

Try it out: Add singleflight to your next project and share the results!! 🚀

check codes here https://github.com/guil95/singleflight