Community routes performance issues

[m0ar]

Description

Documenting perf investigation on the IJ radar in this issue.

The IJ community with 635 nodes in the radar cause a massive slowdown and performance choke on desci-server on related community queries. Calling https://nodes-api-dev.desci.com/v1/communities/298/radar takes anywhere between 45-90 seconds to return.

This is the psuedocode for handling the community radar route, doing massive amounts of queries to get the information needed for the response:

- Call communityService.getCommunityRadar:
    - Largeish 4 table join to get node attestations et al
    - Takes between 1 and >2.5s depending on db cache
- async map attestationService.getNodeEngagementSignals over all nodes:
    - aggregate engagement signals for each node with a largeish join query
    - attach the aggregate signals to the node objects
- map resolveLatestNode over all nodes:
    - node discovery query
    - node versions query
    - fetch manifest over IPFS
    - fall back to repo manifest fetch
- sort resulting (node+engagement+manifest) combo by engagements and claim timestamps

Insights:

• route lacks pagination, and it's not obvious how to add it due to the global sorting requirements on signals
• need to massively reduce the amount of work mapped over each radar entry
• need to prevent preemptively resolving manifests for each node