Chapter 46: PostgreSQL Syllabus

PostgreSQL Syllabus.

This is not just a list of topics. This is the real-world learning roadmap that I would give to any serious student (college student, fresher, backend developer, data analyst, DevOps engineer, or someone preparing for interviews) who wants to go from zero to strong intermediate / production-ready in PostgreSQL in 2025–2026.

I will give you the complete, honest, battle-tested syllabus that actually works — not the official documentation table of contents, but what real companies expect people to know, what appears in interviews (MAANG, startups, product companies), and what saves you from production fires.

PostgreSQL Syllabus 2026 – Realistic & Complete (3–6 months path)

Phase 0 – Setup & First Touch (1–3 days)

1. Installation (choose one path)
   • Docker (recommended 2026)
   • Native (Ubuntu / macOS Homebrew / Windows EDB installer)
   • Cloud free tier (Neon.tech, Supabase, Railway, Fly.io Postgres)
2. First commands in psql
   • \l, \dt, \d table, \?, \h SELECT
   • SELECT version();, SELECT current_timestamp;
3. Create your first playground database
   SQL

   PHP

   0 1 2 3 4 5 6 7

   CREATE DATABASE learn_pg; \c learn_pg

Mini-goal: be able to connect, create db, run \? and feel comfortable in psql

Phase 1 – Core SQL & CRUD (2–4 weeks)

1. Data types you must know by heart (2026 reality)
   • SERIAL / BIGSERIAL / GENERATED ALWAYS AS IDENTITY
   • VARCHAR(n) vs TEXT
   • NUMERIC / DECIMAL vs DOUBLE PRECISION / REAL
   • TIMESTAMP vs TIMESTAMPTZ
   • JSON vs JSONB
   • UUID, ARRAY, RANGE types (brief intro)
2. CREATE / ALTER / DROP TABLE
   • Constraints: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT
   • Foreign keys + ON DELETE / ON UPDATE (CASCADE, RESTRICT, SET NULL)
3. INSERT / UPDATE / DELETE / TRUNCATE
   • Multi-row INSERT
   • RETURNING clause
   • UPDATE … FROM / DELETE … USING
4. Basic SELECT
   • WHERE, ORDER BY, LIMIT / OFFSET / FETCH
   • LIKE / ILIKE, ~ / ~* (regex)
   • IN, BETWEEN, IS NULL / IS NOT NULL
5. String functions
   • ||, CONCAT, SUBSTRING, LEFT/RIGHT, POSITION, TRIM, UPPER/LOWER
6. Date / time
   • CURRENT_DATE, CURRENT_TIMESTAMP, NOW()
   • EXTRACT, DATE_TRUNC, AGE, INTERVAL

Practice goal: be able to create a small schema (students, courses, enrollments) and write 20–30 CRUD queries without looking at notes.

Phase 2 – Joins & Set Operations (2–3 weeks)

1. All JOIN types
   • INNER JOIN
   • LEFT / RIGHT / FULL OUTER JOIN
   • CROSS JOIN (and when NOT to use it)
   • SELF JOIN
2. USING vs ON
3. JOIN + WHERE vs JOIN condition placement
4. Multiple joins (3–5 tables)
5. Anti-join patterns (LEFT JOIN … IS NULL vs NOT EXISTS)
6. Set operators
   • UNION / UNION ALL
   • INTERSECT
   • EXCEPT / MINUS

Practice goal: solve 30–50 join-heavy problems (pgexercises.com Joins section + LeetCode medium database problems)

Phase 3 – Grouping, Aggregation & Window Functions (3–5 weeks)

1. GROUP BY + aggregates
   • COUNT, SUM, AVG, MIN/MAX, STRING_AGG
   • GROUP BY multiple columns
2. HAVING vs WHERE
3. GROUP BY with JOIN (very common)
4. CASE expression (simple & searched)
   • CASE in SELECT, ORDER BY, UPDATE, WHERE
5. Window functions (this is where many people become strong)
   • ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
   • LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
   • Running totals: SUM() OVER (ORDER BY …)
   • Moving average: AVG() OVER (ORDER BY … ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

Practice goal: solve 40–60 problems involving GROUP BY + HAVING + window functions

Phase 4 – Subqueries, CTEs, EXISTS, LATERAL (2–4 weeks)

1. Scalar subquery
2. Correlated subquery
3. EXISTS / NOT EXISTS (anti-join)
4. IN / ANY / ALL with subqueries
5. Common Table Expressions (WITH clause)
   • Non-recursive CTE
   • Multiple CTEs
   • CTE in UPDATE / INSERT / DELETE
6. LATERAL subqueries / JOIN LATERAL
   • Very powerful for per-row computation

Practice goal: rewrite 20 JOIN queries using EXISTS / CTE / LATERAL and see which is more readable/fast

Phase 5 – Advanced & Production Topics (4–8 weeks)

1. Indexes
   • B-tree, Hash, GiST, GIN, BRIN
   • Partial indexes
   • Multi-column indexes
   • Index on expressions / functional indexes
   • Covering indexes
2. EXPLAIN / EXPLAIN ANALYZE
   • Reading plans, Seq Scan vs Index Scan vs Bitmap
   • Cost, rows removed by filter
3. Transactions & Concurrency
   • BEGIN / COMMIT / ROLLBACK
   • Isolation levels (Read Committed vs Repeatable Read vs Serializable)
   • Deadlocks, MVCC basics
4. Performance tuning patterns
   • Avoiding SELECT *
   • Limiting rows early
   • Using EXISTS instead of COUNT(*) > 0
   • Materialized views
5. JSON/JSONB mastery
   • ->, ->>, @>, <@, ?, ?&, ?|, #>, #>>
   • jsonb_path_query, jsonb_set, jsonb_insert
   • GIN index on JSONB
6. Full-text search (tsvector / tsquery)
7. Arrays & range types
8. Partitioning (declarative – very important 2026)
9. Triggers & functions (PL/pgSQL basics)

Practice goal: solve 30–50 advanced problems + optimize 5–10 slow queries + read 10–15 real EXPLAIN plans

Suggested Real-world Learning Path (3–6 months)

Month 1 → Phase 0 + Phase 1 (CRUD + basic SELECT) Month 2 → Phase 2 (Joins + set operations) Month 3 → Phase 3 (Grouping + window functions) Month 4 → Phase 4 (Subqueries, CTE, EXISTS, LATERAL) Month 5–6 → Phase 5 (Indexes, EXPLAIN, transactions, JSONB, production patterns)

Daily routine (ideal):

• 30–60 min theory reading / watching
• 60–120 min solving exercises
• 10–20 min reviewing mistakes + writing notes

Best exercise sources 2026:

• pgexercises.com (free, PostgreSQL specific)
• LeetCode Database (medium & hard)
• HackerRank SQL
• Mode Analytics SQL Tutorial
• StrataScratch
• w3resource PostgreSQL exercises

Want to start right now?

Tell me:

A) Give me 10 PostgreSQL quiz questions right now (mixed difficulty) B) Give me 5–7 practical exercises (with schema + tasks) C) Create a personalized 30-day practice plan based on what we covered so far D) Something else (you tell me)

Your turn — let’s turn knowledge into skill! 🚀