Denormalization Strategies: Performance Optimization Guide

-- Normalized query: 5 JOINs, 12 seconds on 10M rows
SELECT
    c.name AS category,
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM categories c
JOIN products p ON p.category_id = c.id
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY c.name, DATE_TRUNC('month', o.created_at);

-- Denormalized summary table
CREATE TABLE monthly_category_sales (
    category_name VARCHAR(100),
    month DATE,
    order_count INT,
    revenue DECIMAL(12,2),
    units_sold INT,
    PRIMARY KEY (category_name, month)
);

-- Refresh with scheduled job
INSERT INTO monthly_category_sales (category_name, month, order_count, revenue, units_sold)
SELECT
    c.name,
    DATE_TRUNC('month', o.created_at),
    COUNT(DISTINCT o.id),
    SUM(oi.quantity * oi.unit_price),
    SUM(oi.quantity)
FROM categories c
JOIN products p ON p.category_id = c.id
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.created_at >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')
  AND o.created_at < DATE_TRUNC('month', NOW())
  AND o.status = 'completed'
GROUP BY c.name, DATE_TRUNC('month', o.created_at)
ON CONFLICT (category_name, month) DO UPDATE
SET order_count = EXCLUDED.order_count,
    revenue = EXCLUDED.revenue,
    units_sold = EXCLUDED.units_sold;

-- Create materialized view for sales dashboard
CREATE MATERIALIZED VIEW sales_dashboard AS
SELECT
    c.name AS category,
    DATE_TRUNC('day', o.created_at) AS day,
    p.brand,
    COUNT(DISTINCT o.id) AS orders,
    SUM(oi.quantity) AS units,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
WHERE o.status = 'completed'
GROUP BY c.name, DATE_TRUNC('day', o.created_at), p.brand
WITH DATA;

-- Create indexes on the materialized view
CREATE INDEX idx_sales_dashboard_day ON sales_dashboard (day);
CREATE INDEX idx_sales_dashboard_category ON sales_dashboard (category);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_dashboard;

-- Query: now a simple SELECT from one table
SELECT category, SUM(revenue) as total_revenue
FROM sales_dashboard
WHERE day >= NOW() - INTERVAL '30 days'
GROUP BY category
ORDER BY total_revenue DESC;

-- Normalized: needs JOIN for category name
SELECT o.id, o.total, p.name, c.name AS category
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id;

-- Denormalized: category_name stored in products
ALTER TABLE products ADD COLUMN category_name VARCHAR(100);

-- Update existing data
UPDATE products p
SET category_name = c.name
FROM categories c
WHERE c.id = p.category_id;

-- Create trigger to keep in sync
CREATE OR REPLACE FUNCTION sync_category_name()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE products
    SET category_name = (SELECT name FROM categories WHERE id = NEW.id)
    WHERE category_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_category_name
AFTER UPDATE OF name ON categories
FOR EACH ROW
EXECUTE FUNCTION sync_category_name();

-- Without denormalization: compute on every read
SELECT id, quantity, unit_price,
       quantity * unit_price AS line_total
FROM order_items;

-- Denormalized: store the computed value
ALTER TABLE order_items ADD COLUMN line_total DECIMAL(12,2);

-- Backfill
UPDATE order_items
SET line_total = quantity * unit_price;

-- Trigger to maintain
CREATE OR REPLACE FUNCTION compute_line_total()
RETURNS TRIGGER AS $$
BEGIN
    NEW.line_total := NEW.quantity * NEW.unit_price;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_line_total
BEFORE INSERT OR UPDATE OF quantity, unit_price ON order_items
FOR EACH ROW
EXECUTE FUNCTION compute_line_total();

-- Normalized: separate attributes table
CREATE TABLE product_attributes (
    product_id INT REFERENCES products(id),
    attribute_name VARCHAR(50),
    attribute_value TEXT,
    PRIMARY KEY (product_id, attribute_name)
);

-- Denormalized: JSONB column
ALTER TABLE products ADD COLUMN attributes JSONB DEFAULT '{}';

-- Store all attributes in one column
UPDATE products
SET attributes = '{
    "weight": "1.5kg",
    "color": "black",
    "material": "aluminum",
    "warranty": "2 years"
}'::jsonb
WHERE id = 123;

-- Query specific attributes
SELECT id, name,
       attributes ->> 'color' AS color,
       attributes ->> 'weight' AS weight
FROM products
WHERE attributes @> '{"material": "aluminum"}'::jsonb;

-- GIN index for JSONB queries
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

from redis import Redis
import json

cache = Redis()

def order_completed(order_id):
    # Publish event
    cache.publish('order_events', json.dumps({
        'type': 'order_completed',
        'order_id': order_id
    }))

def refresh_summary_table():
    pubsub = cache.pubsub()
    pubsub.subscribe('order_events')
    for message in pubsub.listen():
        if message['type'] == 'message':
            event = json.loads(message['data'])
            if event['type'] == 'order_completed':
                # Increment summary counters
                cache.incr('daily_order_count')
                cache.incrby('daily_revenue', get_order_total(event['order_id']))