Home | Notifications | New Note | Local | Federated | Search | Logout
Note Detail
Reply to @silverpill@mitra.social
Harblinger@harblinger@wizard.casa (2026-07-26 18:52:59)
Got around to it, here's the "slop" proposal (actually pretty good until item 6 "not part of this proposal" which snuck in, can be ignored)
https://git.wizard.casa/nak/chan-fe/src/branch/main/docs/mitra-catalog-endpoint-proposal.md
---Reply---
silverpill@silverpill@mitra.social (2026-07-26 19:55:36)
@harblinger
It's not bad! Creating a sibling to get_direct_conversations is a good idea.
>2. What Mitra already has
>The missing piece is only that nothing orders conversations by activity, and nothing exposes them at public visibility.
This is correct. However, I think the results of get_direct_conversations can be ordered by activity (post.created_at). I made a quick test, the estimated cost of the query is exactly the same.
>3.1 A bump column on conversation
...So this shouldn't be necessary.
>Three details worth deciding deliberately:
>Reposts have no conversation_id
I've never seen an imageboard with reposts. I think only comments should bump threads.
>Only public activity should bump a public thread.
If the cost is not too high, we can bump threads on private comments too. get_direct_conversations already does that.
>3.2 The endpoint
I think /api/v1/conversations is a better prefix for the endpoint.
>root_status ā the OP; this is the catalog card. The query already joins post AS root, it just doesn't return it today.
>last_status ā newest activity, already produced by the lateral. Its id doubles as the max_id for the next page.
Returning whole Status is expensive. Unless you need to know everything about both root_status and last_status, I recommend returning a partial entity (e.g. only a title of the root).
>3.3 Optional: the same for group timelines
>Strictly a follow-up.
š
This should be delayed until private groups are implemented.
Reply
---Replies---
Harblinger@harblinger@wizard.casa (2026-07-27 14:57:13)
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 < '019fa02a-...'::uuid)
-> 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 conversatio