Choosing a database type

Start from the access pattern

There is no best database; there is a best database for a workload. Ask:

1. What are the main queries (by key, by range, by search, by relationship)?
2. How much data and how fast does it grow? Read/write ratio?
3. What consistency and transaction guarantees are needed?
4. What latency is required, and where are users?
5. What can your team operate?

The families

100%

Figure SDA-DDV-05-1 · Map from need to database family · © forcsguy.com

Relational (PostgreSQL, MySQL)

Tables, SQL, joins, constraints, ACID transactions. The default for most business data. PostgreSQL has rich features (JSON, full-text, extensions, many index types); MySQL is widely deployed and simple to operate. They scale vertically very far, and read replicas and sharding extend them. Choose when data is structured, relationships matter and correctness is critical.

Document (MongoDB and similar)

Stores JSON-like documents; flexible schema; related data can be embedded in one document so a single read gets everything. Good for content, catalogues and objects read whole. Watch for duplicated data, joins you must do in code, and weaker multi-document transaction habits.

Key-value (Redis, Memcached; DynamoDB as a key-value/wide hybrid)

Lookup by key at very low latency. Redis adds data structures (hashes, sorted sets, lists, bitmaps), persistence and clustering (see the Redis lesson). Ideal for caches, sessions, counters, rate limits, leaderboards. Not for ad-hoc queries.

Wide-column and partitioned key stores (Cassandra, DynamoDB, HBase)

Data is partitioned by a partition key across many nodes; you design tables around queries. Excellent write throughput, horizontal scale and availability. Cost: limited query flexibility, careful key design (hot partitions), eventual consistency by default. Good for event logs, messaging history, time-ordered user data.

Search engines (Elasticsearch, OpenSearch, Solr)

Build an inverted index (word to documents) for full-text search, relevance scoring, filters and facets. Usually a secondary copy of data from a primary database, kept in sync. Not a system of record: treat it as a derived index that can be rebuilt.

Graph (Neo4j and others)

Stores nodes and relationships and answers questions that follow connections: friends of friends, fraud rings, recommendations, dependency analysis. Where many-hop traversals would need expensive recursive joins in SQL, a graph database is natural.

Time-series (InfluxDB, TimescaleDB, Prometheus)

Optimised for append-heavy, timestamped data with time-range queries, downsampling and retention. Used for metrics, IoT and finance ticks (see the metrics monitoring lesson). Keep tag cardinality low.

Vector databases (and vector indexes in other databases)

Store embeddings (arrays of numbers representing meaning) and find the nearest neighbours, using approximate indexes (graph-based or clustering) for speed. Used for semantic search and retrieval for AI applications. Many general databases now offer vector indexes, which can be enough for moderate scale and avoids another system.

A quick chooser

Need	First choice
Business data with relationships and transactions	Relational
Flexible, self-contained records	Document
Cache, session, counter, leaderboard	Redis
Massive write volume, simple access by key	Wide-column / DynamoDB-style
Full-text search and facets	Search engine
Many-hop relationship queries	Graph
Metrics and sensor data	Time-series
Semantic similarity	Vector index
Analytics on large history	Warehouse or lakehouse (see the big data course)

Polyglot persistence and its price

Many systems use several stores, each for what it does best: a relational database as the source of truth, a cache, a search index and a metrics store fed from it. The cost is keeping them in sync (change events, outbox pattern, rebuild jobs), more to operate and more failure modes. Add a store only when a measured need justifies it.

SQL versus NoSQL, honestly

"NoSQL" is a family of different designs, not one thing. Relational databases scale further than many assume; NoSQL stores often give up joins and some consistency for scale and flexibility. Decide on requirements, not fashion. If in doubt, start relational and specialise later.

Questions before committing

• Can we model our top ten queries easily?
• What happens at 10 times the data? At a regional failure?
• How do we back up, restore, migrate and monitor it?
• Does the team know it, and is it supported well on our platform?

Real-world reading