On the surface, it sounds almost quaint: a treasure hunt game. But for one engineering team, this game’s backend became the epicenter of a 3 AM catastrophe that nearly broke their service. It wasn’t a sudden surge in players; it was a meticulously built system, designed for scalability, that imploded under its own weight, not because of raw traffic, but because of a critical miscalculation in its performance optimization strategy.
This wasn’t a system built on shaky foundations. In closed beta, the core PostgreSQL table, hunt_tiles, partitioned by hunt_id and indexed on (hunt_id, x, y), was humming along nicely. With 1.2 million rows, p99 latency for point queries was a respectable 47ms. They provisioned generously for launch, anticipating a 6x growth, equipping a 32-core R6g.4xlarge with 256 GB RAM. The RDS dashboard even showed a comfortable 11% CPU utilization and a healthy 68% memory free. What could possibly go wrong?
Everything, it turned out.
The /tiles/{huntId}/{x}/{y} endpoint, designed to serve individual map tiles, started puking 503 errors at a mere 1,800 QPS during stress tests. The culprits weren’t the usual suspects—CPU or disk I/O—but the PostgreSQL query planner itself, apparently choking on bitmap index scans across partition keys. The EXPLAIN ANALYZE output was eye-watering: 4.3 milliseconds dedicated just to planning for a single tile fetch. Multiply that by 1,800 concurrent requests, and you’re burning 7.7 seconds of CPU per second, pushing iowait to a dizzying 42%. They had optimized the wrong layer; the database wasn’t the bottleneck, the planning of queries against it was.
Meanwhile, the Veltrix front end had a strict SLA: every tile must arrive within 50 milliseconds, or the map simply refused to render. To meet this, the team implemented a local, in-memory cache within each front-end instance using Caffeine, with a Time-To-Live (TTL) of 30 seconds. For three glorious days, this seemed to do the trick. Then came the collision: the cache invalidation policy ran headlong into the weekly content refresh. Chaos ensued.
The Tuesday Ghost Arrives
The initial fix—SSD-backed Redis with noeviction—offered a tempting latency drop to 3ms p99. But memory usage skyrocketed by 60% week-over-week. Three weeks later, their cache cluster blew past the 72GB memory limit of their cache.m6g.large instances. Auto-scaling kicked in at 85% memory, spinning up a new node. The catch? DNS updates took a glacial 47 seconds. This agonizing delay was just long enough for every user to hit refresh, losing their current hunt session—hence ticket #1782: “Lost session after cache node scale-out.”
Their next attempt involved Redis Cluster Mode with four shards, using hunt_id % 4 as the sharding key. Unfortunately, the top 10 hunts, always the most popular, skewed traffic. Shards 0 and 1 absorbed 70% of the load, making Shard 0 the inevitable bottleneck. CPU hit 94%, and latency climbed to a painful 210ms. A quick fix involved rebalancing hunt_ids via a modulo shift, but the front-end application, with hunt_id hardcoded into its API path, predictably restarted every session.
Then came the L1 cache: an in-memory Caffeine cache within the engine JVM itself, with maximumWeight=512MB and a TinyLFU eviction policy. The JVM’s memory footprint ballooned from 800MB to a hefty 1.8GB. During the next Tuesday rollout, the JVM paused for a staggering 1.4 seconds to perform a full Garbage Collection. The front end, interpreting this pause as a timeout, unceremoniously dropped the map.
The Deeper Architectural Flaw: A Contract Broken
They were chasing latency with memory and layers of complexity, but the fundamental issue was the broken contract between the engine and the front end. The front end demanded every tile within a fixed, impossibly small window, and the caching layer was designed to be ephemeral. The real problem wasn’t how they cached, but the inherent assumption that a cache could consistently meet such stringent, time-sensitive demands while also being susceptible to invalidation and scaling delays. They didn’t need an unbounded cache; they needed a bounded timeline.
The Stream-Time Materialized View Solution
The breakthrough came from abandoning the cache-first paradigm entirely. Instead, they rebuilt the engine as a stream-time materialized view. The core change was profound: instead of caching individual tiles, they pre-computed every active hunt’s entire tile grid as a materialized view within PostgreSQL. This view, hunt_tile_mv, was updated via a Debezium Change Data Capture (CDC) stream directly from the content system.
The definition of hunt_tile_mv itself tells a story:
CREATE MATERIALIZED VIEW hunt_tile_mv REFRESH CONCURRENTLY hunt_tile_mv ON DEMAND AS SELECT hunt_id, x, y, tile_data, version FROM hunt_tiles t WHERE t.active = true AND t.version >= (SELECT max(version) - 2 FROM hunt_versions);
A Kubernetes CronJob, firing every 5 minutes, handled the refresh:
REFRESH MATERIALIZED VIEW CONCURRENTLY hunt_tile_mv;
To prevent concurrent refreshes, especially crucial if a node crashed mid-process, the scheduler employed a distributed lock using Redlock on Redis. If a refresh was interrupted, the next pod would simply wait for the lock to expire, ensuring data integrity without complex state management.
Reads became lightning fast. The engine served /tiles/{huntId}/{x}/{y} with a direct SELECT against hunt_tile_mv, leveraging an index on (hunt_id, x, y). This resulted in an index-only scan, with p99 latency measuring a solid 6.8ms, even under 300 concurrent threads.
Writes remained on the original hunt_tiles table, the source of truth, updated via a REST endpoint. Crucially, after each write, an event was published to a Kafka topic called hunt_tiles_changed. However, the materialized view job didn’t subscribe to this Kafka stream. Instead, it relied on its 5-minute check, comparing the current version against the latest content version. This simplified the consistency model to eventual consistency with a bounded staleness of—you guessed it—5 minutes.
The impact was dramatic. The Redis cluster vanished. Cache nodes were powered down. The engine’s memory footprint shrank from 1.8GB to a lean 512MB. The JVM, once prone to crippling GC pauses, now topped out at a mere 120ms of GC time under 1,200 QPS.
The Proof in the Pudding: Performance Rebounds
Post-rollout, three key metrics told the tale:
- Front-end map render success rate: Jumped from a shaky 89% to a near-perfect 99.8% within 48 hours.
- Engine p99 latency: Stabilized at a consistent 6.8ms, a far cry from the volatile figures of the caching era.
This isn’t just about fixing a bug; it’s a stark reminder that architectural decisions, especially around caching, can have profoundly unpredictable consequences. Sometimes, the fastest path to performance isn’t adding more layers of speed, but fundamentally rethinking how data is structured and served, especially when dealing with real-time demands and complex dependencies.
Why Did The Caching Strategy Fail So Spectacularly?
The initial caching strategy crumbled for several interconnected reasons. First, the front end’s extremely tight latency requirement (50ms per tile) put immense pressure on any caching layer. Second, the chosen caches (local in-memory, then Redis) were susceptible to invalidation issues, particularly during content refreshes, leading to stale data or outright cache misses. Third, Redis’s auto-scaling mechanism, while designed for availability, introduced unacceptable latency in its DNS propagation, causing user session loss. Finally, the sharding strategy in Redis Cluster Mode was naive, failing to account for uneven traffic distribution among popular hunts, thus creating new bottlenecks. The fundamental flaw was treating the cache as a universal performance panacea without fully accounting for the operational complexities and the strict contractual demands of the front end.
Will This Materialized View Approach Work Everywhere?
Not necessarily. This materialized view approach excels in scenarios where:
- Data can be reasonably aggregated: The
hunt_tile_mvpre-computes a large dataset. - Eventual consistency with a bounded staleness is acceptable: A 5-minute delay in updates is tolerable.
- The source of truth is relatively stable or changes can be streamed: CDC from PostgreSQL is key here.
- The query patterns are predictable: Direct reads from the materialized view benefit from indexes.
For highly dynamic, low-latency transactional systems where every individual write needs immediate global visibility, this might be overkill or introduce too much overhead. However, for read-heavy services serving complex, aggregated data like game maps, dashboards, or reporting views, it offers a compelling alternative to traditional caching.
🧬 Related Insights
- Read more: NestJS i18n Goes Vietnamese: Beyond English/Spanish [DevTools Feed]
- Read more: Beyond Code: What Microsoft & Squad Taught About Engineering Culture
Frequently Asked Questions
What does a materialized view in PostgreSQL do? A materialized view in PostgreSQL is like a snapshot of a query result stored physically. Unlike a regular view, which re-executes its query every time it’s accessed, a materialized view stores the data, allowing for much faster reads. This data needs to be refreshed periodically to reflect changes in the underlying tables.
Is 5 minutes of data staleness acceptable for most applications? Whether 5 minutes of data staleness is acceptable depends entirely on the application’s requirements. For real-time financial trading or critical control systems, it’s unacceptable. For many business dashboards, analytical reports, or game states, a 5-minute lag can be perfectly fine, especially when it significantly improves read performance and system stability.
How does Debezium CDC work with PostgreSQL? Debezium is an open-source distributed platform for change data capture. When integrated with PostgreSQL, it monitors the database’s write-ahead log (WAL) and captures every row-level change (inserts, updates, deletes). These changes are then published as events to a message broker like Kafka, allowing other services to react to or consume these updates in near real-time.
