Lesson 12.1• 9 min read

Requirements, estimates and the data model

The primer’s use cases and estimates: a write-heavy service with 2,000 transactions and 200 reads per second, 250 GB a month, and its data model.

What you will learn

  • State Mint.com's use cases and assumptions as the primer does, including its unusual write-to-read ratio.
  • Estimate its transaction rate, read rate and storage.
  • Lay out its data model and follow its asynchronous extraction flow.

The problem

The primer's Design Mint.com is a personal-finance service, an account aggregator: it connects to users' bank and card accounts, pulls in their transactions, categorizes them, and tracks spending against budgets.

Use cases (from the primer)

  • A user connects to a financial account.
  • The service extracts transactions from the account. It updates daily and categorizes transactions; users may override a category manually, and there is no automatic re-categorization. It also analyzes monthly spending by category.
  • The service recommends a budget. Users may set one manually, and it sends notifications when spending approaches or exceeds the budget.
  • The service has high availability.

Assumptions (from the primer)

  • Traffic is not evenly distributed.
  • Automatic daily updates apply only to users active in the past 30 days.
  • Adding or removing financial accounts is rare.
  • Budget notifications don't need to be instant.
  • 10 million users with 10 budget categories each (100 million budget items), and 50,000 sellers that determine categories.
  • 30 million financial accounts.
  • 5 billion transactions and 500 million read requests per month.
  • A 10:1 write-to-read ratio: "users make transactions daily, but few visit the site daily".

That last point is the opposite of most of this course. Here writes dominate.


Back-of-the-envelope estimates

Size per transaction (primer): user_id 8 bytes, created_at 5 bytes, seller 32 bytes, amount 5 bytes, about 50 bytes in all.

50 B×5×109=250 GB of new transactions per month,9 TB in 3 years50\text{ B} \times 5 \times 10^9 = 250\text{ GB of new transactions per month},\quad 9\text{ TB in 3 years}

With 400 requests per second ≈ 1 billion a month:

5×400=2,000 transactions per second on average5 \times 400 = 2{,}000 \text{ transactions per second on average}
0.5×400=200 read requests per second on average0.5 \times 400 = 200 \text{ read requests per second on average}

Both are higher at peak. The primer adds that 2,000 writes per second "might be tough for a single SQL Write Master-Slave". It points to federation, sharding, denormalization and SQL tuning, or moving some data to NoSQL.


Data model (from the primer)

accounts holds each linked account: id, created_at, last_update, account_url, account_login, a hashed account credential and user_id, with indexes on id, user_id and created_at.

transactions holds id, created_at, seller, amount (decimal) and user_id, with the same indexes.

monthly_spending holds id, month_year, category, amount and user_id, indexed on id and user_id.

Two refinements that later lessons rely on:

  • Store money as an exact type: a decimal, as the primer does, or integer cents. Never use binary floating point, which stores some values, such as 0.1, only approximately.
  • Add the provider's transaction id and the account to transactions, with a unique constraint on that pair. That is what makes ingestion idempotent (lesson 2).

The flow

  • The Accounts API stores a newly linked account.
  • Extraction is slow, so it runs asynchronously: the API puts a job on a queue such as Amazon SQS or RabbitMQ. A job is created when the user first links an account, when they refresh manually, and daily for users active in the past 30 days.
  • The Transaction Extraction Service pulls jobs from the queue and fetches the account's transactions, storing the raw results as log files in an object store.
  • It then uses the Category Service to categorize each transaction, and the Budget Service to update monthly spending by category. The Budget Service notifies users who are nearing or over budget.
  • Finally it updates the transactions and monthly_spending tables and notifies the user, again through a queue.
Users1× instanceLoad balancer2× instancesAccounts API2× instancesJob queue (extractionjobs)2× instancesTransactionextraction service3× instancesBanks1× instanceObject store (rawresults)1× instanceCategory service3× instancesBudget service3× instancesSQL database2× instancesNotification queue2× instancesUsers1× instanceLoad balancer2× instancesAccounts API2× instancesJob queue (extractionjobs)2× instancesTransactionextraction service3× instancesBanks1× instanceObject store (rawresults)1× instanceCategory service3× instancesBudget service3× instancesSQL database2× instancesNotification queue2× instances
Figure 1

The primer’s Mint design: the accounts API queues extraction jobs, and the extraction service fetches each account’s transactions, keeps the raw results, categorizes them, updates budgets and notifies users.

Follow one account. A user links it through the load balancer and the accounts API, which stores it in the SQL database and puts an extraction job on the job queue; jobs are also queued when the user refreshes and daily for users active in the past 30 days. The extraction service takes the job, fetches the account’s transactions from the bank, stores the raw results in the object store, asks the category service for each transaction’s category and the budget service to update monthly spending, writes the transactions and monthly spending to the SQL database, and notifies the user through the notification queue. The services are counted at the lab’s 1,000 a second per server, plus one spare: 2,000 transactions a second needs two, so three are drawn; the accounts API serves the 200 reads a second, so two. One load balancer (5,000 a second) and one queue node (10,000 a second) are enough, plus a spare each. The SQL database is a leader and a standby: at the lab’s 2,500 writes a second per database, 2,000 keeps the leader 80% busy, which is why the primer warns that one SQL leader may not keep up.

  • Users: 1 × Users
  • Load balancer: 2 × Load balancer
  • Accounts API: 2 × App server
  • Job queue (extraction jobs): 2 × Queue
  • Transaction extraction service: 3 × App server
  • Banks: 1 × Users
  • Object store (raw results): 1 × Object store
  • Category service: 3 × App server
  • Budget service: 3 × App server
  • SQL database: 2 × Relational database
  • Notification queue: 2 × Queue
  • Users sends requests to Load balancer
  • Load balancer sends requests to Accounts API
  • Accounts API sends requests to SQL database
  • Accounts API sends requests to Job queue (extraction jobs)
  • Job queue (extraction jobs) sends requests to Transaction extraction service
  • Transaction extraction service sends requests to Banks
  • Transaction extraction service sends requests to Object store (raw results)
  • Transaction extraction service sends requests to Category service
  • Transaction extraction service sends requests to Budget service
  • Transaction extraction service sends requests to SQL database
  • Transaction extraction service sends requests to Notification queue
  • Budget service sends requests to Notification queue

Key takeaways

  • Writes dominate here: users make transactions daily but few visit daily, a 10:1 write-to-read ratio.
  • 5 billion transactions a month at about 50 bytes is 250 GB a month and 2,000 writes a second on average, which may be too much for one SQL primary.
  • Store money as a decimal or integer cents, never binary floating point, and give each transaction the provider's ID with a unique constraint.
  • Extraction is slow, so it runs from a queue: fetch, store the raw results, categorize, update the budgets, then notify.
Sources2