One technique that can be useful is imposing domain-specific restrictions, then passing responsibility down the chain.
For example, most people only have a handful of recent conversations; can you get away with ignoring distant-past conversations when it comes to read/unread marking? If so, you could send recent normalized data to the frontend and let it derive the unread state client-side. React has some nice tools for efficiently deriving state from server-sent data.
This isn't always the best solution, but it's something to consider.
Unless the amount of data you need to transmit is large, I always prefer that approach as it enables other UI features without any additional network trips by the client. Also, it lets one use the same mechanism for multiple uses (in this case, get latest messages), so it adds less complexity to the app.
In the articles example application, you can transmit the top X oldest messages after the last seen timestamp for each room, then have the client derive the unreadness of each room. You then can use that same info to do things like preview the oldest unread message for each room and hide latency when opening a room as you already have a screens worth of messages in hand. One doesn't need to get more recent messages as UIs generally don't have room to display large numbers and should switch to a "lots" indicator.
Hmm. So just make a request for "(my) messages since timestamp-x" from a client, build the join-query between "message rooms for me" and "messages since timestamp-x" on the server, then send a list of messages back to the client for the client code to sort out?
That actually sound good enough, as far as I can see: sometimes the client code pulls up 3 days (or max-N rows, or whatever) worth of messages when started (no big deal for me, might be for my daughter...), but usually only asks for new messages in the last minute (or whatever polling interval), after which the database and any application/middleware server are left alone.
For example, most people only have a handful of recent conversations; can you get away with ignoring distant-past conversations when it comes to read/unread marking? If so, you could send recent normalized data to the frontend and let it derive the unread state client-side. React has some nice tools for efficiently deriving state from server-sent data.
This isn't always the best solution, but it's something to consider.