About Lessons Blog

Lessons

Every lesson runs against a real PostgreSQL database in your browser.

Get notified when I publish new lessons

First name

Email

Database modelling

1.Primary keys and foreign keys
2.One-to-many relationships
3.Many-to-many relationships and junction tables
4.Normalizing a database
5.When denormalization makes sense

Constraints

1.NOT NULL and CHECK constraints
2.Enforcing uniqueness with UNIQUE
3.Foreign key actions

NULL values

1.Understanding NULL
2.Working with NULL in queries

Joins

1.Joining related tables with INNER JOIN
2.Keeping unmatched rows with LEFT JOIN
3.Filtering joined data: ON vs WHERE
4.Joining more than two tables
5.RIGHT JOIN, FULL JOIN, and CROSS JOIN
6.How PostgreSQL executes joins

Aggregation

1.Summarizing rows with aggregate functions
2.Grouping rows with GROUP BY
3.Filtering groups with HAVING
4.Conditional aggregation with FILTER and CASE

Subqueries

1.Writing queries with subqueries
2.Checking for related rows with EXISTS and NOT EXISTS

Common Table Expressions (CTEs)

1.Writing readable queries with CTEs
2.Recursive CTEs
3.CTE materialization and query performance

Window functions

1.Understanding window functions
2.Ranking rows with ROW_NUMBER, RANK, and DENSE_RANK
3.Running totals and window frames
4.Comparing rows with LAG and LEAD

Indexes

1.How PostgreSQL executes a SQL statement
2.Understanding PostgreSQL execution plans
3.Using EXPLAIN ANALYZE to measure actual execution
4.Improving query performance with indexes
5.Multicolumn indexes
6.Partial indexes
7.Expression indexes

Pagination

1.Paginating results with LIMIT and OFFSET
2.Understanding the cost of large OFFSETs
3.Introducing keyset pagination
4.Keyset pagination with multiple sort columns
5.Navigating forward and backward with keyset pagination
6.Choosing between offset and keyset pagination

Transactions and Concurrency

1.Why transactions exist
2.Using BEGIN, COMMIT, and ROLLBACK
3.Understanding MVCC
4.Understanding READ COMMITTED
5.Preventing race conditions
6.Understanding REPEATABLE READ and SERIALIZABLE
7.Understanding deadlocks
8.Using savepoints