PostgreSQL: Update Order Totals from Order Items

intermediate

E-commerce site needs to recalculate order totals when items are added/removed. Traditional approach: Loop through orders, calculate total for each. Problem: Very slow (N queries). Solution: Use UPDATE...FROM to calculate all totals in 1 query (100x faster).

-- Problem: Row-by-row update (slow)

-- Application code (pseudocode):

-- for each order:

-- total = SELECT SUM(quantity * price) FROM order_items WHERE order_id = order.id

-- UPDATE orders SET total = total WHERE order_id = order.id

-- Time: 10 seconds for 10,000 orders (10,000 queries)

-- Solution: UPDATE...FROM (100x faster)

UPDATE orders o

SET

subtotal = oi.item_total,

tax = oi.item_total * 0.1,

total = oi.item_total * 1.1,

updated_at = NOW()

FROM (

SELECT

order_id,

SUM(quantity * price) as item_total,

COUNT(*) as item_count

FROM order_items

GROUP BY order_id

) oi

WHERE o.order_id = oi.order_id

AND o.status = 'pending';

-- Time: 0.1 seconds for 10,000 orders (1 query, 100x faster)

-- Verify updates

SELECT

order_id,

subtotal,

tax,

total,

(SELECT COUNT(*) FROM order_items WHERE order_id = o.order_id) as item_count

FROM orders o

WHERE status = 'pending'

ORDER BY order_id

LIMIT 10;

Orders updated with calculated totals in single query. Results: Row-by-row: 10 seconds (10,000 queries). UPDATE...FROM: 0.1 seconds (1 query, 100x faster). Benefits: (1) Single query updates all orders, (2) Aggregation done in subquery, (3) Only pending orders updated (WHERE filter), (4) Atomic operation (all or nothing). Real-world: Amazon recalculates millions of order totals daily using UPDATE...FROM.

UPDATE...FROM is 100x faster because: (1) Single query instead of N queries (eliminates network overhead), (2) Subquery aggregates all order_items at once (efficient GROUP BY), (3) JOIN matches orders with totals in memory (fast hash join), (4) Single transaction (atomic operation). Trade-off: Requires PostgreSQL-specific syntax. Alternative: Correlated subquery works on all databases but slower. Real-world: Amazon updates millions of order totals daily.