When a Redis Leaderboard Lies: Outbox, Idempotency, and Reconciliation

A Redis sorted set makes a leaderboard read cheap: ZREVRANGE returns the highest scores without asking the relational database to aggregate every request. The difficult part is not the ZSET operation. It is keeping that projection trustworthy when the database commit, Redis write, worker process, or network fails.

The unsafe implementation is also the most tempting:

scoreRepository.increment(userId, delta);     // database write
redisTemplate.opsForZSet()
        .incrementScore("leaderboard", userId, delta); // Redis write

Those writes do not share a transaction. If the database commits and Redis fails, the leaderboard is stale. If Redis succeeds and the database rolls back, the leaderboard invents a score. A retry can apply the delta twice. Reversing the order changes which inconsistency is possible; it does not remove the inconsistency.

I built a failure experiment around a different contract:

• PostgreSQL is the source of truth.
• A score command has a stable event ID.
• The score change and an outbox row commit in one database transaction.
• A worker projects absolute score and version into Redis.
• Projection is idempotent and rejects stale versions.
• Reconciliation detects and repairs missing, stale, ahead, or corrupt cache state.

This is an eventually consistent read model, not a distributed transaction disguised as a service method.

Define the consistency contract

Before choosing code, decide what the product may observe:

Question	Contract in this experiment
Authoritative score	PostgreSQL user_scores.score
Leaderboard freshness	Eventual; visible lag while the worker is down
Duplicate client command	One database effect per event_id
Duplicate worker delivery	Safe; absolute score and version are idempotent
Redis loss	Rebuild from PostgreSQL
Redis corruption	Detect and repair through reconciliation
Read during lag	Return the projection with explicit freshness, or fall back when the product requires it

Redis is allowed to be behind. It is never allowed to silently become the authority for a business score.

Commit the score and outbox atomically

The relational schema separates command identity, current state, and pending projection work:

CREATE TABLE score_events (
  event_id uuid PRIMARY KEY,
  user_id text NOT NULL,
  delta integer NOT NULL CHECK (delta BETWEEN -1000 AND 1000),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE user_scores (
  user_id text PRIMARY KEY,
  score bigint NOT NULL DEFAULT 0,
  version bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE leaderboard_outbox (
  id bigserial PRIMARY KEY,
  event_id uuid NOT NULL UNIQUE REFERENCES score_events(event_id),
  user_id text NOT NULL,
  absolute_score bigint NOT NULL,
  score_version bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);

CREATE INDEX leaderboard_outbox_pending_idx
  ON leaderboard_outbox (id)
  WHERE published_at IS NULL;

The command handler performs three changes inside one PostgreSQL transaction:

await client.query('BEGIN');

const inserted = await client.query(
  `INSERT INTO score_events(event_id, user_id, delta)
   VALUES ($1, $2, $3)
   ON CONFLICT DO NOTHING
   RETURNING event_id`,
  [eventId, userId, delta]
);

if (inserted.rowCount === 0) {
  await client.query('COMMIT');
  return { duplicate: true };
}

const score = await client.query(
  `INSERT INTO user_scores(user_id, score, version)
   VALUES ($1, $2, 1)
   ON CONFLICT (user_id) DO UPDATE
   SET score = user_scores.score + EXCLUDED.score,
       version = user_scores.version + 1,
       updated_at = now()
   RETURNING user_id, score, version`,
  [userId, delta]
);

await client.query(
  `INSERT INTO leaderboard_outbox
     (event_id, user_id, absolute_score, score_version)
   VALUES ($1, $2, $3, $4)`,
  [eventId, userId, score.rows[0].score, score.rows[0].version]
);

await client.query('COMMIT');

The primary key on score_events.event_id is the command idempotency boundary. If an HTTP retry sends the same command again, the score is not incremented twice.

The outbox stores the absolute score and monotonic version, not another delta. That decision makes worker replay safe. Applying “score is 17 at version 2” twice is idempotent; applying “add 7” twice is not.

Project with at-least-once delivery in mind

The worker claims pending rows with FOR UPDATE SKIP LOCKED, which allows several workers to claim different batches:

SELECT id, event_id, user_id, absolute_score, score_version
FROM leaderboard_outbox
WHERE published_at IS NULL
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 100;

For every row it updates three Redis structures atomically through Lua:

local currentVersion = tonumber(
  redis.call('HGET', KEYS[3], ARGV[1]) or '-1'
)
local incomingVersion = tonumber(ARGV[3])

if redis.call('EXISTS', KEYS[1]) == 1 then
  return 'duplicate'
end

if incomingVersion > currentVersion then
  redis.call('ZADD', KEYS[2], ARGV[2], ARGV[1])
  redis.call('HSET', KEYS[3], ARGV[1], incomingVersion)
  redis.call('SET', KEYS[1], '1', 'EX', ARGV[4])
  return 'applied'
end

redis.call('SET', KEYS[1], '1', 'EX', ARGV[4])
return 'stale'

The keys represent an event marker, the leaderboard ZSET, and a user-version hash. Lua makes the comparison and writes atomic inside Redis.

The version check remains important after the event marker expires. If an old outbox row is replayed later, it cannot overwrite a newer absolute score. The marker is an optimization for duplicate work; the monotonic version is the correctness boundary.

Design for the crash between Redis and PostgreSQL

The worker performs this sequence:

1. Claim an outbox row in PostgreSQL.
2. Apply the Redis projection.
3. Mark the outbox row published.
4. Commit.

It can crash after step 2. PostgreSQL rolls back the claim, so another worker reads the same row. The Redis operation must therefore tolerate duplicate delivery. That is why the projection uses an event marker, an absolute score, and a version check.

Holding the database transaction open across a Redis call is a conscious tradeoff in this compact experiment. SKIP LOCKED prevents one slow batch from stopping all workers, but a production design should bound Redis timeouts, keep batches small, monitor lock age, and consider a leased-claim state when external latency is high.

Exactly-once delivery is not the goal. One durable database effect plus an idempotent at-least-once projection is easier to reason about and test.

Reconcile because idempotency is not repair

Idempotency handles duplicates. It does not repair deletion, manual edits, expired Redis data, or a software defect that wrote the wrong score.

The reconciliation job reads source rows and classifies each projection:

export function compareProjection(source, projection) {
  if (!projection) return 'missing';
  if (Number(projection.version) < Number(source.version)) return 'stale';
  if (Number(projection.version) > Number(source.version)) return 'ahead';
  if (Number(projection.score) !== Number(source.score)) return 'corrupt';
  return 'current';
}

ahead is not automatically safe. It can mean the source read is stale, the wrong database is being queried, or Redis contains invented state. I would alert and investigate before forcing a repair in a system where cross-region or replica lag is possible.

For missing, stale, or corrupt rows, the repair path writes the source’s absolute score and version. A full rebuild can delete the projection and replay every source row into a new key, then atomically switch readers to that key.

What the failure experiment observed

The experiment intentionally duplicated a command, stopped the worker, and corrupted one Redis score:

Stage	PostgreSQL source	Redis projection	Result
First command for user-a	score 10, version 1	score 10	Applied
Same event ID again	score 10, version 1	score 10	Duplicate detected; no second effect
Worker stopped	user-a 17, user-b 25	only user-a 10	Expected visible projection lag
Worker restarted	user-a 17, user-b 25	both projected	Outbox drained
Redis user-a changed to 999	source still 17	corrupt score 999	Reconciliation classified corruption
Repair complete	user-b 25, user-a 17	identical ranking	One of two rows repaired

The final artifact reported two rows checked: one corrupt and one current. It repaired the corrupt row, and the final source ordering exactly matched the ZSET ordering.

That result demonstrates the failure paths; it does not establish throughput. A production run also needs outbox age, pending count, worker error rate, reconciliation drift count, Redis latency, and leaderboard freshness metrics.

Expose freshness to the product

Eventual consistency becomes a product problem when nobody defines “eventual.” Useful options include:

• return projectedThrough or projection age with the leaderboard;
• fall back to a database query when the outbox age exceeds a limit;
• hide a user’s just-submitted rank until their version is projected;
• offer read-your-write behavior from the command response while global ranking catches up;
• fail closed for rewards or payouts that require authoritative ordering.

The Redis score is a double. Integer values are exact only within the IEEE-754 safe integer range, so very large counters or composite ranking scores need a deliberate representation and tie-break rule.

The operational checklist

• Keep the business score authoritative in one durable system.
• Require a stable command/event ID at the API boundary.
• Commit source state and outbox work in one database transaction.
• Project absolute state with a monotonic version.
• Assume the worker will deliver at least once.
• Make Redis comparison and writes atomic.
• Monitor outbox age, not only row count.
• Reconcile on a schedule and make full rebuilds routine.
• Define freshness and fallback behavior for readers.
• Never award money or irreversible benefits from an unverified cache projection.

Redis documents ZSET behavior in its sorted-set documentation, while Spring’s Redis reference covers the client-side integration. Neither replaces the application-level consistency contract.