Choosing and using a database

The two big families

Relational databases (PostgreSQL, MySQL) store data in tables with rows and columns, linked by keys. You query them with SQL and can join tables. Their key promise is ACID transactions:

• Atomicity: a group of changes happens entirely or not at all.
• Consistency: rules such as foreign keys always hold.
• Isolation: concurrent transactions do not see each other's half-finished work.
• Durability: once committed, data survives a crash.

NoSQL databases is a loose label for stores that give up some relational features for scale or flexibility:

Type	Shape	Examples	Good for
Key-value	key maps to a blob	Redis, DynamoDB	caches, sessions, huge lookups
Document	JSON-like documents	MongoDB, Firestore	content with varied fields
Wide-column	rows with flexible columns	Cassandra, HBase	massive write volumes, time series
Graph	nodes and edges	Neo4j	relationship-heavy queries

A quick map from the kind of question you ask to the kind of store that answers it best:

100%

Figure SDA-FND-04-1 · Which kind of store fits the questions you ask · © forcsguy.com

Image: MarkBuckawicki · CC0 · Source · Wikimedia Commons: Wooden Card Catalog Furniture

Indexes: how reads get fast

Without an index, finding rows where email = '[email protected]' means reading every row. With an index on email the database keeps a sorted structure (usually a B-tree) and finds the row in a few steps, even among millions.

100%

Figure SDA-FND-04-2 · Indexed lookup versus full scan · © forcsguy.com

The trade-off: each index takes space and every write must update it. Index the columns you filter, join and sort on, and do not index everything.

Useful habits:

• Look at how the database plans a query (EXPLAIN) before guessing.
• A composite index on (user_id, created_at) serves queries on user_id alone or on both, but not on created_at alone. Column order matters.
• Very low-variety columns (like a true/false flag) rarely benefit from an index.

Where relational databases get strained

A single relational database handles a lot, but you may hit limits:

• Read load: add copies that serve reads (lesson 5).
• Write load or data size: split the data across several databases, called sharding (lesson 5).
• Shape of data: huge volumes of simple records, or extremely flexible documents, may fit another model better.

Choosing: ask about the access pattern

1. What are the most frequent queries? By key, by range, by search, by relationship?
2. Which guarantees are non-negotiable? Strict consistency for money, or "eventually correct" for like counts?
3. How big and how fast will it grow?
4. What does the team already know how to operate?

A worked example: what an index is worth

A table holds 100 million orders, and the app asks "show this customer's orders" thousands of times a second.

• Without an index the database reads every row to find matches: a sequential scan. At, say, 200 bytes a row that is 20 GB read from disk or cache for a single query, taking seconds. Do that 1,000 times a second and the database collapses.
• With a B-tree index on customer_id the database walks a shallow tree. A page holds a few hundred keys, so 100 million rows need only about 4 levels: root, two internal levels, a leaf. That is roughly 4 page reads to find the row locations, then a few more to fetch the rows. Most of the upper levels live in memory, so the real cost is one or two disk reads, in milliseconds.

The price: every index must be updated on every insert, update and delete. A table with 5 indexes turns one logical insert into about 6 physical writes, and the indexes take space (often as much as the table). So index what your queries actually filter, join and sort on, and not much else.

100%

Figure SDA-FND-04-3 · Finding one row among a hundred million with a B-tree · © forcsguy.com

What a transaction does for you

A transaction groups several changes so they succeed or fail together. A transfer of 100 from account A to account B is two updates, and it must never be applied halfway. Relational databases give transactions the ACID properties:

• Atomic: all of it happens or none of it.
• Consistent: rules such as "balance never negative" are enforced.
• Isolated: concurrent transactions do not see each other's half-finished work (to a degree you choose).
• Durable: once the database says "committed", the change survives a crash.

Durability comes from the write-ahead log (WAL): before changing the data pages, the database appends the change to a log and forces it to disk. If the machine crashes, restart replays the log and the committed work reappears.

100%

Figure SDA-FND-04-4 · Why a committed write survives a crash · © forcsguy.com

You can build a toy version in the write-ahead log lab.

Mistakes people make

• No index on the column you filter by, especially foreign keys. Use EXPLAIN to see whether a query scans.
• Too many indexes. Each one slows writes and uses memory. Drop the ones nobody uses.
• The N+1 query. Loading 100 orders and then running one query per order for its items is 101 round trips. Load them together with a join or a single IN query.
• Money as floating point. Use integers (cents) or exact decimals.
• Choosing NoSQL to avoid thinking about schema. If your data is relational, you will rebuild joins and constraints by hand, badly.
• Treating the database as a queue or a log. Polling a hot table for work is a common cause of lock contention (see the SKIP LOCKED lesson for doing it properly).
• Never testing a restore. A backup you have not restored is a hope, not a backup.

Try it yourself

Open the indexes in depth lesson and the query optimisation lesson next. In the System Builder, see how much load a cache and replicas take off a single database.

Real-world reading