Views, Indexes, and a Safe Transaction

Objectives

By the end of this lesson, you should be able to:

• Package a frequently-run report as a view
• Add indexes to speed up the project’s most common lookups
• Wrap a multi-step operation (placing an order) in a transaction

💡 Why this matters: Report 1 from the previous lesson is exactly the kind of query that gets run constantly, packaging it as a view means every future use is a simple SELECT. And placing a new order genuinely is two related inserts, an order and its line items, that need the same all-or-nothing safety as Module 13’s account transfer.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the schema and data from the previous lessons.

Packaging a Report as a View

CREATE VIEW completed_order_revenue AS
SELECT o.id AS order_id, c.name AS customer, o.order_date, SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY o.id, c.name, o.order_date;

SELECT * FROM completed_order_revenue ORDER BY order_id;

 order_id |    customer     | order_date | order_total
-----------+-------------------+------------+--------------
        1 | Riley Nguyen     | 2024-01-10 |       62.23
        2 | Casey Brooks     | 2024-01-20 |       89.99
        3 | Riley Nguyen     | 2024-02-15 |      103.50

This is Module 12’s CREATE VIEW, saving the per-order revenue calculation once, any future report needing “revenue per completed order” queries completed_order_revenue directly, instead of re-writing the same three-table join and aggregation every time.

Adding Indexes for Common Lookups

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

SELECT indexname FROM pg_indexes WHERE tablename = 'orders' ORDER BY indexname;

        indexname
---------------------------
 idx_orders_customer_id
 orders_pkey

orders.customer_id and order_items.product_id are both foreign keys queried constantly, in every report from the previous lesson. Module 12’s reasoning still applies here: on this project’s small dataset, EXPLAIN would likely still show a sequential scan (exactly like Module 12’s own small-table example), the real benefit shows up once this schema holds real, production-scale data.

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

 Seq Scan on orders  (cost=0.00..1.04 rows=1 width=74)
   Filter: (customer_id = 1)

Exactly as expected, a sequential scan on this small a table, the index is in place and ready to help once the data grows, this is planning for scale, not necessarily an immediate speed difference on sample data.

A Safe Transaction: Placing a New Order

Placing an order means inserting into both orders and order_items, together, if the line items failed to insert for any reason, an order with no products would be meaningless, exactly the kind of multi-step change Module 13 covered.

BEGIN;

INSERT INTO orders (customer_id, employee_id, order_date, status) VALUES (4, 2, '2024-03-01', 'pending');

SELECT id FROM orders ORDER BY id DESC LIMIT 1;

 id
----
  5

The new order gets id = 5 (not 4, the deleted order’s id isn’t reused, Module 3’s SERIAL behavior). With that id known:

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (5, 5, 1, 349.00);

COMMIT;

SELECT id, customer_id, employee_id, status FROM orders ORDER BY id;

 id | customer_id | employee_id |  status
----+--------------+---------------+-----------
  1 |            1 |             2 | completed
  2 |            2 |             3 | completed
  3 |            1 |             2 | completed
  5 |            4 |             2 |   pending

Both inserts succeeded and were committed together. If the order_items insert had failed instead, for instance referencing a product_id that didn’t exist, ROLLBACK (Module 13) would undo the orders insert too, leaving no orphaned, product-less order behind.

Try It

1. Create the completed_order_revenue view and query it, confirming the three rows shown above.
2. Add an index on employees.manager_id, and explain, in your own words, which report from the previous lesson it would help.
3. Place a new order as a transaction: insert into orders, note the new id, insert one or more order_items rows, then COMMIT.
4. Repeat question 3, but this time intentionally reference a product_id that doesn’t exist in the order_items insert, observe the error, then ROLLBACK and confirm the order itself wasn’t left behind.

Recap

• A frequently-run report is worth packaging as a view (Module 12), one saved definition instead of repeated joins and aggregation.
• Indexes on heavily-queried foreign key columns (Module 12) prepare a schema for scale, even if a small sample dataset doesn’t show a dramatic EXPLAIN difference yet.
• Placing an order is a multi-step change, and belongs in a transaction (Module 13), exactly like an account transfer, so a partial failure never leaves incomplete data behind.

Next lesson: wrapping up the project, and challenges to extend it further on your own.