Types of Databases: A Map for Builders
"Just use Postgres" is good advice — until it isn't. Modern systems often need more than one data store: relational for money, a document store for product catalogs, Redis for sessions, a time-series DB for metrics, and a vector index for RAG.
This guide maps twelve database types you will see on architecture diagrams and job interviews. You will learn what each type optimizes for, two concrete products per category, and how to pick a lane without copying an infographic into your RFC.
Running example: checkout — cart in Redis, order+inventory in SQL, recommendations via vector search, analytics in a warehouse. Three or four types on one request path is normal; twelve is not.
Map the twelve types
Group first, memorize brands second. Transactional cores: SQL and NewSQL. Flexible app data: document and object-oriented. Speed layers: key-value and in-memory. Analytics shapes: columnar and time-series. Relationship & place: graph and spatial. AI & trust: vector and blockchain ledgers.
Overlap is normal — PostgreSQL alone covers SQL, JSON documents, PostGIS spatial, Timescale time-series, and pgvector. Specialty engines win when that overlap stops scaling or when the access pattern is extreme (global writes, ANN search at billions of vectors, immutable ledgers).
Quick reference
- Ask: shape of data, query pattern, consistency needs, and ops cost — in that order.
- Most products are polyglot: 2–4 stores, not twelve.
- Managed cloud versions change ops more than the data model.
- Wrong type usually fails as painful queries or expensive workarounds, not as a crash on day one.
Remember this
I can place any database into a type lane before arguing about vendors.
SQL and NewSQL: transactions you can trust
SQL (relational) databases store structured rows in tables, speak SQL, and aim for ACID correctness. They remain the default for money, identities, orders, and anything that needs joins and strong consistency. Examples: MySQL, Microsoft SQL Server (and PostgreSQL in most greenfield apps).
NewSQL keeps SQL + ACID while targeting horizontal scale for large transactional apps. Examples: CockroachDB, Google Spanner. Use NewSQL when you have outgrown a single primary and need distributed SQL — not because the word "New" sounds modern.
Quick reference
- SQL — structured data, ACID, relational model; MySQL, SQL Server, PostgreSQL.
- NewSQL — NoSQL-like scale with SQL guarantees; CockroachDB, Spanner.
- Start relational unless you have a measured reason not to.
- Schema migrations and indexes are part of the job — budget for them.
Remember this
I know when classic SQL is enough and when NewSQL's distributed transactions are worth the cost.
Document, key-value, and in-memory stores
Document databases store JSON/BSON with flexible schemas — natural for web apps and evolving product data. Examples: MongoDB, Couchbase.
Key-value databases map keys to values for fast lookups — ideal for caching, sessions, and real-time counters. Examples: Redis, Amazon DynamoDB (also a flexible NoSQL workhorse).
In-memory databases keep the working set primarily in RAM for ultra-low latency. Examples: SAP HANA, SingleStore (formerly MemSQL). Use when milliseconds matter and you can afford memory — not as a substitute for durable system-of-record without a persistence story.
Quick reference
- Document — flexible schemas, JSON/BSON; MongoDB, Couchbase.
- Key-value — simple pairs, fast lookups; Redis, DynamoDB.
- In-memory — RAM-first speed; SAP HANA, SingleStore.
- Redis often sits in front of SQL as cache — not instead of it.
Remember this
I can match document vs key-value vs in-memory to schema flexibility and latency needs.
Columnar and time-series: analytics shapes
Columnar databases store data by column, which speeds read-heavy analytics and aggregations (scan only the columns you need). Examples in the wild: Amazon Redshift for warehouses; Cassandra is often grouped here in marketing slides but is really a wide-column store for high-write, partition-keyed workloads — know the difference when you design.
Time-series databases optimize timestamped measurements for IoT, monitoring, and finance. Examples: InfluxDB, TimescaleDB (Postgres extension). Prefer them when time ranges and downsampling dominate every query.
Quick reference
- Columnar / analytics — Redshift, BigQuery, Snowflake; wide-column cousins like Cassandra for write-heavy partitions.
- Time-series — InfluxDB, TimescaleDB for IoT and metrics.
- Do not put your OLTP checkout cart in a warehouse engine.
- Retention policies and continuous aggregates matter as much as ingest rate.
Remember this
I separate OLTP tables from columnar analytics and time-series metrics stores.
Graph and spatial: relationships and place
Graph databases make relationships first-class — perfect for social graphs, recommendations, and fraud rings. Examples: Neo4j, Azure Cosmos DB (Gremlin/API for graph among other models).
Spatial databases store geographic and location data for GIS, maps, and urban planning. Examples: PostGIS (PostgreSQL), Oracle Spatial. If your queries are "within radius" and "intersects polygon," you want spatial indexes — not a JSON blob of lat/long with table scans.
Quick reference
- Graph — Neo4j, Cosmos DB graph APIs for relationship-heavy domains.
- Spatial — PostGIS, Oracle Spatial for GIS and mapping.
- Many fraud and recommendation systems combine graph + relational.
- PostGIS is often enough without a separate spatial product.
Remember this
I pick graph for relationship traversal and spatial indexes for geographic queries.
Vector, object, and blockchain ledgers
Vector databases store high-dimensional embeddings for AI, ML, and multimedia retrieval (semantic search, RAG). Examples: Milvus, Pinecone. See also our deeper guide on vector database choices.
Object-oriented databases store data as objects for complex entity modeling. Examples: db4o, ObjectDB — niche today; many teams model objects in code and persist with ORMs to SQL/document stores instead.
Blockchain / ledger databases emphasize decentralized or immutable audit trails for crypto, smart contracts, and verifiable history. Examples: Amazon QLDB, BigchainDB (not GIS tools — ignore copy-paste mistakes on some slides). Use when tamper-evidence and multi-party trust beat mutable CRUD.
Quick reference
- Vector — Milvus, Pinecone for embeddings and similarity search.
- Object-oriented — db4o, ObjectDB; rare in greenfield web stacks.
- Blockchain/ledger — QLDB, BigchainDB for immutable, verifiable history.
- pgvector and Neo4j vector indexes blur lines — start simple when possible.
Remember this
I know when vectors, object DBs, or immutable ledgers are the right specialty — and when they are hype.
Choose with a short checklist
Walk the checklist: (1) Need ACID + joins? SQL (or NewSQL if you must shard transactions). (2) Evolving JSON documents? Document. (3) Cache / sessions / hot keys? Key-value or in-memory. (4) Aggregations over huge fact tables? Columnar. (5) Metrics over time? Time-series. (6) Deep relationship queries? Graph. (7) Maps and GIS? Spatial. (8) Embeddings / RAG? Vector. (9) Multi-party immutable audit? Ledger/blockchain. (10) Pure object persistence without ORM? Only if you truly need an OODB.
Practice: take one product feature and list the primary type plus one optional specialty store. If you need more than three, simplify the design before you simplify the vendor list.
Quick reference
- Default stack for many SaaS apps: PostgreSQL + Redis (+ optional vector).
- Add specialty stores only after a measured pain (latency, query shape, scale).
- Prefer extensions (PostGIS, Timescale, pgvector) before new clusters.
- Document the why in your architecture decision record.
Remember this
I can run a checklist from SQL default through specialty stores without defaulting to twelve databases.
Zoom into one checkout feature
Trace a single checkout: Redis holds the cart (key-value / in-memory), SQL commits the order and inventory (ACID), optional vector search ranks "you might also like," and a columnar warehouse gets the event for analytics later. That is polyglot persistence on purpose — not twelve databases on day one.
When to add a specialty store: measured pain (latency, query shape, scale). When not to: Postgres + Redis already covers the feature and you have not measured a bottleneck.
Quick reference
- Cart / session → key-value or in-memory.
- Money / inventory → SQL transaction.
- Recommendations → vector (or SQL until volume hurts).
- Dashboards → columnar / warehouse, often async.
- Practice: label each store in an app you know by type, not by brand.
Remember this
I can map one product feature onto SQL + cache + optional specialty without inventing twelve databases.
The twelve types — vector, SQL, spatial, object-oriented, blockchain/ledger, key-value, columnar, time-series, document, in-memory, NewSQL, and graph — are lenses on access patterns, not a shopping list. Most teams need a relational core plus one or two specialists.
Your homework: pick a system you know and label each store by type. Then ask which specialty you could collapse into Postgres extensions tomorrow — and which one you would never remove. That exercise beats memorizing logos in a 3×4 grid.
Related Articles
Explore this topic