Foundations: ACID, CAP Theorem, and Indexing

Brief Cheat Sheet

ACID Properties

Atomicity: Transactions are all-or-nothing. If one part fails, the entire transaction fails.
Consistency: Transactions bring the database from one valid state to another, maintaining defined constraints.
Isolation: Concurrent execution of transactions leaves the database in the same state as if transactions were executed sequentially.
Durability: Once a transaction is committed, it remains persistent, even in the event of a system failure.

CAP Theorem

In a distributed data store, you can only guarantee two out of the following three properties:

Consistency: Every read receives the most recent write or an error.
Availability: Every request receives a non-error response, without the guarantee that it contains the most recent write.
Partition Tolerance: The system continues to operate despite an arbitrary number of messages being dropped or delayed by the network.

Indexing Mechanics

Indexes: Data structures (often B-Trees or Hash tables) that improve the speed of data retrieval operations on a database table.
Clustered Index: Sorts and stores the data rows in the table based on their key values. There can be only one per table.
Non-Clustered Index: Contains the non-clustered index key values, and each key value entry has a pointer to the data row that contains the actual data.

Practical Examples

Implementing ACID in Financial Systems

Consider a bank transfer between two accounts:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the system crashes after the first update, Atomicity ensures the transaction is rolled back, preventing lost funds. Consistency ensures account balances do not drop below a defined minimum constraint. Isolation prevents other concurrent transactions from seeing the intermediate unbalanced state. Durability guarantees the transfer is permanently saved once committed to disk.

CAP Theorem in Action

CP (Consistency + Partition Tolerance): Systems like MongoDB (in its default strict configuration) prioritize data consistency. If a network partition occurs, the system might reject requests rather than return stale or conflicting data.
AP (Availability + Partition Tolerance): Systems like Cassandra prioritize availability. During a network partition, they will always accept writes and serve reads, but the data might be temporarily inconsistent across nodes.

Using Indexes for Query Performance

If an application frequently queries users by email, searching a large database without an index results in a slow, full table scan.

-- Creating an index on the email column
CREATE INDEX idx_users_email ON users(email);

-- This query now utilizes the index for rapid retrieval
SELECT * FROM users WHERE email = 'test@company.com';

The index creates a fast-lookup data structure mapping emails to their respective row locations on disk, drastically reducing query execution time from linear to logarithmic complexity.

Foundations