Handbook logo

Handbook

Quick tech ticks and comparisons

Database Types — A Complete Guide

Category: Data

The Big Picture

Not all data is the same, so not all databases are the same. Choosing the right database is like choosing the right tool for a job.

flowchart TD DB["🗄️ Databases"] DB --> REL["📊 Relational (RDBMS)
Tables with rows & columns"] DB --> COL["📑 Columnar
Optimized for analytics"] DB --> NOSQL["📦 NoSQL
Flexible structures"] DB --> VEC["🎯 Vector
AI & similarity search"] NOSQL --> DOC["Document"] NOSQL --> KV["Key-Value"] NOSQL --> GRAPH["Graph"] NOSQL --> WIDE["Wide-Column"] style DB fill:#dbeafe,stroke:#2563eb style REL fill:#d1fae5,stroke:#059669 style COL fill:#fef3c7,stroke:#d97706 style NOSQL fill:#fce7f3,stroke:#db2777 style VEC fill:#e0e7ff,stroke:#6366f1

1. Relational Databases (RDBMS)

What is it?

Data is organized in tables with rows and columns, linked together by relationships. Uses SQL (Structured Query Language) to query data.

flowchart LR subgraph customers["👤 Customers Table"] C1["id | name | email"] C2["1 | John | john@email.com"] C3["2 | Jane | jane@email.com"] end subgraph orders["🛒 Orders Table"] O1["id | customer_id | total"] O2["101 | 1 | $50"] O3["102 | 1 | $75"] O4["103 | 2 | $30"] end customers --> |"customer_id
links to id"| orders style customers fill:#d1fae5,stroke:#059669 style orders fill:#dbeafe,stroke:#2563eb

Real-World Analogy

Think of an Excel spreadsheet with multiple sheets that reference each other. Customer sheet links to Orders sheet via Customer ID.

Key Features

Feature Description
ACID Compliant Guarantees data consistency
Structured Schema Must define table structure upfront
Relationships Tables link via foreign keys
SQL Standard query language
Database Best For Notes
PostgreSQL General purpose, complex queries Open source, feature-rich
MySQL Web applications Simple, fast, widely used
SQL Server Enterprise, Microsoft stack Strong BI integration
Oracle Large enterprises Expensive, powerful
SQLite Embedded, mobile apps File-based, no server needed

When to Use ✅

  • Traditional business applications
  • Financial systems (need ACID transactions)
  • E-commerce (orders, inventory, customers)
  • When data has clear relationships
  • When you need complex joins and queries

When to Avoid ❌

  • Rapidly changing schema
  • Massive scale (billions of rows)
  • Unstructured data (logs, documents)
  • Real-time analytics on huge datasets

2. Columnar Databases

What is it?

Instead of storing data row by row, columnar databases store data column by column. This makes analytical queries incredibly fast.

flowchart LR subgraph row["Row-Based Storage (RDBMS)"] R1["John, 25, NYC"] R2["Jane, 30, LA"] R3["Bob, 28, Chicago"] end subgraph col["Column-Based Storage"] C1["Names: John, Jane, Bob"] C2["Ages: 25, 30, 28"] C3["Cities: NYC, LA, Chicago"] end style row fill:#fecaca,stroke:#dc2626 style col fill:#d1fae5,stroke:#059669

Why Does This Matter?

flowchart TD Q["Query: What's the average age?"] subgraph row_scan["Row Storage"] RS["Must read ALL data
John,25,NYC,Jane,30,LA,Bob,28,Chicago
😰 Slow for analytics"] end subgraph col_scan["Column Storage"] CS["Only read 'Age' column
25, 30, 28
🚀 Super fast!"] end Q --> row_scan Q --> col_scan style row_scan fill:#fecaca,stroke:#dc2626 style col_scan fill:#d1fae5,stroke:#059669

Real-World Analogy

Imagine a library. Row storage is like books on shelves (you grab the whole book). Column storage is like an index at the back — you can quickly find all pages mentioning “history” without reading every book.

Key Features

Feature Benefit
Fast aggregations SUM, AVG, COUNT are blazing fast
Compression Similar values compress well
Analytics-optimized Perfect for BI and reporting
Scan efficiency Only reads columns you need
Database Best For Notes
Amazon Redshift Cloud data warehouse AWS native, scalable
Google BigQuery Serverless analytics Pay per query, massive scale
Snowflake Multi-cloud warehouse Separates storage & compute
ClickHouse Real-time analytics Open source, very fast
Apache Parquet File format Not a DB, but columnar storage format

When to Use ✅

  • Data warehouses
  • Business intelligence / reporting
  • Analytics dashboards
  • Aggregating millions/billions of rows
  • Historical data analysis

When to Avoid ❌

  • Frequent single-row updates
  • OLTP (transactional) workloads
  • Small datasets
  • Need for real-time writes

3. NoSQL Databases

What is it?

“Not Only SQL” — a family of databases that don’t use traditional tables. Designed for flexibility, scale, and specific use cases.

flowchart TD NOSQL["🗄️ NoSQL Types"] NOSQL --> DOC["📄 Document
JSON-like documents"] NOSQL --> KV["🔑 Key-Value
Simple key → value pairs"] NOSQL --> WIDE["📊 Wide-Column
Rows with dynamic columns"] NOSQL --> GRAPH["🕸️ Graph
Nodes and relationships"] style NOSQL fill:#fce7f3,stroke:#db2777 style DOC fill:#dbeafe,stroke:#2563eb style KV fill:#d1fae5,stroke:#059669 style WIDE fill:#fef3c7,stroke:#d97706 style GRAPH fill:#e0e7ff,stroke:#6366f1

3a. Document Databases

Store data as JSON-like documents. Each document can have a different structure.

flowchart LR subgraph doc1["Document 1"] D1["{
'name': 'John',
'age': 25,
'hobbies': ['reading']
}"] end subgraph doc2["Document 2"] D2["{
'name': 'Jane',
'email': 'jane@mail.com',
'address': {
'city': 'NYC'
}
}"] end style doc1 fill:#dbeafe,stroke:#2563eb style doc2 fill:#dbeafe,stroke:#2563eb

Notice: Documents have different fields — that’s okay!

Database Best For Notes
MongoDB General purpose NoSQL Most popular, flexible
Couchbase Mobile, edge computing Built-in sync
Amazon DocumentDB MongoDB-compatible on AWS Managed service
Firebase Firestore Mobile/web apps Real-time sync

When to Use ✅

  • Content management systems
  • User profiles with varying attributes
  • Product catalogs
  • Mobile app backends
  • Rapid prototyping

3b. Key-Value Databases

The simplest database: store a key and its value. Like a giant dictionary/hashmap.

flowchart LR subgraph kv["Key-Value Store"] K1["user:1001"] --> V1["'{name: John, age: 25}'"] K2["session:abc123"] --> V2["'{user_id: 1001, expires: ...}'"] K3["cache:homepage"] --> V3["'...'"] end style kv fill:#d1fae5,stroke:#059669
Database Best For Notes
Redis Caching, sessions In-memory, super fast
Amazon DynamoDB Serverless, scalable Fully managed
Memcached Simple caching Lightweight
etcd Configuration storage Used by Kubernetes

When to Use ✅

  • Caching (sessions, API responses)
  • Real-time leaderboards
  • Shopping carts
  • Rate limiting
  • Pub/sub messaging

3c. Wide-Column Databases

Like a table, but each row can have different columns. Designed for massive scale.

flowchart TD subgraph wide["Wide-Column Store"] R1["Row 1: name='John', age=25, city='NYC'"] R2["Row 2: name='Jane', email='j@mail.com'"] R3["Row 3: name='Bob', age=30, country='USA', zip='10001'"] end style wide fill:#fef3c7,stroke:#d97706
Database Best For Notes
Apache Cassandra Massive scale, high availability Used by Netflix, Apple
HBase Hadoop ecosystem Built on HDFS
ScyllaDB Cassandra-compatible Faster, C++ based
Google Bigtable Huge datasets Powers Google services

When to Use ✅

  • Time-series data (IoT, metrics)
  • Logging at massive scale
  • Messaging systems
  • Recommendation engines
  • When you need “always-on” availability

3d. Graph Databases

Store data as nodes (entities) and edges (relationships). Perfect for connected data.

flowchart LR A["👤 Alice"] --> |"FRIENDS_WITH"| B["👤 Bob"] B --> |"WORKS_AT"| C["🏢 TechCorp"] A --> |"WORKS_AT"| C B --> |"FRIENDS_WITH"| D["👤 Carol"] D --> |"LIKES"| E["🎬 Inception"] A --> |"LIKES"| E style A fill:#dbeafe,stroke:#2563eb style B fill:#dbeafe,stroke:#2563eb style C fill:#d1fae5,stroke:#059669 style D fill:#dbeafe,stroke:#2563eb style E fill:#fef3c7,stroke:#d97706
Database Best For Notes
Neo4j General graph use cases Most popular, Cypher query language
Amazon Neptune Managed graph on AWS Supports multiple models
ArangoDB Multi-model (graph + document) Flexible
TigerGraph Enterprise analytics High performance

When to Use ✅

  • Social networks (friends, followers)
  • Recommendation engines
  • Fraud detection
  • Knowledge graphs
  • Network/IT infrastructure mapping

4. Vector Databases

What is it?

Store data as vectors (lists of numbers) that represent meaning. Used for AI/ML applications to find similar items.

flowchart LR subgraph embed["Converting to Vectors"] T1["'Happy dog playing'"] --> V1["[0.2, 0.8, 0.1, ...]"] T2["'Joyful puppy running'"] --> V2["[0.21, 0.79, 0.12, ...]"] T3["'Sad cat sleeping'"] --> V3["[0.7, 0.1, 0.9, ...]"] end subgraph similar["Similarity Search"] Q["Query: 'Cheerful pet'"] Q --> R["Most similar:
1. Happy dog playing ✅
2. Joyful puppy running ✅"] end style V1 fill:#d1fae5,stroke:#059669 style V2 fill:#d1fae5,stroke:#059669 style V3 fill:#fecaca,stroke:#dc2626

Real-World Analogy

Imagine a map where similar things are close together. “Happy dog” and “Joyful puppy” are neighbors on the map, while “Sad cat” is far away.

How It Works

sequenceDiagram participant U as 👤 User participant A as 🤖 AI Model participant V as 🎯 Vector DB U->>A: "Find similar products to this image" A->>A: Convert image to vector [0.2, 0.5, ...] A->>V: Search for nearest vectors V->>V: Find closest matches V-->>A: Return similar items A-->>U: "Here are similar products!"
Database Best For Notes
Pinecone Managed vector search Easy to use, serverless
Weaviate AI-native search Open source, GraphQL API
Milvus Large-scale similarity Open source, highly scalable
Qdrant Performance-focused Rust-based, fast
Chroma LLM applications Simple, Python-native
pgvector PostgreSQL extension Add vectors to existing Postgres

When to Use ✅

  • Semantic search (search by meaning, not keywords)
  • Recommendation systems
  • Image/video similarity
  • LLM applications (ChatGPT-like apps)
  • Duplicate detection
  • Anomaly detection

When to Avoid ❌

  • Exact match queries
  • Traditional CRUD operations
  • When you don’t have embeddings/vectors

Comparison Chart

flowchart TD subgraph compare["Choose Your Database"] Q1{"What's your
primary need?"} Q1 --> |"Transactions &
Relationships"| RDBMS["✅ RDBMS
PostgreSQL, MySQL"] Q1 --> |"Analytics on
huge data"| COLUMNAR["✅ Columnar
Snowflake, BigQuery"] Q1 --> |"Flexible schema
or specific pattern"| NOSQL["✅ NoSQL
(see subtypes)"] Q1 --> |"AI/Similarity
Search"| VECTOR["✅ Vector
Pinecone, Milvus"] end style RDBMS fill:#d1fae5,stroke:#059669 style COLUMNAR fill:#fef3c7,stroke:#d97706 style NOSQL fill:#fce7f3,stroke:#db2777 style VECTOR fill:#e0e7ff,stroke:#6366f1

Quick Reference Table

Type Structure Best For Popular Options Query Language
RDBMS Tables, rows, columns Transactions, relationships PostgreSQL, MySQL SQL
Columnar Column-oriented Analytics, aggregations Snowflake, BigQuery SQL
Document JSON documents Flexible schemas MongoDB, Firestore Custom/MQL
Key-Value Key → Value pairs Caching, sessions Redis, DynamoDB Simple get/set
Wide-Column Flexible columns Time-series, IoT Cassandra, HBase CQL, custom
Graph Nodes & edges Relationships Neo4j, Neptune Cypher, Gremlin
Vector Numerical vectors AI, similarity Pinecone, Milvus Custom APIs

Decision Flowchart

flowchart TD START["🤔 What are you building?"] START --> Q1{"Need ACID
transactions?"} Q1 --> |"Yes"| RDBMS["Use RDBMS
(PostgreSQL)"] Q1 --> |"No"| Q2 Q2{"Analytics on
big data?"} Q2 --> |"Yes"| COLUMNAR["Use Columnar
(Snowflake/BigQuery)"] Q2 --> |"No"| Q3 Q3{"Need caching
or sessions?"} Q3 --> |"Yes"| REDIS["Use Key-Value
(Redis)"] Q3 --> |"No"| Q4 Q4{"Complex
relationships?"} Q4 --> |"Yes"| GRAPH["Use Graph
(Neo4j)"] Q4 --> |"No"| Q5 Q5{"AI/Semantic
search?"} Q5 --> |"Yes"| VECTOR["Use Vector
(Pinecone)"] Q5 --> |"No"| Q6 Q6{"Flexible
documents?"} Q6 --> |"Yes"| MONGO["Use Document
(MongoDB)"] Q6 --> |"No"| Q7 Q7{"Massive scale
time-series?"} Q7 --> |"Yes"| CASSANDRA["Use Wide-Column
(Cassandra)"] Q7 --> |"No"| DEFAULT["Start with
PostgreSQL"] style START fill:#f1f5f9,stroke:#64748b style RDBMS fill:#d1fae5,stroke:#059669 style COLUMNAR fill:#fef3c7,stroke:#d97706 style REDIS fill:#dbeafe,stroke:#2563eb style GRAPH fill:#fce7f3,stroke:#db2777 style VECTOR fill:#e0e7ff,stroke:#6366f1 style MONGO fill:#dbeafe,stroke:#2563eb style CASSANDRA fill:#fef3c7,stroke:#d97706 style DEFAULT fill:#d1fae5,stroke:#059669

Common Combinations

In real-world systems, you often use multiple databases together:

flowchart LR subgraph app["🌐 Modern Application"] PG["PostgreSQL
Core data"] REDIS["Redis
Caching"] MONGO["MongoDB
User content"] PINECONE["Pinecone
Search"] SNOWFLAKE["Snowflake
Analytics"] end API["🔌 API Layer"] API --> PG API --> REDIS API --> MONGO API --> PINECONE PG --> |"ETL"| SNOWFLAKE style PG fill:#d1fae5,stroke:#059669 style REDIS fill:#fecaca,stroke:#dc2626 style MONGO fill:#dbeafe,stroke:#2563eb style PINECONE fill:#e0e7ff,stroke:#6366f1 style SNOWFLAKE fill:#fef3c7,stroke:#d97706
Database Role in the Stack
PostgreSQL Source of truth for business data
Redis Cache frequently accessed data
MongoDB Store user-generated content
Pinecone Power AI search features
Snowflake Analytics and reporting

Summary

If You Need… Use This Example DB
Transactions & relationships RDBMS PostgreSQL
Fast analytics on big data Columnar Snowflake
Flexible JSON documents Document DB MongoDB
Super fast caching Key-Value Redis
Massive scale writes Wide-Column Cassandra
Relationship traversal Graph DB Neo4j
AI/Similarity search Vector DB Pinecone

Pro Tip: When in doubt, start with PostgreSQL. It’s versatile, reliable, and can handle most use cases. Add specialized databases as your needs grow!