SQL Roadmap

Open a topic for notes and resources. Mark it done when you are ready to move on.

33 stages · 131 topics 0% done
SQL
✓ Execution Plan Reading (EXPLAIN ANALYZE)
✓ Sequential vs Index Scan
✓ Foundations
✓ Join Strategies (Nested Loop, Hash, Merge)
✓ Subquery Optimization (Correlation, Materialization)
✓ Core Concepts
✓ Index-Only Scan & Covering Indexes
✓ Bitmap Scan & Bitmap Heap Scan
✓ Query Rewriting for Performance
✓ B-Tree Index Internals
✓ Intermediate Skills
✓ Composite Index Column Order
✓ Partial & Expression Indexes
✓ Advanced Topics
✓ Covering Indexes with INCLUDE Columns
✓ GiST, GIN & BRIN Indexes
✓ Index Maintenance (Bloat, Reindex)
✓ Index-Only Scan Optimization
✓ Expert Level
✓ OVER & PARTITION BY Basics
✓ ROW_NUMBER, RANK, DENSE_RANK
✓ Mastery
✓ LAG, LEAD & Offset Functions
✓ FIRST_VALUE, LAST_VALUE & NTH_VALUE
✓ Window Frame Specification (ROWS, RANGE, GROUPS)
✓ NTILE & Percentile Functions
✓ Ecosystem
✓ Filtered Aggregates in Window Functions
✓ WITH Clause Basics
✓ Architecture
✓ Recursive CTE (UNION ALL)
✓ Graph Traversal (Adjacency List)
✓ Hierarchy Queries (Org Chart)
✓ CTE Materialization & Optimization
✓ Optimization
✓ Multiple CTEs in One Query
✓ Modifying CTEs (INSERT/UPDATE/DELETE WITH)
✓ Deployment
✓ Range Partitioning
✓ List & Hash Partitioning
✓ Composite Partitioning
✓ Partition Pruning & Performance
✓ Phase 11
✓ Partition Maintenance (ATTACH/DETACH)
✓ Partitioned Indexes
✓ Phase 12
✓ Sub-partitioning
✓ ACID Properties
✓ Read Committed vs Repeatable Read
✓ Serializable Isolation
✓ Phase 13
✓ Snapshot Isolation (MVCC)
✓ Lost Updates & Skew
✓ Phase 14
✓ Distributed Transactions (2PC)
✓ Transaction Anomalies (Phantom, Non-Repeatable)
✓ Row-Level vs Table-Level Locks
✓ Deadlock Detection & Prevention
✓ Phase 15
✓ Advisory Locks
✓ SKIP LOCKED & NOWAIT
✓ Phase 16
✓ Optimistic Locking Patterns
✓ Lock Escalation
✓ Predicate Locks
✓ Stored Function/Procedure Creation
✓ Phase 17
✓ Cursor Management (Explicit, Implicit, Ref)
✓ Exception Handling (WHEN OTHERS)
✓ Phase 18
✓ Dynamic SQL (EXECUTE IMMEDIATE)
✓ Bulk Collect & FORALL
✓ Autonomous Transactions
✓ Package Organization
✓ Phase 19
✓ JSON/JSONB Internals (PostgreSQL)
✓ JSON Path Queries (jsonpath)
✓ Phase 20
✓ Indexing JSONB (GIN)
✓ Partial JSON Updates
✓ JSON Aggregation Functions
✓ JSON vs Relational Performance
✓ Phase 21
✓ Hybrid Document-Relational Design
✓ tsvector & tsquery Types
✓ Phase 22
✓ GIN Indexes for Full-Text
✓ Ranking (ts_rank, ts_rank_cd)
✓ Highlighting Results (ts_headline)
✓ Thesaurus & Synonym Dictionaries
✓ Phase 23
✓ Language Configurations
✓ Full-Text vs LIKE/ILIKE Performance
✓ Phase 24
✓ Vacuum & Autovacuum Tuning
✓ Work Memory & Resource Allocation
✓ Connection Pooling (PgBouncer)
✓ Effective Cache Size Planning
✓ Phase 25
✓ I/O Bottleneck Analysis (pg_stat_user_tables)
✓ Unused Index Detection
✓ Phase 26
✓ Query Normalization & pg_stat_statements
✓ SQL Injection Prevention
✓ Role-Based Access Control (GRANT/REVOKE)
✓ Row-Level Security (RLS Policies)
✓ Phase 27
✓ Encryption at Rest (pgcrypto)
✓ TLS & Connection Security
✓ Phase 28
✓ Audit Logging (pgaudit)
✓ Secure Connection Pools
✓ Streaming Replication (Physical)
✓ Logical Replication (Publications/Subscriptions)
✓ Phase 29
✓ Conflict Resolution
✓ Synchronous vs Asynchronous Replication
✓ Phase 30
✓ Failover & Timeline Management
✓ Cascading Replication
✓ Zero-Downtime Migration Strategies
✓ Normalization (1NF, 2NF, 3NF, BCNF)
✓ Phase 31
✓ Denormalization for Read Performance
✓ Inheritance & Table Partitioning
✓ Phase 32
✓ Foreign Key Performance
✓ Indexing Strategy Planning
✓ Schema Migration (Flyway, Liquibase)
✓ Phase 33
✓ Timestamp & Versioning Strategies
Text version of this roadmap

1. Foundations

  • Execution Plan Reading (EXPLAIN ANALYZE)
  • Sequential vs Index Scan
  • Join Strategies (Nested Loop, Hash, Merge)

2. Core Concepts

  • Subquery Optimization (Correlation, Materialization)
  • Index-Only Scan & Covering Indexes
  • Bitmap Scan & Bitmap Heap Scan

3. Intermediate Skills

  • Query Rewriting for Performance: Performance considerations for query rewriting for performance in Sql significantly impact application behavior. Understanding optimization strategies helps avoid common pitfalls and improve runtime characteristics.
  • B-Tree Index Internals: The internal architecture of b-tree index internals in Sql reveals how the runtime handles Indexing Strategies. This knowledge enables more informed design decisions and debugging.
  • Composite Index Column Order

4. Advanced Topics

  • Partial & Expression Indexes
  • Covering Indexes with INCLUDE Columns
  • GiST, GIN & BRIN Indexes

5. Expert Level

  • Index Maintenance (Bloat, Reindex)
  • Index-Only Scan Optimization
  • OVER & PARTITION BY Basics

6. Mastery

  • ROW_NUMBER, RANK, DENSE_RANK
  • LAG, LEAD & Offset Functions
  • FIRST_VALUE, LAST_VALUE & NTH_VALUE

7. Ecosystem

  • Window Frame Specification (ROWS, RANGE, GROUPS): Window functions perform calculations across sets of rows related to the current row without collapsing them like GROUP BY. The OVER clause defines the window frame. PARTITION BY divides rows into groups. ORDER BY orders rows within each partition for running calculations.
  • NTILE & Percentile Functions
  • Filtered Aggregates in Window Functions

8. Architecture

  • WITH Clause Basics
  • Recursive CTE (UNION ALL)
  • Graph Traversal (Adjacency List)

9. Optimization

  • Hierarchy Queries (Org Chart)
  • CTE Materialization & Optimization
  • Multiple CTEs in One Query

10. Deployment

  • Modifying CTEs (INSERT/UPDATE/DELETE WITH)
  • Range Partitioning
  • List & Hash Partitioning

11. Phase 11

  • Composite Partitioning
  • Partition Pruning & Performance: Performance considerations for partition pruning & performance in Sql significantly impact application behavior. Understanding optimization strategies helps avoid common pitfalls and improve runtime characteristics.
  • Partition Maintenance (ATTACH/DETACH)

12. Phase 12

  • Partitioned Indexes
  • Sub-partitioning
  • ACID Properties

13. Phase 13

  • Read Committed vs Repeatable Read
  • Serializable Isolation
  • Snapshot Isolation (MVCC)

14. Phase 14

  • Lost Updates & Skew
  • Distributed Transactions (2PC)
  • Transaction Anomalies (Phantom, Non-Repeatable)

15. Phase 15

  • Row-Level vs Table-Level Locks
  • Deadlock Detection & Prevention
  • Advisory Locks

16. Phase 16

  • SKIP LOCKED & NOWAIT
  • Optimistic Locking Patterns: Various locking & concurrency patterns emerge when working with optimistic locking patterns in Sql. These patterns provide reusable solutions to common programming challenges.
  • Lock Escalation

17. Phase 17

  • Predicate Locks
  • Stored Function/Procedure Creation
  • Cursor Management (Explicit, Implicit, Ref)

18. Phase 18

  • Exception Handling (WHEN OTHERS)
  • Dynamic SQL (EXECUTE IMMEDIATE)
  • Bulk Collect & FORALL

19. Phase 19

  • Autonomous Transactions
  • Package Organization
  • JSON/JSONB Internals (PostgreSQL): The internal architecture of json/jsonb internals (postgresql) in Sql reveals how the runtime handles JSON & Document Queries. This knowledge enables more informed design decisions and debugging.

20. Phase 20

  • JSON Path Queries (jsonpath)
  • Indexing JSONB (GIN)
  • Partial JSON Updates

21. Phase 21

  • JSON Aggregation Functions
  • JSON vs Relational Performance: Performance considerations for json vs relational performance in Sql significantly impact application behavior. Understanding optimization strategies helps avoid common pitfalls and improve runtime characteristics.
  • Hybrid Document-Relational Design

22. Phase 22

  • tsvector & tsquery Types
  • GIN Indexes for Full-Text
  • Ranking (ts_rank, ts_rank_cd)

23. Phase 23

  • Highlighting Results (ts_headline)
  • Thesaurus & Synonym Dictionaries
  • Language Configurations

24. Phase 24

  • Full-Text vs LIKE/ILIKE Performance: Performance considerations for full-text vs like/ilike performance in Sql significantly impact application behavior. Understanding optimization strategies helps avoid common pitfalls and improve runtime characteristics.
  • Vacuum & Autovacuum Tuning
  • Work Memory & Resource Allocation

25. Phase 25

  • Connection Pooling (PgBouncer)
  • Effective Cache Size Planning
  • I/O Bottleneck Analysis (pg_stat_user_tables)

26. Phase 26

  • Unused Index Detection
  • Query Normalization & pg_stat_statements
  • SQL Injection Prevention

27. Phase 27

  • Role-Based Access Control (GRANT/REVOKE)
  • Row-Level Security (RLS Policies)
  • Encryption at Rest (pgcrypto)

28. Phase 28

  • TLS & Connection Security
  • Audit Logging (pgaudit)
  • Secure Connection Pools

29. Phase 29

  • Streaming Replication (Physical)
  • Logical Replication (Publications/Subscriptions)
  • Conflict Resolution

30. Phase 30

  • Synchronous vs Asynchronous Replication
  • Failover & Timeline Management
  • Cascading Replication

31. Phase 31

  • Zero-Downtime Migration Strategies
  • Normalization (1NF, 2NF, 3NF, BCNF)
  • Denormalization for Read Performance: Performance considerations for denormalization for read performance in Sql significantly impact application behavior. Understanding optimization strategies helps avoid common pitfalls and improve runtime characteristics.

32. Phase 32

  • Inheritance & Table Partitioning
  • Foreign Key Performance: Performance considerations for foreign key performance in Sql significantly impact application behavior. Understanding optimization strategies helps avoid common pitfalls and improve runtime characteristics.
  • Indexing Strategy Planning

33. Phase 33

  • Schema Migration (Flyway, Liquibase)
  • Timestamp & Versioning Strategies