Problem

The homepage post loading was suffering from a classic N+1 query problem when fetching user data (post owners and contributors). For each post displayed on the homepage, the system was making individual database queries to fetch user information, resulting in:

• 1 query to fetch posts
• N queries to fetch post owners (one per post)
• M queries to fetch contributors (one per post with contributors)

This created a significant performance bottleneck, especially visible in the SQL logs as repeated SELECT users.* FROM users WHERE users.ID = $1 LIMIT 2 queries.

Solution

Implemented batch fetching to reduce the query complexity from O(N) to O(1) for user data:

1. Added batch user fetching capability

// New method in UserService
Flux<User> getUsersOrGhostByNames(Set<String> usernames);

This method uses a single query with IN clause to fetch multiple users at once, falling back to ghost users for missing entries.

2. Modified post processing to use batching

// In PostPublicQueryServiceImpl.list()
return collectAllUsernames(posts)
    .flatMap(usernames -> contributorFinder.getUserService().getUsersOrGhostByNames(usernames))
    .collectMap(user -> user.getMetadata().getName())
    .flatMap(userCache -> processPostsWithCache(posts, userCache));

3. Cache-aware post conversion

Added overloaded methods that accept pre-fetched user data:

Mono<ListedPostVo> convertToListedVo(@NonNull Post post, Map<String, User> userCache);

Performance Impact

Before: 1 + N + M database queries
After: 2 database queries (1 for posts, 1 for all users)

For a homepage with 10 posts having 2 contributors each, this reduces queries from ~31 to 2 - a 93% reduction.

Backward Compatibility

• All existing APIs remain unchanged
• Original methods still work for individual post processing
• New batch methods are used internally for optimization
• No breaking changes to external interfaces

Testing

• All existing tests pass
• Added validation for the new batch functionality
• Verified the optimization doesn't affect post display logic
• Manual testing confirms user data is correctly populated

Fixes #7593

💡 You can make Copilot smarter by setting up custom instructions, customizing its development environment and configuring Model Context Protocol (MCP) servers. Learn more Copilot coding agent tips in the docs.