The previous lesson's queries work. This lesson is about the other thing: whether a year from now, when you no longer remember why you wrote that LEFT JOIN, somebody —including you— will be able to touch them without fear. Because SQL has a quirk that makes it especially treacherous: a badly written query doesn't fail, it returns numbers. A badly written program blows up and you find out; a query with one JOIN too many returns a plausible figure that ends up on a slide in front of management.

There's no new syntax here. There's judgement: how things get named, how they get formatted, what you decide when designing the schema, which guarantees go in the database and which in the application, and what process surrounds all of it. Nearly all of it is convention, and with a convention what matters isn't which one you pick, but that the whole team picks the same one. The lesson ends with the catalogue of antipatterns and with a checklist for reviewing a query before you call it done.

1. Naming
2. Formatting and readability
3. Schema design
4. Reliability: where the guarantees live
5. Process: versioning, review, testing and backups
6. Antipatterns
7. Query review checklist
8. Common Mistakes and Tips
9. Exercises
10. Conclusion

The oldest argument in the trade: customer or customers? There are honest arguments for both:

Neither is better. What is objectively bad is mixing them: a schema with customers, order and order_lines forces you to check the dictionary before every query. GreenStore uses plural on every table, without exception, and that's the entire merit of the decision.

• Lowercase snake_case, always. PostgreSQL folds to lowercase any identifier not wrapped in double quotes: if you create "OrderDate", you'll have to write it quoted forever, and the first time you forget you'll get column "orderdate" does not exist. Putting double quotes in a CREATE TABLE is a life sentence.
• ASCII only in identifiers. That's why the whole schema sticks to plain ASCII names (01-06). The data carries accents and ñ; the object names don't.
• No reserved words. user, order, group, table, select, check, end... A column called order forces you to quote it in every query. If the business says "order", the table is orders; if it says "user", users.
• Names that say something. data, table1, temp2, info, field3, x mean nothing six months later. And no home-made abbreviations: order_date, not ord_dt.
• No type prefixes. str_name, tbl_customers, int_stock are Hungarian notation: noise that also lies the moment somebody changes the type.

GreenStore's convention, which is the most widespread and the one this course has used across eleven modules:

Two notes. First: a self-referencing FK is named after the role. employees.employee_id says nothing; manager_id says everything. Second: a negative boolean is a trap —WHERE NOT not_active is unreadable and not_active = FALSE is worse—; always name the true condition.

Name your constraints by hand. If you don't, PostgreSQL generates products_price_check or orders_customer_id_fkey, which are readable but not under your control: they change if the column name changes, and they show up verbatim in the error message the user will see. With CONSTRAINT chk_products_price_positive CHECK (price >= 0), the error says which rule was violated and the application can map it to a decent message.

A query is written once and read twenty times. Compare:

Five categories: Supplements doesn't appear because its only product is discontinued and was never sold, and an INNER JOIN doesn't invent rows. Both queries return the same thing; only one can be modified without rereading it end to end. The rules the second one applies:

• One clause per line, with the keywords left-aligned. Your eyes find the WHERE without looking for it.
• Explicit JOIN, never the comma. FROM a, b WHERE a.id = b.a_id is 1989 syntax: it mixes the join with the filter and, if you forget the condition, it produces a silent Cartesian product (03-06). With JOIN ... ON, join and filter are separate.
• Meaningful aliases. ol, p, cat are understandable; a, b, c force you to scroll up and look. The course's have been fixed since module 3 and never change.
• Explicit AS on column aliases. It's optional in PostgreSQL, and leaving it out means a forgotten comma turns price, cost into price AS cost, a bug no tool will catch.
• Keywords in uppercase, identifiers in lowercase. It isn't cosmetic: at a glance it separates the language from your data.
• JOIN condition in a consistent order: new_table.column = already_known_table.column. Read as a chain, it tells the story of the path.

And for things that belong to the schema, PostgreSQL has the right place: COMMENT ON, which stays inside the database and is visible to anyone with \d+.

Arguing about indentation in a code review is wasted time. Delegate it:

The recommendation: fix a style in a configuration file inside the repository (.sqlfluff, .pg_format) and run it in the pre-commit hook or in continuous integration. That way style stops being an opinion and becomes an automatic check.

The rule, picking up 01-05 and 05-06: start normalized. Normalization removes redundancy, and redundancy is what lets two copies of the same data end up saying different things. Denormalize only when you have a measurement that justifies it and a mechanism that keeps the copy in sync.

GreenStore has both cases, and they're worth telling apart:

The question that settles it: can the value change on its own after being stored? If it can't —the price it was sold at— it isn't redundancy, it's history, and it should be stored. If it can —the order total, the average rating— it's an aggregate and it has a maintenance cost.

A type is the cheapest constraint there is, because checking it costs nothing.

Picking up 04-03: a NULL is a source of complexity that propagates through the whole application —aggregates that ignore it, comparisons that are neither true nor false, a NOT IN that returns zero rows. That's why the default stance should be NOT NULL, and every nullable column should be a decision you can defend.

GreenStore has three nullable columns and all three have an explicit meaning: orders.employee_id = a web order with no sales rep; customers.referred_by_id = they arrived on their own; employees.manager_id = general management. None of them means "we don't know yet" or "the form came in empty", and that's the difference: if a NULL can mean two different things, the design is wrong.

The criterion: a surrogate id as the PK, and the natural key protected with UNIQUE. That's exactly what customers does (PK id, UNIQUE(email)), and that way you get both: stability and business uniqueness.

And the antipattern to recognize so you can run from it: EAV (Entity-Attribute-Value), an attributes(entity_id, attribute_name, value) table that promises infinite flexibility. The price: everything is text, there are no types and no constraints, querying three attributes means three self-joins, and nobody knows which attributes exist. If the fields genuinely vary, the modern answer is JSONB (10-06); if they don't vary, they're columns.

orders.status could just be a VARCHAR, and six months later you'd have delivered, Delivered, DELIVERED and deliverd. There are two ways to prevent it:

GreenStore uses a CHECK for status and payment_method because they're five and four values that are part of the business model. A catalogue of countries or of categories, on the other hand, is a table.

The rule that separates a healthy database from a sick one: integrity lives in the database, not just in the application.

It's tempting to think "I already validate it in the form". That isn't enough, for four reasons that always come true: a second application will arrive (the admin panel, a migration script, an integration); somebody will run an UPDATE by hand in psql one night; there will be a bug in the application's validation; and there will be concurrency, and a "read then write" check done in the application has a race condition that only a UNIQUE constraint really closes (09-04).

The application also validates: to give useful messages and to avoid pointless round trips to the server. But it validates as well, not instead.

Two more details. Sensible DEFAULTs: stock DEFAULT 0, active DEFAULT TRUE, discount DEFAULT 0 stop an incomplete INSERT putting nulls where they don't belong. And TIMESTAMPTZ in UTC for every instant: a TIMESTAMP with no time zone stores a meaningless number, and in a shop selling to Spain, Portugal and France you pay for that on the day the clocks change. Store in UTC, convert when displaying. Pure calendar dates —order_date, signup_date— really are DATE, because 4 March is 4 March everywhere.

The schema is code. Everything in lesson 05-06 boils down to one sentence: if the production schema can't be rebuilt from the repository, you don't have version control.

• Numbered, versioned, immutable migrations. Every change is a file (V007__add_index_orders_date.sql), it goes into Git with the code that needs it, and it isn't edited once applied: you fix it with a new migration. Tools: Flyway, Liquibase, Alembic, or the ORM's migrations (11-05).
• Every migration with its way back, or at least with a written plan of what to do if it fails. And breaking changes go through 05-06's expand/contract pattern: add, deploy, migrate data, and only then remove.
• Code review for the SQL too. A report query deserves the same review as a function: it's just as easy to get wrong and far harder to notice. A JOIN that duplicates rows produces numbers, not exceptions.
• A test environment with realistic data. Realistic in volume and shape —a plan over 20 rows tells you nothing about what will happen with 20 million (08-05)— but fictional or anonymized.

⚠️ Don't copy personal data from production into development. It's the most widespread practice and one of the most dangerous: it multiplies copies of real data across laptops, unencrypted environments and dumps nobody ever deletes. Generate synthetic data, or pseudonymize before copying (replace names and emails, shift dates, round amounts) — with the warning that badly done pseudonymization is reversible. Before moving personal data between environments, check with your organization's data protection officer or legal counsel. It's the same warning as in 05-04, and it applies here too.

• Tested backups. A backup that has never been restored isn't a backup: it's a file about which you assume things. Schedule a periodic test restore in a separate environment and measure how long it takes, because that number is your real recovery time. Check too that the backup includes what you think it does (roles, extensions, sequences) and that the retention covers the time it takes you to spot a problem: if a deletion is discovered after ten days and you keep seven, there is no backup.
• Slow-query monitoring. pg_stat_statements sorted by total time —not by average time, which hides the N+1 (08-04)—, log_min_duration_statement to log anything over a threshold, and a periodic review of the ranking. Without this you don't find out something is wrong: an angry user tells you.

Before calling a query done, in this order:

Correctness

1. Do the JOINs multiply rows? Check the count before and after each one: if orders goes from 20 to 47, you're adding up lines, not orders.
2. Is there a LEFT JOIN where the business allows absence? The 10 web orders with no sales rep disappear with an INNER JOIN.
3. What happens with NULLs? In the WHERE, in the NOT IN, in the aggregates, in the concatenations.
4. Does the result tally with a known figure? If a breakdown's total isn't €727.95, the breakdown is wrong.
5. Is the ORDER BY deterministic? Without a tie-breaker, two runs can come back in different orders.

Performance

6. Are the conditions sargable (a bare column)? Do the indexes it needs exist (08-01)?
7. Does it return only the columns and rows that will be used? Does it have a LIMIT if it's headed for a screen?
8. Have you run it with EXPLAIN ANALYZE over a realistic volume (08-05)?

Maintainability

9. Does it read well? Meaningful aliases, one clause per line, explicit AS?
10. Do the comments explain why, not what?
11. Is it in the repository, with the business question it answers written next to it?

Security

12. Do all the user's values travel as parameters (11-03)?
13. Does it return personal data that whoever runs it shouldn't see?

• Quoting identifiers in the CREATE TABLE. "OrderDate" forces you to write it quoted forever. Lowercase snake_case and that's the end of it.
• Mixing singular and plural, or two FK conventions. The cost isn't aesthetic: it's having to check the dictionary on every query.
• Letting PostgreSQL name your constraints. The generated name ends up in the error message the user sees and changes if the column changes. Name them: chk_, fk_, uq_, idx_.
• Storing money in FLOAT or dates in VARCHAR. It's the most expensive decision of all and the hardest to reverse once there's data in there.
• Putting all the validation in the application. A second application will arrive, and a nightly script, and a race condition. Integrity goes in the database.
• Using TIMESTAMP without a time zone for instants. Store in UTC with TIMESTAMPTZ and convert when displaying.
• Editing a migration that's already been applied. Environments drift out of sync silently. You fix it with a new migration.
• Tip: the first query of any report is SELECT COUNT(*). If the count changes when you add a JOIN, stop and find out why before going on.
• Tip: automate the style with sqlfluff or pg_format in continuous integration. Code reviews should be arguing about logic, not indentation.
• Tip: write the convention down in a file in the repository. Half a page is enough, and it turns "that's how we do it" into something a new colleague can read.

This schema is realistic in the sense that it looks a lot like what you're going to run into. List every naming, type and design problem, and rewrite it.

Your team is arguing about where to put the rule "a cancelled order can't take new lines". There are three proposals: (a) validate it in the web form; (b) a CHECK on order_lines; (c) a BEFORE INSERT trigger. (1) Which works and which doesn't, and why? (2) Which would you choose and what would you do as well? (3) Does the answer change if the system also has an admin panel and a nightly import process?

This query comes to you in a code review, with the note "gives the number of orders and the revenue per sales rep". Run section 7's checklist over it and say what's wrong.

Solution 1

The problems, one by one: quoted identifiers with capitals ("Customer_Orders", "ID", "Status"), which force you to write quotes forever; "user" is a reserved word; mixed languages (fecha_pedido alongside Status); ID VARCHAR(50) as the PK when it should be a surrogate integer; fecha_pedido VARCHAR instead of DATE; total FLOAT for money; VARCHAR(255) out of habit; not_active in the negative and as an INTEGER rather than a BOOLEAN; data TEXT, which doesn't say what it holds; no FK to customers; no NOT NULL, no CHECK and no named constraint anywhere.

And a question to ask yourself before writing anything: total is a precomputed aggregate. If it can be summed from the lines, perhaps it shouldn't exist; and if it exists for performance, you need the mechanism that maintains it (10-05) and a written decision about why.

Solution 2

1. (b) doesn't work: a CHECK can only look at the row being inserted, and the order's status is in another table. PostgreSQL doesn't allow subqueries in a CHECK precisely because it couldn't guarantee it stays true when the other table changes. (a) works but doesn't protect: it covers one of the several entry paths. (c) does work: a BEFORE INSERT trigger on order_lines can query orders.status and raise a RAISE EXCEPTION (10-05).

2. The trigger, and also the form validation, to give a decent message without going to the server. The database guarantees; the interface explains. And you have to think about the symmetric case —what happens if the order is cancelled after it has lines?— which that trigger doesn't cover.

3. The answer doesn't change, it gets stronger. With three entry paths, form validation protects one in three, and the nightly process is precisely the one that will insert thousands of rows with nobody looking at them. That's the whole argument of section 4.

Solution 3

Four correctness problems and several of form. (1) The COUNT(*) is wrong: joining with order_lines makes each order appear once per line, so it isn't counting orders but lines. It has to be COUNT(DISTINCT o.id). (2) The discount is missing: the amount is quantity * unit_price * (1 - discount), and without it the revenue comes out inflated. (3) The INNER JOIN with employees leaves out the 10 web orders: if the report wants "per sales rep", you have to decide explicitly whether those orders are excluded or show up as "Web" with a LEFT JOIN and a COALESCE. (4) GROUP BY e.name groups by first name: two sales reps with the same name would be merged into one row. It has to group by e.id. On form: comma syntax instead of JOIN, no AS and no column aliases, no uppercase and no ORDER BY.

Three sales reps, 10 orders and €378.83 — the phone channel. The other €349.12 are the 10 web orders with no sales rep, and their not appearing here is now a decision, not an oversight.

This lesson added no syntax: it added judgement.

• Naming: lowercase snake_case and ASCII only; singular or plural doesn't matter, mixing them does; id for the PK and <table>_id for the FK, with the role's name on self-referencing ones; booleans in the positive; no reserved words and no data/table1; and constraints and indexes named by hand with chk_, fk_, uq_, idx_.
• Formatting: one clause per line, explicit JOIN ... ON instead of the comma, meaningful aliases, explicit AS, keywords in uppercase, comments that explain why, COMMENT ON for what belongs to the schema, and an automatic formatter configured in the repository.
• Design: normalize by default and denormalize with a measurement and a mechanism behind it; the most restrictive type that works; NOT NULL by default and every NULL with a single meaning; a surrogate id plus a UNIQUE on the natural key; no EAV; and a CHECK for closed lists, a lookup table for the ones that grow.
• Reliability: integrity lives in the database, because a second application will arrive, and a hand-typed UPDATE, and a bug, and a race condition. Sensible DEFAULTs and TIMESTAMPTZ in UTC.
• Process: versioned, immutable migrations, code review for SQL too, a test environment with fictional or anonymized data —never production copies with personal data—, tested backups (a backup you've never restored isn't a backup) and slow-query monitoring by total time.
• And the thirteen-point checklist, which starts with the question that prevents the most errors: does this JOIN multiply rows?

Two of the antipatterns in the table were left with a promise attached: building SQL by concatenation, and the "who can see what" that's been cropping up since module 5. In the next lesson, Security: SQL injection, permissions and roles, both get closed: what exactly an SQL injection is and why it happens; the defence that really works —parameterized queries— written in four languages; what is not a defence; the special case of dynamic identifiers; and then PostgreSQL's permission model in full, with GRANT, REVOKE, roles, ALTER DEFAULT PRIVILEGES, row-level security and a concrete role design for GreenStore.