Manab's Notes - SECTION 5: Data Architecture

21. How do you pick between relational vs document database?

Not a religious choice — match the data model to the access pattern.

Choose relational (PostgreSQL, MySQL) when:

Data has complex relationships requiring joins across entities
ACID transactions across multiple entities are required
Schema is well-defined and stable
Ad-hoc querying flexibility is needed
You need strong consistency guarantees

Choose document (MongoDB, DynamoDB, Firestore) when:

Data is naturally hierarchical and consumed as a whole unit (e.g., a user profile with embedded addresses)
Schema is variable or evolves frequently (sparse attributes)
Access pattern is well-defined and key-based (no complex joins)
Horizontal sharding is a primary requirement from day one
Write throughput at scale exceeds what a single relational node can handle

Practical guidance: Start with PostgreSQL. It handles documents (JSONB), time series, full-text search, and graph queries better than many think. Only move to a specialised document store when you have a concrete scaling or data model requirement that PostgreSQL genuinely can't serve.

22. When would you use distributed transactions vs eventual consistency?

Distributed transactions (2PC, XA):

Use when: data correctness is non-negotiable, the operation is short-lived, and performance can be sacrificed (financial account transfers, inventory reservation)
Avoid when: operations span many services or are long-running — 2PC holds locks and blocks progress, creating performance and availability bottlenecks

Eventual consistency:

Use when: temporary inconsistency is acceptable (user profile updates propagating to downstream services, search index updates, analytics)
Use when: availability and partition tolerance matter more than strict consistency (shopping cart, social feed, recommendation data)

Design principle: Push the consistency boundary as far inward as possible. Strong consistency within a bounded context (one service, one database); eventual consistency at the boundaries between contexts.

The middle ground: Saga pattern gives you a workflow that is eventually consistent but still guarantees a defined outcome (success or compensated failure) without holding distributed locks.

23. How do you design an audit-friendly system without killing performance?

Audit requirements: who did what to which entity, when, and from where.

Approaches:

Event sourcing as audit log: Every state change is an immutable event in an append-only log. The log IS the audit trail. Zero overhead — auditing is a first-class concept, not bolted on.
Change Data Capture (CDC): Capture row-level changes from the database transaction log (Debezium). Ship to an audit store (S3, data warehouse). No application code change required. Performance impact is minimal (reads replication log, not the primary query path).
Application-level audit table: On every mutation, write an audit record (entity_type, entity_id, actor_id, action, timestamp, before_state, after_state) in the same transaction. Keeps audit close to the domain but adds write overhead.
Separate audit store: Write audit events asynchronously to a write-optimised, immutable store (append-only S3 + Athena, ClickHouse, OpenSearch). Never delete from audit store. Decouple audit writes from the critical path.

Performance tips:

Never let audit writes block the critical path — use async pipelines
Compress and partition audit data by date for efficient querying
Retention tiering: hot store (recent 90 days), cold store (S3 Glacier for compliance)

24. What's your strategy for schema evolution without downtime?

The key constraint: During deployment, old and new code run simultaneously. Your schema must support both versions at the same time.

The expand-contract pattern (also called parallel change):

Expand: Add new columns/tables as nullable or with defaults. Old code ignores new columns. New code can start writing to them. No breakage.
Migrate: Backfill data into new columns via a background job (in batches to avoid table locks).
Switch: Deploy code that reads from new columns as the source of truth.
Contract: Remove old columns/tables in a subsequent deployment, after confirming no code references them.

Specific tactics:

Never drop a column in the same deployment that removes the code using it — always do it in a later deployment
Never add a NOT NULL column without a default in a single step on a live table — it locks the table
Use online schema change tools (pt-online-schema-change for MySQL, pg_rewrite or low-lock ALTER TABLE strategies for PostgreSQL, or managed tools like pgroll)
Blue-green deployments buy time: run both schema versions simultaneously across environments

For NoSQL/document stores: Schema evolution is easier structurally (no ALTER TABLE), but harder logically — documents with different shapes coexist. Use versioned schemas and lazy migration (upgrade document shape on read).

Google Sites

Report abuse