SQL Roadmap - simpleprog

Foundations

▸ Execution Plan Reading (EXPLAIN ANALYZE)

▸ Sequential vs Index Scan

▸ Join Strategies (Nested Loop, Hash, Merge)

Core Concepts

▸ Subquery Optimization (Correlation, Materialization)

▸ Index-Only Scan & Covering Indexes

▸ Bitmap Scan & Bitmap Heap Scan

Intermediate Skills

▸ Query Rewriting for Performance

▸ B-Tree Index Internals

▸ Composite Index Column Order

Advanced Topics

▸ Partial & Expression Indexes

▸ Covering Indexes with INCLUDE Columns

▸ GiST, GIN & BRIN Indexes

Expert Level

▸ Index Maintenance (Bloat, Reindex)

▸ Index-Only Scan Optimization

▸ OVER & PARTITION BY Basics

Mastery

▸ ROW_NUMBER, RANK, DENSE_RANK

▸ LAG, LEAD & Offset Functions

▸ FIRST_VALUE, LAST_VALUE & NTH_VALUE

Ecosystem

▸ Window Frame Specification (ROWS, RANGE, GROUPS)

▸ NTILE & Percentile Functions

▸ Filtered Aggregates in Window Functions

Architecture

▸ WITH Clause Basics

▸ Recursive CTE (UNION ALL)

▸ Graph Traversal (Adjacency List)

Optimization

▸ Hierarchy Queries (Org Chart)

▸ CTE Materialization & Optimization

▸ Multiple CTEs in One Query

Deployment

▸ Modifying CTEs (INSERT/UPDATE/DELETE WITH)

▸ Range Partitioning

▸ List & Hash Partitioning

Phase 11

▸ Composite Partitioning

▸ Partition Pruning & Performance

▸ Partition Maintenance (ATTACH/DETACH)

Phase 12

▸ Partitioned Indexes

▸ Sub-partitioning

▸ ACID Properties

Phase 13

▸ Read Committed vs Repeatable Read

▸ Serializable Isolation

▸ Snapshot Isolation (MVCC)

Phase 14

▸ Lost Updates & Skew

▸ Distributed Transactions (2PC)

▸ Transaction Anomalies (Phantom, Non-Repeatable)

Phase 15

▸ Row-Level vs Table-Level Locks

▸ Deadlock Detection & Prevention

▸ Advisory Locks

Phase 16

▸ SKIP LOCKED & NOWAIT

▸ Optimistic Locking Patterns

▸ Lock Escalation

Phase 17

▸ Predicate Locks

▸ Stored Function/Procedure Creation

▸ Cursor Management (Explicit, Implicit, Ref)

Phase 18

▸ Exception Handling (WHEN OTHERS)

▸ Dynamic SQL (EXECUTE IMMEDIATE)

▸ Bulk Collect & FORALL

Phase 19

▸ Autonomous Transactions

▸ Package Organization

▸ JSON/JSONB Internals (PostgreSQL)

Phase 20

▸ JSON Path Queries (jsonpath)

▸ Indexing JSONB (GIN)

▸ Partial JSON Updates

Phase 21

▸ JSON Aggregation Functions

▸ JSON vs Relational Performance

▸ Hybrid Document-Relational Design

Phase 22

▸ tsvector & tsquery Types

▸ GIN Indexes for Full-Text

▸ Ranking (ts_rank, ts_rank_cd)

Phase 23

▸ Highlighting Results (ts_headline)

▸ Thesaurus & Synonym Dictionaries

▸ Language Configurations

Phase 24

▸ Full-Text vs LIKE/ILIKE Performance

▸ Vacuum & Autovacuum Tuning

▸ Work Memory & Resource Allocation

Phase 25

▸ Connection Pooling (PgBouncer)

▸ Effective Cache Size Planning

▸ I/O Bottleneck Analysis (pg_stat_user_tables)

Phase 26

▸ Unused Index Detection

▸ Query Normalization & pg_stat_statements

▸ SQL Injection Prevention

Phase 27

▸ Role-Based Access Control (GRANT/REVOKE)

▸ Row-Level Security (RLS Policies)

▸ Encryption at Rest (pgcrypto)

Phase 28

▸ TLS & Connection Security

▸ Audit Logging (pgaudit)

▸ Secure Connection Pools

Phase 29

▸ Streaming Replication (Physical)

▸ Logical Replication (Publications/Subscriptions)

▸ Conflict Resolution

Phase 30

▸ Synchronous vs Asynchronous Replication

▸ Failover & Timeline Management

▸ Cascading Replication

Phase 31

▸ Zero-Downtime Migration Strategies

▸ Normalization (1NF, 2NF, 3NF, BCNF)

▸ Denormalization for Read Performance

Phase 32

▸ Inheritance & Table Partitioning

▸ Foreign Key Performance

▸ Indexing Strategy Planning

Phase 33

▸ Schema Migration (Flyway, Liquibase)

▸ Timestamp & Versioning Strategies

SQL RoadmapSql

Space mark complete · Esc close · Drag pan · Wheel zoom