Most developers learn one database and use it for everything. Then traffic grows, queries slow down, costs spike — and they wonder why.
The answer is usually simple: wrong database for the workload.
This guide breaks down the 5 types of SQL databases, how each one scales, and exactly when to use which.
🧱 SQL Basics
Skip this if you already know SQL well.
Tables store data in rows and columns. Types are strict — define them upfront, the database enforces them.
Foreign keys link tables instead of duplicating data. An orders table holds a user_id pointing at users. The database prevents orphaned records.
ACID is what makes SQL trustworthy:
| Letter | Meaning |
|---|---|
| Atomic | A transaction fully completes or fully rolls back — no partial writes |
| Consistent | Data is always in a valid state |
| Isolated | Concurrent transactions don't interfere with each other |
| Durable | Committed data survives crashes and restarts |
Indexes are the single biggest performance lever. Without one, a query on a million-row table scans every row. With one on the right column, it reads tens.
💡 Always index foreign keys and any column you filter or sort by.
🗂️ The 5 Types of SQL Databases
⚡ 1. OLTP — For Your App Backend
The everyday workhorse. Built for lots of small, fast reads and writes — logins, cart updates, payment records. Strong on concurrency and data consistency.
📈 Scales: Vertically first → read replicas → sharding
🎯 Use for: Web apps · SaaS · e-commerce · fintech
🛢️ DBs: PostgreSQL · MySQL · MariaDB · SQL Server · Oracle
📊 2. OLAP — For Analytics
Stores data by column instead of by row. A query summing revenue across 10 billion orders only reads the revenue column — not the whole row. Queries that take 20 minutes in PostgreSQL run in 3 seconds here.
📈 Scales: Massively parallel clusters · separate storage & compute
🎯 Use for: BI dashboards · financial reporting · data warehouses
🛢️ DBs: Snowflake · BigQuery · Redshift · ClickHouse · Databricks
📦 3. Embedded — No Server Required
Runs as a library inside your app. The database is just a file on disk. No server, no config, no network.
📈 Scales: Single device only (~1TB)
🎯 Use for: Mobile apps · desktop tools · local-first software
🛢️ DBs: SQLite · DuckDB
🌍 4. Distributed SQL — Global Scale, Still SQL
Full ACID transactions and SQL — but data is spread across multiple servers or regions. Any region can accept writes. Users in Tokyo don't round-trip to Virginia.
📈 Scales: Add nodes → auto-rebalances · multi-region active-active
🎯 Use for: Global apps · multi-region SaaS · geo-redundant finance
🛢️ DBs: CockroachDB · YugabyteDB · Cloud Spanner · TiDB · PlanetScale
☁️ 5. Cloud-Managed — No DBA Needed
Your cloud provider handles backups, patches, failover, and replication. You just connect and query.
📈 Scales: One-click vertical · read replicas in minutes · Aurora → 128TB
🎯 Use for: Startups · teams on AWS/GCP/Azure · apps needing managed HA
🛢️ DBs: Amazon RDS · Aurora · Azure SQL · Google Cloud SQL · Neon
📋 Scaling at a Glance
| Type | How it scales | Max scale | Complexity |
|---|---|---|---|
| OLTP | Vertical + read replicas | ~10TB | Medium |
| OLAP | Parallel compute clusters | Petabytes | Low |
| Embedded | Single device | ~1TB | Very low |
| Distributed SQL | Horizontal, multi-region | Petabytes | High |
| Cloud-managed | Managed vertical/HA | ~128TB | Very low |
🎯 How to Pick the Right One
📱 Building a mobile or desktop app → SQLite
No server, no config. Ships as a single file with your app. Used by iOS, Android, Chrome, WhatsApp, and Airbus aircraft.
Scaling path: Up to ~1TB on device → migrate to PostgreSQL + sync over API → Turso / Cloudflare D1 for edge.
⚠️ Not for multi-user concurrent writes — SQLite uses file-level locking.
🔍 Analysing local files (CSV, Parquet, JSON) → DuckDB
Query a 10GB CSV directly without importing it. Vectorised columnar execution — often 100× faster than SQLite for aggregations.
Scaling path: Single machine → MotherDuck for teams → push results to BigQuery.
⚠️ Not for transactional workloads — not built for frequent small writes.
🌐 Building a web or SaaS app → PostgreSQL or MySQL
Pick PostgreSQL when: Pick MySQL when:Click to compare them
Scaling path (both):
Single instance → Read replicas → Connection pooling → Table partitioning → Sharding
⚠️ Don't start with distributed SQL. The complexity isn't worth it until you have millions of active users.
📈 Dashboards over large datasets → Snowflake, BigQuery, ClickHouse, or Redshift
| Database | Sweet spot |
|---|---|
| Snowflake | Enterprise BI, SQL-familiar teams |
| BigQuery | GCP-native, serverless pay-per-query |
| Redshift | AWS-native, existing Postgres workloads |
| ClickHouse | Real-time analytics, highest ingestion speed |
Scaling path: Virtually unlimited — auto-scale per query (Snowflake/BigQuery) or dozens of self-hosted nodes (ClickHouse).
⚠️ Don't use these as your app's main database — slow for small individual writes.
🗺️ Low latency across multiple regions → CockroachDB, YugabyteDB, or Cloud Spanner
Full PostgreSQL/MySQL-compatible SQL, strongly consistent across regions. Users read from a local replica.
Scaling path: Add regions → nodes rebalance automatically → active-active across all regions.
⚠️ Expensive and operationally complex. Only reach for this when you genuinely have multi-region traffic.
🤖 No DBA on the team → RDS, Aurora, Azure SQL, or Cloud SQL
Fully managed — backups, failover, patching, all automatic. Neon adds serverless Postgres that scales to zero when idle.
Scaling path: One-click vertical → Multi-AZ HA → Aurora Serverless v2 for automatic compute scaling.
⚠️ Costs more than self-hosted at very high throughput. At scale, running your own cluster on EC2 is significantly cheaper.
🗺️ Quick Reference
| Scenario | Choose | Key strength | Managed option |
|---|---|---|---|
| Mobile / desktop | SQLite | Zero setup | Turso / D1 |
| Local analytics | DuckDB | Columnar speed | MotherDuck |
| Web app (complex) | PostgreSQL | Rich features | Supabase / Neon |
| Web app (simple) | MySQL | Easy to learn | PlanetScale |
| BI dashboards | Snowflake / BigQuery | Parallel compute | Native cloud |
| Real-time analytics | ClickHouse | Fastest ingestion | ClickHouse Cloud |
| Global multi-region | CockroachDB | Multi-region ACID | CockroachDB Dedicated |
| PG-compatible dist. | YugabyteDB | PostgreSQL API | YugabyteDB Managed |
| Google stack | Cloud Spanner | External consistency | GCP managed |
| MySQL distributed | TiDB | HTAP workloads | TiDB Cloud |
| AWS managed | Amazon Aurora | 128TB, 15 replicas | AWS managed |
| Azure / .NET | Azure SQL | Microsoft ecosystem | Azure managed |
| GCP stack | Google Cloud SQL | GCP integration | GCP managed |
🧪 Seed any database with realistic fake data
Once you've picked your database, try sql-faker — it generates dialect-correct INSERT statements for all 15 databases in this guide, with the right syntax per database (SERIAL, AUTO_INCREMENT, IDENTITY(1,1), TIMESTAMP_NTZ, MergeTree, and more).
# PostgreSQL
npx sql-faker --db postgresql --template users --rows 1000 --output seed.sql
# Push directly into a live database
npx sql-faker --db mysql --template orders --rows 500 \
--push "mysql://user:pass@localhost:3306/mydb"
# Your own schema
npx sql-faker --schema-example > schema.json
npx sql-faker --template custom --schema schema.json --db snowflake --rows 100
💡 The golden rule
Start simple. A well-indexed PostgreSQL or MySQL instance handles more traffic than most apps will ever need. Add complexity only when you have a proven need — the upgrade path is clear once you actually hit the wall.
Top comments (0)