Transactions, indexes, and migrations

Why this matters

Yesterday's walkthrough ended with two statements that had to both happen: log the maintenance visit, and set the equipment's status. Nothing so far guarantees that. If the second fails, you are left with a service record for a machine the system still believes is broken — and no error anywhere to tell you.

Today you close that gap with transactions, make one slow query fast with an index while understanding what the index costs, and stop typing schema changes by hand. These three are what separate a database you can operate from one you are merely using.

Atomicity: all or nothing

A transaction is a group of statements the database treats as one indivisible unit. Either every statement takes effect, or none does. That property is called atomicity — atomic in the original sense of "cannot be cut into parts".

Three keywords control it:

• BEGIN starts a transaction. Everything after it is provisional.
• COMMIT makes all of it permanent, in one instant, visible to everyone at once.
• ROLLBACK discards all of it, leaving the database exactly as it was before BEGIN.

Here is one business operation — "record a service visit and mark the machine as under maintenance" — done as one unit:

BEGIN;
INSERT INTO maintenance_records (equipment_id, performed_on, hours_spent, notes)
VALUES (4, '2025-06-02', 2.50, 'Impeller replacement');
UPDATE equipment SET status = 'maintenance' WHERE id = 4;
COMMIT;

BEGIN
INSERT 0 1
UPDATE 1
COMMIT

Now the same thing with a fault planted in the second statement — a status value your CHECK constraint does not allow:

BEGIN;
INSERT INTO maintenance_records (equipment_id, performed_on, hours_spent, notes)
VALUES (5, '2025-06-03', 1.00, 'Belt tension check');
UPDATE equipment SET status = 'under repair' WHERE id = 5;
COMMIT;

BEGIN
INSERT 0 1
ERROR:  new row for relation "equipment" violates check constraint "equipment_status_check"
ROLLBACK

Read the last line carefully: you typed COMMIT and PostgreSQL performed a ROLLBACK. Once a statement inside a transaction fails, the transaction is poisoned and can only be thrown away. The INSERT reported INSERT 0 1 and still did not survive:

SELECT count(*) FROM maintenance_records WHERE equipment_id = 5;

 count
-------
     0
(1 row)

That zero is atomicity. Without the transaction, the maintenance record would be sitting in your table right now, attached to a machine whose status was never updated — a half-finished operation that no error message would ever mention again.

In interactive psql you can watch the state on the prompt itself. It reads maintenance=# normally, maintenance=*# inside an open transaction, and maintenance=!# once the transaction has failed. In that failed state every further statement is refused:

maintenance=!# SELECT count(*) FROM equipment;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

The fix is to end the block — type ROLLBACK; and the prompt returns to maintenance=#.

The practical rule: one transaction should wrap one business operation. Not one statement — that is already atomic on its own. Not a whole request handler that also sends email — if the email fails you do not want the database change undone. Ask "what would a user consider one thing happening?" and put the boundary there.

Indexes and what they cost

By default, finding rows means reading the whole table. An index is a separate, sorted structure the database maintains alongside a table so it can jump straight to matching rows.

EXPLAIN ANALYZE runs a query and reports how it was executed. Try it on a table with 50,000 maintenance records:

EXPLAIN ANALYZE
SELECT id, equipment_id, notes FROM maintenance_records WHERE performed_on = DATE '2024-05-19';

 Seq Scan on maintenance_records  (cost=0.00..1093.08 rows=25 width=27)
                                  (actual time=0.010..4.827 rows=26.00 loops=1)
   Filter: (performed_on = '2024-05-19'::date)
   Rows Removed by Filter: 49980
 Execution Time: 4.946 ms

Seq Scan means sequential scan: every row read, 49,980 of them discarded, to return 26. Add an index on the column being filtered:

CREATE INDEX maintenance_records_performed_on_idx ON maintenance_records (performed_on);
ANALYZE maintenance_records;

 Bitmap Heap Scan on maintenance_records  (cost=4.48..87.46 rows=25 width=27)
                                          (actual time=0.051..0.101 rows=26.00 loops=1)
   Recheck Cond: (performed_on = '2024-05-19'::date)
   ->  Bitmap Index Scan on maintenance_records_performed_on_idx
         Index Cond: (performed_on = '2024-05-19'::date)
 Execution Time: 0.162 ms

The scan of 50,000 rows is gone, and execution time fell from 4.9 ms to 0.16 ms. (ANALYZE refreshes the statistics the planner uses to choose; without current statistics it may keep the old plan.)

Now the part people skip. An index is not free:

• Every write pays. Each INSERT, UPDATE, and DELETE must update every index on the table. A table with eight indexes writes nine structures per row.
• It occupies disk and memory, competing with your data for cache.
• It may simply not be used. The planner ignores an index when the filter matches a large share of the table, because reading the whole thing in order is genuinely faster than jumping around. With only five distinct equipment_id values across 50,000 rows, WHERE equipment_id = 3 still gets a Seq Scan — and that is the right choice.

So index deliberately: for columns you actually filter, join, or sort on in queries that matter, and check with EXPLAIN that the index is used. Primary keys and UNIQUE constraints already create indexes for you. Foreign key columns usually deserve one, because they are joined on constantly.

Migrations: schema changes as files

You now know how to add a column: type ALTER TABLE at the psql prompt. Do that and you have a database whose shape exists in exactly one place — that database — and nowhere in your repository. Your teammate's database does not have the column. Neither does production. Nobody can review the change, and nobody can tell what order things happened in.

A migration is a numbered file containing one schema change, committed to Git and applied in order. db/migrations/001_create_equipment.sql, 002_create_maintenance_records.sql, 003_add_location.sql. Three rules make them work:

1. Ordering is the whole point. 003 may depend on 002 having run. Numbering makes the sequence explicit and repeatable on any machine, from empty.
2. Never edit a migration that has been applied anywhere. Your database ran the old version; another already ran the new one; the two now disagree with no way to detect it. Fix a mistake by adding 004.
3. Wrap each migration in BEGIN and COMMIT. PostgreSQL can roll back schema changes, so a migration that fails halfway leaves the schema untouched rather than half-changed.

-- db/migrations/003_add_location.sql
BEGIN;
ALTER TABLE equipment ADD COLUMN location text;
UPDATE equipment SET location = 'Unassigned' WHERE location IS NULL;
ALTER TABLE equipment ALTER COLUMN location SET NOT NULL;
COMMIT;

psql -v ON_ERROR_STOP=1 maintenance -f db/migrations/003_add_location.sql

BEGIN
ALTER TABLE
UPDATE 5
ALTER TABLE
COMMIT

Three statements in one file, because adding a NOT NULL column to a table with existing rows cannot be done in one step: add it nullable, fill it, then tighten it. All or nothing.

ON_ERROR_STOP=1 matters. Without it, psql keeps executing after an error and returns success, which in a deploy script means a broken migration reports as fine. With it, the run stops and exits non-zero. Running 003 twice shows exactly that:

BEGIN
psql:db/migrations/003_add_location.sql:2: ERROR:  column "location" of relation "equipment" already exists

Real projects track which migrations have run in a small table so they are never applied twice. Tools like node-pg-migrate do this for you; the rules above are what they are automating.

Walkthrough: force a rollback on purpose

Do this in psql, watching the prompt change.

BEGIN;
UPDATE equipment SET status = 'retired' WHERE id = 1;
SELECT id, name, status FROM equipment WHERE id = 1;

The SELECT shows retired — inside your transaction, the change is real. Open a second terminal, connect with psql maintenance, and run the same SELECT there: it still shows operational. Uncommitted work is invisible to everyone else. Back in the first terminal:

ROLLBACK;
SELECT id, name, status FROM equipment WHERE id = 1;

operational again. Nothing you did survived, and no other session ever saw it.

Your turn

Produce a migration file and a transaction demo that includes a forced rollback.

1. Create db/migrations/ and move your Day 44 schema into 001_create_equipment.sql and 002_create_maintenance_records.sql, each wrapped in BEGIN / COMMIT.
2. Write 003_add_location.sql adding a location column, in the three steps above. Apply it with psql -v ON_ERROR_STOP=1 maintenance -f ... and confirm with \d equipment.
3. Run 003 a second time. Copy the error and the exit code (echo $?) into db/notes.md, and write one sentence on why re-running is not safe.
4. Write db/transaction-demo.sql: a BEGIN, an INSERT into maintenance_records, an UPDATE of that equipment's status, and a COMMIT. Verify both changes landed.
5. Copy it to a second block, break the UPDATE with an invalid status, and run it. Record that psql printed ROLLBACK at your COMMIT, and prove with a SELECT count(*) that the INSERT did not survive.
6. Load 50,000 rows for the index test: INSERT INTO maintenance_records (equipment_id, performed_on, hours_spent, notes) SELECT 1 + (g % 5), DATE '2020-01-01' + (g % 2000), 1.00, 'bulk ' || g FROM generate_series(1, 50000) AS g;
7. Run EXPLAIN ANALYZE on a query filtering performed_on for one date. Record the scan type and execution time. Add the index in a 004_ migration, run ANALYZE, and record both again.
8. Write one sentence justifying the index: which real query it serves, and what it costs on write.

Common pitfalls

• Leaving a transaction open. A forgotten BEGIN holds locks and blocks other sessions until you COMMIT or ROLLBACK. If psql shows =*#, you are still inside one.
• Expecting statements after an error to run. Once aborted, everything is refused until the block ends. That is a feature, not a fault.
• Indexing every column "to be safe". Each one taxes every write and may never be used. Add one, prove it with EXPLAIN, keep it.
• Editing an applied migration. Databases that ran the old version silently diverge. Add a new file instead.

Verify it yourself

Open today's reference, the PostgreSQL tutorial, and read its chapter on transactions.

1. The tutorial introduces SAVEPOINT, which this lesson skipped. Write down what it lets you do inside a transaction that plain ROLLBACK cannot.
2. This lesson claimed uncommitted changes are invisible to other sessions. Find the wording the documentation uses for that guarantee and record it.

Tomorrow you connect all of this to your Express API.