The Problem We Were Actually Solving
Our core game loop was:
player scans QR → API writes record → Geo-indexer updates spatial index → leaderboard recalculates.
We knew writes would be the hot path, so we cached scan → player_id → last_location in Redis with a 30 s TTL. Simple, fast, and we could afford to lose a few updates if the cache evaporated.
The first mistake was the key schema. We chose {game_id}:{player_id}:location which meant every write to Postgres also went to a single shard. When the Redis node hit its 4 GB maxmemory, it started evicting keys. Our cache hit rate cratered to 47 % and the API p99 latency grew from 90 ms to 1.8 s because every miss forced a round-trip to Postgres plus a Geo-indexer recalculation.
