Your events table on PostgreSQL 15 just crossed 200M rows.
Ingestion runs at 8K writes/sec from a Kafka consumer. P99 write latency was 12ms — now it’s 140ms and climbing.
The dashboard team is screaming. Their query filters on (tenant_id, event_type, created_at) and scans 40M rows per request. They want an index. Yesterday.
You check pg_stat_user_indexes. The table already has 4 indexes. Each new write touches every one of them. Add a fifth and your ingestion lag becomes ingestion failure.
But 92% of dashboard queries hit a single tenant’s last 7 days of signup events. A tiny slice of a huge table.
What do you do?
A) Add a composite B-tree on (tenant_id, event_type, created_at). Standard answer, covers the query, done.
B) Add a covering index with INCLUDE (user_id, payload) so the query never hits the heap. Index-only scan.
C) Stop adding indexes to the primary. Spin up a read replica and route the dashboard there. Keep writes lean.
D) Add a partial index — same columns, but WHERE event_type = ‘signup’ AND created_at &gt; now() - interval ‘7 days’. Only the hot slice gets indexed.
All four are real production patterns. Three of them keep your dashboard fast. Only one does that without burning your write throughput to the ground.
Pick one — A, B, C, or D — and tell me why. Full breakdown in the comments.
If this is the kind of tradeoff question your team argues about, share it — the debate is worth more than the post.
Drop your answer 👇
#30DaysOfSystemDesign #SystemDesign #PostgreSQL #DatabaseDesign

Smiley Face

Joud Awad

A PostgreSQL table with 200M rows is experiencing write latency spikes from 12ms to 140ms at 8K writes/sec, while dashboard queries scanning 40M rows demand faster reads. The post presents four indexing strategies as a system design challenge: a composite B-tree index, a covering index with INCLUDE columns, routing dashboard queries to a read replica, or a partial index targeting only the hot data slice (signup events from the last 7 days). The key tradeoff is balancing read performance against write throughput when the table already has 4 indexes.

12/30 Days System Design Questions!

Answer: D — Partial Index ✅
Here’s why, and why the other three trick smart engineers.
Why D wins (Partial Index)
92% of your queries hit one tenant’s last 7 days of signup events. A partial index only indexes rows that match its WHERE clause — everything else writes to the table without touching this index at all.
The math: if signup events are ~4% of the stream and the 7-day window covers ~5% of the table, your partial index covers roughly 0.2% of rows. Inserts that don’t match the predicate cost you nothing on this index. The write amplification is almost entirely gone.
PostgreSQL’s planner uses the partial index for any query whose WHERE clause is a subset of the index predicate. Your dashboard query gets full coverage. Reads stay fast, writes stay alive.
The catch: when the dashboard team wants 30 days instead of 7, you rebuild. That’s a known cost, not a hidden one.
<img src="https://media.daily.dev/image/upload/s--EtrEr5mz--/f_auto/v1779032470/ugc/content_4dbf76c7-b19d-4dee-b113-b66fa1c89c49?_a=BAMAMiWQ0" alt="ChatGPT Image May 17, 2026, 06_39_13 PM.png">

Why A is wrong (Full composite B-tree)
A (tenant_id, event_type, created_at) index will make the dashboard fast. It will also index every single one of your 8K writes/sec across all tenants and all event types. You’re paying full write cost to serve a query that hits 0.2% of the data. That’s the definition of a bad tradeoff.

Why C is wrong (Read replica)
The indexes still have to exist somewhere. Put them on the replica and it falls behind replaying WAL while maintaining indexes. Put them on the primary and you’re back to the original problem.
Read replicas solve “my primary can’t serve all the reads.” They don’t solve “my write path is bottlenecked by index maintenance.”

Also, it would mean a lot to me if you could support my content and stay in touch 🙏
<ul>
<li>YouTube: <a href="https://www.youtube.com/@system-design-lab" target="_blank" rel="noopener nofollow">https://www.youtube.com/@system-design-lab</a></li>
<li>LinkedIn: <a href="https://www.linkedin.com/in/joud-awad/" target="_blank" rel="noopener nofollow">https://www.linkedin.com/in/joud-awad/</a></li>
<li>Medium Blog: <a href="https://joudwawad.medium.com/" target="_blank" rel="noopener nofollow">https://joudwawad.medium.com/</a></li>
<li>Substack: <a href="https://joudawad.substack.com/" target="_blank" rel="noopener nofollow">https://joudawad.substack.com/</a></li>
</ul>

Why B is the trap (Covering Index with INCLUDE)
This fools senior engineers more than any other option. An index-only scan looks gorgeous in EXPLAIN. The problem is what you stuffed into INCLUDE.
That payload column is probably a JSONB blob averaging a few KB. Every insert now writes the row to the heap and writes a fat index entry containing the full payload. Every UPDATE to user_id or payload rewrites the index entry. You didn’t reduce write cost — you multiplied it.
At 8K writes/sec on a table that’s already index-heavy, a covering index with JSONB inside is how you turn 140ms p99 into 800ms p99. INCLUDE is the right call for narrow columns on read-heavy tables. This is neither.