Denormalisation in Databases — Trigger Drift Pitfalls

-- ─────────────────────────────────────────────────────────────────
-- NORMALISED SCHEMA: 3NF compliant, six-table JOIN to render one
-- order summary row. Clean data model, painful at high read volume.
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,
    full_name     VARCHAR(120) NOT NULL,
    email         VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE addresses (
    address_id    SERIAL PRIMARY KEY,
    customer_id   INT REFERENCES customers(customer_id),
    street        VARCHAR(200),
    city          VARCHAR(100),
    postcode      VARCHAR(20)
);

CREATE TABLE shipping_carriers (
    carrier_id    SERIAL PRIMARY KEY,
    carrier_name  VARCHAR(80) NOT NULL   -- e.g. 'FedEx', 'UPS'
);

CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INT REFERENCES customers(customer_id),
    address_id    INT REFERENCES addresses(address_id),
    carrier_id    INT REFERENCES shipping_carriers(carrier_id),
    ordered_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
    product_id    SERIAL PRIMARY KEY,
    product_name  VARCHAR(200) NOT NULL,
    unit_price    NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_items (
    item_id       SERIAL PRIMARY KEY,
    order_id      INT REFERENCES orders(order_id),
    product_id    INT REFERENCES products(product_id),
    quantity      INT NOT NULL,
    line_total    NUMERIC(10,2) NOT NULL   -- quantity * unit_price at time of order
);

-- ─────────────────────────────────────────────────────────────────
-- The six-table JOIN required to render a single order summary.
-- EXPLAIN ANALYSE this on 1M orders and watch the planner sweat.
-- ─────────────────────────────────────────────────────────────────
SELECT
    o.order_id,
    c.full_name          AS customer_name,
    a.city               AS shipping_city,
    sc.carrier_name      AS carrier,
    COUNT(oi.item_id)    AS item_count,
    SUM(oi.line_total)   AS order_total
FROM orders            o
JOIN customers         c  ON c.customer_id  = o.customer_id
JOIN addresses         a  ON a.address_id   = o.address_id
JOIN shipping_carriers sc ON sc.carrier_id  = o.carrier_id
JOIN order_items       oi ON oi.order_id    = o.order_id
JOIN products          p  ON p.product_id   = oi.product_id
WHERE o.order_id = 4821
GROUP BY o.order_id, c.full_name, a.city, sc.carrier_name;

-- ─────────────────────────────────────────────────────────────────
-- TECHNIQUE 1: FLATTENING
-- Store frequently-joined data directly on the orders table.
-- Trade-off: customer_name here can drift from customers.full_name
-- if you update the customer and forget to update past orders.
-- That's often DESIRED — you want the name as it was at order time.
-- ─────────────────────────────────────────────────────────────────

ALTER TABLE orders
    ADD COLUMN customer_name_snapshot  VARCHAR(120),  -- name at order time
    ADD COLUMN shipping_city_snapshot  VARCHAR(100),  -- address at order time
    ADD COLUMN carrier_name_snapshot   VARCHAR(80);   -- carrier at order time

-- Populate on INSERT via application layer or trigger:
CREATE OR REPLACE FUNCTION orders_snapshot_on_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- Pull the human-readable values once, at write time
    SELECT c.full_name, a.city, sc.carrier_name
    INTO
        NEW.customer_name_snapshot,
        NEW.shipping_city_snapshot,
        NEW.carrier_name_snapshot
    FROM customers         c
    JOIN addresses         a  ON a.address_id  = NEW.address_id
    JOIN shipping_carriers sc ON sc.carrier_id = NEW.carrier_id
    WHERE c.customer_id = NEW.customer_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_snapshot
    BEFORE INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION orders_snapshot_on_insert();


-- ─────────────────────────────────────────────────────────────────
-- TECHNIQUE 2: STORED AGGREGATE (pre-computed order_total)
-- After this column exists, the six-table JOIN collapses to ONE
-- table scan — no joins at all for the summary page.
-- ─────────────────────────────────────────────────────────────────

ALTER TABLE orders
    ADD COLUMN order_total NUMERIC(10,2) DEFAULT 0.00;

-- Keep the aggregate current with a trigger on order_items:
CREATE OR REPLACE FUNCTION sync_order_total()
RETURNS TRIGGER AS $$
BEGIN
    -- Recalculate total for the affected order atomically
    UPDATE orders
    SET    order_total = (
        SELECT COALESCE(SUM(line_total), 0)
        FROM   order_items
        WHERE  order_id = COALESCE(NEW.order_id, OLD.order_id)
    )
    WHERE order_id = COALESCE(NEW.order_id, OLD.order_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_order_total
    AFTER INSERT OR UPDATE OR DELETE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION sync_order_total();


-- ─────────────────────────────────────────────────────────────────
-- TECHNIQUE 5: MATERIALISED VIEW
-- Best for reporting / analytics. Refresh on a schedule or on-demand.
-- CONCURRENTLY means reads are never blocked during refresh.
-- ─────────────────────────────────────────────────────────────────

CREATE MATERIALIZED VIEW order_summary_mv AS
SELECT
    o.order_id,
    o.customer_name_snapshot                AS customer_name,
    o.shipping_city_snapshot                AS shipping_city,
    o.carrier_name_snapshot                 AS carrier,
    o.order_total,
    o.ordered_at
FROM orders o
WITH DATA;   -- populate immediately

-- Create an index so lookups on the MV are still fast:
CREATE UNIQUE INDEX idx_order_summary_mv_order_id
    ON order_summary_mv (order_id);

-- Refresh without locking reads (requires the unique index above):
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary_mv;


-- ─────────────────────────────────────────────────────────────────
-- NOW: the read query is trivially fast — single table, no joins.
-- ─────────────────────────────────────────────────────────────────
SELECT order_id, customer_name, shipping_city, carrier, order_total
FROM   order_summary_mv
WHERE  order_id = 4821;

-- ─────────────────────────────────────────────────────────────────
-- RECONCILIATION QUERY: Run as a nightly job or whenever you suspect
-- drift between the canonical order_items data and the stored aggregate.
-- This is your safety net for the dual-write pattern.
-- ─────────────────────────────────────────────────────────────────

-- Step 1: Find orders where the stored total disagrees with reality
SELECT
    o.order_id,
    o.order_total                    AS stored_total,
    COALESCE(SUM(oi.line_total), 0)  AS real_total,
    ABS(o.order_total - COALESCE(SUM(oi.line_total), 0)) AS drift
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.order_total
HAVING o.order_total != COALESCE(SUM(oi.line_total), 0)
ORDER BY drift DESC
LIMIT 100;  -- surface the worst offenders first


-- Step 2: Heal the drift in a single UPDATE (safe to re-run).
-- Use a CTE so we compute the correct totals once and apply them
-- in a single pass — no per-row sub-select performance hit.
WITH correct_totals AS (
    SELECT
        order_id,
        COALESCE(SUM(line_total), 0) AS recalculated_total
    FROM order_items
    GROUP BY order_id
)
UPDATE orders o
SET    order_total = ct.recalculated_total
FROM   correct_totals ct
WHERE  ct.order_id    = o.order_id
  AND  ct.recalculated_total != o.order_total;  -- only touch drifted rows

-- Verify nothing remains:
SELECT COUNT(*) AS drifted_orders
FROM orders o
LEFT JOIN (
    SELECT order_id, SUM(line_total) AS real_total
    FROM   order_items
    GROUP  BY order_id
) sub ON sub.order_id = o.order_id
WHERE o.order_total IS DISTINCT FROM COALESCE(sub.real_total, 0);

-- ─────────────────────────────────────────────────────────────────
-- BENCHMARK HARNESS: Compare normalised JOIN vs denormalised read
-- Run in psql with \timing on, or wrap in a shell script calling
-- pgbench --file=this_file.sql -c 20 -j 4 -T 30
-- ─────────────────────────────────────────────────────────────────

-- Seed data: 500,000 orders, realistic volume for a mid-size shop
INSERT INTO customers (full_name, email)
SELECT
    'Customer ' || gs,
    'user'       || gs || '@example.com'
FROM generate_series(1, 10000) gs;

INSERT INTO orders (customer_id, address_id, carrier_id, ordered_at)
SELECT
    (random() * 9999 + 1)::INT,   -- random customer_id 1-10000
    1,                              -- simplified: single address
    (random() * 2 + 1)::INT,       -- carrier 1, 2, or 3
    NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 500000);


-- ─────────────────────────────────────────────────────────────────
-- TEST A: Normalised — six-table JOIN to fetch latest 100 orders
-- ─────────────────────────────────────────────────────────────────
EXPLAIN (ANALYSE, BUFFERS, FORMAT TEXT)
SELECT
    o.order_id,
    c.full_name,
    a.city,
    sc.carrier_name,
    SUM(oi.line_total) AS order_total
FROM orders o
JOIN customers         c  ON c.customer_id  = o.customer_id
JOIN addresses         a  ON a.address_id   = o.address_id
JOIN shipping_carriers sc ON sc.carrier_id  = o.carrier_id
JOIN order_items       oi ON oi.order_id    = o.order_id
JOIN products          p  ON p.product_id   = oi.product_id
GROUP BY o.order_id, c.full_name, a.city, sc.carrier_name
ORDER BY o.ordered_at DESC
LIMIT 100;


-- ─────────────────────────────────────────────────────────────────
-- TEST B: Denormalised — single table scan, no joins
-- Same result, radically different execution plan
-- ─────────────────────────────────────────────────────────────────
EXPLAIN (ANALYSE, BUFFERS, FORMAT TEXT)
SELECT
    order_id,
    customer_name_snapshot  AS customer_name,
    shipping_city_snapshot  AS shipping_city,
    carrier_name_snapshot   AS carrier,
    order_total
FROM orders
ORDER BY ordered_at DESC
LIMIT 100;

-- ─────────────────────────────────────────────────────────────────
-- MONITORING SETUP: Automated drift detection and health checks
-- ─────────────────────────────────────────────────────────────────

-- 1. Create a logging table for drift events
CREATE TABLE denorm_drift_log (
    log_id        BIGSERIAL PRIMARY KEY,
    table_name    TEXT NOT NULL,
    column_name   TEXT NOT NULL,
    row_id        INT NOT NULL,
    stored_value  NUMERIC(10,2),
    actual_value  NUMERIC(10,2),
    drift         NUMERIC(10,2) GENERATED ALWAYS AS (ABS(stored_value - actual_value)) STORED,
    detected_at   TIMESTAMPTZ DEFAULT NOW(),
    healed        BOOLEAN DEFAULT FALSE
);

-- 2. Automated reconciliation with logging (pg_cron job)
-- Run nightly: SELECT cron.schedule('nightly-order-heal', '0 2 * * *', 
$$
WITH drift_detection AS (
    SELECT
        o.order_id AS row_id,
        o.order_total AS stored_value,
        COALESCE(SUM(oi.line_total),0) AS actual_value
    FROM orders o
    LEFT JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY o.order_id, o.order_total
    HAVING o.order_total IS DISTINCT FROM COALESCE(SUM(oi.line_total),0)
)
INSERT INTO denorm_drift_log (table_name, column_name, row_id, stored_value, actual_value)
SELECT 'orders', 'order_total', row_id, stored_value, actual_value
FROM drift_detection;

UPDATE orders o
SET order_total = (
    SELECT COALESCE(SUM(line_total),0)
    FROM order_items
    WHERE order_id = o.order_id
)
FROM drift_detection d
WHERE d.row_id = o.order_id;
$$);

-- 3. Alert query: if more than 0.1% of orders have drift today, raise alert
SELECT
    COUNT(*) AS drifted_orders,
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM orders), 2) AS drift_pct
FROM denorm_drift_log
WHERE detected_at >= NOW() - INTERVAL '1 day'
  AND healed = FALSE;