pool/records: the month record can be up to 5 minutes stale on the public page

[defenwycke]

What

1e7235b6c memoised GET /api/v1/pool/records per window to stop a 7–30 s query running on every request. The TTLs (records_ttl, routes.rs):

window	TTL
month	300 s
week	120 s
day	60 s
block / unrecognised	15 s

So a newly-found record can take up to 5 minutes to appear on the public page in the month window, and up to 2 minutes in the week window.

Why it was done this way

The underlying query cannot be made cheap by rewriting. Ranking by rarity means reverse_hex(share_hash) over every row in the window — a function of the column, so no index can serve the ORDER BY. Pre-aggregating each arm of shares_all separately only moved it 39.5 s → 24.6 s. Measured on ghost-vm6 (4 GB RAM, 4.5 GB database):

records?window=month, cold   19.7 s   (up to 40 s under load)
records?window=day            0.059 s
records?window=block          0.006 s

Without the memo, ghost-web asked all eight nodes for this every 60 s, which kept the fleet close to permanently busy.

⚠ This cost predates the share-shard cutover: shares_archive alone (the exact pre-v56 shape) was 30,455 ms vs 40,780 ms through shares_all. v56 made it ~34% worse, it did not create it.

Why the staleness is tolerable, but should not be permanent

A record can only ever improve within its window (a rarer share appears) or age out, so a stale answer is a conservative one — never a wrong kind of value. The client already tolerates exactly this: pool.html caches records itself and enforces window monotonicity, because vm6 had been returning 504 on month at nginx's proxy_read_timeout 10s for some time before any of this.

But "the public page can be five minutes behind on a headline number" is a workaround, not a design.

The actual fix

Make the query cheap, then drop the TTL to something small:

1. A maintained records table. A background task computes best-per-window on a schedule and writes a tiny table the endpoint reads instantly. No schema surgery on shares_archive.
2. A stored display-order hash column with an index. Lets the ORDER BY use an index and terminate early. Structurally the better answer, but it means backfilling ~2M rows on nodes with 3.8 GB RAM — the same reason migration v56 deliberately avoided rebuilding indexes at startup.

Residual behaviour to be aware of until then

A cold memo still costs 3–10 s, and on the slowest node that can exceed nginx's 10 s proxy timeout and return 504 — roughly once per 5 minutes. The other nodes answer and the client's cache covers it, so the page stays populated, but it is visible in the access log.

[defenwycke]

Investigated the ordering cost today. Two shortcuts that look available are both dead, and there is a load problem with fix 1 as written. Recording all three so nobody re-derives them.

The difficulty column cannot serve the ORDER BY

get_best_share_since already selects s.difficulty, so the obvious thought is ORDER BY difficulty DESC LIMIT 1 — index-servable, no new column, no backfill. It is wrong, and not marginally:

• The column stores the committed tier's target, not the achieved difficulty. tier_target_difficulty is explicit: "This is what a share bound to tier_log2 is CREDITED — never the difficulty it happened to achieve." share_checks.rs asserts the same thing — a share achieving ~2536 under tier 11 is stored as 2048.0, and stating 2500.0 is refused as a post-hoc claim.
• It is quantised to powers of two (floor(log2)), so it could at best bucket, never rank.
• Decisively: "the tier is fixed inside the hashed coinbase before the hash is known." The value is chosen before the hash exists, so it carries no information about that hash's rarity.

Nor does it bound the search

The weaker version — take MAX(difficulty) and sort by reverse_hex only within that bucket — fails for the same reason. A hobby miner committed to tier 11 can submit a freak hash worth tier 20; it is stored as 2048.0. The rarest share in a window can sit in any tier bucket, so no filter on difficulty is safe. The issue's statement that no index can serve this ORDER BY is correct as written.

Fix 1 as described does not buy the freshness

A maintained records table computed on a schedule moves the cost off the request path, but the memo already does that. The background task still runs the same 20-40 s query, and to get materially fresher answers it has to run more often than the TTL it replaces — on month, more often than every 300 s. On a node where the database does not fit in RAM (#690) that is strictly more load than today, not less. Run it at the current cadence instead and the staleness is unchanged. Either way there is no win.

So fix 1 only helps if paired with something that makes the computation cheap, which is fix 2.

The option not listed: maintain it incrementally

A record can only change when a new share arrives — a rarer one appears, or the window slides and the incumbent ages out. That is an O(1) comparison per share against the current best, not a scan of the window. No periodic query, no 2M-row backfill, and staleness drops to approximately zero.

The cost is that ageing-out needs handling (when the incumbent falls out of the window, the replacement does require a scan — but only then, which for month is rare), and it puts a comparison plus an occasional tiny UPDATE on the share ingest hot path. That last part is why I have not just built it: the hot path is the wrong place to make an unreviewed unilateral change, and it overlaps the concern in #537.

Flagging for a decision rather than picking one. My recommendation is incremental maintenance over fix 2 — it avoids the backfill that fix 2 needs on 3.8 GB nodes, which is the same reason v56 deliberately avoided rebuilding indexes at startup.