Database Tradeoffs

SQL vs NoSQL

Why Choose SQL:

• Need strong consistency and ACID transactions (banking, e-commerce orders)
• Complex queries with multiple joins (reporting, analytics)
• Well-defined schema that doesn't change frequently
• Team expertise with SQL

Why Choose NoSQL:

• Need to scale horizontally to handle massive traffic (social media feeds, IoT)
• Schema flexibility for rapidly evolving features (startups, prototyping)
• Simple key-value or document lookups (user profiles, session storage)
• Geographic distribution with eventual consistency acceptable

Tradeoff Summary:

• SQL: Consistency + Complex Queries ↔ Harder to scale horizontally
• NoSQL: Scalability + Flexibility ↔ Limited query capabilities, eventual consistency

Common Databases: When to Use What

How to decide quickly: Start with PostgreSQL or MySQL for most OLTP apps; pick DynamoDB/Cassandra when horizontal write scaling and simple access patterns dominate; choose MongoDB for flexible schemas without heavy joins; reach for graph (Neo4j) only when relationships are the core query.

Geospatial/Location Query Tradeoffs

Detailed Decision Flowchart: Location Queries

Question 1: How many location queries per second (QPS)?

• < 100 QPS: PostgreSQL + PostGIS (simplicity, ACID, complex queries)
• 100-10K QPS: Redis Geo or MongoDB (fast reads, moderate scaling)
• > 10K QPS: Redis Geo + PostgreSQL (Redis for hot reads, Postgres for cold/complex)

Question 2: Query complexity?

• Simple radius/nearby: Redis Geo or DynamoDB Geo Partitioning (blazing fast)
• Radius + filters (distance + category): MongoDB Geo Indexes or Elasticsearch
• Complex polygons + analytics: PostgreSQL + PostGIS
• Full-text search + location: Elasticsearch with geo plugin

Question 3: Scale and data distribution?

• Single region, < 10GB data: PostgreSQL + PostGIS
• Multi-region, high write volume: DynamoDB Geo Partition (serverless) or Redis Geo + Postgres replication
• Globally distributed users: S2/H3 geohash + DynamoDB or Cassandra (pre-compute regions)

Question 4: Consistency requirements?

• Strong consistency critical: PostgreSQL + PostGIS (ACID transactions)
• Eventual consistency acceptable: Redis Geo (cache layer), MongoDB, Elasticsearch

Question 5: Need real-time updates?

• Yes (food delivery, rideshare): Redis Geo (TTL for expiring locations) + pub/sub for updates
• Not critical: PostgreSQL + PostGIS with caching, DynamoDB

Common Location Query Patterns

Real-World Trade-offs: Examples

Use PostgreSQL + PostGIS when:

• Complex multi-criteria queries (distance + polygon + filters)
• ACID consistency critical (financial transactions with locations)
• Moderate QPS (<1K), accept eventual caching layer
• Team familiar with SQL and spatial extensions
• Example: Ride-sharing pricing zone verification, property boundary queries

Use Redis Geo when:

• Simple radius searches dominate (nearby driver/restaurant queries)
• Real-time, sub-millisecond latency required
• Can tolerate eventual consistency (cache layer over DB)
• User locations change frequently (need TTL auto-expire)
• Example: Uber finding nearby drivers, food delivery "nearby restaurants"

Use MongoDB Geo Indexes when:

• Flexible schema with location data (user profiles with optional location)
• Combining location filters with other document queries
• Moderate QPS with geographic scaling via sharding
• Example: Dating app "find matches within 20 miles who like hiking"

Use Elasticsearch Geo when:

• Location search + full-text search together (restaurants near me + name search)
• Complex aggregations (heatmaps, zone analytics)
• Accept higher latency (100ms+) for powerful analytics
• Example: Job search "Software engineer jobs in NYC area"

Use DynamoDB Geo Partitioning when:

• Serverless, minimal ops requirement
• Predictable location distribution (geohash bucket design)
• Can pre-compute regions (avoid hot partitions)
• Example: Mobile app tracking user sessions by region, IoT device location tracking

Use S2/H3 Geometry Libraries when:

• Building custom tiling/hexagonal systems
• Globally accurate cells (poles, datelines matter)
• Pre-computing expensive: heatmaps, regional aggregations
• Example: Uber's hexagon-based fleet optimization, disaster response zone mapping

Read Replicas vs Sharding

Why Choose Read Replicas:

• Read:write ratio is 10:1 or higher (news sites, blogs, product catalogs)
• Need to offload reporting/analytics queries from primary
• Geographic distribution of read traffic
• Simple to implement and maintain

Why Choose Sharding:

• Write throughput exceeds single database capacity (high-frequency trading, IoT ingestion)
• Dataset size exceeds single server storage (multi-TB datasets)
• Need to isolate tenant data (multi-tenant SaaS)
• Combined with replicas for maximum scale

Tradeoff Summary:

• Replicas: Easy to implement + Read scalability ↔ No write scaling
• Sharding: Write + Read scalability + Large data ↔ High complexity, cross-shard queries difficult

Normalization vs Denormalization

Why Choose Normalization:

• Data integrity is critical (user accounts, financial records)
• Write-heavy workload (frequent updates to same entities)
• Storage costs are high
• Data changes frequently

Why Choose Denormalization:

• Read performance is critical (newsfeed, product search)
• Read:write ratio is very high
• Willing to sacrifice consistency for speed
• Pre-computed aggregations needed (dashboards, leaderboards)

Tradeoff Summary:

• Normalization: Data integrity + Storage efficiency ↔ Slow reads (joins)
• Denormalization: Fast reads ↔ Data redundancy + Update complexity

Data Structures & Indexing Tradeoffs

Why Choose Bloom Filter:

• Minimize expensive DB hits for non-existent keys
• Protect caches from "thundering misses" or abuse
• Pre-check existence in distributed systems

Why Choose Redis Hash:

• Reduce key overhead vs many small keys
• Update fields atomically without rewriting whole object
• Keep related data co-located for cache locality

Why Choose Trie:

• Prefix search or autocomplete is core query
• Need ordered, lexicographic traversal
• Longest-prefix matching (routing, IPs)

Why Choose B+ Tree:

• Range queries, ordered scans, and pagination
• Disk-based storage where minimizing IO matters
• Multi-column indexes that benefit from ordering

Tradeoff Summary:

• Bloom Filter: Space-efficient existence checks ↔ False positives, no value retrieval
• Redis Hash: Memory efficiency + field-level updates ↔ No per-field TTL, no range queries
• Trie: Fast prefix queries ↔ High memory overhead
• B+ Tree: Range scans + ordered access ↔ More complex writes

Interview Questions & Answers

Q1: Design a recommendation system like Netflix. Would you use SQL or NoSQL? Why?

Answer: Use MongoDB (NoSQL) for the following reasons:

• Flexible schema: User preferences, watch history, and metadata change frequently
• Horizontal scalability: Netflix scales to millions of users globally
• Document model: Natural representation of complex nested data (shows, ratings, genres)
• Eventual consistency acceptable: Real-time recommendations don't need strict ACID

However, use PostgreSQL for:

• Payment and billing (ACID critical)
• User authentication (strong consistency needed)

Hybrid approach: MongoDB for user viewing history/recommendations + PostgreSQL for core transactions = best of both worlds.

Q2: You're building an Uber-like system. How would you handle location queries for "find nearby drivers"?

Answer: Primary: Redis Geo because:

• Drivers' locations update frequently (every few seconds)
• Need <100ms latency for instant matching
• Simple radius search (5-10km around user)
• O(log N) complexity with distance sorting

Secondary: PostgreSQL + PostGIS because:

• Store driver locations persistently (RDB backup)
• Handle edge cases (no drivers in radius, unexpected zones)
• Analytics queries (driver distribution by hour, peak zones)

Architecture:

1. Driver sends location → Redis Geo (real-time, fast)
2. Periodically sync to PostgreSQL (eventual consistency)
3. Use S2/H3 for surge pricing zones (pre-computed hexagons)
4. For analytics, query PostgreSQL historical data

Q3: Your database reads are slow. Should you add read replicas or shard?

Answer: Decision tree:

1. Check read:write ratio: If 10:1 or higher → Read replicas

- Example: News site (mostly reads, few updates) - Simpler, no cross-shard coordination

1. If write-heavy (high traffic, write volume exceeds single DB) → Sharding

- Example: Real-time metrics, IoT sensors, high-frequency trading - Write scaling required; replicas alone won't help

1. Combination approach (most real systems):

- Shard by userid or geographic region - Add replicas to each shard (primary + 2 secondaries) - Example: Stripe payment system (sharded by merchantid, replicated for HA)

Performance check before deciding:

• Run EXPLAIN ANALYZE on slow queries
• Add indexes first (often solves 80% of slowness)
• Then consider replicas/sharding

Q4: When designing a social network (like Facebook), should you normalize or denormalize the user profile?

Answer: Denormalize user profiles because:

• User profiles are read-heavy (viewed hundreds of times, updated rarely)
• Denormalized: { userid, name, profilepic, bio, location, friends_count }
• Avoids expensive joins on every page load

But normalize core data:

• User credentials, email, phone → normalized (change rarely, critical consistency)
• Payments and transactions → heavily normalized (ACID required)

Practical implementation:

Normalized tables:
- users (id, email, password_hash, created_at)
- user_profiles (user_id, name, bio, location) → denormalized cache

On profile update:
1. Update user_profiles (fast read)
2. Sync background job to update cache in Redis
3. Read heavy calls hit cache/denormalized data

Tradeoff: Trade storage for speed; profile denormalization costs 10% more storage but saves thousands of joins.

Q5: How would you design geospatial queries for a map-based service like Google Maps?

Answer: Layered approach:

1. Index level: PostgreSQL + PostGIS

- Store all geographic data (roads, POIs, boundaries) - Handle complex queries (within polygon, distance calculations)

1. Cache level: Redis Geo

- Cache hot queries (major cities, popular routes) - <10ms latency for cached results

1. Pre-computation level: S2/H3 geometry

- Pre-compute tiles and hexagons - Avoid expensive real-time calculations - Use for heatmaps, density-based features

Query flow for "restaurants near me":

User location (40.7128, -74.0060) within 5km

1. Check Redis Geo: GEORADIUS restaurants 40.7128 -74.0060 5km
2. Cache hit → return instantly
3. Cache miss → Query Elasticsearch (location + full-text search on "restaurant")
4. Elasticsearch queries PostgreSQL + PostGIS for complex boundaries
5. Store result in Redis with 1-hour TTL

This hybrid design balances speed (Redis), power (PostGIS), and scale (Elasticsearch).