luokai0 Database Systems Expert
Before Starting
- Relational or NoSQL?
- OLTP or OLAP workload?
- Theory or implementation focus?
Core Expertise Areas
Relational Model
Relations: tables with rows and columns, schema defines types. Keys: superkey, candidate key, primary key, foreign key. Relational algebra: select, project, join, union, difference, rename. Normalization: 1NF, 2NF, 3NF, BCNF — eliminate redundancy and anomalies. Functional dependencies: X -> Y means X determines Y.
Query Processing
Query parsing: SQL to parse tree to logical plan. Query optimization: logical to physical plan, cost-based optimizer. Join algorithms: nested loop, hash join, sort-merge join. Index types: B-tree (range queries), hash index (equality), bitmap (low cardinality). Query plan: EXPLAIN output, seek vs scan, index usage.
Transaction Management
ACID: atomicity, consistency, isolation, durability. Isolation levels: read uncommitted, read committed, repeatable read, serializable. Anomalies: dirty read, non-repeatable read, phantom read. MVCC: multi-version concurrency control — readers do not block writers.
Concurrency Control
Two-phase locking: growing phase acquires locks, shrinking releases. Deadlock: detection via waits-for graph, prevention via ordering. Optimistic concurrency: read, validate, write — no locks during read. Timestamp ordering: each transaction gets timestamp, conflicts resolved by order.
Recovery
Write-ahead logging: log record before data page — guarantee durability. ARIES algorithm: analysis, redo, undo phases after crash. Checkpoints: reduce recovery time by limiting log replay. Shadow paging: copy-on-write alternative to logging.
Best Practices
- Always analyze query plans before deploying to production
- Choose isolation level based on actual consistency requirements
- Index foreign keys to avoid full table scans on joins
- Monitor long-running transactions and lock waits
Common Pitfalls
| Pitfall | Fix |
|---|---|
| N+1 query problem | Use joins or eager loading |
| Missing index on join column | Always index foreign keys |
| Using serializable isolation unnecessarily | Profile and choose appropriate level |
| Not handling transaction rollback | Always handle errors and rollback explicitly |
Related Skills
- postgresql-expert
- distributed-systems-cs-expert
- sql-analytics-expert