Database Simulations: Choosing the Right Data Store

Introduction

When starting a project, many engineers default to the database they know best without testing its behavior under their specific workload.

This is a mistake.

Different databases handle reads, writes, schema changes, and distributed queries in drastically different ways. Choosing the wrong data store early can cripple performance later and require a painful migration to fix.

This guide walks you through evaluating the three primary database categories and matching them to your use case.

The Three Database Categories

1. Relational Databases (SQL)

What it is: Structured tables with rows, columns, and relationships between tables via foreign keys.

How it works:

-- Relational structure
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  created_at TIMESTAMP
);

CREATE TABLE posts (
  id INT PRIMARY KEY,
  user_id INT FOREIGN KEY -> users(id),
  title VARCHAR(255),
  content TEXT
);

-- Join across tables
SELECT u.name, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.id = 123;

Strengths:

• ✅ ACID transactions (Atomic, Consistent, Isolated, Durable)
• ✅ Complex joins across multiple tables
• ✅ Strong consistency (your data is always correct)
• ✅ Enforced data integrity (foreign keys, constraints)
• ✅ Mature, well-understood technology

Weaknesses:

• ❌ Schema changes on large tables are slow
• ❌ Horizontal scaling is difficult (sharding is complex)
• ❌ Not ideal for unstructured data
• ❌ Strict schema can be limiting during rapid prototyping

Best for:

• Financial applications (ledgers, transactions)
• B2B SaaS with relational data
• Multi-tenant systems with strict isolation
• Applications with complex queries
• GDPR compliance (easy to audit and delete)

Examples: PostgreSQL, MySQL, MariaDB, Oracle

---

2. Document/Key-Value Databases (NoSQL)

What it is: JSON documents stored without strict schema, with flexible structure.

How it works:

// Document database (MongoDB, Firebase)
db.users.insertOne({
  id: 123,
  name: "Alice",
  email: "alice@example.com",
  settings: {
    theme: "dark",
    notifications: true,
    preferences: {
      language: "en",
      timezone: "UTC"
    }
  },
  tags: ["vip", "early-adopter"],
  created_at: new Date()
});

// Nested data, flexible structure, no schema required

Strengths:

• ✅ Flexible schema (add fields without migrations)
• ✅ Horizontal scaling (sharding is built-in)
• ✅ Great for unstructured data
• ✅ Fast for simple lookups
• ✅ Rapid prototyping (schema can evolve)

Weaknesses:

• ❌ No ACID transactions (depends on database)
• ❌ Difficult joins (you fetch documents separately)
• ❌ Data duplication (need to denormalize for performance)
• ❌ Eventual consistency (your data might be stale)
• ❌ Harder to enforce data integrity

Best for:

• User profiles and preferences
• Content management systems
• Real-time data (chat, notifications)
• Prototypes where schema is unknown
• Apps with hierarchical data

Examples: MongoDB, Firebase/Firestore, DynamoDB, Supabase (with JSON columns)

---

3. Vector Databases

What it is: High-dimensional vectors (embeddings) optimized for similarity search using specialized indexes.

How it works:

// Vector database (Pinecone, Weaviate, Milvus)
// Store embeddings from AI models

const document = {
  id: "doc_123",
  text: "How to build scalable systems",
  embedding: [0.2, -0.5, 0.8, 0.1, ...] // 1536 dimensions (from OpenAI)
};

// Insert into vector database
await vectorDb.insert(document);

// Search by similarity
const query = "Building distributed systems";
const queryEmbedding = await embedModel.embed(query);
const results = await vectorDb.search(queryEmbedding, k: 10);
// Returns most similar documents based on embedding distance

Strengths:

• ✅ Semantic search (find similar content, not just exact matches)
• ✅ Scales to billions of vectors
• ✅ Fast similarity queries (milliseconds)
• ✅ Powers AI/ML applications

Weaknesses:

• ❌ Requires generating embeddings (expensive)
• ❌ Not useful for traditional queries
• ❌ Requires understanding of embeddings/ML
• ❌ No transactions or ACID guarantees

Best for:

• Semantic search engines
• AI agent memory/retrieval
• Recommendation systems
• Image/video search
• Duplicate detection

Examples: Pinecone, Weaviate, Milvus, Qdrant

---

Database Selection Matrix

Database Type	Primary Use Case	Read/Write Pattern	Consistency	Scalability	When to Use	When NOT to Use
Relational (SQL)	Structured, relational data	Complex queries, many joins	Strong/ACID	Vertical (complex sharding)	Financial, B2B, multi-tenant	Unstructured, rapid schema changes
Document (NoSQL)	Flexible, hierarchical data	Simple lookups, aggregations	Eventual	Horizontal (built-in)	Prototypes, content, user profiles	Strong consistency requirements
Vector	Semantic/similarity search	Similarity queries	None	Horizontal	AI apps, search, recommendations	Traditional CRUD operations

Evaluation Framework

When choosing a database, evaluate against your specific needs:

1. Data Structure

Questions to ask:

• Is my data highly structured with clear relationships? → SQL
• Is my data hierarchical or varies in structure? → Document
• Do I need similarity search? → Vector

2. Query Patterns

Questions to ask:

• Do I need to join data across multiple tables? → SQL
• Do I mostly fetch documents by ID or simple filters? → Document
• Do I need semantic search? → Vector

3. Consistency Requirements

Questions to ask:

• Do I need ACID transactions? → SQL
• Is eventual consistency acceptable? → Document
• Do I not need consistency? → Vector

4. Scale

Questions to ask:

• Will I scale to millions of users? → Document or Vector
• Will I have complex multi-table queries at scale? → SQL (with careful sharding)
• Will I have simple queries at massive scale? → Document

5. Schema Evolution

Questions to ask:

• Will my schema change frequently during development? → Document
• Is my schema stable? → SQL
• Do I know my schema upfront? → SQL or Document

Real-World Examples

Example 1: SaaS Product (Invoicing)

Data structure:
- Users (stable schema)
- Accounts (stable schema)
- Invoices (stable schema)
- Line items (stable schema)
- Transactions (stable schema)

Queries needed:
- Find all invoices for a user
- Calculate total revenue by month
- Ensure invoice amounts sum correctly
- Multi-table joins

Consistency requirement:
- ACID (financial data)

✓ Best choice: PostgreSQL (SQL)

Example 2: Real-Time Chat App

Data structure:
- Users (flexible, settings vary)
- Messages (mostly flat, nested reactions/metadata)
- Channels (flexible configuration)

Queries needed:
- Get last 50 messages for a channel
- Search messages by keyword
- Find all channels for a user

Consistency requirement:
- Eventual is fine (slightly delayed messages OK)

✓ Best choice: MongoDB or Firestore (Document)

Example 3: AI Search Engine

Data structure:
- Documents (each has text + embedding)
- User queries (each has embedding)

Queries needed:
- Find documents similar to query
- Find documents similar to a document
- Semantic clustering

Consistency requirement:
- None (search index)

✓ Best choice: Vector database (Pinecone, Weaviate)
+ SQL or Document for metadata

Example 4: Hybrid Approach (Recommended for Most Apps)

PostgreSQL (SQL):
  - Users, accounts, billing
  - Core business logic
  - Structured, transactional data

Firestore or MongoDB (Document):
  - User preferences and settings
  - Real-time features
  - Flexible data

Redis or DynamoDB (Key-Value):
  - Sessions and caching
  - Rate limiting counters
  - Temporary data

Pinecone or Weaviate (Vector):
  - Semantic search
  - AI features
  - Recommendations

Most production apps use multiple database types for different concerns.

Migration is Painful—Choose Wisely

If you choose the wrong database early, migrating later is expensive:

Monolith → Microservices: Weeks of work
SQL → NoSQL migration: Weeks of work, data loss risk
Small instance → Large instance: Hours of downtime

It's much cheaper to evaluate multiple databases upfront on sample data and choose the right one than to migrate later.

Testing Your Choice

Before committing to a database:

1. Load your actual data (or representative sample)
2. Run your actual queries (or realistic queries)
3. Test at scale (test at 10x your expected size)
4. Measure latency and throughput
5. Calculate cost

Testing matrix:
- 1000 users
- 10,000 users
- 100,000 users
- 1,000,000 users

Measure: Query latency, throughput, cost

Most database vendors offer free tiers for testing. Use them.

Key Takeaways

1. Different databases solve different problems — SQL vs Document vs Vector
2. Choose based on your data structure and queries — Not based on popularity
3. ACID transactions require SQL — No way around this for financial data
4. Document databases scale horizontally — But at the cost of consistency
5. Vector databases enable AI features — But are new and evolving
6. Test at scale before committing — Migration is painful
7. Most apps use multiple databases — SQL for core, others for specific use cases

Choose your database thoughtfully. Changing it later will cost you weeks of engineering time.

---

Remember: The right database for a startup that's validating product-market fit is different from the right database for a scale-stage company with millions of users. Don't over-engineer. Choose for your current needs, with an eye toward migration paths if you grow.