- SQL vs NoSQL: https://www.geeksforgeeks.org/difference-between-sql-and-nosql/
- Database Indexing: https://www.geeksforgeeks.org/indexing-in-databases-set-1/
- ACID Properties: https://www.geeksforgeeks.org/acid-properties-in-dbms/
- Database Replication: https://www.geeksforgeeks.org/data-replication-in-dbms/
- Database Partitioning: https://www.geeksforgeeks.org/database-partitioning/
- SQL vs NoSQL: https://www.youtube.com/watch?v=t0GlGbtMTio
- Database Indexing Deep Dive: https://www.youtube.com/watch?v=VIr8_CG4d4w
- Database Fundamentals: https://www.youtube.com/watch?v=W2Z7fbCLSTw
- SQL vs NoSQL - Gaurav Sen: https://www.youtube.com/watch?v=xQnIN9bW0og
Structured data stored in tables with predefined schemas.
| Feature | Description |
|---|---|
| Schema | Fixed, predefined |
| Query Language | SQL |
| ACID | Full support |
| Relationships | Strong (foreign keys) |
| Scaling | Vertical (primarily) |
| Examples | MySQL, PostgreSQL, Oracle |
Flexible data models designed for specific use cases.
Types of NoSQL Databases:
| Type | Description | Examples | Use Cases |
|---|---|---|---|
| Key-Value | Simple key-value pairs | Redis, DynamoDB | Caching, sessions |
| Document | JSON-like documents | MongoDB, CouchDB | Content management |
| Wide-Column | Column families | Cassandra, HBase | Time-series, analytics |
| Graph | Nodes and relationships | Neo4j, JanusGraph | Social networks, recommendations |
| Use SQL When | Use NoSQL When |
|---|---|
| Complex relationships | Simple lookups |
| ACID compliance required | High write throughput |
| Data is structured | Schema flexibility needed |
| Complex queries (JOINs) | Horizontal scaling priority |
| Financial transactions | Real-time data |
Atomicity
- All operations in a transaction complete or none do
- Example: Money transfer - debit AND credit must both succeed
Consistency
- Database moves from one valid state to another
- All constraints are satisfied before and after transaction
Isolation
- Concurrent transactions don't interfere with each other
- Each transaction appears to run in isolation
Durability
- Once committed, data persists even after system failure
- Typically achieved through write-ahead logging
Indexes are data structures that improve the speed of data retrieval operations.
| Index Type | Description | Best For |
|---|---|---|
| B-Tree | Balanced tree structure | Range queries, equality |
| Hash | Hash table based | Exact matches |
| Bitmap | Bit arrays | Low cardinality columns |
| Full-Text | Text search optimized | Search applications |
Pros:
- Faster reads and searches
- Efficient sorting
Cons:
- Slower writes (index maintenance)
- Additional storage space
- Index selection overhead
Writes
↓
[Master/Primary]
↓
Replication (async/sync)
/ | \
[Slave] [Slave] [Slave]
↑ ↑ ↑
← ← ← Reads → → →
| Type | Description | Use Case |
|---|---|---|
| Synchronous | Wait for all replicas | Strong consistency |
| Asynchronous | Don't wait | Higher availability |
| Semi-synchronous | Wait for at least one | Balance |
Time between write on master and visibility on replica.
Split rows across multiple databases.
User 1-1000 → Shard 1
User 1001-2000 → Shard 2
Split columns across multiple tables.
Users table → user_id, name, email
User_Details → user_id, address, preferences
Maintain a pool of reusable database connections.
| Without Pool | With Pool |
|---|---|
| Create new connection per request | Reuse existing connections |
| High overhead | Lower overhead |
| Risk of exhausting connections | Controlled connection count |
| Slower | Faster |
Common performance issue in ORMs.
Problem:
# 1 query for users
users = User.all()
# N queries for posts (one per user)
for user in users:
print(user.posts) Solution - Eager Loading:
# 2 queries total
users = User.all().includes('posts')| Requirement | Recommended |
|---|---|
| Complex transactions | PostgreSQL |
| Simple key-value | Redis, DynamoDB |
| Document storage | MongoDB |
| Time-series data | TimescaleDB, InfluxDB |
| Graph relationships | Neo4j |
| Wide-column/Analytics | Cassandra, HBase |
| Full-text search | Elasticsearch |