Optimize the spotlight_cache implementation

[nullpointer0x00]

Summary

Optimize the spotlight_cache implementation by replacing the current database storage approach with an in-memory synchronized singleton to improve performance and reduce database load.

Problem Definition

The current implementation of the spotlight_cache involves a scheduled job that runs every 5 seconds, inserting a new record into the spotlight_cache table each time. The corresponding endpoint retrieves and serves only the most recent record, meaning the historical data is not being utilized.

This approach is inefficient for several reasons:

• The database currently holds around 18 million rows, taking up 15GB of space on mainnet.
• Every time the endpoint is called, a database query is made, which could be avoided if the latest spotlight data was stored in memory.
• The current implementation results in unnecessary storage use and increased database load, slowing down the system.

Proposal

• Replace the current implementation that inserts records into the spotlight_cache table with an in-memory synchronized singleton that stores only the most recent spotlight data.
• Before removing the spotlight_cache table from the database, confirm that the history of this data is not being utilized by any other part of the system.
• Implement the necessary logic to replace the old spotlight data in memory with the latest, avoiding the creation of new database records.
• The expected improvements include:
  • Reduced calls to the database for every endpoint request.
  • Faster access to the spotlight data since it will be retrieved from memory.
  • Significant space savings by removing the large and growing spotlight_cache table from the database.

---

For Admin Use

• Not duplicate issue
• Appropriate labels applied
• Appropriate contributors tagged
• Contributor assigned/self-assigned

[nullpointer0x00]

I decided to go with an in memory cache. This will remove the need to store records in the database.

I will drop the whole database table in a different PR.