loc bengaluru, ist | local --:-- srijanshukla18@gmail.com →
[post]/you-a-junior-infra-techie/data-store

data store

/ 4 min read· infra

How I explain data storage choices to juniors. The questions to ask about your data first, then the usual suspects: relational databases, object stores, warehouses, NoSQL, and file systems.

Disclaimer: I have found it a lil tough to translate my mental model of this stuff into words for juniors. I can explain it to them, but I am making up stuff on the fly when I do that. So I am writing it down properly, once.

You are a junior. You gotta store some data. For your application ofcourse.

Where do you put it though? Before you go picking a database coz you read about it on Hacker News, ask these 5 questions about the data first. Once you answer them, the tool basically picks itself.

1. Ask these questions first

  1. What shape is the data?
    • Strict rows and columns with relationships (users, orders, invoices)?
    • Messy / nested JSON documents or logs where fields keep changing?
    • Raw files / blobs (images, videos, PDFs, DB backups)?
  2. How are you reading and writing it?
    • Fetching or updating 1 specific user row in 5ms by ID?
    • Scanning 500 million rows once an hour to calculate revenue by city?
    • Dumping gigabytes of files and only fetching them back by key/URL?
  3. How much data are we talking about?
    • 10 GB? (Almost anything works, don’t overthink it, use Postgres.)
    • 10 TB? 1 PB?
  4. What latency do you need?
    • Single-digit milliseconds because a user is waiting on an API call?
    • Seconds or minutes for an internal analytics dashboard or batch job?
  5. Do you need strict ACID transactions?
    • If two requests update a wallet balance at the same time and something crashes mid-way, how bad is it?

2. The usual suspects

TypeWhat it’s good forExamplesMental model
Relational DB (OLTP)Structured data, ACID transactions, joins, fast point reads/writesPostgres, MySQL, AWS RDSDefault choice. Strict tables, foreign keys, indexes. Start here unless you have a real reason not to.
Object StoreFiles, images, videos, backups, logs, cheap bulk storageAWS S3, GCS, Cloudflare R2Giant key-value bucket for files. Dirt cheap, scales forever, but you can’t SELECT * WHERE inside a file without another tool on top.
Data Warehouse (OLAP)Heavy analytics queries scanning millions/billions of rowsClickHouse, Snowflake, BigQuery, RedshiftColumn-oriented storage. Terrible for updating 1 row at a time, amazing for summing 1 column across 100M rows.
NoSQL / Key-Value / DocumentHigh-throughput key lookups, caching, or flexible JSON documentsRedis, DynamoDB, MongoDBSkip joins and strict schemas in exchange for predictable single-key speed or horizontal scale.
Network File SystemWhen legacy apps or multiple servers literally need a mounted POSIX folderNFS, AWS EFSShared disk folder over the network. Slower and pricier per GB than S3, only use it when the app insists on real filesystem paths.

3. How to actually decide

  • Default for 90% of app data: Just use Postgres (or MySQL). Seriously. Modern Postgres handles relational tables and jsonb documents just fine up to terabytes if you index it properly.
  • Storing images, PDFs, backups, or raw event dumps: Never bloat your Postgres disk with binary files. Put the file in S3 and store only the S3 URL/path string in Postgres.
  • Analytics / dashboards slowing down your prod Postgres: Once GROUP BY reports start hurting your live user traffic, replicate the data into a columnar warehouse (ClickHouse / BigQuery / Snowflake) and run the heavy scans there.
  • Sub-millisecond cache, rate limiters, leaderboards, locks: Put Redis in front of your DB.

Things to keep in mind

  • Hot vs Cold data: Fast SSD-backed database storage is expensive. Don’t keep 3 years of dead audit logs sitting in your hot RDS instance - archive old data out to S3 (or Glacier).
  • Start with the problem, not the tool: Never be a hammer looking for a nail please.