This is the most demanding lesson of the course, and also the one that most resembles real work. There is no warm-up SELECT here: there are window functions, recursive CTEs, jsonb, transactions with error handling, five exercises with two psql sessions in parallel and two on reading execution plans. All of it over BiblioRed.

How to work through this lesson. The same as the previous ones —try before you look— with one important difference: you need two terminals open. The concurrency block cannot be read, it has to be run. Each exercise in that block states exactly what to type in Session A and in Session B, and in what order. The instruction "do not run ahead with Session B until A has done its part" is not a suggestion: if you skip the order, the phenomenon you want to observe does not happen and you will think the exercise is wrong.

Open the two terminals like this, and give them distinguishable prompts so you do not get confused:

A warning about execution plans: the numbers you see on your machine will not match the ones in the solutions, because your loans table has 20 rows and the plans of exercises 12 and 13 correspond to a BiblioRed in production with 900,000. What has to be learned is not the milliseconds: it is the shape of the plan and the relationship between estimated and actual rows.

This lesson does not repeat basic SQL (that is 07-01) or normalization (that is 07-03).

Start from the same dataset as lesson 07-01. If you do not have it loaded, go back there and run the script; the check that it is right is that lesson's counting query (20 loans, 15 copies, 26 registrations).

On top of that dataset three things have to be added that this lesson needs: the reports table with jsonb, a tree of topic categories and a queue of notices.

PostgreSQL version. You need 12 or higher for everything in this lesson; FOR UPDATE SKIP LOCKED has existed since 9.5 and FILTER since 9.4.

SQLite. It supports window functions and recursive CTEs since 3.25, but it has no jsonb (it has different json_* functions), no FOR UPDATE, no SKIP LOCKED and no configurable isolation levels: it locks the whole database when writing. Blocks (b), (c) and (d) of this lesson are intrinsically PostgreSQL.

1. Block A — Advanced queries: windows, recursion and jsonb (exercises 1-3)
2. Block B — Transactions and error handling (exercises 4-6)
3. Block C — Concurrency: five two-session exercises (exercises 7-11)
4. Block D — Indexes and execution plans (exercises 12-13)
5. Common mistakes and tips
6. Reinforcement exercises

Difficulty: Intermediate

Task. Four reports that lesson 07-01 could not solve because window functions were needed:

• (a) The most-loaned material of each branch, with its number of loans. One row per branch. The branch is determined by the copy loaned, and ties are broken alphabetically by title.
• (b) The complete ranking of materials of the North branch, showing ROW_NUMBER, RANK and DENSE_RANK in separate columns, to see how they differ when there is a tie.
• (c) Monthly loans of the Central branch in 2026, with the difference and the percentage change against the previous month.
• (d) The running total of fine collection, payment by payment, ordered by date.

Hint. For (a), number the rows inside each partition and keep number 1; the WHERE cannot filter window functions, you have to wrap it in a CTE.

Solution

Expected result

(a)

(b) North branch:

(c)

(d)

Explanation. Five points, and all of them are real traps:

The East branch does not appear in (a), and that is correct: its two copies (EJ-3085 reserved and EJ-3095 withdrawn) have never gone out, so there is no loans row mentioning it. If the report had to show all four branches with "(none)" at East, you would have to start from branches with a LEFT JOIN against the CTE. It is the same lesson as the anti-join of 07-01 applied to windows.

You cannot filter by a window function in the WHERE. WHERE ROW_NUMBER() OVER (...) = 1 is a syntax error, and it is not a whim: window functions are evaluated after the WHERE and the GROUP BY, near the end of the logical order, alongside the SELECT. That is why they have to be wrapped in a CTE or a derived table and filtered at the outer level.

The three ranking functions do different things when there is a tie, and (b) shows it with data: "The Frozen Heart" and "The Map of Time" have 2 loans each at North.

For a "top 1 per group" you have to use ROW_NUMBER: with RANK you would get two rows for North, because the two tied ones would have rank 1. And if you really do want both in the event of a tie, then RANK is the right one. It is a business decision, not a syntax one.

In (c), LAG is computed over the rows the query returns, not over the calendar. If in some month there had been no loans at all, that month would not appear and LAG would compare with the previous present month, not with the immediately preceding one in time. That gives false comparisons. The robust way is to generate the series of months and left-join it —exactly what exercise 2 does—. It is not needed here because all seven months have loans, but in production it is a very expensive mistake.

In (d), the window frame matters. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW accumulates row by row. The default frame when there is an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which groups all the rows with the same ordering value. If two payments had the same date, with RANGE both would show the same running total (the one after adding both) and with ROWS they would show the intermediate step. That is why the window's ORDER BY includes payment_id: it makes the order unique and removes the ambiguity.

Difficulty: Advanced

Task. Two different uses of recursion:

• (a) The daily occupancy report for July 2026: one row for each day of the month, with the number of loans of that day. Days with no loans must appear with 0. Generate the series of dates with a recursive CTE.
• (b) The subject tree of the topic_categories table: for each category descending from "Fiction" (itself included), its depth level and its full path from the root.

Hint. A recursive CTE always has two parts joined by UNION ALL: the base case and the recursive step, which refers to the CTE itself.

Solution

Expected result

(a) 31 rows. The first ones and the ones with loans:

Total: 4 loans spread over 4 days, and 27 days at zero.

(b)

Explanation. The structure of a recursive CTE is always the same and is worth memorizing:

The engine runs the base case, puts the result in a working table, runs the recursive step using only the new rows from the previous iteration, and repeats until an iteration produces no rows.

The stop condition is your responsibility. In (a), without WHERE day < DATE '2026-07-31' the query generates dates until it blows up. In (b), the stop is implicit: when there are no more categories whose parent is at the current level, the iteration returns zero rows and finishes. But if the tree had a cycle —a category that was its own ancestor— the query would never end. Against that, PostgreSQL 14 and above have the CYCLE clause:

In earlier versions, the manual pattern is to drag an array with the path walked so far and add WHERE NOT c.category_id = ANY(t.path_) in the recursive step. It is exactly what was missing in exercise 3 of lesson 07-02, where the prerequisites of a course could form a cycle A → B → C → A.

In PostgreSQL, (a) has a much better shortcut:

generate_series is shorter, faster and more readable. The recursive version is in the exercise because it is portable (it works in SQLite and in SQL Server) and because understanding the mechanism is what lets you solve (b), where there is no shortcut.

The LEFT JOIN is the whole point of the exercise. A daily occupancy report built with GROUP BY loan_date over the loans table would return 4 rows, not 31. And a chart drawn with those 4 rows presents the month as if there had been continuous activity. Days at zero are data, not an absence of data, and the only way to have them is to generate the complete time axis and left-join it.

Difficulty: Advanced

Task. The satisfaction surveys of the events are stored in event_reports.survey_responses, of type jsonb. You are asked to:

• (a) List each event with the survey channel and the number of responses received.
• (b) The reports whose surveys were done through the web, using the containment operator @>.
• (c) Unnest the array of responses with jsonb_array_elements and compute, per event, the average score, the number of responses that would recommend and the percentage.
• (d) A manual pivot: per event, how many responses gave each score (2, 3, 4 and 5), in columns.

Hint. -> returns jsonb; ->> returns text. The difference matters when you have to compare or convert.

Solution

Expected result

(a)

(b) Events 101, 103 and 105.

(c)

(d)

Explanation. The four parts cover the four operations used 95% of the time with jsonb:

-> versus ->>. survey_responses -> 'channel' returns "web" with quotes, because it is a jsonb value of string type. survey_responses ->> 'channel' returns web, plain text. The practical consequence: WHERE survey_responses -> 'channel' = 'web' fails or finds nothing, because it compares a jsonb with a text. You have to use ->> to compare with text, or -> 'channel' = '"web"'::jsonb. It is mistake number one with jsonb.

@> is the operator that uses the GIN index. WHERE survey_responses ->> 'channel' = 'web' gives the same result as @> but cannot use the GIN index we created in Before You Begin: a GIN index by default indexes the structure of the document and answers containment and existence operators (@>, ?, ?|, ?&), not extractions. With five rows it makes no difference; with 200,000 reports, the difference is three orders of magnitude. If you need to index a specific extraction, the right thing is an expression index: CREATE INDEX ... ON event_reports ((survey_responses ->> 'channel')).

jsonb_array_elements is a function that returns rows, not a value. That is why it appears in the FROM with LATERAL, which lets it see the er.survey_responses column of the row being processed. The word LATERAL is optional in PostgreSQL when the function goes in the FROM separated by a comma, but writing it makes explicit what is happening: for each row of event_reports, as many rows are generated as its array has elements.

The manual pivot with FILTER. PostgreSQL does not have the PIVOT clause of other engines; you do it with a conditional aggregate per column. count(*) FILTER (WHERE score = 5) is standard SQL and is equivalent to sum(CASE WHEN score = 5 THEN 1 ELSE 0 END), which is the portable version. Beware the variant count(CASE WHEN score = 5 THEN 1 END): it works because count ignores nulls, but count(CASE WHEN ... THEN 1 ELSE 0 END) does not work, because it counts the zeros as well and always returns the total. It is a classic mistake.

The limitation of the manual pivot: the columns have to be written out by hand. If tomorrow the survey accepts scores from 1 to 10, the query has to be edited. A pivot with a variable number of columns is not expressible in pure SQL —the number of columns of the result must be known when the query is parsed— and it is solved by generating the SQL from the application or by returning the result in long format and pivoting in the presentation layer.

Difficulty: Intermediate

Task. Iván Pereda (member 15) shows up at the North branch desk to pick up "The Map of Time", which he had reserved (reservation 502, status active). The available copy is EJ-3082 (copy_id 3082).

Write the complete transaction that:

1. Checks that the copy is really available, locking it.
2. Inserts the loan with a 21-day term from August 2, 2026.
3. Marks the copy as on_loan.
4. Closes the reservation as fulfilled.

It must be atomic: if any step fails, nothing must remain. And it must detect the case in which somebody else took the copy between the catalog query and the button press.

Solution

Interactive version in psql, to understand the flow:

Production version, with real error handling, as a function:

Expected result

And checking the effect:

If another session had taken the copy first, the function would raise:

...and nothing of the transaction would remain: not the loan, not the status change, not the closed reservation.

Explanation. Four decisions that separate this transaction from one that works "almost always":

FOR UPDATE in the checking SELECT. Without it, between the SELECT that says "available" and the UPDATE that marks it as loaned there is a window in which another session can do the same. FOR UPDATE locks the row until the end of the transaction: the second session waits at its own SELECT ... FOR UPDATE and, when the first one commits, reads the already updated status. It is the check-then-act pattern, and without the lock it is not atomic.

The UPDATE carries AND status = 'available' even though we already checked it. It is a belt on top of the braces: it turns the UPDATE into a conditional operation whose number of affected rows tells us whether the premise was still true. GET DIAGNOSTICS ... ROW_COUNT is the way to read that number from plpgsql; in interactive psql you see it in the UPDATE 1 / UPDATE 0.

An UPDATE 0 is not an error. It is the most common trap: if the reservation was already closed, step 4 returns UPDATE 0 and the transaction commits all the same. Here that is deliberate —there may be no reservation, and nothing happens—, but in step 3 it is not, and that is why it is checked there. Deciding explicitly, step by step, whether an UPDATE 0 is acceptable or is a failure, is half the work of writing a transaction.

In plpgsql, the whole function is an implicit transaction. You do not have to write BEGIN/COMMIT inside: if the function raises an exception, everything it has done is undone automatically. What it can do is catch exceptions with EXCEPTION WHEN ... THEN, and there it is worth knowing that every EXCEPTION block creates an implicit savepoint, with its cost. A loop of a million iterations with an EXCEPTION block inside is noticeably slower than the same loop without it.

Difficulty: Intermediate

Task. The nightly process of August 2, 2026 must issue a late-return fine for every overdue loan not returned: loans 9, 12 and 15. The amount is €0.20/day with a cap of €15.00.

The problem: the index uq_fine_loan_reason on (loan_id, reason) exists, and some of those loans already have a late-return fine issued. If the process runs as a monolithic transaction, the first clash aborts the whole batch and no fine at all is issued.

Write the batch in such a way that the failure of one element does not prevent the others from being processed, using SAVEPOINT. When it finishes, report how many were issued and how many were skipped.

Hint. ROLLBACK TO SAVEPOINT undoes back to the savepoint and leaves the transaction usable; without it, after an error the transaction is aborted and every later statement fails.

Solution

Interactive version, to see the mechanism:

Production version, with the loop and the count:

Expected result

Specifically:

Final result: 1 fine issued, 2 skipped, and the transaction commits.

Check:

Explanation. What this exercise teaches is a property of PostgreSQL that surprises whoever comes from other engines:

In PostgreSQL, an error inside a transaction aborts the whole thing. From that moment on, every statement returns ERROR: current transaction is aborted, commands ignored until end of transaction block, and the final COMMIT behaves like a ROLLBACK.

That is: without SAVEPOINT, a single unique-key clash on element number 3 of a batch of 500 throws away all 500. And it does not fail loudly: the COMMIT answers ROLLBACK and you have to be watching to notice.

SAVEPOINT is the antidote. It marks a point you can go back to; ROLLBACK TO SAVEPOINT undoes only what was done afterwards and returns the transaction to a usable state. RELEASE SAVEPOINT discards it when it is no longer needed (optional, but advisable in long batches: every live savepoint consumes resources).

In plpgsql you do not write SAVEPOINT: you use a BEGIN ... EXCEPTION ... END block. That block creates and manages the savepoint automatically. It is the idiomatic form and the one to use.

The case of loan 15 deserves a design comment. Its previous fine (number 8) is in status voided, so conceptually it "does not count" and perhaps a new one should be issuable. But the unique index is on (loan_id, reason) and nothing else, and it does not distinguish statuses. If the business wants to allow it, the correct index would be partial:

It is the same partial unique index pattern we used in 07-02 for "a single open rental per copy". A batch skipping an element because of an over-broad constraint is a design symptom, not just a process problem.

Difficulty: Advanced

Task. An operator runs this sequence in a single session. State, for each numbered statement, whether its effect survives the final COMMIT or not, and describe the final state of the affected tables. Justify every answer.

Second part: what would have happened if the ROLLBACK TO SAVEPOINT sp3 had been omitted after statement (6)?

Solution

Final state of the tables:

participations goes from 7 to 8 rows: (107, 4, 'storyteller', 150.00) is added and (106, 4, 'workshop_leader', 400.00) is not.

Second part: without the ROLLBACK TO SAVEPOINT sp3.

Absolutely everything is lost: statement (7) is not even executed, and the COMMIT answers literally ROLLBACK. Speaker 4 is not created, the seats of 106 are still 15 and the participation (107,4) does not exist. A single error, with no savepoint to contain it, throws away the entire piece of work.

Explanation. The point to fix in your mind, and the one almost everybody gets wrong the first time:

ROLLBACK TO SAVEPOINT sp undoes what happened after sp. What happened before sp —including what lies between sp1 and sp2— remains.

Statement (2) is the one that separates whoever has understood it from whoever has not. It sits between two savepoints, and the ROLLBACK TO sp2 goes back to the second one, not to the first. Had the operator wanted to undo it, they would have had to write ROLLBACK TO sp1.

Second point: a ROLLBACK TO does not destroy the savepoint. After ROLLBACK TO sp2, the point sp2 still exists and you can go back to it again. It is RELEASE SAVEPOINT that removes it. And ROLLBACK TO sp1 would automatically invalidate sp2, because it is later.

Third point, the most important in practice: the ROLLBACK answer to a COMMIT is easy to miss. In an interactive psql it jumps out at you; in an application client that does not check the value returned by commit(), the transaction is lost in silence and the application believes it has saved. This is the reason why serious ORMs wrap every operation in a savepoint and why psql's ON_ERROR_STOP (\set ON_ERROR_STOP on) should be active in every migration script.

Instructions for the whole block. Run each line in the session indicated and in the order indicated. When a session "hangs" without giving the prompt back, it is because it is waiting for a lock: that is exactly what we want to observe. Before each exercise, make sure neither session has an open transaction (ROLLBACK; just in case).

Difficulty: Advanced

Task. The regulations add a €2.00 surcharge to pending fines more than 30 days old. Two clerks, at two different branches, apply the surcharge to the same fine: number 3 (Iván Pereda, €1.60, pending).

The application does it in two steps: it reads the amount, adds 2.00 in memory and writes the result.

Part 1. Reproduce the phenomenon. What should the fine be worth in the end and what is it worth?

Part 2. Fix it in two different ways and explain which one you prefer.

Solution — Part 1: the lost update

It should be €5.60 (1.60 + 2.00 + 2.00). It is €3.60. One of the two surcharges has disappeared without a trace: no error, no warning, no entry in any log. Both clerks saw UPDATE 1 and believe their work is done.

Solution — Part 2, option 1: SELECT ... FOR UPDATE

Restore the value (UPDATE fines SET amount = 1.60 WHERE fine_id = 3;) and repeat with locking:

Result: €5.60. Correct.

Solution — Part 2, option 2: the atomic UPDATE

With no explicit BEGIN, each UPDATE is its own transaction. The second waits for the first to commit and re-reads the updated row before applying its change, because in READ COMMITTED a blocked UPDATE re-evaluates the row when it is released.

Result: €5.60. Also correct, and with no explicit locking.

Expected result

Explanation. The lost update is the most treacherous concurrency phenomenon because the engine does not consider it an error. Both UPDATEs are legitimate, both affect one row, both commit. The inconsistency is in the application's head, not in the database.

When to use each solution:

• The atomic UPDATE is always preferable when it is possible. A single round trip, no race window, no lock to manage. The rule: if the new value can be expressed as a function of the old value inside the SQL itself, do it that way.
• FOR UPDATE is necessary when the computation does not fit into the UPDATE: when you have to query other tables, apply complex business logic or decide whether to update. That is the case of the loan transaction in exercise 4.
• Optimistic locking with a version column (exercise 10) is the third way, and the one that fits when the user has a form open on screen: you cannot hold a lock while somebody is thinking.

An important note about READ COMMITTED, which is the default level: at step 7 of option 2, session B's UPDATE does not work on the snapshot it saw at the start; when the lock is released, PostgreSQL re-evaluates the WHERE against the most recent version of the row. That behavior —which is not what a strict isolation would do— is what saves option 2, and it does not work in REPEATABLE READ: there the second UPDATE would abort with ERROR: could not serialize access due to concurrent update.

Difficulty: Advanced

Task. A web catalog query reads twice, within the same transaction, how many available copies there are of material 902 ("The Map of Time"). Between the two reads, a colleague returns a copy.

Run the scenario twice: first with READ COMMITTED and then with REPEATABLE READ. Note what Session A sees on each read and explain the difference.

Starting state: material 902 has two copies, EJ-3081 (on loan) and EJ-3082 (available). Available: 1.

Solution — Scenario 1: READ COMMITTED (the default level)

Session A has seen 1 and then 2 within the same transaction: a non-repeatable read.

Solution — Scenario 2: REPEATABLE READ

Restore the state (UPDATE copies SET status='on_loan' WHERE copy_id=3081;) and repeat:

Session A sees 1 both times. Only after the COMMIT, in a new transaction, does it see the 2.

Expected result

Explanation. The difference lies in when the snapshot is taken of what each transaction reads:

• In READ COMMITTED, each statement takes its own snapshot, when it starts. That is why the second query sees the changes committed in between. It is PostgreSQL's default level, and it is the right one for 95% of applications: it maximizes concurrency and never reads uncommitted data.
• In REPEATABLE READ, the snapshot is taken once only, at the transaction's first statement, and it is kept until the end. Everything the transaction reads will be consistent with itself, as if the world had frozen.

When it really matters. If the monthly management report runs eight queries —loans, members, fines, payments, events...— and it runs in READ COMMITTED while the system is in use, the eight queries may see different states of the database. The total of fines may not match the breakdown by reason. In REPEATABLE READ that is impossible: all eight see exactly the same instant.

The price. In REPEATABLE READ, if your transaction tries to modify a row that another transaction modified and committed after your snapshot, PostgreSQL aborts yours with:

It is not a failure: it is the contract. The application has to be ready to retry the whole transaction. If it is not, raising the isolation level swaps a problem of inconsistent data for a problem of errors in production.

A terminological note. The SQL standard says that REPEATABLE READ allows phantom reads (new rows appearing in a range query). PostgreSQL's implementation, based on MVCC with snapshots, does not allow them: its REPEATABLE READ is stronger than the minimum the standard demands. For write skew —the phenomenon that does slip past it— there is SERIALIZABLE, which is the fourth level and the only one guaranteeing equivalence with a serial execution.

Difficulty: Advanced

Task. Two processes update the data of two members, but in different order: process A starts with Marta Alsina (14) and continues with Iván Pereda (15); process B starts with Iván and continues with Marta.

Part 1. Cause the deadlock and observe what PostgreSQL does. Part 2. Fix it without changing what each process does.

Solution — Part 1: the deadlock

Output in the victim session (the one PostgreSQL decides to abort):

The other session unblocks immediately and can carry on to its COMMIT. The victim is left with an aborted transaction and must ROLLBACK and retry.

Solution — Part 2: ordering the locks

The cause is the reverse acquisition order. The solution is for all the processes to lock the rows in the same order, for instance by ascending primary key:

There is waiting, but no deadlock: B queues behind A and both finish. And in a single statement, better still:

PostgreSQL locks the rows in the order it finds them, which is deterministic for the same query and the same plan.

Expected result

Explanation. A deadlock is a cycle of waits: A waits for a resource held by B, and B waits for one held by A. Neither can move forward and neither is going to let go of what it holds.

PostgreSQL detects it and resolves it on its own. Every so often —deadlock_timeout, one second by default— it checks whether there is a cycle in the wait graph, and if there is, it aborts one of the transactions to break it. It chooses the victim by internal criteria; you cannot predict which one it will be. That is why the diagnosis is always the same:

ERROR: deadlock detected is not a database failure: it is an application failure, one that has requested locks in an inconsistent order. The engine has merely detected it in time.

The three rules for not having them:

1. Consistent order. Fix an ordering criterion —ascending primary key is the simplest— and apply it in all the code that locks several rows. If your process reads a list of identifiers to update, sort it first.
2. Short transactions. The less time a lock is held, the smaller the window. Never make a network call, or wait for a user, with an open transaction.
3. Automatic retry. Even with the two previous rules, a deadlock can happen. Every important transactional operation should be wrapped in a retry loop that catches SQLSTATE code 40P01 (deadlock_detected) and tries again, typically 3 times with increasing waits.

Note: deadlocks also appear without the programmer touching two tables. Two INSERTs into tables related by a foreign key acquire locks on the parent row, and two processes inserting children of different parents in crossed order can block each other. That is why the ordering criterion must be applied to the keys, not to the tables.

Difficulty: Advanced

Task. Event 106 ("Introduction to genealogy workshop", 15 seats) has 14 seats taken: one is left. Two people open the portal's registration form at the same time; both see "1 seat available" and both press "Register" a few seconds apart.

The portal cannot hold a lock while the form is on screen: the user may take minutes or go to lunch. Implement optimistic locking using the events.version column.

Set up the scenario:

Solution

The application version, in plpgsql:

Expected result

And the final state:

Member 13 is not registered and the application can show them an honest message: "the last seat has just been taken".

Explanation. Optimistic locking solves a problem that pessimistic locking cannot: the interval between reading and writing may last minutes, and holding a lock for that long is unacceptable —it would block everybody else and, if the user closes the browser, the lock stays hanging until the connection expires.

The mechanism has three pieces and all three are necessary:

1. A version column (an integer, or a timestamp, or any value that changes with every modification).
2. The application reads and remembers the version it saw.
3. The UPDATE includes AND version = <the one I saw> and increments the version. If somebody got ahead, the condition does not hold and the UPDATE affects 0 rows.

What makes it work is that UPDATE 0 is not an error: it is information. You have to check it explicitly, and that is the point most often forgotten. An UPDATE that returns 0 rows and is not checked turns optimistic locking into a decorative ornament.

Optimistic versus pessimistic:

And the final detail: in Session B, the free-seat check (v_free) would already have returned NO_SEATS in this specific case, because A committed first. The version check is the one covering the worst case: that both sessions reach the UPDATE at the same time, when neither has seen the other's change. The two checks are not redundant: the first gives a better message, the second is the one that guarantees correctness.

Difficulty: Advanced

Task. The notices table has 6 pending notices. Two sending processes run in parallel and each one must take 2 notices to process. No notice may be processed twice and no process should be left waiting for the other.

Part 1. Check what happens with plain FOR UPDATE. Part 2. Solve it with FOR UPDATE SKIP LOCKED.

Solution — Part 1: FOR UPDATE blocks

Session B hangs: it wants the same two rows —they are the first two by notice_id— and they are locked. With two processes, the queue is sequential; with ten, nine are stopped. Plain FOR UPDATE turns a parallel queue into a queue of one.

Solution — Part 2: SKIP LOCKED

Close both transactions (ROLLBACK in both) and repeat:

Expected result

Step 2, Session A:

Step 4, Session B:

Final state of the queue:

Notices 5 and 6 are left for the next round. Zero overlap, zero waiting.

Explanation. SKIP LOCKED changes the locking behavior in a very specific way:

Instead of waiting for a locked row to be released, it skips it and looks for the next one matching the WHERE.

That is exactly what a work queue needs, and it is the reason why PostgreSQL can be used as a queueing system without adding a separate component.

Four indispensable details of the pattern:

1. LIMIT bounds the batch. Without it, the first session would lock the whole pending queue.
2. ORDER BY gives a processing order. Here it is by identifier (FIFO); it could be by priority, by age or by whatever the business requires.
3. The processed mark goes in the same transaction. If the UPDATE ... SET status='sent' were done in a different transaction, between the SELECT and it there would be a window in which the lock no longer exists and another process could pick up the same notice.
4. What happens if the process dies between steps 4 and 7 is the best part of the pattern: the uncommitted transaction is undone, the locks are released and the notices go back to pending. The queue repairs itself. If instead you had marked the notices as "in process" in a separate transaction, a crash would leave them stuck in that status forever and a rescue process would be needed.

When NOT to use SKIP LOCKED. Never in a normal business query. A balance query with SKIP LOCKED would return an incomplete result —it would be missing the rows somebody is modifying— with no warning at all. SKIP LOCKED only makes sense when "any available subset" is a valid answer, and that happens in queues and in practically nothing else.

Difficulty: Advanced

Task. In the production BiblioRed, loans has 912,000 rows and about 3,100 of them are open. The "my current loans" screen runs this query and takes almost a second:

You are asked to: (a) diagnose the plan; (b) propose the index, justifying its shape; (c) predict the resulting plan and estimate the improvement.

Solution

(a) Diagnosis.

The diagnosis is unmistakable: an index is missing. The engine knows there are only 4 candidate rows (it estimates well), but it has no way of finding them without looking at them all. The ratio between rows read and rows returned —456,000 to 1— is the definition of a missing index.

(b) The proposed index.

Three decisions, and each one has its reason:

• A partial index. The condition return_date IS NULL holds for 3,100 rows out of 912,000: 0.34%. The partial index indexes only those and takes up around 80 KB against the ~20 MB of a full index on member_id. Besides, the rows that get closed leave the index automatically when the loan is returned, so it stays small forever.
• member_id as the only column. Inside the partial index, the only discrimination left to do is the member. Adding return_date to the columns would be redundant: in the partial index it is NULL in every row.
• It is not UNIQUE. A member can have several open loans.

(c) The resulting plan.

Expected result

Explanation. What matters in this exercise is not that an index speeds up a query —you knew that since 06-03—, but how the plan is read to reach that conclusion with confidence.

Rows Removed by Filter is the queen metric. It is the number of rows the engine read and threw away. If it is huge compared with the ones it returns, there is an index waiting to be created. If it is small, the Seq Scan may be the right choice: reading 500 rows sequentially is faster than jumping around an index, and the planner knows it.

Buffers distinguishes an I/O problem from a CPU problem. shared hit are blocks that were in memory; read, the ones that had to be brought from disk. Here 8,431 physical reads explain most of the 873 ms. A plan with many hit and few read that is still slow has a different problem (too many comparisons, an expensive function, a badly chosen JOIN).

The comparison between estimated rows= and actual rows= is the other key diagnosis. Here they are 4 and 2: the planner gets it right, so the problem is one of access, not of statistics. Had it estimated 4 and found 300,000, the diagnosis would be the opposite: stale statistics, and the solution ANALYZE loans; before any index.

A warning: after creating the index, run ANALYZE loans; and measure again. And do not create indexes "just in case": every index slows down writes and takes up space. An index is justified by a plan before and a plan after, as in this exercise.

Difficulty: Advanced

Task. Part 1. These three queries from the production BiblioRed do a Seq Scan even though the right indexes exist. Identify the reason for each one and rewrite the query so that the index is used. The existing indexes are:

Part 2. The front desk constantly runs these two queries:

You can only create one composite index. Choose between (material_id, branch_id, status) and (branch_id, status, material_id) and reason with the leftmost prefix rule.

Solution — Part 1

(a) A function over the indexed column.

EXTRACT(YEAR FROM loan_date) is not loan_date. The B-tree index stores dates in order; it knows nothing about the extracted year. Rewriting as a range:

Now the condition is directly on the column and the index is usable. The alternative —if this query were very frequent— is an expression index:

...but the range rewrite is preferable: it works for any interval, not only for whole years.

(b) Type conversion and an expression on the column's side.

code::text forces a conversion and 'EJ-3081' || '' forces a concatenation to be evaluated. The first is the serious one: converting the column takes it out of the index. Rewriting:

General rule: transformations go on the literal's side, never on the column's side. If the parameter's type does not match, convert it yourself before passing it, or declare the parameter with the right type. This is the mistake that shows up most often when an ORM sends a varchar to an integer column or the other way round.

(c) A leading wildcard.

LIKE '%Chernobyl%' has no fixed prefix. A B-tree orders by the beginning of the string: with no known beginning, there is no range to walk. LIKE 'Chernobyl%' would use it, but it changes the meaning of the query.

The correct solution is an index of another type, GIN with trigrams:

The trigram index breaks each title down into sequences of three characters and can answer substring searches at any position. For text search by whole words, the alternative is tsvector + GIN, which also handles stemming and stop words.

Solution — Part 2

The index to create is (material_id, branch_id, status). Reasoning:

Here both options are good for Q1 and only the second is good for Q2. So why choose the first?

Because the frequencies are 900 against 20 per hour, and because Q1 is the interactive query. Q1 is run by a person waiting at the front desk; Q2 is a report that can take 200 ms without anybody complaining. With (material_id, branch_id, status), Q1 goes straight to the small set of copies of that material and the index is very selective from the first column: there are 40,000 materials, so material_id = 902 narrows it down to a handful of rows. With (branch_id, status, material_id), Q1 starts with branch_id, which has only 4 distinct values: the first level of the index barely discriminates, and a much larger part has to be walked.

The leftmost prefix rule, stated precisely:

A composite index (A, B, C) can be used for conditions on A, on A, B and on A, B, C. It cannot be used for conditions that only mention B, C or B, C.

And the design rule that follows: the most selective, always-present column goes first. "Always present" weighs more than "most selective": an index whose first column is missing from the query is not used at all.

Expected result

Explanation. The three cases in part 1 share a single cause: the index indexes the column, not an expression over the column. As soon as the condition transforms the column's value —with a function, with a conversion, with a concatenation—, the engine can no longer search the index, because it does not know what relationship there is between the order of the original values and that of the transformed ones.

The way to spot it in a plan is direct: if the node shows Filter: with an expression around the column name, the index is not being used for that. If it shows Index Cond: with the bare column, it is.

About part 2, three nuances worth keeping in mind:

• The answer changes if the data changes. If BiblioRed had 400 branches instead of 4, branch_id would be much more selective and the choice would get closer. Indexing decisions depend on the real cardinality, and that is measured (SELECT count(DISTINCT ...)), not assumed.
• PostgreSQL can use an index without its prefix, but badly. With enable_seqscan = off you would see an Index Scan walking the whole index comparing every entry. It is worse than the Seq Scan and that is why the planner does not choose it. "It can use it" does not mean "it helps".
• The rule does not apply the same way to GIN and BRIN indexes, which are not ordered trees. The leftmost prefix rule is a property of B-trees.

1. Filtering by a window function in the WHERE. You cannot: they are evaluated afterwards. Wrap it in a CTE or a derived table.

2. Confusing ROW_NUMBER, RANK and DENSE_RANK. For a "top 1 per group" only ROW_NUMBER works; RANK returns all the tied ones.

3. LAG over a series with gaps. It compares with the previous present row, not with the previous period. Generate the complete time axis and left-join it.

4. Forgetting the stop condition of a recursive CTE. Infinite loop. And if the graph can have cycles, use CYCLE or drag the walked path along.

5. Comparing -> with text. -> 'channel' = 'web' finds nothing. Use ->> for text and @> when you want to take advantage of the GIN index.

6. count(CASE WHEN ... THEN 1 ELSE 0 END) in a pivot. It counts the zeros too. Use FILTER or drop the ELSE 0.

7. Check-then-act with no FOR UPDATE. Between the SELECT that confirms and the UPDATE that acts there is a window. If the computation fits inside the UPDATE itself, make it atomic and you will not need a lock.

8. Ignoring an UPDATE 0. In optimistic locking it is the conflict signal; in a business transaction it is usually a silent failure. Always check it and decide explicitly whether it is acceptable.

9. Believing that an error inside a transaction can be ignored. In PostgreSQL it aborts the whole transaction and the COMMIT answers ROLLBACK. Use SAVEPOINT (or EXCEPTION blocks in plpgsql) in every batch process.

10. Locking rows in different orders. It is the cause of 90% of deadlocks. Fix an order —ascending primary key— and respect it throughout the code.

11. Raising the isolation level without implementing retries. REPEATABLE READ and SERIALIZABLE abort legitimate transactions on serialization conflicts. With no retry loop, you swap inconsistent data for errors in production.

12. Using SKIP LOCKED outside a queue. It returns incomplete results with no warning.

13. Transforming the column in the WHERE. EXTRACT, lower(), ::text, ||: any of them disables the index. Transformations go on the literal.

14. Creating indexes without measuring. Every index slows down writes. An index is justified with an EXPLAIN ANALYZE before and another one after.

A method tip for the concurrency block. When a session hangs and you do not know why, open a third one and query who is blocking whom:

pg_blocking_pids returns the list of processes blocking each one. It is the tool that saves the most time in a real production incident.

No hints and more demanding.

In a single query, return per branch: number of events held, offered seats, occupied seats, occupancy percentage, average rating weighted by the number of survey responses, and the branch's position in the rating ranking. Use jsonb for the responses and a window function for the ranking.

Write the function register_return(p_copy_id) which, in a single transaction: locates the open loan of that copy and locks it, records today's return date, puts the copy back to available status, and —if there is a delay— issues a fine of €0.20/day with a cap of €15.00, without duplicating it if the process runs twice. It must fail cleanly if the copy has no open loan. Explain what happens if two sessions call it at the same time on the same copy.

Diagnose this plan from the production BiblioRed, say which is the problematic node, what the root cause is and which two interventions you would propose, in order of priority.

Solution A

Central comes out at 3.88 because its rating is the weighted average of its two events: 101 (4.50 with 4 responses) and 104 (3.25 with 4 responses). (4.50×4 + 3.25×4) / 8 = 3.875. Had the average of the averages been taken it would come out the same by coincidence —both have 4 responses—, but as soon as the sizes differ, the average of averages is wrong. That is the point of the exercise: weight by the number of responses, do not average averages.

A note on the three CTEs: each one aggregates at its own level to avoid the row-multiplication problem of exercise 15 of 07-01. Joining registrations and event_reports in the same JOIN would make each survey response repeat once per registration.

Solution B

What happens with two simultaneous sessions on the same copy. The first runs the SELECT ... FOR UPDATE and locks the loan's row. The second waits at that same SELECT. When the first commits, the second unblocks and re-evaluates the WHERE against the updated version of the row: since return_date is no longer NULL, the row stops matching the condition, FOUND is false and the function raises "has no open loan". It is exactly the desired behavior: the second return of the same copy is an error, not a silent duplication.

The ON CONFLICT ... DO NOTHING is the second safety net, in case the process is retried after a network failure that left the transaction committed but with no response.

Solution C

Problematic node: the inner Index Scan of the Nested Loop. Look at loops=1 against loops=127: that node is executed 127 times, once per row of the outer side, and each execution takes about 53 ms → 127 × 53 ≈ 6,700 ms, which is practically the whole time of the query.

Root cause: a badly wrong estimate in the outer Seq Scan. The planner estimated rows=30 for the filter lower(title) ~~ '%chernobyl%' and found 127. With 30 iterations expected, the Nested Loop looked cheap; with 127 actual ones, it comes out four times more expensive than calculated. The bad estimate is unavoidable: PostgreSQL has no useful statistics for a LIKE with a leading wildcard over a function, and applies a default selectivity.

Two interventions, in order of priority:

1. A GIN index with trigrams on title (pg_trgm). It attacks the root cause: it turns the Seq Scan over 40,000 rows into an indexed access, removes the 39,873 discarded rows and, incidentally, radically improves the estimate, because the index lets the planner bound the number of rows better. With that, the Nested Loop goes on to iterate over a small and correctly estimated set.
2. Raising the statistics target of materials.title (ALTER TABLE materials ALTER COLUMN title SET STATISTICS 500; ANALYZE materials;). It is a complementary patch: it does not fix the Seq Scan, but it improves the estimate and may lead the planner to choose a Hash Join instead of the Nested Loop, which for 127 × 3 rows would be more stable.

What you must not do is touch the inner Index Scan: idx_copies_material works correctly —3 rows per iteration, exactly what it should— and its only problem is that it is called 127 times. In a slow Nested Loop, the culprit is almost never the inner node: it is the number of iterations the outer one imposes on it.

You have closed the module with the thirteen most demanding exercises of the course, and with them you have practiced the complete repertoire of a database professional in production: window functions for the top N per group, rankings with ties and period-against-period comparisons; recursive CTEs to generate time axes with no gaps and to walk hierarchies; jsonb with its navigation, containment and unnesting operators, and the manual pivot with FILTER. Then, real transactions: the loan one with its FOR UPDATE, its check of affected rows and its error handling; SAVEPOINT so that a batch does not die because of one element; and the exact reasoning about what survives a sequence of savepoints. The five two-session exercises have made you see with your own eyes a lost update, the difference between READ COMMITTED and REPEATABLE READ, a deadlock detected by the engine, optimistic locking saving the last seat and a queue consumed in parallel with SKIP LOCKED. And the last two have taught you to read a plan: Rows Removed by Filter, Buffers, loops, the distance between estimated and actual rows, and why an index that exists may be of no use at all.

If there is one idea that sums up the whole module, it is this: in production, the failures that matter do not raise errors. A COUNT(*) that counts one where it should count zero, a report that multiplies the offered seats by the number of registrations, a surcharge that disappears because two clerks applied it at the same time, a batch that commits after having aborted, an index that exists and that the query does not use. None of them shows up in an error log. All of them are detected the same way: by checking the number of rows, by reading the plan, by running the scenario in two sessions and by distrusting the results that come out right first time.

With this lesson module 7 ends, and so does the part of the course in which you wrote isolated queries. What comes next is the whole system. Module 8, Case Studies, goes through three complete projects from beginning to end: in 08-01 a relational system with the full cycle —requirements, model, schema, queries, indexes and operation—; in 08-02 a non-relational case where the same problem is modeled in documents and you check what is gained and what is lost; and in 08-03 polyglot persistence, where a single application combines a relational engine for the transactions, a document store for the catalog, a key-value one for the session and a search one for free text, and you have to decide which datum lives where and how they are kept consistent with each other. After that, module 9 gathers the books, courses and tools with which to carry on by yourself. You can close the second terminal now: in module 8 we go back to looking at the complete blueprint.