PostgreSQL Essentials

This is the code-heavy, do-it-yourself post. Instead of describing Postgres abstractly, we model a tiny shop with two tables and walk every common operation against it: defining a schema with real constraints, inserting and reading, joining, indexing a hot path, and wrapping money-moving writes in a transaction. Every snippet runs as-is in psql.

Read the comments as carefully as the SQL. The keywords tell you what's happening; the comments tell you why each choice was made, which is the part that transfers to your own schemas.

The schema slide establishes the data model and shows constraints doing real work. 'serial PRIMARY KEY' gives each row an auto-incrementing unique id with an index for free. On orders, 'customer_id int NOT NULL REFERENCES customers(id)' is a foreign key — Postgres will refuse to insert an order pointing at a customer that doesn't exist. 'numeric(10,2)' is the correct type for money: exact decimal, never floating-point rounding. 'CHECK (total >= 0)' forbids negative totals at the database level.

Notice how much correctness is declared rather than coded. None of these rules live in application logic; they're guarantees the database enforces for every client forever. 'timestamptz DEFAULT now()' even fills in the creation time automatically.

Inserting and reading is the core loop. The first INSERT uses 'RETURNING id' so you get the generated primary key back in the same round trip — no separate SELECT to discover what id was assigned. The second INSERT shows multi-row insert syntax, adding two orders in one statement.

The closing SELECT reads the orders for customer 1. This is deliberately the same shape as the 'hello world' from post one, reinforcing that everything is built on define-shape, put-data-in, get-data-out. RETURNING in particular is a small habit that saves a query on nearly every insert you do in real applications.

The join is where the relational model pays off. Because orders only stores customer_id, not the customer's name, we JOIN to customers ON c.id = o.customer_id to pull the name alongside each order. INNER JOIN returns only orders that have a matching customer, which here is all of them. The WHERE filters to orders over 20, and ORDER BY sorts the result.

This is the alternative to the N+1 trap covered in post five: rather than looping in application code and querying customers once per order, you express the relationship once in SQL and let Postgres assemble the combined rows efficiently in a single statement.

This compare diagram visualizes how the join lines up the two tables. On the orders side you have the foreign key column customer_id; on the customers side you have the primary key id. The join condition c.id = o.customer_id is literally matching those two columns to stitch each order to its owning customer.

Seeing it laid out side by side makes the 'reference, don't nest' principle concrete. The name lives only in customers; orders borrow it at query time through the key relationship. Change a customer's name once and every joined result reflects it immediately.

Indexing the hot filter is a targeted optimization, not a blanket one. We noticed queries frequently filter orders by customer_id, so we create an index specifically on that column. Then EXPLAIN ANALYZE confirms the payoff: the plan should now show 'Index Scan using idx_orders_customer' instead of a sequential scan over the whole table.

The discipline here is important. We index because we have a known, repeated access pattern — not on every column out of habit. And we verify with EXPLAIN rather than assuming the planner will use the index; on tiny tables it sometimes correctly decides a sequential scan is actually cheaper, which is a useful thing to observe firsthand.

The transaction is the capstone of the example and the concrete form of ACID. BEGIN starts a transaction; the two UPDATEs move 100 from account 1 to account 2; COMMIT makes both permanent atomically. The commented ROLLBACK shows the escape hatch: if anything went wrong, undoing it reverts both updates as if neither happened.

The critical guarantee is atomicity across the two statements. There is no observable moment where the money has left account 1 but not arrived in account 2. Either both updates are visible after COMMIT, or — on ROLLBACK or a crash — neither is. This is exactly the half-write problem from post two, solved at the engine level.

This slide explains the isolation angle of the transaction. While the BEGIN/COMMIT block is open, other sessions continue to see the old balances; they do not observe the intermediate state where one account has been debited but the other not yet credited. The combined change becomes visible to everyone atomically at COMMIT.

That all-or-nothing visibility is the entire reason to group related writes into a transaction. It's what lets you reason about multi-step operations as single logical units, confident that concurrent users and crashes can never catch your data mid-flight.

These patterns are the transferable habits from the example. RETURNING avoids an extra round trip after inserts. Constraints — foreign keys, CHECK, UNIQUE — push correctness into the schema where it's enforced uniformly. Indexing only the columns you filter and join on keeps writes fast while speeding the reads that matter. EXPLAIN ANALYZE turns 'I think the index is used' into proof. And BEGIN/COMMIT wraps any multi-step write in atomic safety.

Adopt these five and your Postgres code is already in better shape than a large share of production schemas — most problems come from skipping exactly these basics.

This pipeline recaps the example as one flow: define the schema with constraints, insert rows, join tables to combine related data, add an index to speed a hot read, and wrap multi-step writes in a transaction. It's a complete, if small, slice of real application data work.

If you ran every snippet in order against a scratch database, you've now exercised the four core verbs, foreign keys, joins, indexing, EXPLAIN, and transactions — the daily toolkit. Scaling up to a real app is mostly more of the same, applied to more tables.

That's Postgres in practice. You have a runnable schema and the patterns that go with it.

The final post in this set turns the lens around: the common mistakes that quietly degrade Postgres in production — missing foreign-key indexes, SELECT * and N+1 storms, ignoring connection limits, sloppy types, and neglected VACUUM — and how to avoid each one before it pages you.