Premier League Predictor: FastAPI & Redis — Chapter 12, Exercise 2
==================================================================

TASK
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.

SOLUTION

Setup: 20 teams, a full double round robin (380 fixtures), random results
0-4 goals per side (seed fixed). Redis: Chapter 7's structures and script.
PostgreSQL 16: teams(id) and fixtures(home_team_id, away_team_id,
home_score, away_score), league table computed by

  WITH results AS (
    SELECT home_team_id AS team_id, home_score AS gf, away_score AS ga
      FROM fixtures WHERE home_score IS NOT NULL
    UNION ALL
    SELECT away_team_id, away_score, home_score
      FROM fixtures WHERE home_score IS NOT NULL)
  SELECT t.id, COUNT(r.team_id), <won/drawn/lost sums>, <goal difference>, <points>, <goals for>
  FROM teams t LEFT JOIN results r ON r.team_id = t.id
  GROUP BY t.id ORDER BY points DESC, gd DESC, gf DESC, t.id

Comparison (played/won/drawn/lost per team, and the (points, goal
difference, goals for) sequence in table order): identical, and still
identical after 300 random corrections applied to both.

Medians of 300 runs, one Python process, local Docker containers:

  read the league table            Redis 2.30 ms   PostgreSQL 1.01 ms
  enter/correct one result         Redis (default config) 0.57-0.80 ms
                                   Redis (appendonly, fsync always) 3.12 ms
                                   PostgreSQL 1.75 ms

The fair write comparison is Redis with fsync always (3.12 ms) against
PostgreSQL (1.75 ms): PostgreSQL's commit is durable by default, so the
Redis figure must also be durable to be comparable. The default Redis
number does not flush to disk (Chapter 9).

The Redis read is two round trips (ZRANGE, then a pipeline of twenty
HGETALL) against one query for PostgreSQL. Both run in about a
millisecond or two for a table this size; these are single-machine
timings, useful for ordering and not as benchmarks.

WHY THIS WORKS AS AN ANSWER
----------------------------
It checks the results agree before timing anything, reports every number
including the ones that don't favour Redis, and picks the like-for-like
write comparison rather than the most flattering one.