CHAPTER 6

Databases and Storage

Choosing, indexing, replicating and partitioning the data layer.

Source: Chapter 6 PDF, pp. 1–10

Learning objective

Choose a data store for a workload, understand transactions and indexes, and recognize when replication, sharding, search or separate analytics storage is justified.

Core PDF content

PDF page 11. Where should an application store its data? p. 1

Imagine a shopping app storing customer profiles, orders, product images, product search and sales reports. These data types have different access patterns, so one storage technology may not be the best fit for every task.

Figure 1: Different data needs can use different storage systems; the architecture should remain as simple as practical. — Figure 1. Different data needs can use different storage systems; the architecture should remain as simple as practical. · PDF p. 1

The first question is not "Which database is popular?" It is "What data do we have, and how will the application use it?"

2. SQL vs. NoSQL p. 1

	Question	Relational / SQL	Non-relational / NoSQL
	Data model	Tables, rows, relationships and defined schemas.	Key-value, document, wide-column, graph and other models.
	Strengths	Transactions, joins, constraints and flexible queries.	Can fit specific high-scale access patterns and flexible models.
	Trade-offs	Write scaling and schema changes need design.	Joins, transactions and consistency vary by system; query patterns often guide modelling.
	Examples	PostgreSQL, MySQL, Oracle, SQL Server.	DynamoDB, Cassandra, MongoDB, Redis, Neo4j.
Practical rule: Start by evaluating a relational database. Choose a specialized database when a real requirement justifies it. NoSQL is not automatically faster, and SQL is not limited to small applications.

PDF page 23. Transactions: how do we keep data correct? p. 2

Consider transferring $500 from Account A to Account B. The deduction and the deposit must behave as one logical operation. If one succeeds but the other fails, the data becomes incorrect.

Figure 2: A transaction commits the related changes together or rolls them back when the operation fails. — Figure 2. A transaction commits the related changes together or rolls them back when the operation fails. · PDF p. 2

ACID property	Plain-language meaning	Example
Atomicity	All operations happen, or none do.	Both sides of a transfer succeed, or the transaction rolls back.
Consistency	Database rules and constraints remain satisfied.	A defined balance or relationship constraint is enforced.
Isolation	Concurrent transactions are controlled so they do not interfere incorrectly.	Two updates do not silently overwrite each other.
Durability	Committed changes survive failures covered by the database guarantees.	A confirmed transfer remains recorded after a crash.

Isolation levels

	Level	What it means
	Read uncommitted	May allow reading changes that have not been committed.
	Read committed	Reads committed data; a later read can return a different value.
	Repeatable read / snapshot	Provides a more stable view during a transaction; exact guarantees vary by database.
	Serializable	Provides behavior equivalent to transactions running one at a time; conflicts may require retries.
Write skew: Two doctors each see that two doctors are on call, then both mark a different doctor off call. Each change looks valid alone, but together no doctor remains on call. Stronger isolation, appropriate locks or a database constraint can prevent this.

PDF page 34. Normalization and indexes p. 3

Normalization reduces repeated facts. For example, keep customer details in a customer table and link orders to the customer ID, instead of copying the same address into every order. Denormalization deliberately duplicates or pre-combines data to speed up important reads.

Indexes help a database find records without scanning the entire table. They use extra storage and add work to inserts, updates and deletes.

Figure 3: A suitable index can narrow the search; without one the database may inspect many rows. — Figure 3. A suitable index can narrow the search; without one the database may inspect many rows. · PDF p. 3

Index type	Useful for	Caution
B+ tree	Equality, ranges, sorting and prefix queries.	Uses space and adds write cost.
Composite (a, b, c)	Queries filtering on the leading columns.	Column order matters; leftmost-prefix behavior is important.
Covering	Query fields all available in the index.	Wider index means more storage and writes.
Hash	Equality lookups where supported.	Not designed for range queries.
Partial / expression	Specific rows or expressions, e.g. lower(email).	Query needs to match applicable predicate/expression.
Inverted / geospatial	Text search / spatial queries.	Specialized structures with additional write or maintenance cost.

Use EXPLAIN to inspect query plans. Watch for unnecessary full scans, low-selectivity indexes, functions applied to indexed columns, implicit type casts and the N+1 query problem. Use joins, batching or data loaders when appropriate. Connection pooling helps avoid excessive database connections.

PDF page 45. Replication: keeping copies of data p. 4

Replication copies data to other database nodes. It can improve availability and read capacity, but replicas can lag behind the primary.

Figure 4: A common primary-replica layout: writes go to the primary; replicas receive changes and may serve reads. — Figure 4. A common primary-replica layout: writes go to the primary; replicas receive changes and may serve reads. · PDF p. 4

Pattern	How it works	Trade-off
Single leader	One primary accepts writes; replicas apply changes.	Simple write model; primary capacity and failover need planning.
Multi-leader	Several nodes accept writes and replicate to one another.	Can reduce regional write latency; conflicts need resolution.
Leaderless	Reads and writes coordinate with a configured number of replicas.	High availability and tunable consistency, but repair and conflict handling are required.

Synchronous vs. asynchronous replication

Mode	Behavior	Trade-off
Synchronous	Write waits for required replica acknowledgement.	Can reduce acknowledged-write loss, but adds latency and depends on configuration.
Asynchronous	Primary acknowledges before all replicas catch up.	Lower write latency; recent writes may be missing during failover.

Failover must choose and promote a suitable replica, redirect writers and prevent the old primary from accepting conflicting writes. Read-your-writes violations can happen when a user writes data and then reads from a lagging replica.

PDF page 56. Sharding: splitting data across machines p. 5

Replication creates copies. Sharding divides the dataset into partitions, each responsible for a portion of the data. It can help when a single database cannot hold the data or handle the write workload.

Figure 5: A router maps each request to a shard; each shard can have its own replicas. — Figure 5. A router maps each request to a shard; each shard can have its own replicas. · PDF p. 5

Strategy	How it works	Main trade-off
Range	Contiguous key or time ranges.	Range scans are convenient, but some ranges can become hot.
Hash	Hash a key to choose a shard.	Can spread data; range queries scatter and simple modulo can make re-sharding costly.
Directory	Lookup table maps keys/tenants to shards.	Flexible placement; directory needs reliable management and caching.
Geographic	Assign data by region or country.	Improves locality and residency fit, but load can be uneven.
Composite	Combine attributes, e.g. tenant ID plus user hash.	Balances locality and load but adds design complexity.

Choose a shard key with high cardinality, even load distribution and good locality for common requests. Examples include user_id for user data or conversation_id for chat. One very popular account can create a hot partition; cache it, split the hot key or give a large tenant dedicated capacity. Re-sharding and cross-shard work Virtual shards can simplify rebalancing. Online migration commonly copies existing data, captures new changes, verifies the new copy, switches reads, and then switches writes. Cross-shard joins and transactions are expensive or may be unsupported, so co-locate related records where possible.

PDF page 67. NoSQL families and search p. 6

NoSQL describes several data models, not one database type. Match the model to the operations the application needs.

Family	Stores data as	Common use
Key-value	Key and associated value.	Sessions, carts, caches and simple lookups.
Document	Nested JSON-like records.	Profiles, catalogs and content.
Wide-column	Partitioned rows and column families.	Large write workloads and time-ordered data.
Graph	Nodes and relationships.	Social graphs, fraud networks and recommendations.
Time-series	Timestamped measurements.	Monitoring, IoT and financial metrics.
Search	Inverted index and ranking data.	Full-text search, facets and logs.
Vector	Embeddings and similarity indexes.	Semantic search, RAG and recommendations.
NewSQL	SQL with distributed transaction support.	Distributed relational workloads requiring strong consistency.

Search engines and inverted indexes A search engine maps terms to the documents that contain them. Instead of scanning every document, it looks up posting lists and combines the matching document IDs. It also analyzes words and ranks results by relevance.

Figure 6: Example: “cheap AND Paris” returns documents 1 and 3 from the inverted index. — Figure 6. Example: “cheap AND Paris” returns documents 1 and 3 from the inverted index. · PDF p. 6

Search indexes are commonly derived from the primary database using events or change data capture (CDC). They may update near-real-time rather than instantly, so keep the source of truth separate when appropriate.

PDF page 78. Object, block and file storage p. 7

Not all data belongs in a database. The storage interface should fit how the application reads and writes the data.

Figure 7: Three common storage interfaces with different access patterns. — Figure 7. Three common storage interfaces with different access patterns. · PDF p. 7

Type	Interface	Use it for	Examples
Object	Access objects by key through an API.	Media, backups, data lakes, ML datasets.	S3, GCS, Azure Blob.
Block	Raw volumes attached to compute.	Database disks and low-latency random I/O.	EBS, Persistent Disk.
File	Files and directories; often shared.	Shared folders, HPC and training scratch.	EFS, Filestore, Lustre.

Durability, replication and storage tiers Three full copies of data require about 200% extra storage beyond the original. Erasure coding stores data plus recovery fragments and can reduce overhead, but adds processing and recovery complexity. The correct approach depends on durability, performance and cost targets.

Tier	Meaning	Typical approach
Hot	Frequently accessed.	Standard storage or SSD for low-latency access.
Warm	Occasional access.	Lower-cost infrequent-access options.
Cold / archive	Rarely needed, retained long term.	Archive storage, accepting slower retrieval.

Lifecycle rules can move older data to cheaper tiers automatically. For large files, pre-signed upload URLs, multipart or resumable uploads, and checksum verification can improve the upload path.

PDF page 89. OLTP vs. OLAP: operations and analytics p. 8

Customer-facing applications and historical analysis have different workloads. Mixing large analytical queries with live transactions can make the operational system slower.

Figure 8: Operational changes flow to an analytics pipeline, which feeds the warehouse and reports. — Figure 8. Operational changes flow to an analytics pipeline, which feeds the warehouse and reports. · PDF p. 8

	OLTP	OLAP
Workload	Many small transactions and point reads/writes.	Large scans and aggregations over history.
Data layout	Often row-oriented for retrieving or changing records.	Often columnar for reading selected columns efficiently.
Examples	PostgreSQL, MySQL, DynamoDB.	BigQuery, Snowflake, Redshift, ClickHouse.
Freshness	Current operational data.	Can lag by seconds to hours depending on ingestion.

Data lake, warehouse and lakehouse

Term	What it means
Data lake	Stores raw files, often in object storage.
Data warehouse	Stores organized, query-ready data for analytics.
Lakehouse	Adds table management and transaction features to lake-based data.

Choosing a data store: quick guide

Need	Consider
Transactions, relations, flexible queries	PostgreSQL / MySQL, plus replicas and caching as needed.
Simple key lookups at very large scale	DynamoDB / Cassandra / Redis depending on access and durability needs.
Full-text search	Elasticsearch / OpenSearch.
Relationship traversals	Graph database.
Embedding similarity	Vector index or database such as pgvector.
Media files and backups	Object storage with suitable access controls and lifecycle rules.
Analytics over large datasets	Columnar data warehouse.

PDF page 910. Operational essentials p. 9

A database design is incomplete without a plan for recovery, safe change and monitoring. Backups and point-in-time recovery Keep suitable backups and preserve transaction logs when point-in-time recovery is required. Practice restoring backups; an untested backup is not a proven recovery plan. Change data capture (CDC) CDC streams committed database changes to downstream systems such as search indexes, caches and analytics pipelines. It can avoid fragile dual writes, but consumers still need to handle duplicate events, ordering and retries appropriately. Safe schema migrations

Figure 9: Expand, migrate and contract: introduce a compatible schema first, move data and traffic, remove old fields last. — Figure 9. Expand, migrate and contract: introduce a compatible schema first, move data and traffic, remove old fields last. · PDF p. 9

This pattern reduces the risk of downtime during a change. Each stage should be monitored, and the plan should allow safe rollback where possible. Capacity signals to monitor

Signal	Why it matters
Slow queries and query plans	Reveal inefficient access patterns and missing or unsuitable indexes.
Connection counts and pool wait	Show whether the application is exhausting database connections.
Replication lag	Indicates how far replicas may be behind the primary.
Disk latency / IOPS	Helps identify storage bottlenecks.
Lock waits and deadlocks	Reveal transaction contention.
Table / index growth	Helps plan storage capacity and maintenance.

Chapter recap p. 9

Choose a data store based on the data and the operations the application needs.
ACID transactions protect correctness; indexes speed up suitable queries but add storage and write work.
Replication creates copies; sharding divides data. Both add operational and consistency considerations.
NoSQL models, search indexes and storage types serve different workloads.
Separate analytics from operational transactions when large queries would interfere.
Backups, restoration tests, safe migrations and monitoring are part of database design.

PDF page 10Check your understanding p. 10

Choose the best answer for each question, then review the answer key.

1. Why might an application use a relational database for orders?

A. Relational databases support relationships and transactions
B. Relational databases can only store small datasets
C. All NoSQL databases lose data

2. What does an index primarily help a database do?

A. Find matching records without scanning every row in many cases
B. Automatically replicate every record
C. Guarantee that all queries are fast

3. What is the difference between replication and sharding?

A. They are identical
B. Replication creates copies; sharding divides data into partitions
C. Replication divides data; sharding always makes full copies

4. Why might a team use a search engine alongside its main database?

A. To replace all transactions
B. To support efficient full-text search and relevance ranking
C. To ensure that data is always instantly consistent

5. Why separate OLAP queries from the OLTP database?

A. To avoid large analytical queries competing with operational transactions
B. To prevent all replication
C. Because operational databases cannot store historical records

6. What should a team do before relying on a database backup?

A. Assume the backup is valid
B. Test restoring the backup
C. Delete the transaction logs

Answer key and explanations

1. A. Orders often need related records, constraints and reliable multi-step transactions.

2. A. An index provides an additional lookup structure, but the query planner must still decide whether it is useful.

3. B. Replication supports availability and sometimes read scaling; sharding spreads data and workload across nodes.

4. B. Search engines build specialized indexes for text retrieval and ranking, commonly using data derived from the source database.

5. A. Separating the workloads protects customer-facing transaction performance.

6. B. A restore test verifies that the recovery process actually works.

Review questions for this chapter have not been written and reviewed yet. The chapter text above is the full source content.

References

Chapter 6 PDF, pp. 1–10; figures 1, 2, 3, 4, 5, 6, 7, 8, 9.

Ch. 5 Ch. 7: Distributed Systems Theory

Tip: use ← and → keys to move between chapters.