Essential Database Types for System Architecture: A Complete Guide

Choosing the right database is one of the most critical decisions in a system design. Your choice impacts:

• Performance under load

• Scalability as data grows

• Complexity of handling real-world scenarios

To help you prepare, here’s a breakdown of the 10 essential database types you need to know. For each, we’ll cover:

✅ What it is
✅ When to use it (with real-world examples)
✅ Key design considerations
✅ Popular databases to reference in interviews

---

1. Relational Databases

Stores data in structured tables (rows and columns) with defined relationships. Uses SQL for querying.

When to Use

✔ Structured, relational data (e.g., e-commerce with Users, Orders, Products tables)
✔ Strong consistency & ACID compliance (e.g., banking transactions)
✔ Complex queries & reporting (joins, aggregations)

Design Considerations

🔹 Indexing – Speed up reads but can slow writes (index user_id, email).
🔹 Normalization vs. Denormalization – Normalize for consistency; denormalize for read-heavy workloads.
🔹 Sharding – Split data horizontally (use high-cardinality keys like user_id).
🔹 Scaling – Vertical (add CPU/RAM) or horizontal (read replicas, caching).

Example Databases

• PostgreSQL (open-source, feature-rich)

• MySQL (LAMP stack staple)

• Oracle DB (enterprise-grade)

---

2. In-Memory Databases

Stores data in RAM instead of disk—ideal for ultra-low latency.

When to Use

✔ Real-time applications (e.g., gaming leaderboards)
✔ Caching layer (e.g., Redis for session storage)
✔ Temporary data (e.g., rate-limiting counters)

Design Considerations

🔹 Volatility – Data lost on crash unless persisted (Redis offers RDB snapshots).
🔹 Eviction Policies – LRU, LFU, or TTL to manage limited RAM.
🔹 Replication – Async replication for failover (but risk of data loss).

Example Databases

• Redis (supports rich data structures)

• Memcached (simple key-value caching)

---

3. Key-Value Stores

Simple key → value pairs (like a distributed hashmap).

When to Use

✔ Fast lookups by key (e.g., URL shorteners, session stores)
✔ High-throughput workloads (millions of ops/sec)
✔ No complex queries needed

Design Considerations

🔹 No joins or secondary indexes – Only key-based access.
🔹 Schema-less – Values can be JSON, strings, or binary blobs.
🔹 Easy horizontal scaling – Consistent hashing for distribution.

Example Databases

• Redis (also supports advanced structures)

• DynamoDB (managed, scalable)

---

4. Document Databases

Stores flexible JSON-like documents (schema-less).

When to Use

✔ Variable data structures (e.g., CMS with different content types)
✔ Nested/hierarchical data (e.g., user profiles with embedded addresses)
✔ Rapid iteration (no schema migrations)

Design Considerations

🔹 Indexing – Critical for performance (index user_id, email).
🔹 Document size limits – MongoDB caps at 16MB; split if needed.
🔹 Denormalization – Embed related data to avoid joins.

Example Databases

• MongoDB (most popular)

• Firestore (realtime updates for apps)

---

5. Graph Databases

Optimized for relationships (nodes + edges).

When to Use

✔ Social networks (friend-of-friend queries)
✔ Recommendation engines ("users who bought X also bought Y")
✔ Fraud detection (pattern analysis)

Design Considerations

🔹 Traversal efficiency – Handles deep relationships better than SQL joins.
🔹 Query languages – Cypher (Neo4j) or Gremlin.
🔹 Scalability – Some (Neo4j Enterprise) support distributed graphs.

Example Databases

• Neo4j (industry leader)

• Amazon Neptune (managed service)

---

6. Wide-Column Stores

Like spreadsheets on steroids—each row can have different columns.

When to Use

✔ Massive write scalability (e.g., IoT sensor data)
✔ Time-series or sparse data (e.g., user activity logs)

Design Considerations

🔹 Schema design – Partition keys impact performance (avoid hotspots).
🔹 Denormalization – Joins are expensive; duplicate data instead.

Example Databases

• Cassandra (Netflix, Instagram)

• ScyllaDB (high-performance alternative)

---

7. Time-Series Databases

Built for timestamped data (metrics, logs).

When to Use

✔ Monitoring/observability (e.g., Prometheus for Kubernetes)
✔ Financial tick data
✔ Rollup aggregations (e.g., daily averages)

Design Considerations

🔹 Time-based indexing – Queries are fast within time ranges.
🔹 Downsampling – Aggregate raw data to save space.

Example Databases

• InfluxDB

• TimescaleDB (PostgreSQL extension)

---

8. Text-Search Databases

Optimized for full-text search (inverted indexes, fuzzy matching).

When to Use

✔ E-commerce search (e.g., "running shoes" with filters)
✔ Log analysis (free-text log queries)

Design Considerations

🔹 Tokenization & stemming – "Running" → "run" for better matches.
🔹 Relevance scoring – TF-IDF or BM25 ranking.

Example Databases

• Elasticsearch (most popular)

• Solr (enterprise search)

---

9. Spatial Databases

Handles geographic data (locations, shapes).

When to Use

✔ Ride-hailing apps (find nearby drivers)
✔ Geofencing (e.g., delivery zones)

Design Considerations

🔹 Spatial indexing – R-trees for efficient queries.
🔹 Approximations – Bounding boxes for performance.

Example Databases

• PostGIS (PostgreSQL extension)

• MongoDB (geospatial queries)

---

10. Blob Stores

For large binary files (images, videos).

When to Use

✔ Media storage (e.g., YouTube videos)
✔ Backups & logs

Design Considerations

🔹 Metadata management – Store in a separate DB.
🔹 CDN integration – Speed up global delivery.

Example Services

• Amazon S3 (industry standard)

• Google Cloud Storage

---

Final Thoughts

Each database type excels in specific scenarios. In interviews:

1. Identify access patterns (reads vs. writes, query complexity).

2. Consider scalability needs (vertical vs. horizontal).

3. Combine databases if needed (e.g., Redis cache + PostgreSQL).

Core Architecture Concepts in RAG, LLMs & GenAI

 

1. 

Embeddings

• What it is: A dense vector representation of data (e.g., words, sentences, code).

• Why it matters: Converts discrete data (like text) into continuous numerical space that models can process.

• Example:

  • “Dog” → [0.25, -0.12, ..., 0.83]

  • Words with similar meanings have vectors close in space (semantic similarity).

• Used in:

  • Semantic search in RAG

  • Input for LLMs

  • Vector databases

---

2. 

Vector Spaces

• What it is: A high-dimensional space where embeddings live.

• Why it matters: Vectors allow fast similarity search using measures like cosine similarity or dot product.

• Used in:

  • Finding relevant documents in RAG

  • Nearest neighbor searches in FAISS or similar vector DBs

---

3. 

Attention Mechanism

• What it is: A technique that allows the model to focus on relevant parts of the input sequence when producing output.

• Types:

  • Self-attention: Used in Transformers; compares all tokens in a sequence to each other.

  • Cross-attention: Used in RAG; queries from LLM attend to retrieved documents.

• Why it matters:

  • Solves long-range dependency problems in sequences.

  • Enables parallelism (vs. RNNs).

• Key math:

  \text{Attention}(Q, K, V) = \text{softmax}\left(\frac{QK^T}{\sqrt{d_k}}\right)V

---

4. 

Transformers

• What it is: The architecture underlying modern LLMs.

• Components:

  • Input Embedding + Positional Encoding

  • Multi-head Attention

  • Feed-forward Neural Networks

  • Layer Normalization

  • Residual Connections

• Why it matters: Allows LLMs to scale, understand context, and generate coherent text.

---

5. 

Large Language Models (LLMs)

• What it is: Neural networks (typically Transformers) trained on massive corpora to predict and generate human-like language.

• Examples: GPT, BERT, Claude, Gemini

• Key Traits:

  • Pretraining: On vast text data using next-token prediction or masked language modeling.

  • Fine-tuning: For specific tasks (e.g., chat, summarization).

  • Inference: Generates text one token at a time using learned probabilities.

---

6. 

Generative AI (GenAI)

• What it is: Any AI model that can generate new content (text, images, code, etc.).

• In NLP:

  • Models that produce novel text based on prompts or questions.

  • LLMs are a subset of GenAI.

• Modalities:

  • Text (GPT, Claude)

  • Code (Codex)

  • Images (DALL·E, Midjourney)

  • Video (Sora)

  • Audio (MusicGen)

---

7. 

Retrieval-Augmented Generation (RAG)

• What it is: A hybrid GenAI method that augments LLMs with retrieval from external knowledge.

• Flow:

  1. Embed Query → vector space

  2. Retrieve Documents → from vector DB using similarity search

  3. Augment Prompt → LLM receives query + retrieved context

  4. Generate Answer → grounded, up-to-date, accurate

• Why it matters:

  • Reduces hallucination

  • Enables up-to-date, domain-specific responses

  • Keeps LLMs smaller and more efficient (vs. training on entire domain data)

---

8. 

Tokenization

• What it is: Breaking text into tokens (smaller pieces) before inputting into a model.

• Example:

  • “ChatGPT is smart.” → [‘Chat’, ‘G’, ‘PT’, ‘ is’, ‘ smart’, ‘.’]

• Why it matters:

  • LLMs operate on tokens, not raw text.

  • Affects context length and cost.

---

9. 

Context Window

• What it is: The maximum number of tokens a model can consider at once.

• LLMs have limits (e.g., GPT-4 can handle 128k tokens).

• Why it matters: Limits how much data (prompt + docs) you can include during RAG.

---

10. 

Prompt Engineering

• What it is: Crafting input prompts to guide the LLM’s behavior.

• In RAG: Used to incorporate retrieved documents properly.

• Example:

  You are a Java expert. Based on the following context, answer the user’s question. Context: [...]. Question: [...]

  
  

  ---

  11. 

  Vector Databases

  • What it is: Specialized databases that store and search high-dimensional vectors.

  • Popular tools: FAISS, Pinecone, Weaviate, Qdrant

  • Role in RAG:

    • Store document embeddings

    • Retrieve semantically relevant docs during generation

  ---

  12. 

  Similarity Search

  • What it is: Finding vectors in the database closest to the query vector.

  • Common Metrics:

    • Cosine Similarity

    • Dot Product

    • Euclidean Distance

  ---

  13. 

  Fine-tuning vs. Prompting vs. RAG

  Technique	When to Use
  Fine-tuning	You want model to learn new tasks from scratch
  Prompting	Quick instructions using existing model knowledge
  RAG	Inject external, non-memorized knowledge

          ┌─────────────┐

          │  User Query │

          └─────┬───────┘

                │

                ▼

        ┌──────────────┐

        │  Embed Query │

        └─────┬────────┘

              ▼

    ┌─────────────────────┐

    │   Vector DB Search  │  ←— uses cosine similarity

    └─────┬───────────────┘

          ▼

  ┌───────────────────────┐

  │  Retrieved Documents  │

  └─────┬─────────────────┘

        ▼

┌────────────────────────────┐

│ Prompt + Retrieved Context │

└─────┬──────────────────────┘

      ▼

┌────────────────┐

│     LLM        │

│  (e.g. GPT-4)  │

└─────┬──────────┘

      ▼

┌─────────────┐

│   Answer    │

└─────────────┘