Skip to main content

91. PostgreSQL for Durable Tournament State

Status: Accepted Date: 2025-07-06

Context​

A Dike tournament can be a long-running process, potentially lasting for hours or even days. During this time, it generates critical state, including the tournament's overall status, the configuration, the participants in each round, and the results of every comparison. We need to decide where to store this state. Storing it in memory is not an option, as a server restart would lose all progress. Storing it in Redis is an option, but Redis is primarily an in-memory store and is not designed for long-term durability in the same way a relational database is.

Decision​

All critical and durable state for Dike tournaments will be stored in our primary PostgreSQL database.

This includes:

  • The main Tournament entity, with its configuration and overall status (running, paused, completed).
  • A TournamentRound entity to track the state of each round.
  • A TournamentComparison entity to log the inputs and results of every single pairwise market comparison.

While the orchestration of the tournament is managed by BullMQ flows (adr://bullmq-flow-processing), the authoritative state and history of the tournament will reside in PostgreSQL. The BullMQ jobs will read from and write to the PostgreSQL database as their source of truth.

Consequences​

Positive:

  • Durability and Recoverability: PostgreSQL provides strong ACID guarantees and durability. If the application or even the Redis server crashes, the exact state of every tournament can be fully recovered from the database. We can resume a tournament from exactly where it left off.
  • Rich Querying and Auditing: Storing the full history in PostgreSQL allows for rich, structured querying. We can easily run analytical queries to audit a tournament's results, analyze the performance of the AI comparisons, or debug issues.
  • Transactional Integrity: We can use database transactions to ensure that state updates are atomic. For example, updating the results of a comparison and advancing a market to the next round can be wrapped in a single transaction.

Negative:

  • Higher Latency than Redis: Reading from and writing to PostgreSQL is slower than an in-memory store like Redis. This could potentially slow down the processing of tournament jobs.
  • Increased Database Load: The high volume of writes (one for every comparison) could put a significant load on the primary database.

Mitigation:

  • Asynchronous Processing: Since the entire tournament runs in background jobs, the slightly higher latency of PostgreSQL is not user-facing and does not impact the application's responsiveness. The jobs are designed to be long-running anyway.
  • Bulk Operations and Indexing: We will optimize our database interactions. For example, the "next round" job can fetch all results for the previous round in a single query. We will also ensure that all relevant columns in the tournament tables are properly indexed to keep query performance high.
  • Separation of Concerns: Redis is still used for its intended purpose: managing the queue of jobs. PostgreSQL is used for its intended purpose: reliably storing important business data. This is the correct use of both technologies (adr://postgresql-redis-architecture).