Post #4130122

Okay, this is above my pay grade (I'm an SQL noob), and I hope it's not annoying slop, let me know if you want me to run any further tests: Numbers, from a copy of a production instance: 59,720 conversations, 256,613 posts, post is 562 MB against 128 MB shared_buffers, so it doesn't fit in cache. Postgres 18.3. Public-catalog variant, 40 rows, warm, EXPLAIN (ANALYZE, BUFFERS). Ordering by a lateral max, no stored column: page 0 1621 ms 356,442 buffers offset 5000 1784 ms 349,889 buffers Stored conversation.last_activity_id with a btree index: page 0 0.388 ms 183 buffers offset 5000 1.183 ms 188 buffers Both return the same 40 conversations in the same order (EXCEPT between them is 0 rows). The flat cost across page depth in the first pair comes from where the cursor lands in the plan: -> Subquery Scan on last_post Filter: (last_post.id Limit -> Sort (Sort Key: p.id DESC) -> Index Scan using post_conversation_id_btree on post p Index Searches: 58835 The cursor is a filter on the lateral's output rather than an index condition, so all 58,835 candidate conversations are probed on every page. With the stored column it's Index Cond: (last_activity_id < ...): one btree descent, and constant with depth. One structural difference between the two queries: get_direct_conversations filters conversation.audience IS NULL, which on this instance is 467 of 59,720 rows, applied before the sort. The public variant has no equivalent — its candidate set is every conversation with a public root, 58,804 here — so that predicate doesn't carry over to the sibling. A third shape worth recording: drive from the post index instead of from conversation, scanning post by id descending and deduping on conversation_id. 26.6 ms, 2,187 buffers, page 0. It's window-approximate — 2,000 posts yielded 1,477 distinct conversations — and cursor-paged it can emit the same conversation on two consecutive pages when a thread has a newer post above the cursor. Separately: max(uuid) doesn't exist in 18.3, so the §3.1 backfill needs ORDER BY post.id DESC LIMIT 1. Proposal updated. Measurements are from a busy production box, so there's noise in them, though not at this magnitude. The proposal.md has also been updated (again) after running the measurements. And more (hopefully refined enough) slop re: other points you brought up -- forgive any repeats Claude misinterpreted my monkeyslop reponse so I figured I should leave in the restatements in case I wasn't clear: Reposts: I'd like to keep these bumping, if it's not too costly. chan-fe treats a repost as a bump today and labels it as one on the card, which reads naturally on an imageboard — it's the useful version of the contentless "bump" posts you see on desuarchive, using an AP primitive that already exists instead of a junk comment. The mechanical cost is one extra resolution in the maintenance path: post has CHECK ((conversation_id IS NULL) != (repost_of_id IS NULL)), so a repost carries no conversation_id and the update has to follow repost_of_id → target.conversation_id. No disclosure concern, since a repost is a public object either way. If you'd rather they didn't bump, that's workable but it isn't something clients can restore — once the server owns the ordering, chan-fe can't re-sort a page it only partly holds — so it'd be a behavior change for us rather than a preference. Either way it needs documenting, since the distinction isn't visible from the response. Endpoint prefix. /api/v1/conversations is fine. The only wrinkle is that the bare path is Mastodon's DM conversation list, which Mitra already serves, so the public one likely wants to be a sibling path — /api/v1/conversations/public or similar. No deeper implication than the name being taken. Your call. Partial entities. Agreed on the principle, with one addition. last_status can shrink to id and created_at — id is the next page's cursor and nothing else is read. But a single newest post isn't quite what the card needs. chan-fe shows the last three activities on hover, so a reader can see why a thread is on the page — "3m: reply from @x", "1h: repost by @y" — which is what makes filtering a decision rather than a guess. That wants, per card: [{ id, created_at, type: reply|repost, account: { acct, display_name } }, ...] -- newest 3 If that's cheap enough, it replaces both last_status and the accounts participants array from §3.2 — participants is a different question from who bumped it recently, and the activity list is the one that gets rendered. For root_status, what the card reads is: id, created_at, url, title, content, spoiler_text, sensitive, media_attachments (the thumbnail is the card, so type, preview_url, url, blurhash), account (acct, display_name, avatar), the three counts, and favourited/reblogged for the card's own buttons. That's a Status minus emojis, poll, card, mentions, tags, application, language and in_reply_to_id. If you'd rather define one lean entity and have clients fetch full statuses on demand, that works too — one request per card on first render, still far better than the current up-to-200. Private comments bumping. Adding to what I said earlier: the DM query can bump on private posts because the viewer is a query parameter. A public catalog has no viewer to scope to, and much of its traffic is anonymous, so either everyone sees a bump with no visible new content, or the ordering becomes viewer-dependent — and a viewer-dependent order can't be a stored value or a cached page, which puts it back at the 1.6 s measured in §7.