SQL vs NoSQL Flashcards
Explain the CAP THeorem
- Consistency: All nodes see the same data at the same time
- Availability: Nodes failures do not stop other nodes
- Partition tolerance: Loss of messages does not cause failure
=================
the CAP theorem is about replication. It only addresses the behavior of a distributed system in which multiple nodes share the same piece of data.
the CAP theorem is about failure. Even more, it’s about a specific type of failure – a network failure between the nodes that eliminates the ability to communicate updates.
This network failure is called a ‘partition’ (the “P” in CAP). In this failure scenario, the CAP theorem states that you have to choose between two properties: Availability and
Consistency.
================
Check different scenarios https://www.alexdebrie.com/posts/when-does-cap-theorem-apply/
Explain the set of properties BASE of NoSQL
- BA = Basically Available: it is always there
- S = Soft State: when one node changes, all the nodes will eventually update. The app/client only updates one node. Things happen without your knowledge in the background
- E = Eventually Consistent: a consequence of the previous definition (Soft State)
Explain the set of properties ACID
ACID is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.
Atomicity: the whole transaction occurs or nothing happens,
Consistency: ensures that a transaction can only bring the database from one consistent state to another. Any DATA WRITTEN MUST BE VALID according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transactions.
Isolation: ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. A lower isolation level allows many users to access the same data at the same time, increasing the possibility to obtain old data in the read,
Durability: guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory
EXPLAIN: replication, partitioning, clustering, and sharding
Replication - Copying an entire table or database onto multiple servers. Used for improving the speed of access to reference records such as master data.
Partitioning - is a division of a logical database or its constituent elements into distinct independent parts
Sharding - Sharding is actually a type of database partitioning, more specifically, Horizontal Partitioning. Sharding is replicating [copying] the schema, and then dividing the data based on a shard key onto a separate database server instance, to spread the load.
Sharding and partitioning are both about breaking up a large data set into smaller subsets. The difference is that sharding implies the data is spread across multiple computers while partitioning does not. Partitioning is about grouping subsets of data within a single database instance.
Clustering - Using multiple application servers to access the same database. Used for computation-intensive, parallelized, analytical applications that work on non-volatile data.
When to use Partitioning
- In Normalization we use Vertical Partitioning
- Tables greater than 2 GB should always be considered as candidates for partitioning.
- Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month’s data is updatable and the other 11 months are read-only.
- When the contents of a table need to be distributed across different types of storage devices.
