Databases flashcards, generated for you.

SQL uses structured schemas with ACID compliance and relational tables; NoSQL uses flexible schemas (document, key-value, graph) and prioritizes availability/partition tolerance (BASE model).

Atomicity (all-or-nothing), Consistency (valid state), Isolation (concurrent safety), Durability (persisted). Critical for financial/transactional systems where data corruption is unacceptable.

Consistency (all nodes see same data), Availability (system always responds), Partition tolerance (survives network splits). You can guarantee only 2 of 3; real systems choose AP or CP.

Normalization reduces redundancy and update anomalies but increases join complexity and query costs; denormalization speeds reads via pre-computed data but risks inconsistency and higher write costs.

Indexes speed up SELECT/WHERE queries from O(n) to O(log n), but consume extra storage, slow down INSERT/UPDATE/DELETE operations, and require maintenance overhead.

Sharding horizontally partitions data across servers by key (e.g., user_id % num_shards). Solves scalability but creates complexity: hotspots, uneven distribution, cross-shard joins become expensive, and rebalancing is hard.

System guarantees that if no new writes occur, all nodes converge to identical state, but reads may temporarily see stale data. Acceptable for social feeds, caches, analytics; unacceptable for inventory, payments.

WAL records every write to disk before applying it in memory. Enables durability (survives crashes) and recovery; trade-off is that every write requires 2 I/O operations (log + data).

Fetching parent records then looping to query children individually results in N+1 queries. Solve via: join in single query, batch loading, lazy loading with eager load hints, or GraphQL DataLoader pattern.

Vertical = bigger machine (CPU, RAM, disk); simple but hits limits and requires downtime. Horizontal = more machines; handles growth better but requires distributed consensus, replication, sharding logic, and cross-node transaction complexity.