100k RPS System Design: The Ultimate Interview Guide

Master senior engineering hires with this high-concurrency RDBMS challenge

I recently designed an interview question for our team’s hiring process. I put a lot of thought into it and hid some clever details inside. Without further ado, let’s take a look at the prompt.

[Interview Prompt: Large-Scale Security Agent Status & Event Tracking System]
We have an enterprise-grade security monitoring product. The system must ingest telemetry from 100,000 endpoint Security Agents deployed globally. The reported data includes “Endpoint Connection Status” (e.g., Online/Offline) and “Security Events / Alerts”.
Due to the massive fleet size and high-frequency reporting, the system experiences a write throughput of 100,000 RPS.
To evaluate your understanding of core database mechanics, you are strictly constrained to use a traditional RDBMS (PostgreSQL or MySQL) as the primary storage layer. You may not rely on NoSQL solutions (such as Redis or Elasticsearch) to bypass this constraint.
Please provide the following:

1. DB Schema Design: Detail your table structures, column data types, and Primary Keys.
2. Index Design: Specify which indexes you would create and explain the reasoning behind your choices.
3. SQL Queries: Write the exact SQL statements responsible for “Agents reporting data” (writes) and “Querying an Agent’s current status and events” (reads).

The beauty of this question lies in its hidden details. For example:

1. It closely mirrors our actual product scenarios. This allows me to introduce our daily work and job requirements during the interview.
2. Depending on my guidance or the candidate’s approach, we can turn this into a system design question for senior roles. Alternatively, we can scale it down to a basic CRUD problem for mid-level positions.
3. It looks simple but contains many traps. It is very easy to make mistakes.

Let’s walk through the details one by one. I will explain this from the perspective of interviewing a senior candidate.

Level 1: CQRS

First, I expect the candidate to ask the most critical question. With such high RPS, what is the read-to-write ratio? I can tell them it is 1:100 or even higher. This is heavily write-bound.

When designing the database schema, we must optimize for writes. This means we need to design a separate data model specifically for reads.

How we design the read model leads to the second key question. What do the reports look like? After all, we need to customize the read model to handle this massive volume of data.

If we are building time-based aggregated reports, we might need to consider a sliding window design. If we are tracking recent events for individual agents, we could use a simple, lean table and implement data rotation to balance costs.

This is the first level. If the candidate fails this, I mentally lower the bar and switch to mid-level mode. Naturally, they will not secure a senior offer from me at that point.

Level 2: Insert or Update

Usually, a candidate’s first reaction is to design an agent table. Each agent gets a row with a status field indicating online or offline.

This design is fine for small datasets. However, it carries severe risks at scale, such as lock contention. When a company has a massive fleet of agents and the network is unstable, online and offline events trigger constantly. If we update a single column, countless services might end up waiting for the same row lock at the same time. This creates massive backpressure and can crash the service.

Therefore, we should prioritize an append-only design pattern for the status table.

But this introduces another problem. How do we handle agent retries? For instance, an agent sends a request but does not receive a response due to network issues. The standard behavior is to retry. How do we guarantee idempotency? A common approach is for the agent to generate a GUID (like UUIDv4) for each request. The service then writes this field to the database to deduplicate entries.

Level 3: Pagination

Whether we use CQRS or a single table for reads and writes, querying massive fact tables always requires pagination. We obviously cannot load all the data at once. So, how do we paginate?

I am sure many people immediately think of offset and limit. This is known as offset-based pagination. When the offset gets huge, the pressure on the database becomes immense. If a candidate can suggest keyset-based pagination here, they definitely earn bonus points.

What we need to do is design an auto-incrementing ID in the fact table. Every batch remembers the maximum ID of the current fetch. We then use that value as the starting point for the next batch. This method drastically reduces database pagination overhead.

If the candidate notices that this incrementing ID should be the primary key, that earns extra points too.

I have written an article about pagination before. Feel free to check it out if you are interested. I used several real-world examples to explain why we need to abandon offset. Explaining Pagination in Elasticsearch

This also brings up another issue. Keyset-based pagination makes it impossible to jump to a specific page or get the total record count. What if the product requires those features? This is not a technical test question, but I want to leave it here for everyone to ponder.

Level 4: Auto-increment

The previous level mentioned using an auto-incrementing ID as the primary key. The problem is that such massive tables will easily overflow a standard auto-increment integer. We have two options here.

The first option is to use a relatively large number space as the primary key. Timestamps are a good example. It is even better if the candidate knows about Snowflake IDs.

Timestamps solve most problems, but we do need to watch out for clock skew. However, clock skew does not cause major issues on this type of fact table.

The second option is to consider data partitioning or sharding. This leads us directly to the next level.

Level 5: Shard Key

Given our current setup with both a fact table and a dedicated read model, how would we shard the data?

There are two common approaches. One is to shard by tenant or even by agent. This is simple, intuitive, and effective. However, it creates hotspots. Shards belonging to large or highly active customers might not hold up for long.

I previously shared a classic formula for choosing a shard key. If you have not seen it, you can review it here. How to Choose a MongoDB Shard Key

The other approach is to shard by time. This adds extra complexity to our queries. This is standard practice in data engineering, but backend engineers are usually not accustomed to it.

Level 6: Index

How should we build indexes on the read table? The answer is simple. It completely depends on the specific product requirements. But what about the fact table?

The answer might be surprising. Do not use indexes. More accurately, do not build any extra indexes other than the primary key.

I actually already mentioned the reason. We are using keyset-based pagination. We only need the primary key as our query condition. We will not use any other queries.

Every added index on a fact table is an extra burden. This includes hardware costs and write performance penalties. The hardware burden happens because fact tables are massive, meaning the index will also consume a huge amount of disk space. On the other hand, writing data also involves updating the index B-tree, which slows down performance.

Level 7: Hot and Cold Data Separation

No matter how we shard, infinitely growing data will eventually hit physical limits or budget constraints.

How do we implement hot and cold data separation? This steps a bit outside the realm of pure SQL. However, a candidate who can consider this level of architecture is definitely expert-tier.

Why do we need to think about this from the start? If we do not establish this premise early on, adding it later as a new requirement becomes exceptionally troublesome.

I wrote an article detailing my practical experience with hot and cold data separation. It took months to finally push it to production. I linked the article right here, and it is absolutely a great read. How SHOPLINE Saves 40% Space in Main Database

Therefore, it is best to factor this into the design right from the beginning.

Wrap Up

The clever part of this problem is its flexibility. We can adjust the scenario at any time based on the candidate’s answers. This allows us to evaluate every candidate as fairly as possible.

The biggest problem with system design interviews is the lack of objective standards. Carefully crafted questions like this let us clearly grade a candidate’s actual skills.

Practical validation has shown that this approach brings solid guidelines to otherwise chaotic system design interviews.

Besides that, we can always scale this question down into a coding interview. For instance, we can ask the candidate to design a pagination algorithm or a sliding window algorithm. We can set goals based on the desired difficulty while avoiding the trap of AI-generated answers. I find this type of question very interesting.

I will continue to try expanding my own question bank. I want to design even more useful and practical interview questions in the future.

Originally published on Medium