chadtmiller.com/knotless

Switch repository

Loading…

What changes

A profile's badges read an account's pulls by index. The query behind them (pulls_where, for the pulls an account opened, commented on, or sent from forks) joined each pull to the records of its repository that were it or about it. That OR read every record of the repository once for each pull. It is now two statements in a UNION ALL: the pulls, then what is about each by forge_subject, in that order (CROSS JOIN).

What is taken down is a table, not a view. down, which nearly every read filters on (uri NOT IN down), was a temporary view over takedown, inactive and label. SQLite built the whole list of it for each NOT IN down of each statement. inactive holds every account the network said is deactivated, taken down or throttled, 17,248 on prod and growing with all of atproto, none of which wrote anything here. down is now a table with the subject as its key, so each NOT IN down is a lookup in its index (USING INDEX … FOR IN-OPERATOR):

• Triggers on takedown, inactive and label keep it. A subject leaves only when none of the three keeps it down. label gains an index on uri for that.
• It is filled afresh from the three as the index opens, which also covers a rebuild that dropped label.
• A label that lapses is put back by the sweep every ten minutes (Index::lapse), which also clears what pages kept of it. Before, a lapsed label was left out of reads at once, but pages kept what they had drawn until something else cleared them.

Why

The profile page took about 2 s to answer, and every tab of it pays that, since each loads getProfile. On prod, getProfile for chadtmiller.com took 1,425 ms. The new sql log put 1,014 ms of it in the one statement. An account with no pulls answered in about 40 ms.

On a copy of prod's index (1,992 forge records, 462 of them this account's pulls):

• The pulls an account opened: 840 ms → 4 ms, the same 1,834 rows.
• Sent from forks: 73 ms → 2 ms.
• badges_of as a whole: about 1.3 s. With the query fixed, a release build on that copy took 330 ms. 200 ms of that was 28 lookups of a batch's states, each one index search plus building down for its NOT IN down.
• A repository's records (forge, behind getForge and others, logged at ~105 ms on prod): 28 ms → 12 ms warm.

Making inactive hold only accounts known here would also shrink the list. It would hide a taken-down account that a later backfill finds, though, and it still grows; a key lookup stays the same at any size.

How it was checked

• The label test gains a hide label replaced by one that lapsed: it hides until lapse puts it back.
• The existing tests cover takedowns, inactive accounts, labels, a rebuild, and forgetting an account through the triggers. The plan check covers every statement the tests run.
• Timings are from prod's logs, and from a copy of prod's index with the sqlite3 shell and a release build of badges_of; that test was not kept.
• Left to CI: the whole test run and the coverage gate.

Shortcomings

• The activity tab's feed still takes about 450 ms on the server, and the global feed about as long, in many small statements rather than one slow one. Each now skips building down, so it should drop some, but finding the rest is its own pull.
• The plan check did not catch the OR join. It was a search by repository for each pull, not a scan, and only a scan or a sort fails it.

Changeset: none

🤖 Generated with Claude Code

This pull does two things. pulls_where now reads the pulls first and then what is about each by forge_subject, in a UNION ALL, so it no longer joins on an OR. down becomes a keyed table that triggers on takedown, inactive and label keep, refilled when the index opens. A new Index::lapse, run by the ten-minute sweep, puts back labels that have lapsed.

I read the diff and found nothing that must change.

• Triggers: the insert and delete triggers keep down correct. INSERT OR REPLACE on takedown and label fires only the insert trigger. That is harmless, because the row stays down, and lapse covers a replacement that has lapsed. The new test exercises that case.
• pulls_where: the rewrite returns the same rows. Ordering within a repository doesn't matter to the grouping, which only needs the rows ordered by r.uri.
• Checks: check passes. test was still running when I looked, so the coverage gate and the plan check are not yet confirmed.

Two small things, neither blocking:

• A lapsed label now keeps its subject out of reads for up to one sweep interval. docs/design/serve.md:492 still says labels hold "until they expire". A sentence there would keep the docs true.
• lapse drops its error with let _ = in sweep. A failure there would be silent, so a log line would help.