PostgreSQL Course
Learn PostgreSQL and SQL: data types, constraints, joins, aggregation, window functions, indexes with EXPLAIN, transactions and concurrency.
14 lessons in 2 categories
PostgreSQL Foundations
PostgreSQL and the Relational Model
What a relational database actually is, what PostgreSQL is specifically, the Spring Boot → Spring Data JPA → Hibernate → SQL → PostgreSQL stack, and how a call to this project's own real TopicRepository travels through it -- no SQL syntax yet, just the mental model. The 1st lesson in both the PostgreSQL course and its PostgreSQL Foundations category.
Connecting to PostgreSQL: psql and a Real Spring Boot DataSource
Run PostgreSQL locally with Docker, connect directly with psql, and see what this project's own Spring Boot DataSource configuration actually means at the PostgreSQL level. The 2nd lesson in the PostgreSQL Foundations category.
Databases, Schemas, Tables, and Basic SQL Syntax
The server → database → schema → table hierarchy, CREATE TABLE syntax read through this project's own real V1__init_schema.sql, column definitions/constraints, SQL comments, and a first distinction between DDL and DML. The 3rd lesson in the PostgreSQL Foundations category.
PostgreSQL Data Types
INTEGER/BIGINT/BIGSERIAL, VARCHAR/TEXT, BOOLEAN, and DATE/TIMESTAMP/TIMESTAMPTZ -- how each one maps to its corresponding Java field type (Long/String/boolean/LocalDateTime) in this project's own real Topic/TopicTranslation/Question entities. The 4th lesson in the PostgreSQL Foundations category.
Constraints and Keys
PRIMARY KEY, FOREIGN KEY and referential integrity, ON DELETE CASCADE vs. RESTRICT (with a real contrast from this project's own quiz_question_link table), composite UNIQUE, CHECK constraints, and how BIGSERIAL PRIMARY KEY connects to GenerationType.IDENTITY. The 5th lesson in the PostgreSQL Foundations category.
Inserting, Updating, and Deleting Data
INSERT/UPDATE/DELETE, with this project's own real INSERT ... SELECT and UPDATE ... WHERE migrations, RETURNING, ON CONFLICT (upsert), and how Flyway guarantees this project's migrations never need ON CONFLICT. The 6th lesson in the PostgreSQL Foundations category.
SELECT and Filtering
The basic shape of SELECT, naming columns explicitly instead of SELECT *, comparison/logical operators, LIKE pattern matching, IN/BETWEEN, and why NULL can only be filtered with IS NULL, not = -- on this project's own real topic/category table. The 7th lesson in the PostgreSQL Foundations category.
Sorting, Limiting, and Pagination
ORDER BY, LIMIT/OFFSET, and NULLS FIRST/LAST -- what Pageable/Sort/Page<T>, already covered in the Spring Data JPA course, actually compiles down to underneath, on this project's own real topic table. The 8th lesson in the PostgreSQL Foundations category.
JOINs
INNER/LEFT/RIGHT/FULL JOIN, with this project's own real topic→category→course chain and its real TopicRepository.findBySlugWithCategoryAndCourse JPQL join fetch -- plus a real LEFT JOIN example finding topics not yet published in English. The 9th lesson in the PostgreSQL Foundations category.
Aggregation and GROUP BY
COUNT/SUM/AVG/MIN/MAX, GROUP BY, and HAVING -- entirely new material with no equivalent in Spring Data JPA's Pageable/Sort/projection vocabulary -- with this project's own real "topics per category" LEFT JOIN + GROUP BY query. The 10th and FINAL lesson in the PostgreSQL Foundations category.
Advanced PostgreSQL
Subqueries, CTEs, and Window Functions
Scalar and correlated subqueries, chained WITH (CTEs), and window functions (ROW_NUMBER, RANK, PARTITION BY) -- computing across rows without collapsing them the way GROUP BY does, with this project's own real topic table. The 1st lesson in the Advanced PostgreSQL category.
PostgreSQL-Specific Data Types: UUID, JSON/JSONB, and Arrays
UUID (as an alternative to BIGSERIAL, with trade-offs), JSON vs. JSONB, querying JSONB with ->/->>/@>, and arrays (ANY/@>/unnest) -- honestly noting this project's real schema uses none of them, with illustrative examples grounded in this project's own domain. The 2nd lesson in the Advanced PostgreSQL category.
Indexes and Query Performance with EXPLAIN
EXPLAIN/EXPLAIN ANALYZE, Seq Scan vs. Index Scan, CREATE INDEX with this project's own real idx_topic_category B-tree index, partial/expression indexes, and revisiting OFFSET's cost with keyset pagination. The 3rd lesson in the Advanced PostgreSQL category.
Transactions and Concurrency in PostgreSQL
Real BEGIN/COMMIT/ROLLBACK in psql, MVCC explained mechanically (xmin/xmax), row-level locking with SELECT ... FOR UPDATE, and a deadlock produced and explained on purpose -- without repeating "Transaction Management"'s isolation coverage. The 4th and FINAL lesson in the Advanced PostgreSQL category -- completing 14/14 topics in the PostgreSQL course.