Core Development
Databases & SQL
Almost every application is, underneath, a thin layer of logic wrapped around a database. This module goes far beyond "learn SQL" — it covers the full landscape a working engineer actually chooses between today: relational databases like PostgreSQL and MySQL, non-relational stores like MongoDB and Redis, and the newest category to become mainstream infrastructure, vector databases like Pinecone and Weaviate, now core to how AI features actually work.
Topic Map
Everything in this module, at a glance
In Depth
Every topic, explained — with real-world industry context
Relational Databases & SQL
Relational databases organize data into tables with strict, defined relationships — SQL is the language used to define, query, and modify that data.
Basics
- Tables, rows, columns & primary keys
- Basic SQL — SELECT, INSERT, UPDATE, DELETE
- Filtering, sorting & aggregating data
- Relationships — foreign keys & joins
Intermediate
- Normalization — organizing schema to avoid duplicate/inconsistent data
- Complex joins, subqueries & window functions
- PostgreSQL vs. MySQL — where each is chosen and why
- Views & stored procedures
Advanced
- Schema design for a real, evolving application
- PostgreSQL extensibility — JSON columns, full-text search, extensions
- Query plan analysis (EXPLAIN) for real optimization
- Choosing between denormalization and strict normalization for performance
Real-World Industry Use Case
PostgreSQL has become the go-to open-source foundation for teams specifically because of its extensibility — including handling AI workloads via extensions like pgvector — while MySQL remains a rising, stable choice for standard web application development.
Indexing & Query Optimization
An index is what lets a database find a row without scanning every single one — understanding indexes is the single highest-leverage database performance skill.
Basics
- What an index is, and the table-scan problem it solves
- Primary vs. secondary indexes
- When adding an index helps — and when it doesn't
- Reading a basic query plan
Intermediate
- Composite indexes & column order
- Index types — B-tree, hash, full-text
- The cost of indexes on write performance
- Identifying slow queries in a real application
Advanced
- Covering indexes & query-plan-driven schema decisions
- Partial & expression indexes
- Diagnosing and fixing N+1 query problems at the database layer
- Monitoring query performance in production
Real-World Industry Use Case
A search feature or dashboard that works fine in development but times out in production is, overwhelmingly often, a missing-index problem — this is one of the most common real-world performance incidents at growing companies.
Transactions & ACID Guarantees
A transaction groups multiple operations so they either all succeed or all fail together — the guarantee that makes databases trustworthy for anything involving money or critical state.
Basics
- What a transaction is, and why "half-completed" writes are dangerous
- ACID — Atomicity, Consistency, Isolation, Durability, explained plainly
- BEGIN / COMMIT / ROLLBACK in practice
- A simple multi-step transaction example
Intermediate
- Isolation levels — read committed, repeatable read, serializable
- Race conditions & why isolation level choice matters
- Deadlocks — what causes them & how to avoid them
- Transactions across an ORM, not just raw SQL
Advanced
- Optimistic vs. pessimistic concurrency control
- Distributed transactions & the two-phase commit problem
- Eventual consistency — where strict ACID gets traded away deliberately
- Designing a schema that minimizes transaction contention
Real-World Industry Use Case
A bank transfer moving money from one account to another is the textbook transaction example for a reason — if the debit succeeds but the credit fails, real money disappears. Every payment system in production is built around exactly this guarantee.
Non-Relational (NoSQL) Databases
NoSQL databases trade the strict structure of SQL for flexibility and horizontal scale — a family of different data models, each solving a different shape of problem.
Basics
- Document databases — MongoDB & storing JSON-like data
- Key-value stores — Redis & simple, blazing-fast lookups
- When flexible schema actually helps vs. hurts
- Basic CRUD in a document database
Intermediate
- Wide-column stores — Cassandra & massive write-heavy workloads
- Graph databases — modeling relationships directly (social graphs, recommendations)
- Schema design patterns in a schemaless database
- Choosing SQL vs. NoSQL for a specific real feature
Advanced
- Consistency models in distributed NoSQL systems
- DynamoDB — managed, serverless key-value/document design
- Aggregation pipelines & advanced querying in MongoDB
- Polyglot persistence — using multiple database types in one system deliberately
Real-World Industry Use Case
NoSQL databases like MongoDB, Cassandra and Redis are the standard choice for social media, e-commerce, and big-data workloads specifically because they handle large volumes of flexible, fast-changing data better than a rigid relational schema.
Vector Databases & AI-Native Search
The newest major database category — purpose-built to store and search "embeddings," the numeric fingerprints AI models use to represent meaning, not just keywords.
Basics
- What an embedding is — turning text/images into vectors of numbers
- Similarity search — finding "things like this," not exact matches
- Introduction to Pinecone, Weaviate, Milvus & Qdrant
- pgvector — adding vector search to a Postgres database you already have
Intermediate
- Distance metrics — cosine similarity vs. Euclidean distance
- Indexing strategies for vectors (approximate nearest neighbor / HNSW)
- Metadata filtering alongside vector search
- Building a basic semantic search feature
Advanced
- Retrieval-Augmented Generation (RAG) — giving an LLM real, current knowledge
- Hybrid search — combining keyword and vector search
- Scaling vector search — sharding & horizontal scaling of embeddings
- Choosing a vector database vs. a vector extension for your use case
Real-World Industry Use Case
With the rise of agentic AI, vector databases have moved from a niche tool to core infrastructure — Pinecone, Weaviate, and Milvus now let AI products "remember" and retrieve proprietary company data instantly, which is exactly what powers RAG-based chatbots and AI search features shipping today.
Scaling Databases
A database that works for 100 users needs real architectural changes to work for 100 million — this is where a single database becomes a distributed system.
Basics
- Vertical scaling (a bigger server) vs. horizontal scaling (more servers)
- Read replicas — spreading read traffic across copies
- Basic backup & restore as a scaling/safety prerequisite
- Connection pooling under real load
Intermediate
- Replication — synchronous vs. asynchronous
- Sharding — splitting data across multiple databases
- Choosing a shard key that avoids hot spots
- The CAP theorem, applied specifically to database choice
Advanced
- NewSQL — CockroachDB & Google Spanner, distributed SQL with ACID
- Cloud-native, serverless databases (Aurora, PlanetScale, Neon)
- Multi-region database architecture
- Migration strategy for resharding a live production database
Real-World Industry Use Case
Every global app that survives a regional outage does so because of replication and thoughtful sharding — this is the direct database-layer counterpart to the distributed systems concepts introduced back in the Web Fundamentals module.
Database Tooling, Backup & Security
Working with a database professionally is as much about the tooling and discipline around it as the queries themselves.
Basics
- ORMs (Prisma/Sequelize) — mapping code objects to database rows
- Migrations — versioning your schema like you version your code
- Basic backup strategy — automated, regular & tested
- Environment separation — dev, staging & production databases
Intermediate
- Point-in-time recovery
- Database access control & least-privilege permissions
- Encrypting data at rest & in transit
- Seeding & anonymized data for safe local development
Advanced
- Disaster recovery planning & recovery time objectives (RTO/RPO)
- Auditing & compliance requirements (GDPR-style data handling)
- Zero-downtime schema migrations on a live production database
- Choosing a managed database provider vs. self-hosting, in practice
Real-World Industry Use Case
A "we lost data and had no working backup" incident is one of the most reputation-damaging failures a company can have — this is precisely why backup strategy and migration discipline are treated as core engineering skills, not an afterthought.
Ready to learn this — live?
See how this module fits into the complete curriculum.