Implement non-blocking database save for character repository

[ismael-sensei]

Currently, the CachingCharacterRepository saves data to both the cache (Redis) and the database (PostgreSQL) synchronously. This behavior introduces potential latency, especially when the database operation is slow or under high load.

To improve performance and responsiveness, the database save operation should be made non-blocking while keeping the cache update synchronous. This ensures quick cache updates while delegating the database save to a background process.

---

Proposed Solutions

1. ThreadPoolExecutor

Use Python's built-in concurrent.futures.ThreadPoolExecutor to execute the database save operation in a separate thread.

• Implementation:

  from concurrent.futures import ThreadPoolExecutor
  
  class CachingCharacterRepository(CharacterRepository):
    def __init__(self, cache_repo: CharacterRepository, db_repo: CharacterRepository):
        self.cache_repo = cache_repo
        self.db_repo = db_repo
        self.executor = ThreadPoolExecutor(max_workers=1)
  
    def save(self, user_id: str, guild_id: str, character: Character):
        self.cache_repo.save(user_id, guild_id, character)
        self.executor.submit(self.db_repo.save, user_id, guild_id, character)

• Pros:

  • Simple to implement.
  • Does not require additional libraries or infrastructure.
  • Lightweight for a small-scale application.

• Cons:

  • Limited scalability due to a fixed number of threads (max_workers).
  • No built-in retry mechanism for failed operations.
  • Error handling in threads needs explicit configuration.

---

2. Celery with Redis as Task Queue

Integrate a task queue system using Celery to offload the database save operation to a worker process.

• Implementation:

  • Define a Celery task to handle the database save:

    @app.task
    def save_character_to_db(user_id: str, guild_id: str, character_data: dict):
    character = Character.from_json(character_data)
    db_repo.save(user_id, guild_id, character)

  • Modify CachingCharacterRepository:

    def save(self, user_id: str, guild_id: str, character: Character):
    self.cache_repo.save(user_id, guild_id, character)
    save_character_to_db.delay(user_id, guild_id, character.to_json())

• Pros:

  • High scalability: can process multiple tasks in parallel with multiple workers.
  • Built-in retry mechanism for failed operations.
  • Resilient to worker crashes or server restarts.

• Cons:

  • Requires additional setup for Celery and Redis.
  • More complex implementation and deployment.
  • Overhead of running a task queue and worker processes.

---

Comparison Table

Aspect	ThreadPoolExecutor	Celery
Ease of Use	Simple and lightweight	Requires additional setup
Scalability	Limited by max_workers	Highly scalable
Retry Mechanism	Needs custom implementation	Built-in retry support
Error Handling	Must handle manually	More robust out-of-the-box
Ideal For	Small applications or low concurrency	Larger, high-load systems

---

Acceptance Criteria

1. Implement a non-blocking save operation for CachingCharacterRepository using one of the proposed solutions.
2. Clearly document the chosen solution and its trade-offs.
3. Ensure proper logging and error handling for background tasks.
4. Provide unit tests for the new save behavior.

---

Additional Notes

• If Celery is chosen, ensure Redis is already configured as the broker.
• If ThreadPoolExecutor is chosen, ensure thread safety and proper cleanup of threads during shutdown.