Data
bases.

A SQL (relational) database stores data in tables with a fixed schema, joined by keys. It is strong at complex queries and at enforcing data integrity. PostgreSQL and MySQL are examples.

A NoSQL database comes in several kinds: document (MongoDB), key-value (Redis), wide-column (Cassandra), graph (Neo4j). They drop the rigid schema to gain speed and horizontal scale. Data is often denormalized, grouping what you read together into one place.

SQL splits data into many tables to avoid duplication, called normalizing. At read time, a query JOINs the tables back together. Writes stay clean and consistent, but joining large tables is expensive.

A NoSQL document store usually denormalizes: it nests related data inside one document, so a single read fetches everything with no join. The price is duplicated data and updates that must touch many places. This is the trade-off between normalizing for clean writes and denormalizing for fast reads.

ACID is four guarantees of a database transaction. Atomicity: the group of operations either all complete or none do. Consistency: data always stays valid against its constraints. Isolation: concurrent transactions do not step on each other. Durability: once committed, it survives even a power loss.

Traditional SQL databases provide strong ACID. This is why financial systems choose them. Many NoSQL databases relax ACID for speed and scale, guaranteeing only eventual consistency.

A transfer has two steps: debit account A, credit account B. If only half runs and the server dies, money vanishes. Atomicity prevents that.

We wrap the steps in a transaction (between BEGIN and COMMIT). If any step fails, the database rolls back everything, leaving it as if nothing happened. There is never a debit without a matching credit. This is the foundation every payment system relies on.

An index lets a database find rows without scanning the whole table. Without one, a query reads every row (a full scan). With one, it jumps straight to what it needs.

Two common index structures. A B-tree is optimized for reads and range queries, the SQL default. An LSM-tree is optimized for heavy writes, writing sequentially then merging later, used in Cassandra and RocksDB. Read-heavy favors B-tree, write-heavy favors LSM.

An index makes reads much faster, but it is not free. Each index costs extra storage. Every write must also update the index, so writes get slower. So only index columns you truly use for filtering or sorting.

When a query is slow, a tool like EXPLAIN shows whether the database does a full scan or uses an index. A full scan on a large table is a sign you need the right index. This is the first tuning step for most slow queries.

A database usually has one primary taking all writes and several replicas taking reads. Copying data from primary to replicas is called replication. Sending writes to the primary and spreading reads across replicas is a read/write split. It handles heavy read load and gives a backup when the primary dies.

Replication has two modes. Synchronous waits for a replica to confirm before reporting the write done, safe but slow. Asynchronous reports done immediately, fast but the replica trails by a moment. That delay is replication lag: reading from a replica right after a write may show stale data. When you need fresh data, read from the primary.

When one database is too big for a single machine, we shard it: split the data into pieces, each on its own machine. A shard key decides which row goes to which machine.

Splitting by a hash of the key spreads data evenly but makes range queries hard. Splitting by range makes range queries easy but invites a hot shard when data piles into one region. Sharding unlocks horizontal scale. The price is that cross-shard joins and transactions become much harder.

The CAP theorem says that when the network between nodes is split (a partition), a distributed system can keep only one of two things: Consistency (every node sees the same latest data) or Availability (every request still gets an answer).

With no partition there is no choice to make. During a partition, a CP system refuses to serve the doubtful part to keep data correct. An AP system keeps answering but accepts possibly stale data. Banks pick CP. Social feeds usually pick AP.