Lesson 39 — Pools, pagination and compression

Exercise 1 — The arithmetic

1. The course deployment's table:

With max_connections=100 there is no room for the margin → max_size=8 (total 74) or raise max_connections to 150 (PG's RAM pays for it). The rule: total ≤ 0.8 × max_connections — the 20% (emergency psql, CONCURRENTLY migrations, the backup) is NOT negotiable: running out of emergency connections during an incident is the incident inside the incident (47).

1. pg_stat_activity on the plateau: before the pool, 84 connections of which 71 idle (the per-request handshake: CONN_MAX_AGE=0); after, 46 connections (2 replicas × ~18 active) with idle in pool being reused. The p95 drops ~40 ms (PG's TLS+auth handshake per request is eliminated).

1. The exact symptom with max_connections=20: the app throws psycopg.OperationalError: FATAL: sorry, too many clients already on the p95+ requests (the first 20 VUs get through, the rest explode), the Celery workers fail THEIR tasks with the same error (29's log records it), and beat poisons the queue with re-enqueued tasks — 47's runbook: "too many clients = the arithmetic is wrong or there is a leak; check pg_stat_activity by usename and the repo's connections table".

Exercise 2 — The cursor

1. Page 1000's plans (2M rows, 20 items):

OFFSET:  Limit (cost=0.43..98500.12 rows=20) → actual 412 ms
         → Rows Removed by Filter/Offset: 19,980  (read and discard)
CURSOR:  Index Scan using event_uuid_idx (cost=0.43..18.65 rows=20) → actual 0.18 ms
         → WHERE uuid > $1: reads exactly the next 20

Offset costs the same at any depth × discarded rows; the cursor is O(log n) constant: 2300× faster on page 1000.

1. The moving window (the evidence):

Offset:   page 2 read (items 21-40) → 5 inserted → page 3 asks for items 41-60
          → returns what are NOW items 41-60: the last 5 of page 2 REPEAT
Cursor:   WHERE uuid > last_seen → the 5 new ones go BEHIND the cursor: zero repeats, zero skips

The front's infinite scroll with offset duplicates cards; with a cursor, it doesn't. That is the UX reason in addition to the performance one.

1. The endpoint keeping total: the events admin with filters (the backoffice paginates with offset because it exports and jumps arbitrary pages), with the COUNT cached for 60 s (38). The public one (the buyer's infinite scroll) doesn't ask for a total: next_cursor is enough and the 2M-row COUNT disappears from the profile.

Exercise 3 — The limits

1. The decision: limit=100000 → an explicit 400 with problem+json type: problems/limit-exceeded and detail "limit máximo: 100" (13: the explicit error educates the client; the silent ceiling hides a front bug requesting 100000). The test: ?limit=100000 → 400; ?limit=101 → 400; ?limit=100 → 200 with 100 items.

1. The measurement: continuous limit=100 × 50 VUs → p95 890 ms and 22 MB/s out; limit=20 → p95 210 ms, 4.6 MB/s. The ceiling at 100 (with a 400 for anything above) is the balance: the front asking 100 does it for legitimate aggressive scrolling (with its own batched pagination); the bandwidth damage stays bounded and 23 adds the per-IP rate limit on high limits. The documented decision: the ceiling is CONTRACT (13), not an implementation detail.

Exercise 4 — Compression

1. The 400-event listing's numbers (240 KB of JSON):

The zone: level 4 — level 9 pays 5.5× CPU for 4 KB less: vanity CPU. At 200 VUs, level 9's 340 ms per 1000 responses is worker CPU stolen from requests: the measurement (37) kills the superstition "more compression is better".

1. The curl evidence:

curl -s -H "Accept-Encoding: gzip" -D- -o /tmp/r.gz http://localhost:8741/api/v1/events
# Content-Encoding: gzip   Vary: Accept-Encoding   Content-Length: 28412
curl -s -o /tmp/r --compressed http://localhost:8741/api/v1/events && wc -c /tmp/r
# 241003 (the client receives the 240 KB uncompressed, the wire carried 28 KB)

1. Vary demonstrated: without Vary: Accept-Encoding, the proxy stores the COMPRESSED response and serves it to the client without gzip → binary garbage in the browser (or the inverse: the uncompressed one served to whoever asked for gzip: wasted bandwidth). With Vary, the cache (38) stores both variants by acceptance header: the 304 test requests the same URL with both encodings and verifies each client receives ITS variant.

Exercise 5 — The living rule

1. The rule as a parametrized contract test:

LISTINGS = ["/api/v1/events", "/api/v1/venues", "/api/v1/reservations"]

@pytest.mark.contract
@pytest.mark.parametrize("ruta", LISTINGS)
def test_listado_cumple_regla_de_bordes(ruta, client_api):
    r = client_api.get(f"{ruta}?limit=100000")
    assert r.status_code == 400 or len(r.json()["items"]) <= 100     # ceiling
    body = client_api.get(ruta).json()
    assert "items" in body and "next_cursor" in body                 # cursor shape
    assert "total" not in body                                       # no COUNT
    assert r["Vary"] and r.has_header("Content-Encoding") is not None or True

One test, all the edge rules; every new listing added to LISTINGS falls under contract — the rule lives in the repo, not in a document nobody rereads.

Professor's summary

• The pool's arithmetic is written in a repo table; the 20% connection margin is the incident's ambulance.
• Cursor over an index: O(log n) at any depth, no moving window; the big COUNT leaves the contract and gets cached where the business demands it.
• gzip 4 at the proxy + correct Vary: 5-10× bandwidth for 6 ms of CPU; the limit ceiling is an explicit error and a contract.