Problem Definition

The HibernateRepositoryActivityDao.findMostRecentActivityTime() method exhibits performance degradation in environments with large activity datasets. In production environments with 102+ million activity records, this query can take over 100 seconds to execute, causing delays in the search indexing processes

2025-07-29 03:39:50,851 | search-indexing:thread-1 | - | - | -
[100956.5ms] - Date com.atlassian.bitbucket.dmz.repositorymanagement.DmzRepositoryManagementService.getRecentActivity(Repository)
 [1.2ms] - Stream com.atlassian.stash.internal.mirror.hash.MirrorHashDao.streamByRepositoryIds(Collection)
 [100955.2ms] - Date com.atlassian.stash.internal.repository.RepositoryActivityDao.findMostRecentActivityTime(int)

Suggested Solution

• Optimize the query further (some fixes were already made as part of BSERV-14405 and BSERV-19616)
• Use a different method of retrieving the most recent activity time, which may not be as accurate compared to deriving it from the activity tables, but is close enough as an estimate. It should be noted that this is used in the Advanced Repo Management feature.

Why This Is Important

• The search indexing process frequently calls this method and becomes blocked by slow queries.
• The current implementation doesn't scale with repository activity volume, which will be an issue for large Bitbucket DC customers

Workaround

No workaround is available at the moment