The Professor
Index Course index

Module 8 · Performance and caching

Lesson 39 — Pools, pagination and compression

Connections, listings and lighter responses without cutting content.

Published
In this lesson
  1. Objectives
  2. 1. The connection pool: the arithmetic that avoids collapse
  3. 2. The Redis pool and the two collapses
  4. 3. Pagination: offset vs cursor
  5. 4. The pagination contract (13) and the limits
  6. 5. Compression: the measured trade-off
  7. Self-assessment

Stack: Django/DRF · Project: TicketFlow Status: Published — closing the performance module Prerequisite: Lesson 38 — Caching strategies

Objectives

  1. Size the connection pool (DB and Redis) with the arithmetic of gunicorn workers × threads and CONN_MAX_AGE.
  2. Paginate listings with a cursor (keyset) instead of offset, and negotiate the page size without breaking the contract (13).
  3. Compress responses (gzip/brotli) measuring the CPU vs bandwidth trade-off that 36 already measured.

1. The connection pool: the arithmetic that avoids collapse

Postgres has a hard connection limit (max_connections, default 100) and each connection costs ~2-10 MB of RAM. The disaster's arithmetic: 4 gunicorn workers × 8 threads × 3 replicas = 96 potential connections — plus the Celery worker (same arithmetic), plus beat, plus management commands: you exceed the limit and the too many clients error is the symptom (47). TicketFlow's design: CONN_MAX_AGE + pool per process:

python
# settings.py
DATABASES = {"default": {
    ...env.db("DATABASE_URL"),
    "CONN_MAX_AGE": 60,               # reuse the connection for 60 s (avoids the per-request handshake)
    "OPTIONS": {"pool": {"min_size": 2, "max_size": 10}},   # psycopg 3: a real pool per process
}}

With psycopg 3 the pool lives inside each process (each gunicorn worker: its pool of 2-10 connections). The final budget: Σ (processes × max_size) < max_connections × 0.8 — the remaining 20% is for emergency psql and migrations. The managed alternative: PgBouncer in transaction pooling mode (43) when the process count scales, with ITS rules (prepared statements disabled, no advisory locks in transaction mode — 31 uses advisory: a documented decision).

2. The Redis pool and the two collapses

Redis is single-threaded: the collapse is not about connections but slow commands. The project's rules (already applied in 12/38): NO KEYS (use SCAN or namespaces), large values (>100 KB) get compressed or split, and the client pool (CONNECTION_POOL_KWARGS = {"max_connections": 50} per process) with the same arithmetic as above. The second collapse: delete_pattern without a namespace (38): a SCAN over 1M keys blocks Redis's event loop — always with a bounded prefix (profile:{user_id}:), never a naked *. Monitoring: redis-cli --latency and slowlog get (46) — a slowlog with entries > 10 ms is the finding before the disaster.

3. Pagination: offset vs cursor

Offset (?page=3) is the comfortable default and the classic problem: LIMIT 20 OFFSET 6000 makes Postgres READ AND DISCARD 6000 rows (09), the p95 worsens linearly with depth, and with concurrent inserts page 3 repeats or skips rows (the moving window). The cursor (keyset) paginates by a stable key:

python
# GET /api/v1/events?limit=20&cursor=01H...  (the ordered uuid, 13)
def listar_publicados(cursor: str | None, limit: int = 20):
    qs = Event.objects.publicados().order_by("uuid")
    if cursor:
        qs = qs.filter(uuid__gt=decode_cursor(cursor))     # WHERE uuid > $cursor → pure index
    page = list(qs[: limit + 1])                            # ask for N+1 to know if there is a next
    has_more = len(page) > limit
    items = page[:limit]
    next_cursor = encode_cursor(items[-1].uuid) if has_more else None
    return {"items": items, "next_cursor": next_cursor}      # no "total": don't ask for it

The (uuid) index makes the cursor O(log n) at any depth; the order does NOT change with inserts (the cursor is "I've seen up to here", not "page 3"). The total (COUNT) gets removed from big listings: a COUNT over 2M rows for every page is pagination's N+1 — "next_cursor" is enough for the front's infinite scroll; if the business demands a total, it gets counted cached (38) or approximated.

4. The pagination contract (13) and the limits

13's public shape: {"items": [...], "next_cursor": "...|null"} with negotiated limits: limit default 20, maximum 100 (min(limit, 100) — the client asking ?limit=100000 is a bandwidth attack, 23 limits it the same way). Compatibility (14): the old listings with ?page= stay, with DRF's CursorPagination configured as default and PageNumberPagination marked deprecated with a header (14) until the front migrates:

python
REST_FRAMEWORK = {
    "DEFAULT_PAGINATION_CLASS": "core.pagination.CursorPagination",
    "PAGE_SIZE": 20,
}

The contract test (35): page 1 and 2 share no items, page 2's next_cursor is null at the end, and the items keep the uuid order — three asserts the front programs against without reading your code.

5. Compression: the measured trade-off

gzip/brotli shrinks the JSON 5-10× at a CPU cost. The decision is NOT doctrinal: 36/37 measured it — the 400-event listing uncompressed: 240 KB, 38 ms of app; with gzip level 4: 28 KB, +6 ms of CPU. In the global p95: the network (from the datacenter to the user's phone) spends 180 ms on 240 KB vs 25 ms on 28 KB: compression wins for EVERYTHING leaving for the Internet; only massive intra-VPC traffic (31's job reading 2M rows from app to PG) pays CPU without gaining bandwidth. Implementation: at the gunicorn/nginx level (43) — never in Django middleware if there is a reverse proxy:

nginx
# nginx.conf (43): the layer you already have
gzip on;
gzip_types application/json;
gzip_comp_level 4;            # 4-6: the trade-off zone; 9 is vanity CPU
gzip_min_length 1024;         # compressing 200 bytes wins nothing

The final trade-off: Vary: Accept-Encoding (38's/CDN cache must differ by encoding or it serves compressed to whoever can't read it), and payloads already large by design (23's GDPR export) leave compressed by the job, not by the request.

Self-assessment

  1. Do your deployment's full connection arithmetic (workers × threads × replicas + Celery workers), and why is the 20% margin not negotiable?
  2. CONN_MAX_AGE vs psycopg 3's pool vs PgBouncer: which problem does each solve, and which one breaks advisory locks?
  3. Why does offset degrade linearly and the cursor not? Which moving window does offset break with concurrent inserts?
  4. Why is total removed from big listings, and what is offered to the front instead?
  5. When does compression WIN and when does it pay CPU for nothing? What role does Vary: Accept-Encoding play with the cache?

Continue with the exercises. The solutions only after trying it yourself.

^ top