Relational to vector — 15 database categories, query languages, scaling strategies.
Database Type Reference
| Type | Model | Notable Examples | Query Language | Best For | Scaling Approach |
|---|---|---|---|---|---|
| Relational (SQL) | Tables + rows + FK | PostgreSQL, MySQL, SQLite, MSSQL, Oracle | SQL | ACID transactions, structured data, reports | Vertical + read replicas |
| Document | JSON / BSON trees | MongoDB, CouchDB, Firestore, RavenDB | MQL, JSON filter | Flexible schema, semi-structured, CMS, catalogs | Horizontal sharding |
| Key-Value | K → V pairs | Redis, DynamoDB, Memcached, Riak | GET / SET / DEL | Cache, sessions, counters, leaderboards | Horizontal (consistent hashing) |
| Wide-Column | Column families | Cassandra, HBase, BigTable, ScyllaDB | CQL, HQL | Time-series, IoT, logs, write-heavy workloads | Horizontal (ring topology) |
| Graph | Nodes + edges + properties | Neo4j, ArangoDB, Amazon Neptune, TigerGraph | Cypher, Gremlin, SPARQL | Social networks, knowledge graphs, fraud detection | Moderate (graph partitioning is hard) |
| Time-Series | Timestamped rows + retention | InfluxDB, TimescaleDB, Prometheus, QuestDB | InfluxQL, SQL, PromQL | Metrics, sensor data, monitoring, finance ticks | Horizontal (time-based partitioning) |
| Vector | High-dim embedding vectors | Pinecone, Weaviate, Qdrant, pgvector, Chroma | ANN similarity search | Semantic search, RAG, recommendation, AI/ML | Horizontal (HNSW / IVF indexing) |
| Search / Full-Text | Inverted index | Elasticsearch, OpenSearch, Meilisearch, Typesense | Lucene DSL, JSON query | Full-text search, log analytics, autocomplete | Horizontal (shards + replicas) |
| In-Memory | RAM-first (optional persist) | Redis, Memcached, VoltDB, Apache Ignite | Varies | Ultra-low latency cache, pub/sub, rate limiting | Limited by RAM |
| Object | Object graph (OO persistence) | db4o, ObjectDB, Realm (mobile) | OQL, proprietary | CAD models, complex object graphs, mobile | Single-node / embedded |
| Embedded / File | File-based, local | SQLite, LevelDB, RocksDB, LMDB | SQL, key-value API | Mobile apps, IoT, local desktop, browser | Single-node |
| Ledger / Blockchain | Append-only, cryptographic | Amazon QLDB, Hyperledger Fabric, BigchainDB | SQL-like, chaincode | Audit trails, compliance, supply chain, DeFi | Distributed consensus |
| Spatial / GIS | Geospatial indexing | PostGIS, MongoDB, CockroachDB | ST_ functions, GeoJSON | Maps, routing, geofencing, location services | PostGIS extends PostgreSQL |
| Columnar / OLAP | Column-oriented storage | ClickHouse, DuckDB, BigQuery, Redshift | SQL | Analytics, warehousing, OLAP queries | Massive horizontal (MPP) |
| Multi-Model | Combines models | ArangoDB, Cosmos DB, FaunaDB | AQL, SQL-like, GraphQL | Apps needing doc + graph + key-value in one DB | Horizontal |
CAP Theorem
Every distributed database trades off between: Consistency (all nodes see same data), Availability (every request gets a response), Partition tolerance (survives network splits). You can only guarantee two of the three.
ACID vs BASE
| Property | ACID (SQL) | BASE (NoSQL) |
|---|---|---|
| Focus | Strong consistency | High availability |
| Consistency | Immediate | Eventual |
| Transactions | Full ACID | Often limited |
| Examples | PostgreSQL, MySQL | MongoDB, Cassandra |
