When do you choose SQL vs NoSQL, and what are the consistency trade-offs?
py-sys-003
Your answer
Answer as you would in a real interview — explain your thinking, not just the conclusion.
Model answer
SQL (relational) databases provide ACID transactions, a rich query language (joins, aggregations), schema enforcement, and strong consistency. They excel when data relationships are complex and correctness is non-negotiable — financial records, user accounts, order management. NoSQL databases trade some of these guarantees for horizontal scalability, flexible schema, or specialised data models. Document stores (MongoDB) excel for nested, heterogeneous data. Key-value stores (Redis, DynamoDB) excel for simple, high-throughput lookups. Wide-column (Cassandra) excels for time-series and write-heavy workloads. Graph databases (Neo4j) for traversal-heavy relationship queries. The CAP theorem forces a choice under partitions: SQL databases are typically CP (PostgreSQL, MySQL), Cassandra is AP. In Python: Django/SQLAlchemy for SQL, Motor/PyMongo for MongoDB, redis-py for Redis. Real systems use polyglot persistence — e.g., PostgreSQL for user data + Redis for sessions + Elasticsearch for search.
Code example
# PostgreSQL JSONB — structured + flexible in one store
from sqlalchemy import Column, Integer, String
from sqlalchemy.dialects.postgresql import JSONB
class UserProfile(Base):
__tablename__ = "user_profiles"
id = Column(Integer, primary_key=True)
user_id = Column(Integer, unique=True, index=True)
# Flexible attributes without schema migration
attributes = Column(JSONB, default=dict)
# Query JSON path with GIN index
# CREATE INDEX idx_attrs ON user_profiles USING GIN (attributes);
# SELECT * FROM user_profiles WHERE attributes @> '{"tier": "premium"}';
# MongoDB for truly dynamic schemas
from motor.motor_asyncio import AsyncIOMotorClient
async def save_event(db, event: dict):
# No schema — any shape accepted
await db.events.insert_one(event)
# But: no joins, eventual consistency in replica setsFollow-up
How would you use PostgreSQL JSONB to get document-store flexibility while keeping ACID guarantees — and what are the trade-offs vs MongoDB?