Chapter 4 Interview: Databases & Storage

Yeah so the way I think about it — it starts with the data relationships. If I've got highly structured data with lots of relationships, like an order system where orders reference users and products and inventory, I'm reaching for SQL every time. You get ACID transactions, you get JOINs, and your data integrity is enforced at the database level.

But if the data is more document-shaped — like user profiles where each one might have different fields, or if I'm dealing with massive write throughput and can tolerate eventual consistency — that's where NoSQL shines. Something like MongoDB for flexible documents or Cassandra for high write volumes across clusters.

In my experience, most teams default to SQL and only reach for NoSQL when they hit a specific scaling wall or have genuinely unstructured data. The "choose NoSQL because it scales better" argument is usually premature — Postgres can handle way more than people think.

• SQL: structured data, relationships, ACID needs, complex queries
• NoSQL: flexible schemas, horizontal scale, high write throughput, denormalized access patterns
• Hybrid: many systems use both — SQL for core data, Redis/Mongo for specific use cases

So sharding is basically splitting your database horizontally — you take your data and distribute it across multiple machines based on some key. I'd probably start by saying you don't shard until you absolutely have to, because it introduces serious complexity.

When would you shard? When a single machine can't handle your data volume or throughput anymore. You've already done read replicas, you've optimized your queries, you've added caching — and you're still hitting limits. That's when sharding enters the picture.

For the "how" — you pick a shard key, and that decision is critical. A bad shard key gives you hot spots where one shard gets hammered. Like, if you shard by user ID with a hash function, you get pretty even distribution. But if you shard by date, all recent traffic hits one shard. You've gotta think about your access patterns.

The painful parts? Cross-shard queries become expensive, you can't do simple JOINs across shards, and rebalancing when you add shards is a whole project.

• Shard key choice: determines distribution — hash-based (even) vs range-based (locality)
• Hot spots: uneven key distribution overloads specific shards
• Cross-shard ops: JOINs and transactions become application-level concerns
• Resharding: adding shards requires data migration — consistent hashing helps

I'd explain it like this — an index is basically a separate data structure, usually a B-tree, that maintains a sorted reference to your rows. Instead of scanning every row in a table to find matches, the database walks the tree and jumps straight to the relevant data. It's like the index at the back of a textbook versus reading every page.

The trade-off is pretty straightforward: reads get faster, writes get slower. Every INSERT, UPDATE, or DELETE now has to maintain the index structure too. Plus indexes take up disk space — sometimes significant space for large tables with many indexes.

In practice, I think about it this way: index the columns you filter on, the columns you join on, and the columns you sort by. But don't just index everything — I've seen tables with 15 indexes where writes are painfully slow. You've gotta profile and be intentional about it.

Composite indexes are also worth mentioning — the column order matters. An index on (country, city) helps queries filtering by country alone, but NOT queries filtering by city alone. Left-to-right prefix rule.

• B-tree: balanced tree structure, O(log n) lookups vs O(n) full scan
• Write penalty: every mutation updates the index — more indexes = slower writes
• Storage cost: indexes consume additional disk space
• Composite index ordering: leftmost prefix rule determines query eligibility

Right, so replication lag is that window where you write to the primary but the replicas haven't caught up yet. It's inherent to async replication, and honestly you can't eliminate it entirely — you manage it.

The first technique I'd reach for is read-your-own-writes consistency. After a user writes something, their subsequent reads go to the primary — or to a replica that's confirmed it's caught up to that write's position in the replication log. Everyone else can read from replicas with slight staleness.

Another approach is using monotonic reads — you pin a user to the same replica so they never see time go backwards. Without this, a user might hit replica A (caught up) then replica B (lagging) and see data disappear.

For critical paths, you can do synchronous replication to at least one replica before acknowledging the write — semi-sync. It adds latency to writes but guarantees at least one replica has the data. It's a spectrum between performance and consistency.

• Read-your-own-writes: route user's reads to primary after their writes
• Monotonic reads: pin sessions to same replica to avoid "going back in time"
• Semi-synchronous: wait for at least one replica before ACK
• Lag monitoring: alert on replica lag and divert traffic if too stale

Okay so I'd start by identifying the core entities and their relationships. You've got users, products, orders, order_items, categories, and probably inventory and payments.

Users table is straightforward — id, email, hashed password, addresses as a separate table because a user can have multiple. Products have id, name, description, price, category_id. I'd keep inventory as its own table — product_id, warehouse_id, quantity — because inventory management has different access patterns than product browsing.

Orders are the interesting part. An orders table with id, user_id, status, total, timestamps. Then order_items with order_id, product_id, quantity, price_at_purchase. That last column is crucial — you snapshot the price at purchase time because product prices change. Never reference the live price for historical orders.

For categories, I'd use an adjacency list or materialized path if there's deep nesting. And I'd definitely have indexes on user_id in orders, product_id in order_items, and status + created_at for the admin dashboard queries.

• Core tables: users, addresses, products, categories, orders, order_items, inventory
• Price snapshot: store price_at_purchase in order_items — never derive from live data
• Separate concerns: inventory vs product catalog have different write patterns
• Indexes: foreign keys, status fields, timestamp ranges for common queries

So denormalization is intentionally duplicating data to avoid expensive JOINs at read time. I'd reach for it when you've got a read-heavy workload and specific queries that are too slow even with proper indexes.

Classic example — a product listing page that shows the seller's name and rating. In a normalized schema, that's a JOIN across products, users, and maybe a ratings aggregate. If that page gets millions of hits, you might store seller_name and seller_rating directly on the product row. Reads become a single table scan but now you've gotta keep that data in sync when a seller updates their name.

The rule I follow: normalize first, measure, then denormalize the specific hot paths. And when you do denormalize, you need a clear strategy for keeping duplicated data consistent — whether that's triggers, application-level updates, or async events that propagate changes.

I'd also mention that some "denormalization" is really just caching in disguise. Materialized views, precomputed aggregates — same concept, different mechanism.

• When: read-heavy paths with expensive JOINs that indexes can't fix
• Cost: data duplication, sync complexity, potential inconsistency
• Sync strategies: DB triggers, app-level writes, async event propagation
• Alternatives: materialized views, caching layers, precomputed aggregates