When you’re building a product, your database is not just a storage engine—it’s a performance lever, a scalability factor, and a long-term architectural commitment. In this post, we’ll break down the main types of databases, where each shines, and what can go wrong if you pick the wrong one.
1. Relational Databases (PostgreSQL, MySQL)
Best for: Financial systems, CRMs, e-commerce platforms, subscription-based apps.
How it works:
Relational databases store data in tables with rows and columns. You define strict schemas, use SQL for querying, and rely on JOINs to relate tables.
Real-world example:
An e-commerce platform (like Shopify or WooCommerce) stores users, orders, products, and reviews in separate but related tables. You want strong consistency: if a user deletes their account, all related orders and reviews should update or be removed.
Why not something else?
Using MongoDB here would be messy. JOINs are awkward or impossible, data duplication becomes common, and ensuring referential integrity is painful. You also lose strong ACID guarantees.
2. File-based Databases (SQLite)
Best for: Mobile apps, CLI tools, small internal tools, desktop applications.
How it works:
All data is stored in a single file on disk. No network, no server. Simple and embedded.
Real-world example:
A note-taking mobile app like Bear or Obsidian stores user notes locally. No need for a server. SQLite is fast, lightweight, and doesn’t require configuration.
Why not something else?
Using PostgreSQL for a single-user mobile app is overkill. It would need a server, more resources, and be harder to ship and maintain.
3. NoSQL Databases (MongoDB, Firebase, DynamoDB)
Best for: Realtime apps, flexible data models, user-generated content, high write loads.
How it works:
NoSQL systems are schema-less or have flexible schemas. They store data as documents (MongoDB), key-value pairs (DynamoDB), or graphs.
Real-world example:
A chat app like Discord stores messages as documents in MongoDB. The message structure can change over time (e.g., reactions, attachments), and strict schemas would slow down iteration.
Why not something else?
Using PostgreSQL would require schema migrations for every new feature, slow down dev speed, and scale less gracefully under write-heavy workloads.
4. Graph Databases (Neo4j, Dgraph)
Best for: Social networks, recommendation engines, fraud detection, complex relationships.
How it works:
Data is stored as nodes and relationships (edges). Queries explore paths through the graph, like “friends of friends” or “who bought what together.”
Real-world example:
A social network like LinkedIn stores user connections as a graph. Finding 2nd or 3rd-degree connections is fast and natural.
Why not something else?
In a relational DB, recursive JOINs for deep relationships are slow and complex. Queries get expensive, and performance drops quickly as the graph grows.
5. Time-Series Databases (InfluxDB, TimescaleDB)
Best for: Monitoring, IoT sensors, analytics, financial price data.
How it works:
Optimized for time-based data. You store values with timestamps and query ranges, aggregates, and trends.
Real-world example:
A DevOps team tracks CPU usage, memory, and disk stats every second with InfluxDB. Dashboards pull the last 24 hours, week, or month.
Why not something else?
A traditional RDBMS can store timestamps, but it lacks efficient compression, downsampling, or retention policies. Queries are slower and storage costs rise.
6. In-Memory Key-Value Stores (Redis, Memcached)
Best for: Caching, sessions, counters, real-time leaderboards.
How it works:
Data lives in RAM. Extremely fast read/write (sub-millisecond). Optional persistence (Redis).
Real-world example:
A web app caches logged-in user profiles in Redis. Every API call fetches the profile from Redis in microseconds instead of querying Postgres.
Why not something else?
If you hit the DB for every request, response time suffers, DB load increases, and you lose scalability. Caching cuts costs and latency.
Final Thoughts: Hybrid Architecture is the Norm
Most mature systems use multiple databases:
- PostgreSQL for core business data
- Redis for caching
- MongoDB or ElasticSearch for flexible search or logs
- InfluxDB for time series monitoring
Choosing the right tool doesn’t mean picking one DB. It means picking the best combination to meet your needs. Think long-term, test assumptions, and build smart.
Album of the day:
