// Learning · 08

Data & Databases

Where most performance problems actually live.

Review 10 flashcards→

1.Indexes
Full table scanleads toJump straight to rows
A sorted lookup structure so queries skip reading every row.
Open the diagram→
- Orders query fails / error path Full table scan (no index)
- Orders query uses / calls Composite index (1 lookup)
- Composite index uses / calls Index seek (2 seek)
- Index seek uses / calls Few rows returned (3 fetch)
2.ACID Transactions
Several related writesleads toAll or nothing
A group of writes that succeeds together or not at all.
Open the diagram→
- BEGIN uses / calls Insert order (1 insert order)
- Insert order uses / calls Insert lines (2 insert lines)
- Insert lines uses / calls Decrement stock (3 decrement)
- Decrement stock uses / calls COMMIT (4 COMMIT)
- Decrement stock fails / error path Stock check fails (stock < 0)
- Stock check fails fails / error path ROLLBACK (5 ROLLBACK)
3.Isolation Levels
Concurrent writers collideleads toPick which anomalies to allow
How much concurrent transactions may see of each other.
Open the diagram→
- T1 uses / calls Seats row (1 read)
- T2 uses / calls Seats row (2 read)
- T1 creates Bookings table (3 insert)
- T2 creates Bookings table (4 insert)
- T1 uses / calls Seats row (1 lock + read)
- T1 fails / error path T2 (2 waits on lock)
- T1 creates Bookings table (3 insert)
- T2 uses / calls Seats row (4 re-read = 0)
4.Replication
One copy of the dataleads toCopies on several nodes
Keep copies of the data on several nodes for reads and failover.
Open the diagram→
- Web app uses / calls Primary (1 write)
- Web app uses / calls Replica 1 (2 read)
- Web app uses / calls Replica 2 (2 read)
- Primary replicates / copies Replica 1 (3 async log ship)
- Primary replicates / copies Replica 2 (3 async log ship)
- Primary fails / error path Primary down (crash)
5.Sharding
Writes outgrow one serverleads toData split by shard key
Split one dataset across databases by a key, each owning a slice.
Open the diagram→
- Shard router uses / calls Shard 1 (1 by tenant_id)
- Shard router uses / calls Shard 2
- Shard router uses / calls Shard 3
- Cross-shard query fails / error path Shard 1
- Cross-shard query fails / error path Shard 2 (costly fan-out)
- Cross-shard query fails / error path Shard 3
6.Denormalization
Same joins on every readleads toData stored read-ready
Store data redundantly so reads stop paying for joins.
Open the diagram→
- Product list uses / calls Normalized tables (1 4-table join)
- Product writes creates products (1 write)
- products replicates / copies product_list (2 same tx)
- Product list uses / calls product_list (3 one lookup)
7.Cache-Aside
Same data read repeatedlyleads toServed from cache first
Check the cache first; on a miss, load from the DB and store it.
Open the diagram→
- Product API uses / calls Cache (1 GET (miss))
- Product API uses / calls products DB (2 SELECT)
- Product API creates Cache (3 SET + TTL)
- Product update uses / calls products DB (4 UPDATE)
- Product update uses / calls Cache (5 DEL key)
8.N+1 Queries
One query per rowleads toOne query for the set
Loading a list, then querying again for every item in it.
Open the diagram→
- Orders endpoint uses / calls Orders DB (1 SELECT orders)
- Orders endpoint fails / error path Lines query x N (2 per order)
- Orders endpoint uses / calls Orders DB (2 queries total)
9.Optimistic Concurrency
Two users edit one recordleads toSecond save is rejected
Detect conflicting edits at save time instead of locking up front.
Open the diagram→
- User A creates Order row (1 v7 to v8)
- User B uses / calls Order row (2 WHERE v = 7)
- Order row fails / error path 412 Precondition (3 0 rows)
10.Connection Pooling
Open a connection per queryleads toReuse a warm pool
Reuse a small set of open connections instead of opening new ones.
Open the diagram→
- App instances uses / calls Database (direct)
- App instances uses / calls Connection pooler (pooled)
- Connection pooler uses / calls Database (fewer conns)
- App instances fails / error path Leaked connection (not closed)
- Leaked connection fails / error path Database (holds a slot)

1.Indexes

2.ACID Transactions

3.Isolation Levels

4.Replication

5.Sharding

6.Denormalization

7.Cache-Aside

8.N+1 Queries

9.Optimistic Concurrency

10.Connection Pooling