Scaling to 1.5 Billion Lookups: Why We Chose CQRS over NoSQL

When designing the backend architecture for a high-traffic consumer application, the database choice is often the most critical decision you will make. Recently, while scaling the MacAppUpdater backend to support a projected 5 million active users, we hit a classic architectural crossroads: Do we migrate to a massive NoSQL database, or do we stick with our trusted PostgreSQL setup?

Our solution wasn not to choose between SQL and NoSQL. Instead, we implemented a pattern called CQRS (Command Query Responsibility Segregation) using a combination of PostgreSQL and Redis.

Here is a deep dive into the problem we faced, and why CQRS provided the perfect solution.

The Dual-Workload Problem

MacAppUpdater essentially serves two completely different masters simultaneously:

1. The Backend Data Processing Our backend runs heavy, complex background jobs to process, aggregate, and manage a highly structured database of over 100,000 software records. This workload is highly analytical. We need to identify duplicates, join tables, aggregate metrics, and enforce strict JSON schemas. SQL (PostgreSQL) is the undisputed king here.

2. The Client Traffic (The Frontend) On the flip side, we have 5 million end-users running our native macOS app. If each user periodically checks for updates on 300 installed apps, that translates to 1.5 Billion app lookups per day.

When a client asks for the latest versions of their installed applications, they expect an answer in milliseconds. Forcing a relational database to execute 1.5 billion complex bulk SELECT queries every day would melt our PostgreSQL server's CPU instantly. Key-Value stores (like Redis or DynamoDB) are the undisputed kings here.

The NoSQL Temptation

The standard modern advice for handling 1.5 billion simple key-value lookups is to dump everything into a horizontally scalable NoSQL database like MongoDB or DynamoDB.

While a pure NoSQL database would handle the front-end client traffic beautifully, it would be catastrophic for our backend data processing. Identifying duplicates or aggregating dashboard statistics across 100,000 schemaless JSON documents is incredibly slow and complex compared to a simple SQL JOIN or GROUP BY. We would have to rewrite our entire data processing engine.

The Solution: CQRS (PostgreSQL + Redis)

Instead of forcing a single database to handle two conflicting workloads, we implemented CQRS.

CQRS dictates that the system that writes data (the Command model) should be separated from the system that reads data (the Query model).

1. The Command Model: PostgreSQL PostgreSQL acts as our absolute Source of Truth. All of our internal background jobs and administration dashboards strictly interact with Postgres. It handles the heavy relational lifting, data normalisation, and complex analytical queries.

2. The Query Model: Redis Redis acts as our highly optimised Read Model. Whenever the backend processes a new software update in Postgres, it fires off an event that drops a tiny JSON object into Redis:

// Key: app:com.apple.FinalCut
{
  "latest_version": "10.6.8",
  "download_url": "https://..."
}

Storing 100,000 of these tiny hashes in Redis takes roughly 20-30 MB of RAM.

When a user's macOS app wants to check for updates on 300 apps simultaneously, our FastAPI engine bypasses PostgreSQL completely. It executes a single MGET (Multi-Get) command to Redis. Redis grabs all 300 latest versions directly from RAM and returns them in under 1 millisecond.

The Result

By pairing PostgreSQL and Redis, we built an architecture with zero compromises:

We retain the power of complex relational SQL queries for our backend administration.
Our API workers remain completely stateless and can scale horizontally indefinitely.
We achieve sub-millisecond response times for 1.5 billion daily consumer lookups.

Sometimes, the best database for the job isn't a single database at all. It's understanding your workloads, and splitting the responsibilities using CQRS.

A Quick Update: The Journey from Idea to Production

If you found this architectural deep-dive interesting, I am currently writing a comprehensive two-part book series documenting the entire software engineering lifecycle; taking an application from a raw idea, through architecture and production, and finally to scaling for real users.

I am have currently nailied down Chapters 1-3 and sent it off to publications for review.

If you want to follow along with the book's progress, get more technical deep-dives like this, or just hear about my latest running and cycling adventures, be sure to subscribe to the newsletter!