Your reporting service is crushing the production database every morning at 9 AM.
Finance exports a “Monthly Revenue Report.”
Operations opens a “Top Selling Products” dashboard.
Managers refresh KPI widgets every 30 seconds.
Meanwhile, your checkout API latency jumps from 120ms to 4s.
The problem:
your OLTP database is handling transactional traffic and heavy analytical queries at the same time.
Current setup:
Web App → PostgreSQL Primary DB
Checkout API → PostgreSQL Primary DB
Analytics Dashboard → PostgreSQL Primary DB
Daily Reporting Jobs → PostgreSQL Primary DB
…and traffic keeps growing.
You’re asked to reduce load on the primary database without breaking real-time transactions.
What do you do?
A) Add Read Replicas — route reporting and dashboard reads away from the primary database.
B) Introduce Redis Cache — cache report responses and dashboard queries.
C) Move analytics workloads to a Data Warehouse — isolate OLAP from OLTP entirely.
D) Increase PostgreSQL CPU &amp; RAM — vertically scale the database server.
Three of these are valid production strategies.
Only one solves the core architectural problem long-term.
Pick one — A, B, C, or D — and tell me why.
I’ll drop the full breakdown in the comments (including why one of the “good” answers becomes a disaster at scale).
If your team has ever fought over scaling databases, share this with them. The tradeoff discussion is the real lesson.
Drop your answer 👇

A Django-focused development team that architects scalable systems, clean data models, and maintainable backend structures.

django

A system design scenario where a PostgreSQL database is overwhelmed by simultaneous OLTP and OLAP workloads, causing checkout API latency to spike from 120ms to 4s. Four scaling strategies are presented: read replicas, Redis caching, migrating analytics to a data warehouse, and vertical scaling. Readers are challenged to identify which single option best solves the core architectural problem long-term, with the answer promised in comments.

Day-1 style system design question

<h3>Why D is the trap answer</h3>
<h4>D) Increase CPU &amp; RAM</h4>
Vertical scaling works…
until it doesn’t.
Yes, stronger hardware may buy time:
<ul>
<li>faster queries</li>
<li>larger memory buffers</li>
<li>more concurrent connections</li>
</ul>
But:
<ul>
<li>cost increases rapidly</li>
<li>scaling has hard limits</li>
<li>architectural bottlenecks remain</li>
</ul>
You’re treating symptoms, not workload design.

<h3>Correct Answer: C) Move analytics workloads to a Data Warehouse</h3>
<h4>Why C is correct</h4>
The real issue is that the same OLTP database is serving:
<ul>
<li>transactional workloads (checkout/orders/payments)</li>
<li>analytical workloads (aggregations, reports, dashboards)</li>
</ul>
These workloads have fundamentally different access patterns.
Transactional systems need:
<ul>
<li>low latency</li>
<li>fast writes</li>
<li>row-level operations</li>
<li>strict consistency</li>
</ul>
Analytics systems need:
<ul>
<li>large scans</li>
<li>aggregations</li>
<li>joins across millions of rows</li>
<li>long-running queries</li>
</ul>
A data warehouse separates OLAP from OLTP.
Typical architecture:
<ul>
<li>PostgreSQL handles transactional traffic</li>
<li>ETL/CDC pipeline streams data into:SnowflakeAmazon RedshiftBigQuery</li>
<li>reports/dashboards query the warehouse instead</li>
</ul>
This is the scalable long-term architecture.

<h3>Why A is close — but not enough</h3>
<h4>A) Read Replicas</h4>
This is a very common production optimization.
It helps because:
<ul>
<li>reporting queries move away from primary DB</li>
<li>read traffic gets distributed</li>
<li>primary DB handles fewer queries</li>
</ul>
But it does NOT solve:
<ul>
<li>expensive aggregations</li>
<li>heavy joins</li>
<li>analytical scan workloads</li>
<li>replica lag under high load</li>
</ul>
Eventually, reporting queries simply crush the replicas instead of the primary.
Read replicas are a scaling tactic.
A warehouse is a workload isolation strategy.
That distinction matters.

<h3>Why B helps temporarily — but misses the root problem</h3>
<h4>B) Redis Cache</h4>
Caching absolutely reduces load.
Great for:
<ul>
<li>repeated dashboard requests</li>
<li>hot metrics</li>
<li>frequently accessed reports</li>
</ul>
But caching breaks down when:
<ul>
<li>users need fresh data</li>
<li>filters become dynamic</li>
<li>reports are generated ad hoc</li>
<li>cache invalidation gets complex</li>
</ul>
Redis reduces repeated reads.
It does not separate analytical workloads from transactional systems.
This becomes a band-aid, not an architecture.