--- title: Day 26/30 AWS System Design Patterns | daily.dev description: A quiz-style breakdown examines a DynamoDB data modeling problem in a fintech transactions table with 800 million items. The core issue: a GSI with account_id... canonical: https://daily.dev/posts/day-26-30-aws-system-design-patterns-tdlth09dj twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Day 26/30 AWS System Design Patterns | daily.dev og:description: A quiz-style breakdown examines a DynamoDB data modeling problem in a fintech transactions table with 800 million items. The core issue: a GSI with account_id... og:url: https://daily.dev/posts/day-26-30-aws-system-design-patterns-tdlth09dj og:image: https://api.daily.dev/og/posts/TdLTh09dj.png og:image:alt: Day 26/30 AWS System Design Patterns 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. # Day 26/30 AWS System Design Patterns **[Joud Awad](https://daily.dev/sources/iac4jsbu0lv8wbsc85fsh)** · [@joudawad](https://daily.dev/joudawad) · 2 min read · 82 upvotes · 26 comments ## Summary A quiz-style breakdown examines a DynamoDB data modeling problem in a fintech transactions table with 800 million items. The core issue: a GSI with account_id as partition key and status as sort key creates a low-cardinality sort key problem, since status only has 3 values, so querying settled transactions on a high-volume account returns 4.1 million items causing DynamoDB to paginate through them until the request times out. The correct fix involves a composite sort key combining status and timestamp to scope queries by both dimensions. ## Content A fintech platform stores financial transactions in DynamoDB _(managed NoSQL database, queries are bounded by partition key — all items under one partition key are scanned if the key condition alone matches millions of items)_. The table has grown to 800 million items over 3 years. The primary access pattern: fetch all transactions for an account, sorted by timestamp, filtered by status (pending, settled, failed). The current table design uses `accountid`_ as the partition key and _`transactionid` as the sort key. The team added a GSI _(Global Secondary Index — secondary access pattern index, billed independently per write)_ with `account_id` as the partition key and `status` as the sort key — trying to support filtered queries by status per account. In production, the query `accountid = "acct123" AND status = "pending"` works. But queries for settled transactions on high-volume accounts time out. `acct_123` has 4.2 million transactions, of which 4.1 million are settled. What is the fundamental data modeling problem? A) The GSI sort key should be `createdat`_ instead of _`status`_ — this supports time-range queries, but removes the ability to filter by status at the key level entirely; you'd need a composite sort key like _`status#createdat` to scope queries by both dimensions simultaneously B) The GSI partition key is still `account_id` — hot partition throttling has migrated from the base table to the GSI; but the scenario shows timeouts, not `ProvisionedThroughputExceededException` errors — timeout on a 4.1M item result set is a query size problem, not a throttle C) DynamoDB cannot store 4.2 million items under a single partition key — the 10 GB per-partition storage limit applies; but typical transaction records are 1–2 KB each, so 4.2M items at 1 KB each is ~4 GB — under the limit; the timeout is from reading millions of matching items, not from storage overflow D) The query is doing a full partition scan filtered client-side — `status` has only 3 distinct values, so querying `status = "settled"` returns 4.1 million items from the GSI partition; DynamoDB reads all of them and returns up to 1 MB per call, causing the application to paginate through millions of records until it times out Answer in the comments. #SystemDesign #AWS #DynamoDB #DataModeling #BackendEngineering ## Community discussion Top comments from developers on daily.dev. **@joudawad** · 13 upvotes > A — Switching the sort key to `created_at` supports time-range queries but removes status filtering at the key level entirely. The correct fix encodes both dimensions — status and timestamp — into a composite sort key so you can scope by both simultaneously. **@joudawad** · 12 upvotes > C — The 10 GB limit applies per physical partition, and without a local secondary index DynamoDB splits a large item collection across additional partitions rather than rejecting writes — so 4.2 million transactions under one key (roughly 4–8 GB at 1–2 KB each) is legal and would remain legal well past 10 GB. The timeout is caused by reading millions of matching items across paginated calls, not by storage overflow. **@joudawad** · 12 upvotes > B — Hot partition throttling on the GSI is a real risk for high-volume accounts. But throttling manifests as `ProvisionedThroughputExceededException` errors — not query timeouts on large result sets. The scenario describes a query that completes but takes too long because it reads 4.1 million items. That is a query size problem, not a throttle. **@joudawad** · 8 upvotes > The answer is D. > > The GSI _(Global Secondary Index)_ has `accountid`_ as the partition key and _`status`_ as the sort key. A DynamoDB (managed NoSQL database) sort key supports range queries and exact matches. The query _`accountid = "acct_123" AND status = "settled"` is an exact match — it works. The problem is what happens after the key condition resolves. > > DynamoDB returns all items where `accountid = "acct123"` AND `status = "settled"`. For `acct_123`, that is 4.1 million items. DynamoDB reads them all from the GSI partition, returns up to 1 MB per call, and the application paginates... **@jliter85** · 6 upvotes > I’m going with D. The query works, but asking DynamoDB to walk through 4.1 million settled transactions is the real problem. This is a good example of why “the query is valid” and “the data model fits the access pattern” are two very different things. ## Similar posts on daily.dev - [DynamoDB crash course: part 2 – data model](https://daily.dev/posts/dynamodb-crash-course-part-2-data-model-67qh0m1qw) · Planet Python · 0 upvotes · 0 comments - [Scaling a Financial Reconciliation Pipeline With Serverless](https://daily.dev/posts/scaling-a-financial-reconciliation-pipeline-with-serverless-gynvqfhrg) · Cloud Native Now · 0 upvotes · 0 comments - [The Migrating Bottleneck](https://daily.dev/posts/the-migrating-bottleneck-ep4fzxzi1) · Webframp · 0 upvotes · 0 comments - [DynamoDB crash course: part 3 – design patterns](https://daily.dev/posts/dynamodb-crash-course-part-3-design-patterns-gx7z1mhbm) · Planet Python · 1 upvotes · 0 comments --- Tags: [#aws](https://daily.dev/tags/aws), [#career](https://daily.dev/tags/career), [#nosql](https://daily.dev/tags/nosql), [#aws-dynamodb](https://daily.dev/tags/aws-dynamodb) [View this post on daily.dev](https://daily.dev/posts/day-26-30-aws-system-design-patterns-tdlth09dj) ```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/day-26-30-aws-system-design-patterns-tdlth09dj","headline":"Day 26/30 AWS System Design Patterns","text":"A quiz-style breakdown examines a DynamoDB data modeling problem in a fintech transactions table with 800 million items. The core issue: a GSI with account_id as partition key and status as sort key creates a low-cardinality sort key problem, since status only has 3 values, so querying settled transactions on a high-volume account returns 4.1 million items causing DynamoDB to paginate through them until the request times out. The correct fix involves a composite sort key combining status and timestamp to scope queries by both dimensions.","url":"https://daily.dev/posts/day-26-30-aws-system-design-patterns-tdlth09dj","datePublished":"2026-08-22T17:58:26.016Z","dateModified":"2026-09-13T21:28:33.476Z","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":84960}},"image":"https://media.daily.dev/image/upload/s--mxdP5jqb--/f_auto/v1787421509/posts/TdLTh09dj?_a=BAMAMicg0","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":82},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":26}],"comment":[{"@type":"Comment","text":"A — Switching the sort key to created_at supports time-range queries but removes status filtering at the key level entirely. The correct fix encodes both dimensions — status and timestamp — into a composite sort key so you can scope by both simultaneously.","datePublished":"2026-08-22T17:58:48.458Z","url":"https://daily.dev/posts/TdLTh09dj#c-GJRB2KqH9","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":13}},{"@type":"Comment","text":"C — The 10 GB limit applies per physical partition, and without a local secondary index DynamoDB splits a large item collection across additional partitions rather than rejecting writes — so 4.2 million transactions under one key (roughly 4–8 GB at 1–2 KB each) is legal and would remain legal well past 10 GB. The timeout is caused by reading millions of matching items across paginated calls, not by storage overflow.","datePublished":"2026-08-22T17:58:59.600Z","url":"https://daily.dev/posts/TdLTh09dj#c-NbRRXWvLN","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":12}},{"@type":"Comment","text":"B — Hot partition throttling on the GSI is a real risk for high-volume accounts. But throttling manifests as ProvisionedThroughputExceededException errors — not query timeouts on large result sets. The scenario describes a query that completes but takes too long because it reads 4.1 million items. That is a query size problem, not a throttle.","datePublished":"2026-08-22T17:58:54.037Z","url":"https://daily.dev/posts/TdLTh09dj#c-oVQQXjdvR","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":12}},{"@type":"Comment","text":"The answer is D.\nThe GSI (Global Secondary Index) has accountid_ as the partition key and status as the sort key. A DynamoDB (managed NoSQL database) sort key supports range queries and exact matches. The query _accountid = "acct_123" AND status = "settled" is an exact match — it works. The problem is what happens after the key condition resolves.\nDynamoDB returns all items where accountid = "acct123" AND status = "settled". For acct_123, that is 4.1 million items. DynamoDB reads them all from the GSI partition, returns up to 1 MB per call, and the application paginates through millions of records. This behaves exactly like a full scan — and times out like one.\nThe root issue is status having only 3 values. Putting a low-cardinality attribute as the sort key on a GSI with account_id as the partition key does not reduce the data read for the dominant value (“settled”) — it just sorts by it. You still read every settled transaction for the account.\nThe correct design is a composite sort key: status#createdat_ (e.g., settled#2024-03-15T10:22:00Z). The query becomes: partition key = acct123, sort key beginswith "settled#2024-03" — which limits the scan to a time window instead of the entire settled history. Alternatively, a time-bucketed partition key like _accountid#YYYY-MM breaks large accounts across multiple partitions.","datePublished":"2026-08-22T17:59:31.295Z","url":"https://daily.dev/posts/TdLTh09dj#c-JBwh345Or","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":8}},{"@type":"Comment","text":"I’m going with D. The query works, but asking DynamoDB to walk through 4.1 million settled transactions is the real problem. This is a good example of why “the query is valid” and “the data model fits the access pattern” are two very different things.","datePublished":"2026-08-23T02:04:51.675Z","url":"https://daily.dev/posts/TdLTh09dj#c-d6uT9KEZ8","author":{"@type":"Person","name":"John Liter","url":"https://daily.dev/jliter85","image":"https://media.daily.dev/image/upload/s--DQ2HUrO4--/f_auto/v1785100315/avatars/avatar_yHf9cPdgTQEtokv6d8qhM?_a=BAMAMicg0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":6}}],"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":"Day 26/30 AWS System Design Patterns"}]} ```