Database Selection and Trade-offs Questions

Data model

• Relational (SQL): Structured, schema-first tables with rows/columns and foreign keys. Good for user profiles and normalized product catalog.
• Non-relational (NoSQL): Schemaless or flexible (document, key-value, column, graph). Good for session tokens (key-value), activity logs (append-heavy documents), and denormalized product views.

Query capabilities

• SQL: Rich declarative queries, complex joins, aggregates, window functions — excellent for cross-entity queries (e.g., user + orders + catalog).
• NoSQL: Query power varies. Document stores support indexing and simple queries; key-value stores are lookup-optimized; complex joins typically require application-side logic or denormalization.

Transaction support

• SQL: Strong ACID transactions across multiple rows/tables by default.
• NoSQL: Often offers eventual consistency or single-document ACID; some systems offer multi-document transactions but with limits or performance trade-offs.

Scaling strategies

• SQL: Vertical scaling common; horizontal sharding possible but complex (manual or via middleware). Read scaling via replicas.
• NoSQL: Designed for horizontal scaling and partitioning (automatic sharding), good for high write throughput and large datasets.

Typical use-cases

• SQL: Financial data, user accounts, relationships requiring consistency and complex queries.
• NoSQL: High-volume logs, session stores, caching, flexible schemas, geo-distributed systems.

Recommendation
Choose relational when you require strong consistency and complex joins (e.g., normalized user profiles with transactions, billing): it simplifies correctness and querying. Choose non-relational when you need high write throughput, flexible schemas, and horizontal scale (e.g., activity logs, session tokens, denormalized product catalogs served to read-heavy APIs). If you need both, use a hybrid approach: primary relational store for transactional core data and NoSQL for logs, caching, and high-throughput or schema-flexible components.

Relational DBs (e.g., PostgreSQL) vs NoSQL (e.g., MongoDB, DynamoDB)

Core differences

• Data model: Relational uses tables, rows, joins and normalized schemas; NoSQL uses document/ key-value/ wide-column/graph models (MongoDB = document, DynamoDB = key-value/wide-column).
• Querying: SQL with expressive joins/aggregations vs APIs/DSLs (MongoDB query language, DynamoDB queries/filters).
• Transactions & consistency: RDBMS provides ACID transactions across multiple rows/tables. NoSQL often favors single-item atomicity and eventual consistency at scale (DynamoDB offers configurable consistency; MongoDB supports multi-document transactions but with limits).
• Schema: RDBMS enforces schema at DB level; changes require migrations. NoSQL supports flexible/implicit schemas, easier schema evolution.
• Indexing: Both support indexes. PostgreSQL: B-tree, hash, GIN, GiST, partial and expression indexes for complex queries. MongoDB: single, compound, TTL, text, hashed indexes; DynamoDB: primary key + secondary indexes (GSI/LSI) optimized for known access patterns.
• Scaling: RDBMS scale vertically or via sharding; NoSQL designed for horizontal scaling and high throughput.

Concrete use-cases

• PostgreSQL: transactional financial system (banking ledger) requiring complex joins, strong consistency, and constraints.
• MongoDB: content management system storing varying article shapes and quick iteration on schema.
• DynamoDB: high-throughput user-session store or time-series telemetry at massive scale with predictable key access patterns.

Tooling/commands I’ve used

• PostgreSQL: psql, CREATE TABLE, ALTER TABLE, EXPLAIN ANALYZE, VACUUM, pg_dump/pg_restore; ORMs (SQLAlchemy, ActiveRecord).
• MongoDB: mongo shell, db.collection.find(), aggregate(), createIndex(), mongodump/mongorestore; Mongoose in Node.js.
• DynamoDB: AWS Console, aws-cli (put-item, query, update-item), DynamoDB local for testing, provisioned vs on-demand capacity tuning.

Trade-offs summary
Choose RDBMS when data integrity, complex transactions and ad-hoc relational queries matter. Choose NoSQL when you need flexible schemas, massive horizontal scale, or predictable access patterns optimized by key-based indexing.

Product catalog — Document store (e.g., MongoDB)

• Why: Catalog items often have variable attributes (clothing size, electronics specs). A document DB stores each product as a self-contained JSON-like document, making schema evolution and denormalized product views easy.
• Query patterns: Read-heavy, product-by-id, filters, faceted search; documents support rich nested queries and indexes (single-field, compound, text, and partial) for these patterns without costly joins.
• Transactions & consistency: Single-document atomicity is usually enough; for multi-document invariants you can use two-phase updates or DB transactions if supported.
• Operational trade-offs: Easier to scale reads via replica sets; sharding helps scale writes but adds complexity. Index design and document sizing matter to avoid performance pitfalls.

Order service — Relational DB (e.g., PostgreSQL)

• Why: Orders require strong ACID guarantees (inventory decrement, payment, order status) and complex constraints (foreign keys, uniqueness). Relational DBs provide robust transactions and integrity.
• Query patterns: Inserts and multi-table reads/joins (orders, line_items, shipments), aggregates, reporting — relational queries and indexes excel here.
• Transactions & consistency: Multi-row, multi-table ACID transactions enforce correctness for billing and inventory.
• Operational trade-offs: Vertical scale limits can be mitigated with read replicas and partitioning. Consider CQRS or event-sourcing if extreme write scale or eventual consistency is acceptable.

Alternatives / hybrid:

• Use polyglot persistence: product catalog in a document store for flexible reads, core order/financial data in PostgreSQL. Use a cache/key-value store (Redis) for hot lookups (session, cart) and an async event stream for integration between systems.

Clarify trade-offs first, then recommend a pattern.

Pros of storing BLOBs in DB:

• Strong transactional guarantees: ACID — file and metadata can be updated atomically.
• Simpler access control if DB auth is central.
• Easier to maintain referential integrity (no orphan blobs if transactions enforced).

Cons of DB:

• Backup size and restore time: DB backups balloon, slower snapshots and more expensive storage.
• Performance: DB I/O optimized for lots of small records; large objects hurt throughput and increase latency for queries.
• Cost: Many DB providers charge more for storage/IO than object storage.
• Scalability: Harder to scale reads (replicas carry full blobs).

Pros of object storage (e.g., S3) + DB URLs:

• Cost-effective: lower per-GB price and lifecycle policies (glacier) reduce cost.
• Performance & scalability: optimized for large objects, integrates with CDN for low-latency reads.
• Smaller DB backups and faster restores (only metadata).
• Features: multipart uploads, versioning, lifecycle, event notifications.

Cons of object storage:

• Weaker transactional guarantees: writes to S3 and DB are separate — risk of inconsistent state (orphan files or broken links).
• Access control complexity: need signed URLs or proxying to enforce auth.
• Extra engineering for upload flow, retries, and garbage collection.

Recommendation for a social media app (frequent uploads/reads):

• Use object storage for media and store immutable metadata/URL in the DB.
• Upload pattern: client uploads directly to S3 via pre-signed multipart URLs (reduces server bandwidth), then calls backend to create DB record referencing S3 URL.
• Ensure consistency: backend validates upload (S3 ETag or upload-complete event via S3 notifications + background job) before marking media as "available". Use idempotent operations and a small state machine: UPLOADING → PENDING_VERIFY → AVAILABLE → DELETED.
• Serve through CDN with signed URLs or short-lived pre-signed URLs to enforce auth.
• Implement garbage collection: periodic job to delete unreferenced or PENDING files after a TTL.
• Backups: back up only DB metadata; rely on object storage durability and lifecycle for long-term retention.
• Cost/perf tuning: use appropriate storage classes, compress images, transcode videos, and cache aggressively.

This pattern balances cost, scalability, performance, and practical consistency with modest engineering complexity.

Situation: We need strong consistency for inventory during flash sales — avoid oversell while sustaining high throughput.

Approach: Compare three options across throughput, correctness (safety), and operational complexity, then give recommendation.

A) Single relational DB row with transactions

• Throughput: Low-to-moderate. A single row becomes a hot spot; row-level locking/serialization limits concurrent updates (tens to low hundreds/sec depending on DB and hardware).
• Correctness: Strong — ACID transactions prevent oversell when using decrement-with-check (UPDATE ... WHERE stock >= n).
• Operational complexity: Low to moderate. Simple to implement; requires DB scaling strategies (read replicas for reads, sharding/partitioning if needed) and careful schema/connection tuning. Risk: availability/latency under flash loads.

B) Distributed counter with CAS in a key-value store

• Throughput: High — K/V stores (Redis, etcd) give very fast ops and CAS enables concurrency without full locks; can handle thousands–tens of thousands/sec.
• Correctness: Good but subtle. CAS ensures atomic compare-and-swap; however, retries under contention increase latency and may starve some requests. Need persistent/replicated store to avoid data loss; lost writes during failover can lead to oversell if not durable.
• Operational complexity: Moderate. Requires careful client retry/backoff, strong durability config (AOF/fsync), and handling leader failover. Also need reconciliation if store and downstream systems diverge.

C) Optimistic reservations with compensating transactions

• Throughput: Very high for initial reservations (fast lightweight writes), because you accept reservations without blocking final decrement.
• Correctness: Eventually consistent; avoids immediate oversell if you cap reservations carefully, but requires robust compensation to handle cancellations/timeouts/failed payments. Risk of oversell if compensation fails or reservation windows overlap.
• Operational complexity: High. Need reservation lifecycle, reliable background jobs to reconcile/expire, idempotent compensating actions, metrics to detect leaks, and possibly quotas to prevent over-reservation.

Recommendation:

• For strict correctness with simple ops and moderate scale: start with relational row transactions.
• For very high throughput with low latency and where you can tolerate more operational effort: use a CAS-backed counter in a durable K/V store (ensure durability and strong replication).
• For massive scale with UX requirements (fast checkout) and where you can accept eventual finalization: use optimistic reservations + robust reconciliation; combine with final commit against a durable store or use a hybrid: CAS for final commit after reservation.

Key trade-offs: correctness vs latency/throughput vs operational burden. In practice, hybrid patterns (fast reservation + CAS-backed final decrement) give a pragmatic balance.