--- title: 12/30 Days System Design Questions! | daily.dev description: 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... canonical: https://daily.dev/posts/12-30-days-system-design-questions--iebswke3k twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: 12/30 Days System Design Questions! | daily.dev og:description: 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... og:url: https://daily.dev/posts/12-30-days-system-design-questions--iebswke3k og:image: https://api.daily.dev/og/posts/IEbSWke3K.png og:image:alt: 12/30 Days System Design Questions! og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # 12/30 Days System Design Questions! **[Joud Awad](https://daily.dev/sources/iac4jsbu0lv8wbsc85fsh)** · [@joudawad](https://daily.dev/joudawad) · 2 min read · 237 upvotes · 18 comments ## Summary 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. ## Content 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 > 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 ## Community discussion Top comments from developers on daily.dev. **@joudawad** · 32 upvotes > **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... **@joudawad** · 3 upvotes > **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." **@joudawad** · 3 upvotes > **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. **@joudawad** · 2 upvotes > Also, it would mean a lot to me if you could support my content and stay in touch 🙏 > > - YouTube: [https://www.youtube.com/@system-design-lab](https://www.youtube.com/@system-design-lab) > - LinkedIn: [https://www.linkedin.com/in/joud-awad/](https://www.linkedin.com/in/joud-awad/) > - Medium Blog: [https://joudwawad.medium.com/](https://joudwawad.medium.com/) > - Substack: [https://joudawad.substack.com/](https://joudawad.substack.com/) **@joudawad** · 2 upvotes > **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... --- Tags: [#career](https://daily.dev/tags/career), [#postgresql](https://daily.dev/tags/postgresql), [#kafka](https://daily.dev/tags/kafka) [View this post on daily.dev](https://daily.dev/posts/12-30-days-system-design-questions--iebswke3k) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"DiscussionForumPosting","mainEntityOfPage":"https://daily.dev/posts/12-30-days-system-design-questions--iebswke3k","headline":"12/30 Days System Design Questions!","text":"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.","url":"https://daily.dev/posts/12-30-days-system-design-questions--iebswke3k","datePublished":"2026-05-17T15:40:42.742Z","dateModified":"2026-05-17T15:41:16.688Z","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0","description":"Principal Solution Architecture ","worksFor":{"@type":"Organization","name":"Metalab","logo":"https://www.google.com/s2/favicons?domain=metalab.com&sz=128"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"EndorseAction"},"userInteractionCount":81870}},"image":"https://media.daily.dev/image/upload/s--96FCQclk--/f_auto/v1779032449/posts/IEbSWke3K?_a=BAMAMiWQ0","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":237},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":18}],"comment":[{"@type":"Comment","text":"Answer: D — Partial Index ✅\nHere’s why, and why the other three trick smart engineers.\nWhy D wins (Partial Index)\n92% 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.\nThe 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.\nPostgreSQL’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.\nThe catch: when the dashboard team wants 30 days instead of 7, you rebuild. That’s a known cost, not a hidden one.","datePublished":"2026-05-17T15:41:15.786Z","url":"https://daily.dev/posts/IEbSWke3K#c-pRKusdDsr","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":32}},{"@type":"Comment","text":"Why C is wrong (Read replica)\nThe 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.\nRead replicas solve “my primary can’t serve all the reads.” They don’t solve “my write path is bottlenecked by index maintenance.”","datePublished":"2026-05-17T15:41:34.118Z","url":"https://daily.dev/posts/IEbSWke3K#c-otOrjLri7","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":3}},{"@type":"Comment","text":"Why A is wrong (Full composite B-tree)\nA (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.","datePublished":"2026-05-17T15:41:26.278Z","url":"https://daily.dev/posts/IEbSWke3K#c-vMN9e8tAY","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":3}},{"@type":"Comment","text":"Also, it would mean a lot to me if you could support my content and stay in touch 🙏\n\nYouTube: https://www.youtube.com/@system-design-lab\nLinkedIn: https://www.linkedin.com/in/joud-awad/\nMedium Blog: https://joudwawad.medium.com/\nSubstack: https://joudawad.substack.com/","datePublished":"2026-05-17T15:41:59.485Z","url":"https://daily.dev/posts/IEbSWke3K#c-EUwKu0pJm","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":2}},{"@type":"Comment","text":"Why B is the trap (Covering Index with INCLUDE)\nThis fools senior engineers more than any other option. An index-only scan looks gorgeous in EXPLAIN. The problem is what you stuffed into INCLUDE.\nThat 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.\nAt 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.","datePublished":"2026-05-17T15:41:21.232Z","url":"https://daily.dev/posts/IEbSWke3K#c-xptjlOCPc","author":{"@type":"Person","name":"Joud Awad","url":"https://daily.dev/joudawad","image":"https://media.daily.dev/image/upload/s--dOB9RaXY--/f_auto/v1773320801/avatars/avatar_iaC4JsBU0lV8wBsc85fSh?_a=BAMAMiiu0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":2}}],"isPartOf":{"@type":"WebPage","url":"https://daily.dev/sources/iac4jsbu0lv8wbsc85fsh","name":"Joud Awad"}} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Joud Awad","item":"https://daily.dev/sources/iac4jsbu0lv8wbsc85fsh"},{"@type":"ListItem","position":3,"name":"12/30 Days System Design Questions!"}]} ```