Data Systems
SQL Deep Dive
SQL databases remain the backbone of most systems. Master PostgreSQL and MySQL internals, indexing strategies, transaction isolation levels, and scaling patterns.
- ACID — Strong consistency guarantees for critical data
- Indexing — B-trees, hash indexes, and query optimization
- Scaling — Read replicas, connection pooling, and sharding
SQL databases are not legacy—they are battle-tested foundations for reliable systems.
SQL Database Architecture
Understanding the internal architecture of SQL databases is essential for making informed design decisions.
PostgreSQL vs MySQL
| Feature | PostgreSQL | MySQL |
|---|---|---|
| ACID Compliance | Full | InnoDB only |
| JSON Support | Excellent (JSONB) | Good (JSON) |
| Full-Text Search | Built-in | Built-in |
| Replication | Streaming, logical | Asynchronous, semi-sync |
| Extensions | Rich ecosystem | Plugin-based |
| Performance | Complex queries | Simple queries |
| Concurrency | MVCC | MVCC (InnoDB) |
| Best For | Complex analytics, geospatial | Web apps, read-heavy |
Storage Engine Internals
B-Tree Indexing
Buffer Pool and Page Management
Transaction Isolation Levels
| Isolation Level | Phenomena Prevented | Performance |
|---|---|---|
| Read Uncommitted | Dirty reads | Highest |
| Read Committed | Dirty reads | High |
| Repeatable Read | Dirty, non-repeatable reads | Moderate |
| Serializable | All anomalies | Lowest |
Indexing Strategies
When to Create an Index
| Access Pattern | Index Type | Example |
|---|---|---|
| Equality lookup | B-tree | WHERE id = 123 |
| Range query | B-tree | WHERE date > '2024-01-01' |
| Full-text search | GIN/GiST | WHERE content ILIKE '%keyword%' |
| Geospatial | GiST | WHERE ST_DWithin(loc, point, 1000) |
| Array contains | GIN | WHERE tags @> ARRAY['sql'] |
| JSON field | GIN | WHERE metadata @> '{"key": "value"}' |
Composite Indexes
Query Optimization
EXPLAIN ANALYZE
The most important tool for query optimization:
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
GROUP BY u.name
ORDER BY COUNT(o.id) DESC
LIMIT 10;
Common Performance Anti-Patterns
| Anti-Pattern | Problem | Solution |
|---|---|---|
SELECT * | Fetches unnecessary columns | Select only needed columns |
| N+1 queries | Repeated queries in loop | Use JOIN or batch loading |
| Missing indexes | Full table scans | Add appropriate indexes |
| Over-indexing | Slow writes, storage waste | Remove unused indexes |
| Implicit type conversion | Prevents index usage | Match types explicitly |
Scaling SQL Databases
Read Replicas
Connection Pooling
Practice Exercises
-
Index Design: Given a users table with 100M rows and these query patterns, design the optimal indexes:
- Lookup by email (unique)
- Find users by city and age range
- Search users by name (partial match)
- Get recent users by creation date
-
Query Optimization: Rewrite this query for better performance:
SELECT * FROM orders WHERE YEAR(created_at) = 2024 AND status IN ('pending', 'processing') ORDER BY created_at DESC; -
Scaling Decision: Your PostgreSQL database handles 10K QPS reads and 1K QPS writes. The read latency is 50ms P99. Design a scaling strategy that maintains <100ms P99 read latency while supporting 2x growth.
-
Transaction Design: Design the transaction boundaries for a banking transfer operation. Consider isolation levels, deadlock prevention, and recovery from failures.
What to Learn Next
-> NoSQL Deep Dive Document, key-value, column-family, and graph databases.
-> PostgreSQL Deep Dive Advanced PostgreSQL features, extensions, and optimization.
-> Database Indexing B-trees, hash indexes, and indexing strategies.
-> Data Replication Sync vs async replication, leader election, and consistency.
-> Choosing the Right Database Systematic framework for database selection.
-> Data Partitioning Sharding strategies, consistent hashing, and partition keys.