678 Postgres round-trips: the board was not hung

In July 2026 the job board looked frozen. The server was up. The overlay stayed on “Loading board…” because GET /api/board was still working — for tens of seconds. Germany was worst, around 90 seconds. It was easy to blame remote Postgres, or the job-description payloads we had just started storing. Neither was the bug.

One Germany load made 678 Postgres round-trips for 98 companies. The jobs SQL was 0.67s. Location-label helpers ate ~44s.

What we saw

Timings on page 1, default newest sort, panel talking to EC2 Postgres:

A first “fix” made Germany ~103s. The preview pass duplicated flatten work. Optimizations can regress.

What it was not

• “Remote Postgres is slow.” WAN latency (~60ms) multiplied the bug. It was not the bug. Local Postgres would have hidden it longer.
• “Descriptions are huge now.” We had just stored job text for MCP. Germany’s description column was ~3.8 MB. Worth omitting from list reads. Catalog SQL without descriptions was still 0.67s.
• “Newest sort loads everything.” True architectural cost. After the cache it was about 1–2s, not 90s.

A profiler split it cleanly:

SQL total                         0.67s
post-process (location sync × 98) 43.97s

Postgres was fine. Python was not.

An innocent helper

The board is not a table read. Each request merges catalog companies with per-user tracking, then normalizes locations. Every company goes through sync_company_location_fields(). That calls country_label(), which called all_country_labels(), which ran this on every call:

def all_country_labels() -> dict[str, str]:
    merged = dict(load_custom_countries())  # cached
    for key in list_catalog_country_keys():  # DB query EVERY call
        merged.setdefault(key, key.replace("-", " ").title())
    return merged

list_catalog_country_keys() is SELECT DISTINCT country from companies and country meta. Custom-country labels were cached. The merge with catalog keys was not. Inside a per-company, per-city loop that is hundreds of identical queries.

Counter on one Germany load: 678 calls of all_country_labels for 98 companies.

What we shipped

Cache the merged label dict in process memory. Invalidate on country writes and catalog writes. Stop re-sorting suffix labels per city. Board pagination omits description_text. Admin stats load on a separate request. MCP summaries select flags, not PDF bytes.

Ten days later a “cache invalidation” feature put Redis on that same helper and took the board down again. That write-up is cache check in the hot path.

The rule

Profile SQL versus post-process before blaming the database. Never put remote I/O inside a helper that runs per row of a list. country_label() looks free. In a loop over the board it is not.