Adding the cache, and proving it helped

The database is correct and the queries work. This final lesson adds the second tier — Redis — for the three jobs the brief gave that PostgreSQL should not do: holding seats during checkout, caching availability, and rate-limiting bookings. Every Redis command here was run against Redis 7; this is the polyglot design of module 13 made concrete, and the course's last piece.

The seat hold — the reason this system is polyglot

The brief: while a customer picks seats and pays, hold them a few minutes; if they abandon checkout, the seats free up automatically. This is not a booking — it is transient, per-seat, and self-expiring, which is a Redis key with a TTL, not a PostgreSQL row.

The critical part is claiming a hold only if the seat is not already held — atomically, so two customers cannot both hold seat A5. That is exactly what SET ... NX (set if Not eXists) does:

SET hold:show:1:seat:A5 "customer:2" NX EX 120     → OK        (held, expires in 120s)
TTL hold:show:1:seat:A5                              → 120

# a SECOND customer tries to hold the same seat:
SET hold:show:1:seat:A5 "customer:3" NX EX 120     → (nil)     ← refused, already held

The nil is the whole point: NX makes "check and claim" one atomic operation, so the hold is race-free in exactly the way "check then book" in application code is not. EX 120 means the hold expires by itself after two minutes — the "frees up automatically" requirement, for free, with no cleanup job. If the customer completes the booking, you DEL the hold; if they wander off, it vanishes on its own.

Why this is not in PostgreSQL: a hold is worthless data the moment it expires, there are many of them, and they must self-clean. A PostgreSQL table of holds would need a cron job deleting stale rows and would clutter the booking logic. Redis holds are the right tool — and note they are disposable: if Redis restarts and holds are lost, customers simply re-pick. No booking is affected, because bookings live in PostgreSQL. This is the source-of-truth boundary in action.

The hold and the booking work together: hold in Redis (fast, transient), then the real UNIQUE (show_id, seat_id) constraint in PostgreSQL is the final authority when the booking commits. The hold prevents most collisions cheaply; the constraint guarantees correctness even in the rare case where a hold expired at just the wrong moment. Two layers, each doing its job.

Caching availability — cache-aside with invalidation

The availability query runs on every seat-page load for a popular show. Cache it (module 11's cache-aside), with a short TTL as the staleness bound:

# on a cache miss, compute from PostgreSQL, then store:
SET avail:show:1 "98" EX 30      → OK
GET avail:show:1                  → "98"     (subsequent reads: instant, no database hit)
TTL avail:show:1                  → 30

The application logic is cache-aside from module 11:

def seats_available(show_id):
    key = f"avail:show:{show_id}"
    cached = redis.get(key)
    if cached is not None:
        return int(cached)                    # HIT — no database touched
    n = db.query("SELECT total - booked ...")  # MISS — the query from the last lesson
    redis.set(key, n, ex=30)
    return n

But availability changes when someone books — so a stale cache would show seats that are gone. The fix is delete-on-write (module 11): when a booking commits, invalidate the show's cache:

DEL avail:show:1     → 1
GET avail:show:1     → (nil)      ← miss; the next read recomputes from PostgreSQL

Delete, not update — module 11's rule: the next read rebuilds the correct value from the source of truth, avoiding the concurrent-write race that updating-in-place risks. The 30-second TTL is a safety net: even if an invalidation were somehow missed, the cache self-corrects within half a minute. Bounded staleness, misses-not-corruption — the achievable goal from the caching lesson.

Rate-limiting bookings

To stop a single client hammering the booking endpoint (or a bot hoarding seats), limit attempts per customer — the atomic Lua counter from module 11, because INCR-then-EXPIRE as two commands has the crash-in-between bug:

local c = redis.call('INCR', KEYS[1])
if c == 1 then redis.call('EXPIRE', KEYS[1], ARGV[1]) end
return c

EVAL <script> 1 rl:book:customer:2 60   → 1     (first attempt this minute)
EVAL <script> 1 rl:book:customer:2 60   → 2
...                                       → 6

Reject when the counter exceeds the limit (say 5/minute). One atomic operation per attempt, the count and its expiry set together, shared across every application instance — none of which PostgreSQL would do well, and all of which is ephemeral (a lost counter just resets a window).

The complete architecture, running

Put it together and the two-tier system is exactly what module 13 described:

              ┌─────────────── PostgreSQL (source of truth) ───────────────┐
  book seat → │ UNIQUE(show_id,seat_id) guarantees no double-booking (ACID) │
              │ movies, shows, seats, bookings, revenue, search             │
              └────────────────────────────────────────────────────────────┘
                      ▲ invalidate on booking      ▲ final authority
              ┌───────┴──────────── Redis (fast, disposable) ──────┐
  pick seat → │ hold:show:seat  (NX, TTL 120s)  — race-free holds   │
  view page → │ avail:show      (TTL 30s)       — availability cache │
  rate limit→ │ rl:book:customer (Lua INCR+EX)  — attempt limiting   │
              └──────────────────────────────────────────────────────┘

Apply the source-of-truth test one last time: if Redis vanished right now, in-progress holds are lost (customers re-pick), the availability cache is cold (rebuilds from PostgreSQL on next read), and rate-limit windows reset. The application is slower and briefly less smooth — but not wrong. Every booking, every payment, every seat assignment is safe in PostgreSQL. That is the definition of a healthy polyglot system, and building one you can say that about is the whole point of this course.

What you have built, and what you have learned

A real data layer: a relational schema that models the domain and guarantees correctness under concurrency; the queries an application needs, written to avoid the N+1, fan-out and empty-result traps; and a Redis tier for the transient, fast, disposable work — with a clear source of truth and disposable derived stores. You chose each database for what it is good at, drew the boundary deliberately, and can defend every decision from a specific lesson.

That is the skill this course set out to build: not "how to use MongoDB" or "how to write SQL", but how to reason about data — to look at a problem, know which tool fits, use it correctly, and justify the choice. You now have it. Go and build something, start it on PostgreSQL, and add the next database only when a real requirement — measured, not imagined — asks for it.

Check your work

Why a seat hold is Redis, not PostgreSQL. It is transient, per-seat, self-expiring, and disposable — a TTL key, not a row needing a cleanup job.

What SET ... NX guarantees for a hold. Atomic check-and-claim, so two customers cannot both hold the same seat — race-free where application "check then hold" is not.

What EX 120 provides. The hold expires by itself — "frees up automatically" with no cleanup job.

How the hold and the UNIQUE constraint work together. The hold prevents most collisions cheaply; the PostgreSQL constraint is the final authority guaranteeing correctness.

The caching pattern for availability. Cache-aside with a short TTL, invalidated by delete-on-write when a booking commits.

Why delete, not update, the availability cache. The next read rebuilds from the source of truth, avoiding the concurrent-write race; the TTL is a safety net.

Why the rate limiter uses a Lua script. INCR then EXPIRE as two commands can leave the key without an expiry on a crash; the script makes them atomic.

The source-of-truth test on the finished system. If Redis vanished — holds lost (re-pick), cache cold (rebuild), limits reset — the app is slower, not wrong; all durable data is in PostgreSQL.

The one-sentence skill of the course. Look at a problem, know which database fits, use it correctly, and justify the choice.

Practice

1. Implement the seat hold with SET ... NX EX. From a second shell, try to hold the same seat and confirm the nil.
2. Let a hold expire (use a short TTL) and confirm the seat becomes holdable again with no cleanup job.
3. Implement cache-aside for availability. Log hit/miss and watch the first read miss and the rest hit.
4. Book a seat, invalidate the cache with DEL, and confirm the next read recomputes.
5. Try updating the cached count on booking instead of deleting it, and construct the concurrent scenario where it goes wrong.
6. Build the rate limiter with the Lua script and confirm attempts beyond the limit are rejected.
7. Reproduce the INCR-then-EXPIRE bug (two commands, kill between) and confirm the Lua version fixes it.
8. Write out what happens to the whole system if Redis is flushed mid-operation. Confirm no booking is lost.
9. Draw the final architecture from memory, labelling which store owns what and how they interact.
10. Extend the system: add "premium seats" priced differently, and decide what changes in PostgreSQL and what (if anything) in Redis.

Official documentation

• Redis — SET with NX and EX — The atomic race-free hold.
• Redis — EXPIRE — Self-expiring holds and cache TTLs.
• Redis — EVAL (Lua) — The atomic rate limiter.
• Redis — Caching — Cache-aside and invalidation.
• PostgreSQL — Constraints — The UNIQUE constraint that is the final authority.
• Martin Fowler — Polyglot persistence — The two-tier architecture you built.

This is the end of the Database Foundation course. You can design a schema, explain why a query is slow, use a cache without creating a correctness bug, and justify the database you chose. Build something.