💬
✉️
📸
▶️

Loading…

Choosing a database: SQL, documents, graphs, and search

Database Worlds
Database Worlds

Data Warehouses (Columnar OLAP)

Built for scanning billions of rows, not updating individual ones

Row-store databases organise data like a spreadsheet where you read across complete rows. Columnar stores flip this: they store all values for a single column together on disk. When an analytical query only needs 3 of 80 columns, the columnar engine reads 97% less data.

Analytical workloads are append-heavy and read-heavy. Data flows in from operational systems (ETL/streaming), then analysts run aggregate queries over historical records. The column-oriented layout enables extreme compression and vectorised CPU operations across entire columns — making aggregation 10–100× faster than row stores for typical BI queries.

Amazon Redshift

Cloud data warehousing at petabyte scale

Mental model

Imagine a warehouse with thousands of shelves, each storing one type of item. When you need to count all items from a certain category, you walk directly to that shelf — you never touch the others. Redshift organises your data exactly this way, on disk, in the cloud.

Why it exists

AWS launched Redshift in 2012 to replace expensive on-premise Oracle/Teradata data warehouses with cloud-native columnar storage. It made petabyte analytics accessible without infrastructure teams.

Shines at

  • BI dashboards on large historical data
  • ETL pipeline destinations
  • Ad-hoc analytical queries
  • Revenue and cohort analysis
  • AWS-native data architectures

Breaks down when

  • Real-time transactional workloads
  • Frequent small updates
  • Very low-latency query requirements (<100ms)
Performance note: Easily scans billions of rows per second using MPP (massively parallel processing) across a cluster of nodes.

Real-world use

Netflix, Lyft, and Intuit run petabyte-scale analytical pipelines on Redshift.

Google BigQuery

Serverless analytics at any scale

Mental model

BigQuery removes the concept of a server entirely. You upload your data into Google’s distributed columnar engine and query it with SQL. Google’s infrastructure automatically parallelises your query across thousands of workers, then bills you for the bytes scanned.

Why it exists

Emerged from Google’s internal Dremel system — designed to democratise petabyte querying without requiring infrastructure management. ‘Serverless’ analytics is BigQuery’s core identity.

Shines at

  • Serverless analytics with zero infrastructure management
  • Querying petabyte datasets in seconds
  • ML integration via SQL
  • Real-time ingestion via Streaming API
  • Google Cloud ecosystem

Breaks down when

  • Cost at scale if queries are poorly optimised (billed per TB scanned)
  • Low-latency OLTP requirements
  • Joins on non-partitioned enormous tables
Performance note: Can scan terabytes in seconds through automatic distributed parallelism. No cluster sizing required.

Real-world use

Twitter, Spotify, and Media.Monk use BigQuery for large-scale analytics and ML pipelines.

Snowflake

Separated compute and storage, perfect elasticity

Mental model

Snowflake separates where data lives (cloud storage: S3/GCS/Azure Blob) from where processing happens (virtual compute clusters). You can spin up 10 independent warehouses each querying the same data simultaneously — and pay only for the compute you use.

Why it exists

Built by ex-Oracle engineers who wanted cloud-native elastic analytics. The key insight: decouple storage from compute so teams can scale independently without data duplication.

Shines at

  • Multi-team analytics with shared data
  • Elastic compute scaling — scale up/down in seconds
  • Cross-cloud data sharing via Snowflake Marketplace
  • Semi-structured data (JSON, Parquet) analysis

Breaks down when

  • Very high write-throughput ingestion pipelines
  • Extremely low-latency dashboards requiring pre-aggregated caches
Performance note: Scales compute horizontally by adding warehouse nodes. Query concurrency is excellent — simultaneous workloads don’t contend.

Real-world use

Capital One, Adobe, and DoorDash use Snowflake as their primary data platform.

Your Path

Database
└─
Direct Jump
└─
Data Warehouse

Key Traits

✓ Columnar scans✓ Petabyte scale✓ SQL analytics✗ No row-level writes

Pick When

  • BI / reporting on large datasets
  • ETL pipeline destination
  • Historical trend analysis

Used By

SnowflakeBigQueryRedshiftClickHouse

Core Tradeoff

Fast aggregations — not a source of truth for app writes

Browse database families
Decision explanations
What operational model best fits this workload?

A platform records thousands of financial operations per minute. Each operation touches multiple tables — deducting from one account, crediting another, updating a balance ledger. Partial writes must never succeed. Queries need to join across user, account, and transaction tables in real time.

Transactional (OLTP)

OLTP = individual, frequent writes where every single transaction must be correct. OLAP is the opposite — it scans huge amounts of historical data to answer questions like 'what was total revenue last quarter?' When every write matters, you need OLTP.

Analytical (OLAP): OLAP databases are built for scanning large amounts of old data all at once (like monthly reports). They trade away per-row correctness for bulk scan speed — terrible for thousands of individual write operations per second.

What storage architecture optimises this workload?

You run nightly BI reports: total revenue by region, cohort retention, anomaly detection. Queries scan hundreds of millions of rows but nothing is ever updated after it's written. Tables have 80+ columns per row but queries only need a few at a time.

Data Warehouse (Columnar)

Columnar storage is a direct response to analytical query patterns. When you query 2 out of 80 columns across 500M rows, a row-store reads all 80 columns for every row — wasting 97% of disk I/O. A columnar store reads only those 2 columns, compresses them heavily, and aggregates them in a single scan pass.

RDBMS (Row Store): Row-oriented stores are fast at fetching complete records — but analytical queries scan billions of partial rows. An RDBMS touching 500M rows would deserialise all 80 columns per row even when you only need 2 — wasting enormous I/O bandwidth and making queries 10–100× slower than a columnar store.

Which transactional database family fits this workload?

Your app handles 8,000 transactions per second in a single region. Schema is stable. Queries join across 5–8 tables per request. No need to spread writes across multiple servers.

RDBMS

NewSQL’s distributed write scaling adds operational complexity only justified when you need to shard writes across regions — typically at 100k+ TPS or for global multi-master deployments. This workload fits well within primary-replica RDBMS capacity, making NewSQL overhead unnecessary.

NewSQL: NewSQL solves one problem: writing to multiple servers across regions simultaneously. Great at Google scale. But it adds latency overhead per write and is complex to operate. If one server handles your load, RDBMS is the simpler, faster choice.

What data organisation model fits a workload where every entity carries a different field set?

Your product catalog has 200M items. Electronics have wattage. Clothes have size and material. Furniture has dimensions. No two categories share the same fields. 40% of queries fetch a full item. 40% filter by attribute combinations. Schema changes every time a new category launches.

Document-Oriented

Document databases let each item carry exactly its own fields — no null columns for attributes a product doesn't have. You can add an index on any field and query it directly. Key-value stores can only look up by exact key — you'd need to build an entire query engine on top just to filter by attributes.

Key-Driven Access: Key-value = 'give me the item with key #12345'. That's the only query it supports. No 'show me all electronics under $100', no filtering by attributes. For a query-heavy product catalog you'd have to pre-compute every possible filter combination yourself — that's the application's job, not the database.

As multi-hop relationship traversal becomes the dominant query pattern, what architectural response is correct?

Your social app runs on MongoDB. A new feature needs: 'find friends-of-friends who bought the same product the user did' — that's 4 levels of relationships. These queries take 9–14 seconds and pin the CPU. The deeper the social graph grows, the worse it gets.

Graph Database

Graph databases store relationships as physical pointers. Each hop is O(1) — instant, no matter how big the database is. In MongoDB, every hop is another aggregation step that multiplies the work. At 4 hops on 10M users, that's billions of operations. Graph databases solve this in milliseconds.

Denormalised Documents: Embedding relationships in arrays works for simple things like 'a post's top 5 comments'. Social graphs are different — they're unbounded. A user's 3-hop network could be millions of people. Storing that as nested arrays uses gigabytes per user and the query is still slow. It doesn't fix the fundamental problem.

For this read-dominated, key-exact workload, which architecture serves the latency and access pattern?

A ride-hailing app tracks live GPS for 3M drivers (200k writes/sec, 1M reads/sec, must respond in under 2ms). Also serves surge pricing per city zone and manages session data per active ride. All lookups are exact key lookups — no filtering or joins needed.

In-Memory Key-Value Store

The bottleneck is read speed: under 2ms. In-memory key-value stores (like Redis) keep all data in RAM — reads are microseconds, not milliseconds. Column-family databases write to disk, so reads take 15–50ms. When you need blazing-fast reads, you need data in memory.

Distributed Column-Family: Cassandra excels at absorbing massive write storms — IoT sensors writing millions of events per second. The trade-off: reads hit disk, taking 15–40ms. Perfect for 'store everything and query later' data. Terrible for a live driver map that needs to respond in under 2ms.

When time is the organising dimension and every query is a time range, what database family is architecturally aligned?

80,000 connected vehicles each send 400 sensor readings per second (speed, battery, brakes, GPS...) = 32M data points per second. You need: 'plot battery over the last 4 hours for vehicle X', 'alert if brake pressure drops', 'fleet average fuel consumption over 7 days'. No text search. Every query is a time window.

Time-Series Database

Time-series databases are built exactly for sequential numeric measurements. They compress repeated similar values efficiently (battery at 80%, 81%, 80%... is stored as tiny deltas) and run time-window queries natively. Search engines index words — vehicle sensor data has no words. You'd pay enormous memory cost for indexes that are completely useless here.

Search / Log Analytics Engine: Search engines shine for text: log messages, error descriptions, user queries. They build word indexes. But vehicle telemetry is pure numbers — speed: 62, battery: 77%, brake: 94%. There's nothing to text-search. You'd waste memory on word indexes and miss the compression and time-range query features that time-series gives you.