Schemas change. Always. You need somewhere to store customers' phone numbers, the VARCHAR(60) turns out to be too small, somebody realises that column was badly named, marketing wants a total column in orders so they don't have to recompute it for every report, and a business rule that until now lived in the code really belongs in a CHECK. The database you created in 05-01 and filled in 05-02 isn't a monument: it's a living organism in production that will mutate dozens of times.

ALTER TABLE is the statement that makes that possible. Its syntax takes twenty minutes to learn. What separates a professional from an accident isn't the syntax: it's knowing which operations lock the table and which don't, understanding that in PostgreSQL a migration can be undone with ROLLBACK and in MySQL it can't, knowing the pattern that lets you change a schema without stopping the service, and accepting the rule that governs the whole industry: no structural change is ever typed by hand into a production console.

This lesson covers both halves, and closes the module.

⚠️ Safety warning

ALTER TABLE is a destructive operation, irreversible in its effects on the data. A DROP COLUMN removes an entire column and all its contents; an ALTER COLUMN TYPE can truncate values; an ADD CONSTRAINT can reject existing rows.

• Run all the examples on your practice database (greenstore), never in production.
• Take a backup beforehand: pg_dump -U sql_course -d greenstore -f backup.sql.
• Work inside BEGIN … ROLLBACK: in PostgreSQL the DDL is transactional and you can undo it.
• On a real system, every schema change must be reviewed by the database owner, tested beforehand in an environment equivalent to production and applied as a versioned migration, never by hand.

1. ALTER TABLE: the map of operations
2. ADD COLUMN, with and without a DEFAULT
3. DROP COLUMN
4. RENAME COLUMN and RENAME TO
5. ALTER COLUMN TYPE and the USING clause
6. SET / DROP NOT NULL and SET / DROP DEFAULT
7. ADD / DROP CONSTRAINT, and the NOT VALID + VALIDATE technique
8. What locks the table and what doesn't
9. Transactional DDL: PostgreSQL against MySQL
10. The expand/contract pattern
11. Versioned migrations
12. What to do and what not to do in production
13. Common Mistakes and Tips
14. Exercises
15. Module conclusion

The most common actions:

One syntax detail that saves a lot of time: several actions fit in a single statement, separated by commas. And that isn't just elegance — it means the table is locked once instead of N times:

A warning about this lesson's objects. customers.phone, customers.newsletter and orders.total are one-off examples: they aren't part of GreenStore's canonical schema and they won't appear in the following modules. Reload greenstore.sql when you're done.

(Reload greenstore.sql before going on: this section's examples add the previous section's two columns again, this time one by one.)

The column is born nullable and every existing row is left with NULL. It's instantaneous regardless of the table's size: PostgreSQL just records the new column in its catalogue, without touching a single byte of data.

All fifteen rows have FALSE. And here's one of PostgreSQL's most important improvements of the last decade:

Since PostgreSQL 11, adding a column with a constant DEFAULT no longer rewrites the table. Before that, this operation walked through and rewrote every row to store the value: on a table of a hundred million rows that meant hours of exclusive locking — the classic case study of "how to take production down with one line of SQL".

Now PostgreSQL keeps the default value in the catalogue and returns it "on the fly" for the old rows, writing it physically only when those rows get updated for some other reason. The operation goes from hours to milliseconds.

The exception, which is still expensive:

If the DEFAULT isn't constant —a random function, a NOW() that has to differ per row— each row needs its own value and there's no possible shortcut. The rule:

Dialect note: MySQL 8 with InnoDB allows ADD COLUMN with ALGORITHM=INSTANT in many cases (a column at the end of the table, with no change of row format); otherwise it rebuilds. Oracle has had an equivalent optimisation since 11g. SQLite adds columns at the end cheaply but doesn't allow adding them with a non-constant DEFAULT. SQL Server distinguishes between adding a nullable column (instantaneous) and a NOT NULL one with a DEFAULT (instantaneous since 2012 Enterprise, a rewrite in the other editions).

This is instantaneous too: PostgreSQL doesn't erase the data, it marks the column as dropped in the catalogue and stops showing it. The space is reclaimed when each row is rewritten (by an UPDATE or by a VACUUM FULL).

That has two implications worth knowing:

1. The data is still physically on disk until the row is rewritten. If the column contained sensitive information, a DROP COLUMN is not a secure erase.
2. The operation is irreversible from SQL once committed. There's no UNDROP.

If anything depends on the column (a constraint, a view, an index), DROP COLUMN fails:

With CASCADE it takes whatever depends on it with it:

And with that you've just destroyed the catalogue's most important column along with its constraint. CASCADE in ALTER TABLE deserves the same respect as in DELETE.

Both are purely catalogue operations: instantaneous, without touching data, and reversible with another RENAME.

And both are, on a system with applications connected, among the most dangerous operations there are. The reason isn't technical: it's that the instant you commit the change, all the code mentioning the old name stops working:

A two-second ALTER TABLE has broken the whole application. And there's no transition window: either the code uses the old name, or it uses the new one.

The rule: a RENAME in production is never done in one go. It's done with section 10's expand/contract pattern, or it isn't done at all. And if the name is ugly but it works, very often the right answer is to leave it ugly.

We undo the two changes before going on:

Widening a NUMERIC's precision works straight off, because every existing value fits in the new type. The same happens going from VARCHAR(60) to VARCHAR(120) or to TEXT: PostgreSQL recognises those cases as binary-compatible and rewrites nothing.

Narrowing, on the other hand, can fail:

And this is good news: PostgreSQL checks every row before applying the change, and if a single one doesn't fit, it aborts without touching anything. It never truncates silently.

Dialect note: MySQL, depending on its SQL mode, can truncate silently when narrowing a VARCHAR. With sql_mode at STRICT_TRANS_TABLES (the default since MySQL 5.7) it raises an error; in relaxed mode, it trims the text and emits a warning almost nobody reads. It's one of the most dangerous behavioural differences between engines.

When the conversion isn't automatic, PostgreSQL tells you and offers you the way out:

A direct attempt to convert the text into a date:

USING takes an expression that computes the new value from the old one, row by row:

USING accepts any expression, not just conversions. For instance, to move order_lines.discount from a fraction (0.10) to a whole percentage (10):

Three warnings about ALTER COLUMN TYPE:

It fails if there are rows with NULL… except that in GreenStore every customer has a city, which is the case here. Let's try one that does have nulls:

The ten web orders prevent it, and rightly so: that NULL means something (04-03).

The inverse operation always works, because relaxing a constraint can never invalidate existing data:

Default values work the same way, and they're pure catalogue:

Changing the DEFAULT doesn't affect the existing rows. It only changes what will be filled in on future insertions. Expecting otherwise is a classic mistake.

Here 05-01's insistence on naming constraints pays off: to drop one you have to name it, and you can't name what you don't know the name of.

It works because none of the 20 products has a cost higher than its price. If one did, PostgreSQL would reject the whole ALTER TABLE:

Dropping it:

And the other three families:

Adding a CHECK or a FOREIGN KEY forces PostgreSQL to check every existing row, and it does so with an exclusive lock. On a table of ten million rows that can be a long minute during which nobody can read or write.

The two-stage solution:

What NOT VALID does exactly:

That is: NOT VALID gives you the protection going forward immediately, and leaves the check on the history for a quieter moment. It's the standard technique for adding constraints to large tables in production.

And to check which constraints are unvalidated:

We undo the example:

This is the section that separates a harmless migration from a production outage.

PostgreSQL protects each operation with a lock level. The most aggressive is ACCESS EXCLUSIVE: while it's held, no other session can even read the table. Every query simply waits.

And now the truly important part, which almost nobody explains:

An "instantaneous" operation isn't harmless if it can't get the lock.

An ALTER TABLE ADD COLUMN takes a millisecond… once it has obtained the ACCESS EXCLUSIVE. If at that moment there's a long query reading the table, the ALTER starts waiting. And while it waits, PostgreSQL queues every new query behind it, because locks are granted in order of arrival.

A one-millisecond ALTER TABLE has just made the table unreachable for five minutes. The standard protection:

If it can't get the lock within three seconds, it aborts with canceling statement due to lock timeout instead of blocking the database. It gets retried later. Setting lock_timeout in every migration is one of the most profitable practices there is.

This is a critical difference between engines, and it completely changes how a migration is written.

In PostgreSQL, the DDL is transactional:

As if it had never happened. The two ALTER TABLEs have been undone.

The practical consequence is enormous: in PostgreSQL you can wrap a fifteen-step migration in BEGIN … COMMIT and have the guarantee that either it's applied in full or nothing is applied. If step 12 fails, the schema is left exactly as it was.

In MySQL and Oracle, every DDL statement implicitly commits the transaction in progress. If a fifteen-step migration fails at step twelve, the first eleven are already applied and there's no way to undo them. That's why on those engines every migration needs its hand-written reversal script, and why the tools in section 11 insist so much on the concept of a down migration.

If you work with MySQL, internalise this: the safety net doesn't exist. Each migration step has to be reversible on its own, and the order matters a great deal more.

How to change a schema without stopping the service.

The underlying problem: the database and the application are deployed separately, and for a while the old and new versions of the code coexist. Any change that breaks either of them causes errors. A RENAME COLUMN, as you saw, breaks the old version the instant it's committed.

Expand/contract (also called parallel change) solves this by breaking the change into five phases, each of them compatible both forwards and backwards:

The key is that at no point does a state exist in which the application can fail: between phase 1 and phase 5, both versions of the code work.

GreenStore computes each order's total by adding up its lines and adding the shipping. That's correct (01-05 justified it: no precomputed aggregates), but module 4's reports repeat that expression over and over, and at high volume the cost shows. Management asks for a denormalised total column.

Phase 1 — Expand: add the column, nullable.

Instantaneous, nullable, with no DEFAULT. The old code keeps working: it doesn't know the column exists and it doesn't need it.

Phase 2 — Double write. A version of the application is deployed that, as well as creating the order and its lines, fills in total. New orders have a value; old ones are still NULL. No schema change in this phase — it's pure code deployment.

Phase 3 — Backfill: filling in backwards.

With this module's tools, with no subqueries, in two steps:

Notice the AND o.total IS NULL: it makes the UPDATE idempotent (05-03) and, above all, it guarantees the backfill doesn't overwrite the values phase 2 is already writing in real time. It's the detail that turns a dangerous backfill into a safe one.

On a real table the backfill would be done in batches —WHERE total IS NULL AND id BETWEEN 1 AND 10000, repeated— so as not to hold a giant transaction locking rows.

Phase 4 — Switch the reads, and verify.

Before the reports start using the new column, you have to check it matches the live calculation:

(The first 8 of 20 rows.)

And the global check, which is the one that really matters:

€846.20: exactly module 4's canonical figure (€727.95 of goods + €118.25 of shipping). Twenty orders, none left unfilled. The backfill is correct.

In this phase, the professional thing is to leave the comparison running for a few days with an alert: if total ever stops matching the sum of the lines, the double write has a hole in it.

Phase 5 — Contract: consolidate and clean up.

Now that every row has a value, the column can be mandatory. And the old calculation is removed from the code.

One problem is left open, and it's worth saying clearly: orders.total is a precomputed aggregate and it has to be kept in sync. If somebody modifies an order line tomorrow with a direct UPDATE, total goes stale and nobody notices. The three solutions —a trigger that recomputes it, a materialized view, or a generated column if the formula lived in the same row— belong to module 10. It's exactly 01-05's warning, section 8: denormalise with the measurements in hand, and document how you're going to keep it up to date.

The same pattern, applied to a change of shape. Suppose GreenStore had been born with a single full_name column and you wanted to split it:

Notice what is never done: a RENAME COLUMN in one go, or a DROP COLUMN before nobody reads it. Every phase is reversible and none of them breaks the previous version of the code.

Everything above has a prior requirement that isn't technical:

A schema change isn't a command: it's a versioned file in the repository.

A numbered file, with a descriptive name, containing a schema change and (ideally) its reversal:

A migration tool keeps a control table inside the database itself with the migrations already applied, and on startup it applies only the missing ones, in order.

Why this is non-negotiable:

They all solve the same problem and they differ mostly in whether the change is written in SQL or in the application's language. Writing it in plain SQL (Flyway, Sqitch, golang-migrate) gives you total control over lock_timeout, NOT VALID and CONCURRENTLY; writing it in the application's language (Alembic, Rails, Django) gives you portability between engines and automatic reversal, at the price of losing the fine-grained control large tables need.

1. One migration = one change. If the file does five things and the third fails in production, you won't know what state it's been left in (and in MySQL, on top of that, you won't be able to undo it).
2. Always with a reversal script. Even if your engine has transactional DDL, writing the down forces you to think about whether the change is reversible. Very often you'll discover it isn't — a DROP COLUMN can't be undone — and that's invaluable information before applying it.
3. Tested in an environment like production. Not on your laptop with 20 rows: on a copy with realistic volume. An ALTER COLUMN TYPE that takes 40 ms with 20 rows takes 40 minutes with 40 million.
4. With a backup taken and verified beforehand. And verified means restored at some point, not "the cron job says it works".
5. Never by hand in the production console. Not "just this once", not "it's a small change", not "it's urgent". A change that isn't in the repository doesn't exist, and the next environment that gets created won't have it.

And a corollary that comes out of expand/contract's phase 2: migrations breaking backward compatibility have to be split into several. V003 adds the column, V004 fills it, V005 makes it mandatory — each one deployable separately, and between them the code that needs them gets deployed.

The rule that sums up both tables: in production, the question isn't "does this ALTER TABLE work?", but "what happens while it's running, and what happens if it fails halfway?". If you can't answer both, the migration isn't ready.

• Writing ALTER TABLE by hand in production. The root error from which almost all the others come.
• Believing ADD COLUMN with a DEFAULT always rewrites the table. Since PostgreSQL 11 it doesn't, unless the DEFAULT is volatile.
• Believing an "instantaneous" operation is harmless. It needs the ACCESS EXCLUSIVE, and if it can't get it, it queues the whole workload behind it. lock_timeout, always.
• A RENAME in one go. It breaks the old code the instant of the COMMIT. Expand/contract, or don't do it.
• ALTER COLUMN TYPE with no USING when the conversion isn't automatic. PostgreSQL's HINT tells you exactly what to write.
• Narrowing a VARCHAR without checking the data. PostgreSQL aborts; MySQL in relaxed mode truncates silently.
• ADD CONSTRAINT with no NOT VALID on large tables. A full scan with an exclusive lock.
• Expecting a change of DEFAULT to update the existing rows. It doesn't: it only affects future insertions.
• Using DROP COLUMN as a secure erase of sensitive data. The data is still on disk until the row is rewritten.
• Assuming DDL is transactional everywhere. In PostgreSQL, SQL Server and SQLite it is; in MySQL and Oracle it isn't: a half-applied migration stays half-applied.
• A backfill in a single giant transaction. It locks rows, it inflates the WAL and if it fails you have to start again. In batches.
• A backfill that overwrites the double write. Add WHERE column IS NULL so you only touch what's missing.
• Migrations with no reversal script. Writing it is the cheapest way of discovering the change isn't reversible.
• Tip: \d table before and after every ALTER TABLE. Checking what you've done costs two seconds.
• Tip: wrap migrations in BEGIN … COMMIT if your engine allows it. In PostgreSQL, fifteen changes become atomic.
• Tip: measure the time on a copy with real volume before touching production. It's the difference between a five-minute maintenance window and a five-hour one.

Work on the freshly reloaded database, inside BEGIN … ROLLBACK.

GreenStore wants to record the channel each order comes in through (web or phone), something that until now was inferred indirectly from whether employee_id was NULL.

1. Add a channel VARCHAR(10) column to orders, mandatory, with default value 'web' and protected by a named CHECK that only allows 'web' and 'phone'.
2. Backfill it: the orders with a sales rep assigned are 'phone'; the rest, 'web'.
3. Check the result by grouping by channel.
4. Answer: why doesn't step 1 require rewriting the table? And what would have happened if you'd put the CHECK in before filling in the data?

A colleague hands you this migration file to review before applying it to production, where products has 4 million rows and order_lines has 90 million:

1. Identify all the problems, saying for each one whether it's a locking, compatibility, reversibility or process problem.
2. Estimate which of the six operations are instantaneous and which aren't.
3. Rewrite the migration as it should be, splitting it into as many files as necessary.

Apply the complete expand/contract pattern to add a total_spent NUMERIC(10,2) column to customers accumulating what each customer has bought (goods + shipping).

1. Write the five phases, saying for each one what's a schema change, what's a code change and what's a data change.
2. Run phases 1, 3 and 5 on GreenStore (2 and 4 belong to the application).
3. Verify that the sum of every customer's total_spent matches the course's order total.
4. Discuss: should the column be NOT NULL? What value do the three customers with no orders have, and what does that imply?

Solution 1

Ten and ten, exactly the split 01-06 described: half the channel is web. And 04-04's three forms of COUNT confirm the consistency: in the phone channel all 10 orders have a sales rep, in web none do. The shipping adds up to €72.15 + €46.10 = €118.25, module 4's canonical figure — and along the way it reveals something nobody had ever looked at: the phone channel pays considerably more shipping, because it concentrates the orders to Portugal and France.

4. The two questions.

Why it doesn't rewrite the table: the DEFAULT 'web' is a constant, and since PostgreSQL 11 that gets stored in the catalogue and returned on the fly for the old rows. If the default value had been something volatile, it would indeed have rewritten the 20 rows (irrelevant here, decisive with 20 million).

What would have happened with the CHECK before the data: in this particular case, nothing bad, because the DEFAULT 'web' leaves every row with a valid value. But if we'd added the column nullable and with no DEFAULT and then the CHECK, it wouldn't have failed either: the 20 rows would have NULL, and a CHECK evaluating to UNKNOWN is accepted (05-01, section 3.5). The CHECK would have gone through without complaining, over an empty column. It's the classic trap: order matters, but the CHECK doesn't always warn you that you've done things the wrong way round. The NOT NULL would have.

On a large table the correct, safe order would be: add the column nullable → backfill in batches → ADD CONSTRAINT ... NOT VALID → VALIDATE CONSTRAINT → SET NOT NULL.

Solution 2

1 and 2. The problems, operation by operation:

And two process problems affecting the whole file:

• Six heterogeneous changes in one migration. It violates the first golden rule: if the fourth fails, you're left halfway (in PostgreSQL the ROLLBACK saves you; in MySQL, it doesn't).
• No lock_timeout. Any of these operations can queue the entire workload behind it.

Timing estimate at those volumes: operations 2, 3 and 4 are measured in minutes or hours each; number 5, in several minutes. The complete migration would leave the catalogue unreachable for all that time.

3. The correct version, split into files:

And the three remaining operations:

The exercise's lesson: most of a migration review consists of saying no. Of six operations, one is well conceived and five shouldn't be applied as they are.

Solution 3

1. The five phases:

2. Running phases 1, 3 and 5:

The temptation is to do it in one statement, joining customers, orders and order_lines:

shipping_cost lives in orders, and after the JOIN each order appears as many times as it has lines: the shipping would be multiplied and the total sum would give €278.70 instead of €118.25. It's exactly the mistake module 3 warned about three times and module 4 settled.

The correct way is to aggregate in two stages, reusing section 10's order_totals table:

Sofia Moreira Costa tops the ranking with €131.68 across two orders, ahead of Lucía, who has placed three. And the last three, with NULL, are 01-06's customers with no orders.

3. Verification:

€846.20: the course's canonical figure (€727.95 of goods + €118.25 of shipping). Twelve customers with purchases and three with none, exactly 01-06's deliberate gaps. 04-04's three forms of COUNT do all the verification work again.

4. The discussion. Should it be NOT NULL?

There's no universal answer, and that's the right answer. It depends on whether "hasn't bought" and "has bought €0" are the same business fact. In GreenStore they aren't: Núria, Hugo and Inés have never ordered anything, and that's information. If you want NOT NULL for arithmetic convenience, you have to document that the 0 means two things and add an orders_placed INTEGER NOT NULL DEFAULT 0 column that does distinguish them.

It's exactly 04-03's discussion, section 11 —designing with NULL, with sentinels or with NOT NULL— and the proof that a schema decision is never purely technical.

And the open problem, the same one as with orders.total: this column is a precomputed aggregate and it has to be kept in sync. Every new order, every modified line and every return leaves it stale. The solution —a trigger, or a materialized view that gets refreshed— belongs to module 10. Until it exists, the column is a time bomb, and that's why phase 2 of the pattern (the double write) isn't optional: it's the only thing stopping the data from rotting.

ALTER TABLE closes module 5 and with it your ability to operate on a complete database:

• The ALTER TABLE operations: ADD COLUMN (instantaneous, even with a constant DEFAULT since PostgreSQL 11, unless it's volatile), DROP COLUMN (instantaneous, irreversible and not a secure erase), RENAME (instantaneous and the most dangerous, because it breaks the old code on the spot), ALTER COLUMN TYPE with USING for the non-trivial conversions, SET/DROP NOT NULL, SET/DROP DEFAULT —which doesn't touch the existing rows—, and ADD/DROP CONSTRAINT, where 05-01's insistence on naming them pays off.
• NOT VALID + VALIDATE CONSTRAINT: immediate protection for the new rows and a check on the history without blocking reads or writes. The standard technique on large tables.
• What locks and what doesn't, with the table of levels; and the detail that really takes production down: an instantaneous operation that can't get the ACCESS EXCLUSIVE queues the entire workload behind it. SET LOCAL lock_timeout is the protection.
• Transactional DDL: in PostgreSQL, SQL Server and SQLite an ALTER TABLE can be undone with ROLLBACK and a fifteen-step migration is atomic; in MySQL and Oracle it can't, and a migration that fails halfway stays halfway.
• Expand/contract: add the new thing → write to both → fill in backwards → switch the reads → remove the old one. Five phases in which no state ever exists that breaks the application. Demonstrated with orders.total, whose idempotent backfill (WHERE total IS NULL) added up to the course's canonical €846.20.
• Versioned migrations: one numbered file per change, in the repository, reviewed as code, applied by a tool (Flyway, Liquibase, Alembic, Rails, Django, Laravel, Sqitch). And the five golden rules: one migration = one change, always with a reversal, tested with realistic volume, with a verified backup, and never by hand in production.

And with that, module 5 closes. Look back at what you've gained in six lessons. You know how to create tables with CREATE TABLE and declare the six constraints you'd spent five modules reading, understanding why CHECK accepts nulls, why UNIQUE allows several of them and why GENERATED BY DEFAULT is what forces that setval block. You know how to insert with INSERT, always listing the columns, in batches, and using RETURNING to retrieve the id you need to hang an order's lines off. You know how to modify with UPDATE, with a five-step protocol and an open transaction, understanding that all the assignments are evaluated over the old row and that price * 1.05 isn't idempotent. You know how to delete with DELETE, how to tell it apart from TRUNCATE, how to predict what each ON DELETE takes with it, and —most importantly— how to decide whether anything should be deleted at all, because active = FALSE exists precisely so that it isn't. You know how to merge with ON CONFLICT and with MERGE, and why look-then-decide is never safe. And you know how to change the schema without taking the service down.

Something more than the repertoire of statements has changed: the level of responsibility has changed. In modules 2 to 4, a mistake of yours returned a wrong number. From this module on, a mistake of yours modifies real data. That's why here you've learned as many habits as syntax: write the SELECT before the UPDATE, count the rows, open the transaction, check the RETURNING, take a backup first, set lock_timeout, version the migration. None of those habits appears in the language reference, and all of them separate someone who has spent years touching databases from someone who has spent a month.

With GreenStore you can now do everything: query it, join it, aggregate it, populate it, correct it and make it evolve. What you still can't do is transform and present what you get out of it. You're still returning name and last_name in two columns when you want one; you're still writing ROUND(...) by hand without knowing its siblings; you still can't extract the year from a date, work out how long a customer has been with you, put a piece of text in upper case, convert one type into another explicitly, replace a NULL with something readable, or classify rows into categories according to a condition. In module 6, Functions, the missing tools arrive: string functions to compose and clean up text, numeric functions to round and compute with judgement, date and time functions to answer at last "how long did that order take?", CAST and COALESCE to convert types and tame the nulls you've spent two modules dodging, and CASE to put conditional logic inside a query. From then on, your queries will stop returning data and start returning answers.