Relational Databases versus Key Value Stores: When to Use Each

Relational databases (PostgreSQL, MySQL) and key value stores (Redis, DynamoDB) represent opposite ends of the database spectrum, each optimized for fundamentally different access patterns. Understanding when to choose each prevents costly architectural mistakes.

Relational databases excel at complex transactions across multiple tables with joins. Stripe uses PostgreSQL for payment ledgers because a single charge involves updating accounts, transactions, and audit logs atomically. PostgreSQL guarantees that either all updates succeed or none do, preventing double charges or lost money. The trade-off is write latency of 10 to 100ms and throughput limited to roughly 10,000 writes per second on a single instance. Queries like finding all orders for a customer in the last year with product details require joining three tables, which PostgreSQL handles efficiently but would be extremely difficult in a key value store.

Key value stores optimize for simple lookups by key with sub millisecond latency. Twitter caches timelines in Redis, serving over 1 million reads per second with less than 1ms latency. DynamoDB powers Amazon shopping carts, handling 100,000+ operations per second with predictable single digit millisecond latency. The critical limitation: you can only query by primary key efficiently. Finding all sessions for a user requires a secondary index (adding latency) or scanning the entire dataset (impossible at scale).

The decision boils down to access patterns. If you need to ask questions like "show me all users who purchased product X in the last month" (ad hoc queries with filters), relational databases are required. If you always access data by known keys like "get session for session_id" and need extreme speed, key value stores win. Many systems use both: PostgreSQL for transactional data with complex queries, Redis for caching frequently accessed items.

💡 Key Takeaways

•Query flexibility versus speed: PostgreSQL handles any query you write tomorrow but delivers 10 to 100ms latency, Redis requires knowing exact access patterns upfront but delivers sub 1ms performance

•Throughput scaling differs fundamentally: PostgreSQL writes limited to 10,000 per second on single instance (vertical scaling expensive), Redis and DynamoDB handle 100,000+ per second horizontally with more nodes

•Transaction guarantees cost performance: PostgreSQL ACID transactions prevent data corruption but require coordination overhead, Redis lacks multi key transactions but achieves 100x higher throughput

•Secondary indexes change performance characteristics: DynamoDB allows querying non key attributes via Global Secondary Indexes (GSI) but latency increases from 5ms to 20ms and doubles costs

•Data relationships impossible in key value stores: joining shopping cart items with product details requires multiple round trips (network latency) or denormalizing data (storage cost and consistency risk)

📌 Examples

Shopify architecture combines both: PostgreSQL stores order tables with relationships between orders, line items, and inventory (needs ACID for stock tracking), Redis caches product pages accessed thousands of times per second (read heavy, simple key lookups)

Airbnb uses DynamoDB for session storage: sessions accessed by session_id only, 100,000+ requests per second during peak booking, sub 10ms latency required, no complex queries needed, perfect key value use case

Stripe cannot use key value store for payment ledger: auditing requires queries like "find all failed charges for merchant X in date range Y", reconciliation joins transactions with refunds and disputes, ACID prevents money loss

← Back to Choosing Databases by Use Case Overview