Persistent Storage - formation masterclass

Relational databases ensure that each database table has a predefined schema. Every column has a data type.
Whenever new attributes (columns) need to be added in the database, the schema has to be changed beforehand and those schema changes need to be applied to the database before that new column can be used.
Relational databases enforce data integrity constraints, such as primary key, foreign key, unique key, and check constraints, to maintain data consistency.

Usually, relational databases scale less than non-relational databases, and are less performant (think about a) Transactional Support, b) all the integrity checks that need to be made after every transaction). At that cost, they provide high data integrity and high reliability and better support for joins.

Query Performance. It's understandable that the database is doing a lot of work and checks while writing data (constraints, datatype checks, referential integrity checks, etc.), which means in general queries, specially write queries in relational DBs are much slower than in non-relational DBs

!Pasted image 20250129000708.png

ACID

https://www.youtube.com/watch?v=GAe5oB742dw
In the context of a relational database, a transaction refers to a logical unit of work that consists of one or more database operations (such as inserts, updates, or deletes) that are executed as a single, indivisible unit. This means that either all the operations happen or they all don’t. The concept of a transaction is fundamental to ensuring data integrity, consistency, and reliability in relational databases.

The key aspects of a transaction are atomicity, consistency, durability, and isolation. This is where the acronym ACID comes from. Let's look at each of them briefly.

1. Atomicity:
2. 1. Atomicity ensures that all operations within a transaction are executed as a single, indivisible unit. Either all operations in the transaction are completed successfully, or none of them are.
   2. If any operation within the transaction fails or encounters an error, the entire transaction is rolled back, and the database returns to its previous state (i.e., all changes made by the transaction are undone).
3. Consistency
4. 1. Consistency guarantees that the database remains in a valid and consistent state before and after the execution of a transaction.
   2. Transactions must adhere to all integrity constraints, such as primary key constraints, foreign key constraints, and unique constraints, to ensure the integrity of the data.
5. Isolation
6. 1. Isolation ensures that the changes made by one transaction are isolated from the changes made by other concurrent transactions.
   2. Concurrent transactions execute as if they were isolated from each other, preventing interference and maintaining data integrity.
   3. Isolation levels, such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable, define the degree of isolation between concurrent transactions.
7. Durability
8. 1. Durability guarantees that the changes made by a committed transaction persist even in the event of a system failure or crash.
   2. Once a transaction is committed, its changes are written to durable storage (such as disk) and remain intact, even if the system crashes or loses power.

Non-relational databases generally scale more easily than relational databases and thus can achieve higher throughput. They do well with large amounts of data and are more performant (because of no integrity or schema checks). Of course, at that cost, they often don't provide transactional support (we will speak about this later) and are less performant for queries with joins.