Database Types

When You Need Database Types

Choosing the right database for each component of your system is a core design skill. In interviews, you should be able to explain why you chose PostgreSQL for user data, Redis for caching, and Elasticsearch for search.

What It Is

There are many types of databases, each optimized for specific use cases: relational (PostgreSQL, MySQL), document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra, HBase), graph (Neo4j), time-series (InfluxDB, TimescaleDB), search (Elasticsearch), and vector (Pinecone, pgvector).

How It Works

Most production systems use multiple database types (polyglot persistence). A typical architecture: PostgreSQL for core business data, Redis for caching and sessions, Elasticsearch for search, Kafka for event streaming, ClickHouse for analytics.

The Decision Framework

• Relational: Tables, SQL, ACID. Best for structured data with relationships. PostgreSQL, MySQL.
• Document: JSON documents, flexible schema. Best for content, catalogs, user profiles. MongoDB, CouchDB.
• Key-Value: Simple get/set by key. Extremely fast. Best for caching, sessions. Redis, Memcached, DynamoDB.
• Wide-Column: Rows with dynamic columns. Best for time-series, event logs. Cassandra, HBase.
• Graph: Nodes and edges. Best for social networks, recommendations. Neo4j, Amazon Neptune.
• Time-Series: Optimized for timestamped data. Best for monitoring, IoT. InfluxDB, TimescaleDB.
• Search Engine: Full-text search, ranking. Best for product search, log analysis. Elasticsearch, OpenSearch.
• Vector: Similarity search on embeddings. Best for AI/ML applications. Pinecone, pgvector.

What the Industry Uses

Uber uses PostgreSQL (trips), Redis (caching), Cassandra (analytics), Elasticsearch (search).

Airbnb uses MySQL (core data), Redis (caching), Elasticsearch (search), HBase (ML features).

Discord uses Cassandra (messages), PostgreSQL (user data), Redis (presence, rate limiting).

Performance and Tradeoffs

• Generality vs Optimization: Relational databases work for most things but are not the best at anything specific.
• Operational overhead: Each database type requires different expertise for maintenance and monitoring.
• Data consistency: Using multiple databases means managing distributed consistency — changes may need to be applied atomically across stores.

Mistakes Engineers Make

1. Using a single database for all use cases
2. Choosing based on hype rather than access patterns
3. Not considering operational complexity of managing multiple database types

Practice These Interview Questions

1. What are the main types of databases?
2. When would you use a document store vs a relational database?
3. What is polyglot persistence?

Practical Implementation for .NET Developers

In a .NET application, you would typically implement this pattern using the following approach:

ASP.NET Core setup: Create a service class that encapsulates the logic, register it with dependency injection, and inject it into your controllers or minimal API endpoints. The built-in DI container handles lifecycle management.

Entity Framework Core: For database interactions, EF Core provides the ORM layer. Use migrations for schema management and raw SQL for performance-critical queries. Consider Dapper for read-heavy paths where EF Core's overhead matters.

Azure integration: If deploying to Azure, leverage managed services — Azure Cache for Redis, Azure SQL, Azure Service Bus, Azure Cosmos DB. These eliminate operational overhead and provide built-in monitoring through Application Insights.

Testing: Use xUnit with Testcontainers for integration tests that spin up real databases in Docker. Mock external dependencies with NSubstitute. The WebApplicationFactory class lets you test your entire HTTP pipeline in-process.

Monitoring: Add Application Insights telemetry to track request latency, dependency calls, and custom metrics. Use structured logging with Serilog to make production debugging possible:

Log.Information("Processing order {OrderId} for {CustomerId}", orderId, customerId);

This gives you searchable, structured logs in Azure Monitor or Seq.

Further Reading

• sql-vs-nosql
• acid-transactions
• database-scaling

The Real-World Incident That Made This Famous

Understanding Database Types became critical after multiple high-profile production incidents at major tech companies. When systems handle millions of users, even small misunderstandings about Database Types can lead to cascading failures that cost millions in lost revenue and erode user trust. Companies like Netflix, Google, Amazon, and Meta have all invested heavily in mastering Database Types because they learned the hard way that ignoring it leads to outages.

The key lesson from these incidents: Database Types is not just a theoretical concept — it is a practical skill that separates engineers who build resilient systems from those who build fragile ones.

How Senior Engineers Think About This

Senior engineers approach Database Types differently from textbook definitions. Instead of memorizing rules, they build mental models. They ask: "What problem does Database Types solve? When does it fail? What are the alternatives?" This problem-first thinking leads to better design decisions because every system has unique constraints.

When evaluating Database Types in a system design context, experienced engineers consider the failure modes first. What happens when this component goes down? How does the system degrade? Is the degradation graceful or catastrophic? These questions reveal more about your understanding than any textbook definition.

Common Interview Mistakes

Mistake 1: Giving a textbook definition without context. Interviewers want to see you connect Database Types to real systems and real problems.

Mistake 2: Not discussing trade-offs. Every design decision involving Database Types has trade-offs. Discuss what you gain and what you give up.

Mistake 3: Overcomplicating the solution. Start with the simplest approach to Database Types that meets the requirements, then add complexity only when justified.

Production Checklist

• Define clear metrics for measuring the effectiveness of your Database Types implementation
• Set up monitoring and alerting that specifically tracks Database Types-related failures
• Document your Database Types design decisions in Architecture Decision Records (ADRs)
• Test failure scenarios related to Database Types in staging before production deployment
• Review and update your Database Types implementation quarterly as system requirements evolve
• Train new team members on the specific Database Types patterns used in your system

Read the original source | Content from System-Design-Overview