From Your First SELECT to Production

We opened this track with five tables and a single SELECT. Fifty-odd chapters later, it is the same five tables. But you now know enough to make it a database you would trust in production, not a toy you query at a prompt. This last chapter is short and practical. We take the bookshop as it stands and apply a handful of ideas from across the four series, running each one so you can see the payoff. Then we close the whole track.

A constraint that deletes a bug class

Start with data integrity, the theme of this fourth series. A line item with a quantity of zero is nonsense, but nothing has stopped one so far. Watch it slip in:

INSERT INTO order_items (order_id, book_id, quantity, unit_price)
VALUES (23871, 1, 0, 10.00);

INSERT 0 1

The database accepted it, and now every revenue query that multiplies quantity by price silently includes a zero-value phantom. You could hunt for these in application code, in every insert path, forever. Or you can state the rule once, where the data lives, and let the database enforce it for all writers:

ALTER TABLE order_items ADD CONSTRAINT quantity_positive CHECK (quantity > 0);

Now the same insert is not caught and logged, it is impossible:

INSERT INTO order_items (order_id, book_id, quantity, unit_price)
VALUES (23871, 1, 0, 10.00);

ERROR:  new row for relation "order_items" violates check constraint "quantity_positive"
DETAIL:  Failing row contains (23871, 1, 0, 10.00).

One line of DDL removed an entire category of bad data, permanently, for every application that will ever connect. This is the argument the whole track has been building toward: a rule enforced by the database holds no matter what the code above it does.

An index that turns a scan into a lookup

The app looks up a customer’s orders constantly, and there is no index on orders.customer_id. Series 3 taught us to look at the plan rather than guess, so we look:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT order_id, order_date, status FROM orders WHERE customer_id = 2;

 Seq Scan on orders (actual rows=17.00 loops=1)
   Filter: (customer_id = 2)
   Rows Removed by Filter: 59983
   Buffers: shared hit=383

To find seventeen rows, Postgres read all sixty thousand and threw away 59,983 of them. Build the index:

CREATE INDEX orders_customer_id ON orders (customer_id);

 Bitmap Heap Scan on orders (actual rows=17.00 loops=1)
   Heap Blocks: exact=17
   Buffers: shared hit=17 read=2
   ->  Bitmap Index Scan on orders_customer_id (actual rows=17.00 loops=1)
         Index Cond: (customer_id = 2)

From 383 buffers to nineteen, from reading the whole table to reading the seventeen rows we asked for. The same index also speeds the row-level security policy from the last chapter, whose customer_id = ... filter now has an index to ride. One structure, two payoffs.

A view as the app’s interface

An order summary joins orders to order_items, sums the line items, and groups. Making every caller write that join is how two teams end up computing “order total” two different ways. Series 4 taught the view as an interface: define the shape once, and let the app select a business object instead of reassembling one.

CREATE OR REPLACE VIEW order_summaries AS
SELECT o.order_id, o.customer_id, o.order_date, o.status,
       sum(oi.quantity)                          AS items,
       round(sum(oi.quantity * oi.unit_price),2) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id;

SELECT * FROM order_summaries WHERE customer_id = 1 ORDER BY order_id;

 order_id | customer_id | order_date |  status   | items | order_total
----------+-------------+------------+-----------+-------+-------------
    23871 |           1 | 2025-01-03 | returned  |     7 |      186.87
    27508 |           1 | 2023-12-21 | returned  |     3 |       61.96
    29004 |           1 | 2023-02-27 | shipped   |     4 |      187.75
    38038 |           1 | 2025-06-27 | cancelled |     5 |       80.77
    47274 |           1 | 2023-03-15 | placed    |     2 |      106.12
    53048 |           1 | 2024-07-29 | delivered |     8 |      271.11
(6 rows)

The app now asks for order_total and gets one definition of it, forever. The join lives in the database, the “total” means one thing across every team, and the interface stays stable even if the tables behind it change.

And the fourth piece is already in place from the previous chapter: the row-level security policy on orders. It scopes the app role to the one tenant whose id is in the session. Put the four together and the bookshop has become a system with edges: bad data cannot enter, common lookups are fast, the read shape is defined once, and each tenant is walled off from the others. None of it lives in application code. All of it lives in the database, where it holds for every client that connects.

Final thoughts — closing the whole track

That is the arc of these four series, and it is worth seeing whole.

Series 1, the fundamentals, taught you the language. Everything is a table. You describe the set you want rather than the loop that would collect it. SELECT, WHERE, JOIN, and GROUP BY are how you say it. The one idea under all of it was the set-based stance, and every chapter since has been a way to describe a set more precisely.

Series 2, the analytical toolkit, taught you to think in questions, not just rows. CTEs to stage a query as readable layers. Window functions to rank, run totals, and look forward and back. Date bucketing, FILTER, and grouping sets to pivot. jsonb and arrays to bend the row model. That series ended by assembling a real monthly business report in a single statement. That is the payoff of the whole toolkit: you can ask a business almost anything and get the answer without leaving the database.

Series 3, how the database runs your query, taught you what happens underneath. You learned to read EXPLAIN and to build the indexes the planner chooses between. You learned MVCC and the isolation levels, and how to spot locks, bloat, and the anti-patterns that quietly disable an index. The habit that series drilled — don’t guess, look at the plan — is the one you just used to justify an index a few paragraphs ago.

Series 4, this one, taught you to live with a database in production. Normalize so each fact lives in one place. Then use keys, constraints, generated columns, views, triggers, and functions to push correctness into the schema. Evolve it with migrations that never break what is running. And connect to it well from application code: parameterize every query to shut out injection, pool your connections, keep transactions short, avoid N+1, and hand the app a least-privilege role behind a row-level security policy.

Put the four together and you have the shape of the whole thing. The language lets you ask. The analytical toolkit lets you ask hard questions. The performance series explains how the machine answers and how to help it answer fast. And this last series is about everything around the query: the schema that keeps the data honest, the migrations that let it change, and the application seam where SQL meets the rest of your system.

There is a lot more Postgres than a track can hold: replication, extensions, full-text search, the vast provider-specific corners. You are now equipped to go read about any of it, because you know how to do the one thing that matters most: run it, look at what actually happened, and believe the output over the folklore. Every query in every chapter of this track was run against PostgreSQL 18, its result pasted back exactly as it came. That is the only way to know a thing is true rather than merely plausible. Take that habit with you. Open a psql prompt against your own data, ask it a question, and read the answer. That was the whole point, from the first SELECT to here.