Databases

How it works

1. Model schema/indices
2. Define access patterns
3. Tune consistency/replication
4. Scale via replicas/shards

Overview

• Databases persist and organize data with trade-offs across models (relational, document, KV, columnar, graph).
• Choose based on data shape, query patterns, consistency needs, and scale.

When to use

• Relational: strong consistency, complex joins/transactions.
• Document: flexible schema, nested aggregates, evolving models.
• KV: ultra-fast lookups, caching/sessions.
• Column-family: wide time-series/analytics at scale.
• Graph: relationship-centric queries (paths, recommendations).

Trade-offs

• RDBMS: rigid schema, vertical scale limits but strong ACID.
• NoSQL: flexible and scalable but limited joins and often eventual consistency.

Patterns

• Indexing: B-tree/GIN/GiST for RDBMS; secondary indexes in NoSQL.
• Normalization vs denormalization based on read/write mix.
• Transactions and isolation levels (Read Committed, Repeatable Read, Serializable).
• Replication (sync/async), read replicas; partitioning/sharding for scale.

Anti-patterns

• Blind dual-writes to multiple stores; no single source of truth.
• Missing indexes causing table scans; over-indexing harming writes.
• Treating NoSQL as a relational store or vice versa.

📐 Quick Diagram

      App ▶ ORM/Driver ▶ DB Primary ▶ Replicas
                         └▶ Partitions/Shards ▶ Nodes
      

❓ Interview Q&A (concise)

• Q: Pick DB for analytics vs OLTP? A: OLTP → RDBMS; Analytics → columnar/warehouse; hybrid via CDC/ETL.
• Q: Fix slow query? A: Add proper index, examine execution plan, denormalize or rewrite, and cache.
• Q: Isolation anomalies? A: Dirty/non-repeatable/phantom reads; use higher isolation or SELECT FOR UPDATE.

🎯 Database Categories

Relational Databases (RDBMS)

• Structure: Tables with rows and columns
• Consistency: ACID properties
• Query Language: SQL
• Examples: PostgreSQL, MySQL, Oracle
• Use Cases: Financial systems, e-commerce, CRM

NoSQL Databases

#### Document Stores

• Structure: JSON-like documents
• Examples: MongoDB, CouchDB
• Use Cases: Content management, catalogs

#### Key-Value Stores

• Structure: Simple key-value pairs
• Examples: Redis, DynamoDB, Riak
• Use Cases: Caching, session storage, shopping carts

#### Column-Family

• Structure: Wide columns, column families
• Examples: Cassandra, HBase
• Use Cases: Time-series data, IoT, analytics

#### Graph Databases

• Structure: Nodes and relationships
• Examples: Neo4j, Amazon Neptune
• Use Cases: Social networks, recommendation engines

🛠️ Choosing a Database

Consider these factors:

• Data Structure: Structured vs. unstructured
• Scalability Requirements: Read vs. write heavy
• Consistency Needs: Strong vs. eventual consistency
• Query Complexity: Simple lookups vs. complex joins
• Performance Requirements: Latency and throughput

📈 Database Scaling

1. Read Replicas: Scale read operations
2. Sharding: Horizontal partitioning
3. Federation: Split databases by function
4. Denormalization: Trade storage for performance