Capstone: Comparing This Against the PostgreSQL Variant, and Integrating Into the Existing Astro Site

Premier League Predictor: FastAPI & Redis

Chapter 12 · Capstone: Comparing This Against the PostgreSQL Variant, and Integrating Into the Existing Astro Site

Two jobs remain. First, the question Chapter 1 asked and eleven chapters have been answering piece by piece: how does this variant actually compare with its PostgreSQL sibling? Second, the reason the whole project exists — getting the tool onto the live Astro site. As before, everything below was run: a real PostgreSQL 16 loaded with the same season for a head-to-head, and a real nginx mounting the app at a sub-path in front of the Chapter 11 deployment.

What Each Chapter Found

Ch.	What it built	The Redis-specific finding (all reproduced)
1	Setup, the shared spec	FastAPI's lifespan handler, not the deprecated startup hook, even where an official tutorial still shows the old one
2	Modeling	Key names are the only schema; `HSET` merges two records silently; nothing checks a fixture's team IDs exist
3	Admin	One `season:current_id` key replaces the relational "clear the old flag" step; but `MULTI`/`EXEC` does not roll back a failed command
4	Fixture entry	A real check-then-act race, closed with `WATCH`, whose retry must re-validate
5	Predictions	A hash field per source makes "one prediction per source" free; Redis validates nothing; a Lua script closes the lock-after-result gap
6	Results and scoring	Increment-based totals aren't idempotent; store per-fixture points and apply new-minus-old; `HINCRBYFLOAT` drifts
7	League table	A sorted set has one score, so three sort keys are packed into one; ties fall back to member string
8	Prediction leaderboard	Mixed units ranked a guest first by ~100×; Pub/Sub keeps no history; Chapter 6's rounding accumulates
9	Promotion and relegation	Keys need season scope; a second structure for one fact goes stale; defaults lose data on a hard kill
10	Selector UI	`SCAN` is not a list of seasons; two bugs in Chapter 4's route; stale-state bugs in the client
11	Deployment	The default pool fails at its limit; `allkeys-lru` silently deletes data; the defaults are open; backups are as old as the snapshot

Read down the right-hand column and one pattern repeats: a relational database enforces something, and here the application has to. Uniqueness, integrity, atomicity across two writes, keeping a derived view consistent, surviving a crash — each had a guard that was written by hand, and each was found by failing without it.

A Measured Head-to-Head

The same 380 random results (20 teams, a full double round robin) were loaded into this course's Redis structures and into a PostgreSQL 16 database with a teams table and a fixtures table. The PostgreSQL league table was computed with the standard query: a UNION ALL of home and away rows, aggregated per team. (This is an equivalent query, written for this comparison, not a copy of the sibling course's own.)

Verified: Same Answer From Both

fixtures loaded: 380
played/won/drawn/lost identical for every team:      True
(points, goal difference, goals scored) order equal: True
after 300 random corrections, still equal:           True

The maintained sorted-set table and the recomputed SQL table agree exactly — including after the corrections that Chapter 7's reverse-then-apply script exists to handle.

Then the timings, each the median of 300 runs from the same Python process over local Docker containers:

Operation	Redis (default config)	Redis (append-only, fsync always)	PostgreSQL 16
Read the league table	2.30 ms	not measured	1.01 ms
Enter or correct one result	0.57–0.80 ms	3.12 ms	1.75 ms

How to Read That Honestly

The read: PostgreSQL was more than twice as fast. Reading the Redis table takes two round trips — the ZRANGE, then a pipeline of twenty HGETALL calls for the stats — while the SQL query is one. A single query over 380 rows costs almost nothing, so there was no scale at which Redis's maintained table paid for itself here.

The write: Redis looks faster, but only with its defaults, which don't flush to disk (Chapter 9). Configured to survive a crash, as Chapter 11 recommends, it was slower than PostgreSQL, which commits durably by default. The fair comparison is the middle column against the right-hand one.

These are one machine, one connection and local Docker containers, so treat the numbers as an ordering at this scale, not as benchmarks. They do say plainly that performance was never a reason to choose Redis for this app.

So Which One?

A judgement, resting on the evidence above. For this app the PostgreSQL variant is the better fit as the system of record: it is durable by default, it enforces integrity without hand-written guards, its table is one query, and it is at least as fast at this size. What this variant contributes is what it was built to show — sorted sets are an excellent way to keep a ranked list current, atomic scripts and WATCH are real tools for check-then-act problems, and knowing where the guards have to go is a useful skill. A reasonable design that uses both keeps PostgreSQL as the source of truth and Redis for ranked views, rebuilt from it whenever needed; Chapter 7 already showed the table can be rebuilt by replaying results through the script.

Getting It Onto the Site

The live site is an Astro site. Four realistic ways to add this app:

Option	How it works	Trade-off
Subdomain	The app runs at `predictor.example.com`, separately from the site	Simplest; nothing shares a path. Needs its own DNS and certificate; visitors leave the main site's address
Path-mounted reverse proxy	nginx serves the site at `/` and forwards `/predictor/` to the app	One address; needs a server the owner controls. Built out below
API behind the existing frontend	The app becomes API-only and the Astro site renders the pages	Most integrated; a rewrite of the page, and a decision on how the site calls the API
Fuller migration	Move the site itself onto a framework that can host both	A large project in its own right

As in the Astro sibling, whether the site's actual hosting can run a reverse proxy at all is a separate question this course can't answer; if it can't, the subdomain is the fallback. The rest of this chapter builds the second option.

The nginx Configuration

server {
  listen 80;
  root /usr/share/nginx/html;                 # the existing site

  location = /predictor { return 301 /predictor/; }
  location /predictor/ {
    proxy_pass http://plapp:8000/;            # trailing slash: strips /predictor/ before forwarding
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-For $remote_addr;
  }
  location / { try_files $uri $uri/ =404; }
}

The app itself is untouched: it still believes it lives at /, because nginx removes the prefix. Redis appears nowhere in this file. It stays on the private network (the app reached it by container name in Chapter 11's test), and only nginx's port is public — a published Redis port was used in this chapter's measurements only so the test scripts could reach it.

The Absolute-Path Problem, Again

Each sibling capstone hit the same underlying issue: an app that assumes it sits at the web root breaks when mounted under a prefix. Here it shows up in exactly one place. The page's stylesheet and scripts are referenced without a leading slash, so they resolved fine under /predictor/ (all four returned 200). The fetch() calls, though, were written as /api/...:

Verified: The Page Loads and Shows Nothing

Loaded in headless Chrome at /predictor/: the heading, the styled selector bar and the empty "Fixtures" section rendered, with both menus empty and no team buttons. The browser's /api/seasons went to the existing site's root, which returned 404 (the same request under /predictor/api/seasons returned 200). The app was fine; the client was asking the wrong address. Nothing on the server side ever noticed, because the requests never arrived.

The fix is to drop the leading slash from the four fetch() URLs (fetch("api/seasons")), so they resolve against the page's own address. Same page, mounted, after the change: menus populated, twenty team buttons, the four already-used teams struck through, two fixtures listed — identical to the unmounted page. A fixture created through the proxy (POST /predictor/api/gameweeks/2/20/fixtures) returned 200.

Verified: Why the Trailing Slash Matters

A relative URL resolves against the page's address, and the address without its trailing slash is a different base:

new URL("api/seasons", "http://h/predictor")   ->  http://h/api/seasons              # wrong
new URL("api/seasons", "http://h/predictor/")  ->  http://h/predictor/api/seasons     # right

That is what the location = /predictor { return 301 /predictor/; } line is for (it returned 301 as intended). Without it, someone who types the address without the slash gets the broken page above. This is the counterpart of the FastAPI sibling's <base> tag and the Astro sibling's base-path variable — with a static page and no server-rendered links, the client's URLs are the only place a path lives.

Protecting the Entry Page

Chapter 3 noted the app has no authentication at all, and Chapter 11's Redis account protects Redis, not the routes. Anyone who can reach /predictor/ can enter fixtures. As in the Astro sibling, a pragmatic fix is HTTP Basic Auth at nginx:

location /predictor/ {
    auth_basic "Predictor";
    auth_basic_user_file /etc/nginx/conf.d/htpasswd;     # created with: openssl passwd -apr1
    proxy_pass http://plapp:8000/;
    ...
}

Verified

no credentials, page:         401        no credentials, POST:  401  (nothing written to Redis)
wrong password, page:          401        right password, page:   200      right password, POST:  200
existing site's root:          200        # unaffected

Basic Auth is credentials in every request, so it needs HTTPS in front of it (see the HTTPS/TLS Fundamentals course) — this test ran over plain HTTP on localhost, and that would not be acceptable on a real site.

What's Still Genuinely Open

The relational siblings ended by naming the unresolved items from the project notes, and they apply here with a Redis flavour:

Historical backfill. Importing past seasons is a strength of the design: the result-entry script is idempotent, so a bulk import is just replaying results through it. Replaying a full 380-result season twice left the order, scores and every stats hash unchanged (Exercise 3).
Guest count. Still undecided as a product question. Chapter 8's sixtieths scheme is exact for up to six guests in a week, and not beyond.
Fixture rescheduling. Not built here. Moving a fixture between gameweeks would touch several structures at once — its own hash, two gameweeks' fixture sets, both used-teams sets, and the season's gameweek list — so it is exactly the kind of multi-structure update this course has warned about, and would need its own script.
Single user. The app has no user concept; the Basic Auth above is a gate, not accounts. Multiple users would mean predictions keyed by user, which the current one-field-per-source hash does not model.
Championship data. Promoted clubs arrive as manual input, as in every variant.

Hands-On Exercises

Exercise 1

Mount the app at /tools/predictor/ instead, and separately try a location whose proxy_pass has no trailing slash. Report the status codes for the page, a script file and an API call in each case, and say what changed in the app (nothing) versus in nginx.

📄 View solution

Exercise 2

Load the same 380 results into Redis and PostgreSQL and compare the league tables, then time reading the table 300 times in each and entering a result 300 times in each, including Redis with the append-only file and fsync always. Report the medians and say which pair of numbers is the fair comparison for writes.

📄 View solution

Exercise 3

Import a full 380-result season through the Chapter 7 script, snapshot the sorted set and every team's stats hash, import the same 380 results again, and snapshot again. Report whether the two snapshots are identical and what that means for a backfill that gets interrupted and re-run.

📄 View solution

Chapter 12 Quick Reference

The pattern across the course — a relational database enforces it; here the application must, and each guard was found by failing without it
Verified: identical tables — 380 results in Redis and PostgreSQL agree on every count and the full order, before and after 300 corrections
Measured: no performance case for Redis here — read 2.30 ms vs 1.01 ms; a durable write is 3.12 ms vs 1.75 ms (the default Redis write, 0.57–0.80 ms, doesn't flush to disk)
Path-mounting — proxy_pass with a trailing slash strips the prefix; the app is unchanged; Redis stays private
Verified: absolute fetch("/api/...") breaks under a prefix — the page renders with empty menus; relative URLs fix it, and need the trailing-slash redirect
Verified: nginx Basic Auth gates the page and the API — needs HTTPS in real use
Open — rescheduling, multiple users, guest count; backfill is safe to re-run

Course Complete

This closes Premier League Predictor: FastAPI & Redis, the last of the four Premier League Predictor courses, joining its PostgreSQL, Django and Astro siblings. Its honest conclusion is not that Redis is wrong for this app, but that it is the right tool for the ranked views and the wrong place, by default, for the only copy of the data.