Index, view and transaction

Fast, reusable, all-or-nothing

Three PostgreSQL tools every real app needs: an index so looking up a dish's sales doesn't scan every row, a view so the report is one simple SELECT, and a transaction so an order and its stock change happen together — or not at all.

The script

CREATE INDEX order_items_dish_idx ON order_items (dish_id);

CREATE VIEW dish_revenue AS
SELECT d.name AS dish, SUM(oi.qty * d.price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN dishes d ON d.id = oi.dish_id
WHERE o.status = 'served'
GROUP BY d.name;

BEGIN;
WITH new_order AS (
  INSERT INTO orders (customer_id) VALUES (2) RETURNING id
)
INSERT INTO order_items (order_id, dish_id, qty)
SELECT id, 1, 3 FROM new_order;
UPDATE dishes SET stock = stock - 3 WHERE id = 1;
COMMIT;

Fast, reusable, all-or-nothing

The script

The rest of this lesson