Databases Cheatsheet

As described by the CAP Theorem, when a network partition prevents nodes in a distributed database from communicating reliably, a choice must be made:

• Prioritize Consistency (CP): The system refuses to respond (becomes unavailable) if it cannot guarantee that the data read or written is consistent across the partition. This prevents stale reads or conflicting writes but impacts uptime during network issues.
  Example Use Case: Financial transactions where data accuracy is non-negotiable.
• Prioritize Availability (AP): The system continues to respond to requests even if it cannot guarantee consistency across the partition. Nodes might serve potentially stale data or accept writes that need later reconciliation. This maximizes uptime but requires applications to handle potential inconsistencies.
  Example Use Case: Social media feeds or shopping carts where showing slightly old data is often acceptable to keep the service responsive.

Understanding the application's tolerance for inconsistency vs. downtime is crucial when selecting a distributed database and configuring its consistency levels.

The PACELC theorem extends CAP by observing that even in the absence of Partitions (P), distributed systems face another fundamental tradeoff: choosing between lower Latency (L) and higher Consistency (C) during normal operation.

The theorem states: If there is a Partition (P), how does the system trade off Availability (A) versus Consistency (C)? Else (E), when operating normally, how does the system trade off Latency (L) versus Consistency (C)?

• Prioritize Consistency (PC/EC): Achieving higher consistency (e.g., ensuring a write is replicated to multiple nodes before acknowledging success) often requires more network communication and coordination, increasing the time (latency) it takes to complete an operation.
• Prioritize Latency (PA/EL): Responding faster might mean acknowledging a write after it's only been accepted by one or a few nodes, relying on asynchronous replication to propagate it later. This reduces latency but introduces a window where reads might see stale data (lower consistency).

Different databases optimize for different points on this spectrum. For example, systems designed for strong consistency might inherently have higher latency than those prioritizing speed with eventual consistency.

Choosing how a database will grow impacts cost, complexity, and ultimate capacity.

• Vertical Scaling (Scale-Up):
  • Approach: Add more power (CPU, RAM, faster disks) to the existing server.
  • Best For: Applications with moderate growth expectations, systems where operational simplicity is paramount initially, traditional monolithic applications. Often the default starting point for SQL databases.
  • Limits: Physical hardware constraints, cost increases non-linearly at the high end, potential single point of failure.
• Horizontal Scaling (Scale-Out):
  • Approach: Add more servers (nodes) and distribute the load/data (replication, sharding).
  • Best For: Applications expecting massive growth, systems requiring high availability and fault tolerance, cloud-native applications. The native approach for most NoSQL databases.
  • Limits: Increased architectural and operational complexity (managing clusters, network latency, consistency issues), potential need for application changes to leverage distribution effectively.

Many modern systems use a combination, starting vertical and implementing horizontal strategies (like read replicas) as needed, or using databases specifically designed for horizontal scaling from the start.

The approach to data structure definition involves significant tradeoffs.

• Rigid Schema (Schema-on-Write):
  • Benefit: Ensures data consistency, integrity, and predictability. Clearly defines the expected data structure. Optimizes storage and indexing.
  • Drawback: Requires upfront design effort. Adapting to changes requires formal schema migrations, which can be complex, slow, and potentially require downtime. Less suitable for unstructured or rapidly evolving data.
  • Typical Databases: SQL databases (Postgres, MySQL, SQL Server, SQLite).
• Flexible Schema (Schema-on-Read / Schema-less):
  • Benefit: Allows for rapid development and easy adaptation to changing requirements. Can handle diverse and evolving data structures easily. No need for complex migrations for simple field additions.
  • Drawback: Can lead to inconsistent or messy data if not managed carefully at the application layer. Querying data with heterogeneous structures can be complex and less performant. Data meaning might depend on application logic.
  • Typical Databases: Document DBs (MongoDB), Key-Value Stores (Redis), Wide-Column Stores (Cassandra). (Note: Even flexible systems often benefit from some level of schema definition or validation).

The database's design influences the types of queries it handles efficiently.

• SQL Databases:
  • Strength: Designed for complex, ad-hoc queries involving multiple tables (JOINs), aggregations, subqueries, and filtering on various columns. The relational model and SQL language provide a powerful, standardized way to access related data.
  • Consideration: Performance of very complex JOINs across huge tables can still be a challenge, requiring careful indexing and query optimization.
• NoSQL Databases:
  • Strength: Often optimized for very high performance on specific query patterns based on their data model:
    • Key-Value: Extremely fast lookups by key.
    • Document: Fast retrieval/update of entire documents or querying fields within a document.
    • Wide-Column: Efficient retrieval of specific columns for a range of rows.
    • Graph: Fast traversal of relationships between nodes.
  • Consideration: Queries that don't fit the optimized pattern (e.g., JOIN-like operations across different document types in a Document DB, complex filtering on non-indexed fields, graph-wide aggregations) might be inefficient, require multiple application-level queries, or may not be possible directly. Data modeling often needs to be done with specific query patterns in mind ("query-first design").

Aligning your application's data structure with the database's native model significantly impacts development ease and performance.

• Relational Model (SQL - Tables): Best fit for highly structured data with well-defined relationships that need to be enforced, requiring complex queries across different entities. Examples: Financial records, inventory systems, user accounts with complex permissions.
• Document Model (MongoDB): Ideal for semi-structured data where each entity might have varying attributes, or data naturally nests (like blog posts with comments). Good when data maps closely to application objects. Examples: Content management, product catalogs, user profiles.
• Key-Value Model (Redis): Suitable when the primary access pattern is retrieving a value based on a known key, requiring very low latency. Examples: Caching, session state, user preferences lookups.
• Wide-Column Model (Cassandra): Good for handling massive datasets where access is typically by row key, but the columns needed per row might vary or be very numerous (sparse data). Examples: Time-series data, IoT sensor readings, activity logs.
• Graph Model (Neo4j): The best choice when the relationships *between* data points are as important as the data points themselves, and queries involve traversing these connections. Examples: Social networks, recommendation systems, fraud detection rings.
• Search Model (Elasticsearch): Optimal for text-heavy data requiring sophisticated search capabilities (relevance, faceting, geospatial) or complex aggregations for analytics. Examples: Log analysis, product search, document repositories.

Trying to force data into an unsuitable model often leads to complexity and poor performance (e.g., simulating complex relationships in a Key-Value store, or storing large binary blobs in a relational database optimized for structured data).