Home | Notifications | New Note | Local | Federated | Search | Logout

Note Detail

Reply to @harblinger@wizard.casa
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---
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

---Replies---

silverpill@silverpill@mitra.social (2026-07-28 05:31:19)
@harblinger


True, I'm going to re-skin AP 'repost' as 'bump' eventually.  Good AP primitive to fix the no-signal bump posts common on image boards (picrel).


That's an interesting idea.

Maybe reposts should be tied to conversations, but let's keep this focused on comment-bumps for now.


Just implemented mitra subs recently too :)


🔥


Filtering private comments is to prevent users not in the convo being confused by what appears to be no activity bumped threads.


You can pass the current user to the query... But no need to make it more complicated than necessary.


I hope it's not annoying slop, let me know if you want me to run any further tests


Stored conversation.last_activity_id with a btree index



Premature optimization. It's never too late to add extra column.


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.


Yes, we should use /api/v1/conversations/public


slop slop slop


Do you intend to send a patch?