Types of Databases - SQL, NoSQL, and When I Actually Pick Each

A “database type” is not a brand. PostgreSQL and MySQL are both relational. MongoDB and Firestore are both document stores. Redis is a key-value engine that teams treat as a cache, a queue, and sometimes as a dangerous primary store. If you pick a database because a tutorial used it, you are picking a fashion. If you pick it because of how the data is shaped and how the app queries it, you are doing architecture.

This is the map I use when someone asks “SQL or NoSQL?” - usually the wrong first question. The first question is: what is one row (or document, or node) in this product, what must stay consistent, and which questions will the app ask a thousand times a day?

1. What “type” actually means

Three axes matter more than the marketing category. Get these wrong and the logo on the box will not save you.

• Data model: tables with relations, nested documents, keys, graphs, time buckets, or vectors.
• Query model: joins, filter-by-field, get-by-key, graph traversal, range over time, or “what is close to this embedding?”
• Guarantees: ACID transactions, eventual consistency, single-node simplicity versus a cluster you will debug at 2 a.m.

2. Relational (SQL) - the default for money and relations

PostgreSQL, MySQL, SQLite. Rows in tables, foreign keys, joins, migrations, ACID transactions. This is still the right home for most web products: users, workspaces, roles, orders, invoices, bookings, inventory. If a row must exist because another row exists, or a charge must not happen twice, you want a relational engine - not a JSON blob and a prayer.

PostgreSQL is my default in 2026: JSONB when a field is genuinely schemaless, built-in full-text for small search, pgvector when RAG shows up later. You can grow a long way before you “need” a second database. SQLite is underrated for local-first apps, CLIs, and tiny products. MySQL is fine if the team already runs it - I do not start a greenfield there.

• Pick SQL when integrity, reporting with joins, and “who paid for what in this workspace” are the job.
• Do not pick SQL because it is “enterprise.” Pick it because the domain is a graph of facts that must stay true together.

3. Document databases - flexible objects, expensive relations

MongoDB, CouchDB, Firestore, DynamoDB in document mode. A document is a JSON tree. That is a good fit when the unit of work is one object with nested bits that always load together: a CMS article, a product with a messy attribute bag, a user profile you always fetch whole.

The trap: you still have users, workspaces, and invoices. You will reinvent joins in the application, lose multi-document transactions (or pay for them), and spend a year adding uniqueness rules a relational engine would have given you on day one. I use a document store when the product is content-shaped and the access pattern is “load this blob by id.” I do not use it because the frontend speaks JSON. Postgres JSONB already speaks JSON.

4. Key-value - Redis and friends

Redis, Memcached, DynamoDB as a pure key-value store. Get, set, expire. Optionally lists, sets, pub/sub. This is not where the business of record lives. It is the fast layer next to it.

• Good: sessions, rate limits, feature flags, idempotency keys, cache of expensive reads, short-lived locks.
• Bad: the only copy of orders, users, or permissions. One flush or eviction policy and you have a support nightmare.

5. Wide-column - when writes never stop

Cassandra, Scylla, Bigtable. You partition by a key, write a lot, read by that key, and accept that ad-hoc SQL is not the point. Right for telemetry at huge scale, inbox-style feeds, multi-region write-heavy workloads. Wrong for a booking SaaS with twelve tables. If you do not already have a number like “millions of writes per second,” skip this category. The operational cost of the cluster is the product.

6. Graph - when the question is the connection

Neo4j, Amazon Neptune, and - for small graphs - SQL with recursive CTEs. Use a graph engine when the product is the network: recommendations, fraud rings, org charts, permission inheritance, “who is two hops from this supplier.” Do not use one because you drew boxes and arrows on a whiteboard. Almost every domain has relations. Relational databases already model relations. Graph engines earn their keep when traversal depth is the query, not the schema diagram.

7. Time-series - metrics, sensors, events over time

InfluxDB, TimescaleDB, Prometheus for ops, ClickHouse for analytics-shaped events. If every row is “this device or metric at this timestamp,” a general OLTP schema will hurt: too many inserts, range scans, retention policies. TimescaleDB is attractive because it stays PostgreSQL - same backups, same SQL, hypertables for the hot path. ClickHouse is for analytics, not for the checkout transaction. Do not dump your orders table into a time-series engine because the created_at column exists.

8. Vector - embeddings, not a second source of truth

Pinecone, Qdrant, Weaviate, and pgvector inside Postgres. You store vectors so you can ask “what text is close to this question?” That is RAG, semantic search, duplicate detection. The article, the price, the user - those still live in the operational database. The vector index is a derived index, like search. Rebuild it. Do not let checkout or permissions depend on it.

In 2026 I add pgvector to Postgres first. A dedicated vector database when the corpus size and QPS justify another moving part - not because a pitch deck said “AI-native.”

9. Search is not a database of record

Elasticsearch, OpenSearch, Typesense, Meilisearch. Full-text, typo tolerance, facets, ranking. Rebuild from the source of truth. Never let checkout, stock, or permissions depend only on an index that can drift after a failed reindex. Postgres full-text is enough for many small catalogs. Add a search engine when users type messy queries and you need relevance, not when you are bored of SQL.

10. How I actually choose

I run the filters in this order. The stack comes after the answers, not before the comparison table.

• What is the unit of consistency? If money or inventory moves, SQL with transactions. Not “we will fix doubles in the app.”
• What is the hot query? By id, join, full-text, similarity, time range, or graph hop? Match the engine to that shape.
• Who operates this at 2 a.m.? One managed Postgres beats a clever cluster nobody on the team can debug.
• Can one engine cover 80%? Postgres plus Redis covers most web products I actually ship.

A 2026 default I would actually start with

Unless the data model forces a specialist from day one, this is the stack I would open with - and refuse to expand until a measured bottleneck appears, not a blog post.

• PostgreSQL as the source of truth. JSONB where a field is genuinely schemaless. pgvector if RAG is a real feature, not a slide.
• Redis for cache, sessions, rate limits, and light queues. Not for orders.
• Object storage for files. A search engine only when LIKE and Postgres FTS are not enough.

The type is the shape of the work

SQL vs NoSQL was a 2010s argument. The useful question in 2026 is: which data model matches the job, and how few systems can you run well? Start with a relational source of truth. Add a specialist store when the query shape is genuinely different - cache, search, vectors, time-series. Hybrid is normal. Five databases on day one of an MVP is how the experiment dies.