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 Systems/Configs: Strongly consistent RDBMS clusters, HBase , some MongoDB / Redis Cluster configurations.
  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 Systems/Configs: Cassandra , DynamoDB , Riak , most default NoSQL configurations, eventual consistency modes.
  Example Use Case: Social media feeds, shopping carts, product views 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 distributed systems face another fundamental tradeoff *during normal operation* (Else case, when there's no Partition): choosing between lower Latency (L) and higher Consistency (C) .

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 - High C, Higher L): Achieving higher consistency (e.g., synchronous replication to a quorum of nodes before acknowledging a write) often requires more network round trips and coordination, increasing the time (latency) it takes to complete an operation.
  Example Systems: Paxos / Raft based systems like etcd , ZooKeeper , strongly consistent RDBMS.
• Prioritize Latency (PA/EL - Low L, Lower C): Responding faster might mean acknowledging a write after it's only been accepted locally or by one replica, relying on asynchronous replication to propagate it later. This reduces latency but introduces a window where reads might see stale data (lower consistency).
  Example Systems: Cassandra (tunable), Redis (async replication), many default NoSQL settings.

Different databases optimize for different points on this spectrum (e.g., DynamoDB often described as PA/EL, BigTable / HBase as PC/EC, Cassandra as PA/EL but tunable towards EC). Understanding this helps select systems matching application needs for speed vs. data freshness.

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

• Vertical Scaling (Scale-Up):
  • Approach: Add more power (CPU, RAM, faster disks/NVMe) 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 (max CPU/RAM per socket/board), cost increases non-linearly at the high end, potential single point of failure, downtime often needed for upgrades.
• Horizontal Scaling (Scale-Out):
  • Approach: Add more commodity servers (nodes) and distribute the load/data (e.g., replication , sharding / partitioning , clustering).
  • Best For: Applications expecting massive growth or spiky loads, systems requiring high availability and fault tolerance, cloud-native architectures. The native approach for most NoSQL databases and modern distributed SQL.
  • Limits: Increased architectural complexity (sharding logic, distributed transactions), operational overhead (managing clusters, network latency, consistency across nodes), potential "hot spots" if sharding key isn't chosen well, inter-node communication overhead.

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 (e.g., Cassandra , Vitess , CockroachDB ).

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 based on known types. Self-documenting structure.
  • Drawback: Requires upfront design effort. Adapting to changes requires formal schema migrations (e.g., `ALTER TABLE`), which can be complex, slow, lock-intensive, and potentially require downtime for large tables. 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 or diverse data sources. Can handle heterogeneous structures easily (e.g., different attributes per document). No blocking migrations for simple field additions.
  • Drawback: Can lead to inconsistent or "dirty" data if not managed carefully via application logic or optional validation rules. Querying data with varying structures can be complex or inefficient. Data meaning might be ambiguous without application context or documentation. Potential for increased storage size if field names are repeated.
  • 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/validation for critical fields).

The choice depends on the volatility of the data structure, the need for strict integrity enforcement vs. development speed, and the team's discipline in managing flexible schemas.

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

• SQL Databases:
  • Strength: Designed for complex, ad-hoc queries involving multiple related tables (JOINs), sophisticated aggregations (GROUP BY, window functions), subqueries, and filtering on various columns. The relational model and declarative SQL language provide a powerful, standardized way to interrogate structured data. Mature query optimizers.
  • Consideration: Performance of very complex JOINs or full table scans on huge tables requires careful indexing, query tuning, and potentially denormalization. Can struggle with graph-like traversals (recursive CTEs can be complex/slow).
• NoSQL Databases:
  • Strength: Often optimized for very high performance and scalability on specific, often simpler, query patterns based on their data model:
    • Key-Value ( Redis ): Blazing fast O(1) lookups by primary key.
    • Document ( MongoDB ): Fast retrieval/update of entire documents; efficient querying on indexed fields within documents, including nested structures/arrays.
    • Wide-Column ( Cassandra ): Efficient retrieval of specific columns for a range of rows based on partition/clustering keys. Optimized for writes.
    • Graph ( Neo4j ): Extremely fast traversal of relationships (finding paths, neighbors) starting from known nodes.
    • Search ( Elasticsearch ): Powerful full-text search, relevance scoring, complex filtering, and aggregations across documents.
  • Consideration: Queries that don't fit the optimized access pattern (e.g., JOIN-like operations across collections/buckets, filtering on non-indexed fields, graph-wide scans, full-text search in non-search DBs) might be inefficient, require multiple application-level lookups, necessitate data duplication/denormalization, or be impossible directly. Data modeling often requires a "query-first" design approach.

Aligning your application's conceptual data structure and primary query needs with the database's native model significantly impacts development ease, performance, and scalability.

• Relational Model (SQL - Tables): Best fit for highly structured data with well-defined relationships that need enforcement (foreign keys) and integrity (constraints), requiring complex queries/transactions across different entities.
  Examples: Financial records, ERP systems, user accounts with detailed permissions, normalized inventory.
• Document Model ( MongoDB ): Ideal for semi-structured data where entities (documents) can have varying attributes, or data naturally nests (e.g., orders with line items). Good when data maps closely to application objects.
  Examples: Content management, product catalogs, user profiles, session data, event logs.
• Key-Value Model ( Redis , Memcached ): Suitable when the primary access pattern is retrieving a value based on a known unique key, requiring extremely low latency.
  Examples: Caching layers, session state, real-time counters, feature flags.
• Wide-Column Model ( Cassandra , HBase ): Good for handling massive datasets where access is typically by row key (partition key), but the columns needed per row might vary or be very numerous (sparse). Optimized for writes and range scans within partitions.
  Examples: Time-series data, IoT sensor readings, activity logs, user event tracking.
• Graph Model ( Neo4j , Neptune ): The best choice when the *relationships* between data points are as important as the data itself, and queries involve traversing these connections (e.g., path finding, centrality, pattern matching).
  Examples: Social networks, fraud detection rings, recommendation engines, knowledge graphs, dependency analysis.
• Search Model ( Elasticsearch , OpenSearch , Solr ): Optimal for text-heavy data requiring sophisticated search capabilities (relevance ranking, faceting, fuzzy matching, geospatial) or complex aggregations for near real-time analytics.
  Examples: Log analysis, product search, document repositories, SIEM data, application monitoring.
• Vector Model ( Milvus , Pinecone , etc.): Specifically designed for storing and querying high-dimensional vector embeddings based on similarity.
  Examples: Semantic search, RAG for LLMs, image/audio retrieval by content.

Trying to force data into an unsuitable model (e.g., complex relations in K/V, full-text search in basic SQL, tabular data in graph) often leads to convoluted application logic, poor performance, and scaling difficulties.