NoSQL Flashcards
(18 cards)
What are the fundamental differences between SQL and NoSQL?
SQL: Fixed schema, table-based, ACID compliance, vertical scaling
NoSQL: Flexible schema, various models (document/key-value/column/graph), eventual consistency, horizontal scaling
Key: SQL enforces structure upfront; NoSQL provides runtime flexibility
How does CAP theorem affect database choice?
SQL: Usually CA (Consistency + Availability), sacrificing partition tolerance
NoSQL: Often AP (Availability + Partition tolerance) or CP (Consistency + Partition tolerance)
Impact: SQL may go down during network issues; NoSQL stays up but may serve stale data
How do data modeling approaches differ?
SQL: Normalized tables with foreign keys and JOINs
Document: Embedded JSON-like structures (MongoDB)
Key-Value: Simple key-based lookup (Redis, DynamoDB)
Column-Family: Wide rows, time-series friendly (Cassandra)
Graph: Nodes and relationships (Neo4j)
What consistency options exist?
SQL: Strong consistency (immediate across all nodes)
NoSQL:
Eventual consistency (will be consistent later)
Strong consistency (immediate but impacts availability)
Session consistency (consistent per user session)
Choose based on: Business needs for freshness vs availability
How do scaling approaches differ?
SQL: Vertical scaling, read replicas, complex manual sharding
NoSQL: Horizontal scaling, automatic data distribution, linear growth
Performance: SQL optimized for complex queries; NoSQL for simple operations at scale
Sharding: Manual in SQL; automatic in most NoSQL
Compare querying between SQL and NoSQL.
SQL: Standardized language, complex JOINs, subqueries, aggregations
NoSQL: Vendor-specific languages, limited JOINs, requires denormalization
Examples: SQL for complex reports; NoSQL for simple lookups and aggregations
Trade-off: SQL more expressive; NoSQL faster for targeted operations
How do schema changes work?
SQL: Requires DDL migrations, potential downtime, strong typing
NoSQL: Schema-on-read, no downtime for new fields, app-level validation
Development: NoSQL enables faster iteration; SQL provides stronger guarantees
Best Practice: Version your schemas in NoSQL applications
What transaction support exists?
SQL: Full ACID across multiple tables, complex rollbacks
NoSQL:
Document: Single-document atomicity (MongoDB has limited multi-doc)
Key-Value: Atomic single-key operations
Column: Row-level atomicity
Graph: Full ACID (Neo4j)
Trend: More NoSQL systems adding ACID support
When to use SQL vs NoSQL?
SQL: Complex business logic, financial systems, reporting, stable schemas
Document: Content management, user profiles, catalogs
Key-Value: Caching, sessions, real-time recommendations
Column: Time-series, IoT, logging
Graph: Social networks, recommendations, fraud detection
How do operational aspects compare?
SQL: Mature tooling, established DBA practices, standardized procedures
NoSQL: Vendor-specific practices, automatic cluster management, different monitoring needs
Backup: SQL has point-in-time recovery; NoSQL uses distributed snapshots
Cloud: Managed services reduce complexity for both
How is data integrity handled?
SQL: Foreign keys, constraints, triggers, type enforcement
NoSQL: App-level validation, eventual consistency risks, optional schema validation
Risk: NoSQL denormalization can cause data anomalies
Best Practice: Comprehensive app-layer validation for NoSQL
What are the cost implications?
SQL: Higher hardware costs, licensing fees, specialized expertise
NoSQL: Commodity hardware, often open-source, higher storage due to denormalization
Cloud: NoSQL pricing models often favor distributed workloads
Consider: Development velocity vs operational complexity
How do security approaches differ?
SQL: Row/column-level permissions, mature RBAC, SQL injection risks
NoSQL: Document/collection-level security, app-level auth, NoSQL injection risks
Encryption: Both support at-rest and in-transit encryption
Best Practice: Defense-in-depth regardless of type
What are key tuning differences?
SQL: Index optimization, query plans, partitioning, connection pooling
NoSQL: Data model optimization, shard key selection, consistency tuning
Monitoring: SQL focuses on queries; NoSQL on cluster health
Approach: SQL tunes queries; NoSQL tunes data distribution
How to migrate between SQL and NoSQL?
SQL to NoSQL: Denormalize model, implement app-level joins, dual-write pattern
NoSQL to SQL: Normalize structures, extract relationships, add constraints
Pattern: Strangler Fig - gradually replace old system
Key: Maintain data consistency during transition
How are SQL and NoSQL converging?
SQL Evolution: JSON columns, horizontal scaling solutions, cloud-native designs
NoSQL Evolution: ACID transactions, SQL-like queries, strong consistency options
NewSQL: Distributed SQL with horizontal scaling (CockroachDB, Spanner)
Multi-model: Single systems supporting multiple data models
When should you choose SQL?
- If you need ACID transactions, strict data consistency
- Structured data whose schema won’t change much
- Complex queries with joins
- Mature tooling, predictable performance
- Data integrity
When should you choose NoSQL?
- Unstructured data, flexible schema
- Massive scale - horizontal scaling is easier
- Fast lookups/writes
- Fast development - schema can evolve rapidly
