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
- 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)?
- 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?
- How much data are we talking about?
- 10 GB? (Almost anything works, don’t overthink it, use Postgres.)
- 10 TB? 1 PB?
- 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?
- 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
| Type | What it’s good for | Examples | Mental model |
|---|---|---|---|
| Relational DB (OLTP) | Structured data, ACID transactions, joins, fast point reads/writes | Postgres, MySQL, AWS RDS | Default choice. Strict tables, foreign keys, indexes. Start here unless you have a real reason not to. |
| Object Store | Files, images, videos, backups, logs, cheap bulk storage | AWS S3, GCS, Cloudflare R2 | Giant 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 rows | ClickHouse, Snowflake, BigQuery, Redshift | Column-oriented storage. Terrible for updating 1 row at a time, amazing for summing 1 column across 100M rows. |
| NoSQL / Key-Value / Document | High-throughput key lookups, caching, or flexible JSON documents | Redis, DynamoDB, MongoDB | Skip joins and strict schemas in exchange for predictable single-key speed or horizontal scale. |
| Network File System | When legacy apps or multiple servers literally need a mounted POSIX folder | NFS, AWS EFS | Shared 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
jsonbdocuments 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 BYreports 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.