About Lessons Blog

Home
Forum

Forum

Ask a question or start a discussion about any PostgreSQL lesson.

Primary keys and foreign keysLearn how a primary key identifies each row in a PostgreSQL table, how a foreign key references it, and how PostgreSQL enforces referential integrity between the referencing and referenced tables.One-to-many relationshipsLearn how a one-to-many relationship works in PostgreSQL, why the foreign key lives on the many side, and why one row can appear several times in the result of a join.Many-to-many relationships and junction tablesLearn how a junction table turns a many-to-many relationship into two one-to-many relationships in PostgreSQL, and why the junction table is also the right home for attributes of the relationship itself.Normalizing a databaseLearn how normalization gives every fact one clear place to live, how it prevents update, insertion and deletion anomalies, and why a repeated foreign key is not the same thing as duplicated data.When denormalization makes senseLearn when it is worth storing duplicated, historical or precomputed data in PostgreSQL, why a join is not a reason to denormalize, and the three questions to answer before you do.NOT NULL and CHECK constraintsLearn how NOT NULL and CHECK constraints let PostgreSQL enforce data rules within a row, why a CHECK constraint accepts NULL, and why the database is the right place for a rule application code can miss.Enforcing uniqueness with UNIQUELearn how a UNIQUE constraint works in PostgreSQL, how it covers several columns, how it treats NULL, and why it is the only reliable way to stop two concurrent requests inserting the same value.Foreign key actionsLearn what PostgreSQL does to referencing rows when a referenced row is deleted or updated, how NO ACTION, RESTRICT, CASCADE, SET NULL and SET DEFAULT differ, and how to choose between them.Understanding NULLLearn why NULL is neither zero nor an empty string, why comparing anything with NULL gives an unknown result rather than true or false, how three-valued logic changes what WHERE and NOT return, and how IS NULL and IS DISTINCT FROM test for missing values.Working with NULL in queriesLearn how NULL propagates through arithmetic, how COALESCE supplies a fallback and NULLIF avoids division by zero, why COUNT() and COUNT(column) disagree, why NOT IN breaks when its list contains NULL, and how NULL behaves in UNIQUE constraints and index ordering.Joining related tables with INNER JOINLearn how INNER JOIN combines rows from two related tables in PostgreSQL, how the join condition decides which rows match, and how table aliases keep join queries readable.Keeping unmatched rows with LEFT JOINLearn how a LEFT JOIN keeps every row from the left table in PostgreSQL, fills the missing columns with NULL, and how that makes it easy to find rows with no related row in another table.Filtering joined data: ON vs WHERELearn why a condition in the ON clause of a LEFT JOIN decides which rows count as matches, while the same condition in WHERE filters the joined result and removes the unmatched rows you meant to keep.Joining more than two tablesLearn how to chain several JOIN clauses in PostgreSQL so one query can return columns from four related tables, and why a join returns one row per matching combination rather than one row per record.RIGHT JOIN, FULL JOIN, and CROSS JOINLearn the remaining join types in PostgreSQL: RIGHT JOIN keeps every row from the right table, FULL JOIN keeps unmatched rows from both tables, and CROSS JOIN returns every combination of rows.How PostgreSQL executes joinsLearn the three algorithms PostgreSQL uses to execute a join, nested loop, hash join and merge join, how to see the chosen strategy in an execution plan, and why the join type you write is not the algorithm the planner picks.Summarizing rows with aggregate functionsLearn how COUNT, SUM, AVG, MIN, and MAX reduce many rows to a single summary value, how COUNT() differs from COUNT(column), why most aggregates skip NULL, and why an empty result gives 0 from COUNT but NULL from everything else.Grouping rows with GROUP BYLearn how GROUP BY divides rows so an aggregate produces one result per group, which columns a grouped query may select, how grouping by several columns works, and why grouping is not the same as sorting.Filtering groups with HAVINGLearn why WHERE cannot reference an aggregate, how HAVING filters groups after aggregation, how the two clauses work together in one query, and when a condition belongs in WHERE instead.Conditional aggregation with FILTER and CASELearn how the FILTER clause gives each aggregate its own subset of rows so one query can produce a whole dashboard, how the CASE form works and where COUNT with ELSE 0 goes wrong, and when to reach for WHERE instead.Writing queries with subqueriesLearn how a scalar subquery supplies one value to an outer query, how IN compares a value against a set of rows, how a subquery in FROM becomes a derived table you can join, and what makes a subquery correlated.Checking for related rows with EXISTS and NOT EXISTSLearn how EXISTS keeps an outer row when a matching inner row is found, how NOT EXISTS finds rows with no related record, why NOT IN behaves differently when NULL is present, and why correlation does not force a row-by-row execution plan.Writing readable queries with CTEsLearn how a WITH clause names an intermediate result so a larger statement reads as a sequence of steps, how to chain several CTEs, how a CTE compares with a subquery, and how a data-modifying CTE returns the rows it changed.Recursive CTEsLearn how WITH RECURSIVE lets a CTE refer to its own output, how PostgreSQL evaluates the non-recursive and recursive terms one iteration at a time, how to track depth while walking a hierarchy, and why every recursive query needs a path to termination.CTE materialization and query performanceLearn when PostgreSQL folds a CTE into the surrounding query and when it calculates the CTE separately, what MATERIALIZED and NOT MATERIALIZED change, and why the old advice that CTEs are always optimization barriers no longer describes current PostgreSQL.Understanding window functionsLearn how a window function calculates across a set of related rows without collapsing them the way GROUP BY does, and how OVER, PARTITION BY, and the window ORDER BY decide which rows each calculation sees.Ranking rows with ROW_NUMBER, RANK, and DENSE_RANKLearn how ROWNUMBER, RANK, and DENSERANK assign positions to rows, how they differ when values tie, how PARTITION BY restarts the numbering for each group, and how to filter on a ranking result.Running totals and window framesLearn how a window frame decides which rows inside a partition a calculation sees, how ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW produces a running total, and why the default frame changes what LASTVALUE returns.Comparing rows with LAG and LEADLearn how LAG and LEAD read a value from an earlier or later row in the same window, how the offset and default arguments work, and how PARTITION BY keeps previous and next inside one group.How PostgreSQL executes a SQL statementLearn how PostgreSQL executes a SQL statement through four stages: parsing, rewriting, planning and optimization, and execution.Understanding PostgreSQL execution plansLearn how to use PostgreSQL EXPLAIN to read execution plans and understand plan nodes, estimated costs, row estimates, and other planner information.Using EXPLAIN ANALYZE to measure actual executionLearn how to use PostgreSQL EXPLAIN ANALYZE to compare estimates with actual execution times, row counts, loops, buffers, and total runtime.Improving query performance with indexesLearn how PostgreSQL indexes improve selective queries by replacing sequential scans with index scans, and understand their storage and write costs.Multicolumn indexesLearn how multicolumn B-tree indexes work, why column order matters, and how to choose an index that supports your query patterns.Partial indexesLearn how partial indexes cover a selected subset of rows, when PostgreSQL can use them, and why they can reduce storage and maintenance work.Expression indexesLearn how expression indexes store the result of an expression such as lower(email), when PostgreSQL can use them, and what they cost to maintain.Paginating results with LIMIT and OFFSETLearn how to build offset-based pagination in PostgreSQL using ORDER BY, LIMIT, and OFFSET, and why a predictable sort order matters.Understanding the cost of large OFFSETsLearn why large PostgreSQL OFFSET values make pagination slower, how much extra work PostgreSQL performs, and why an index doesn’t eliminate that cost.Introducing keyset paginationLearn how PostgreSQL keyset pagination uses the last row as a cursor to fetch the next page efficiently and avoid the growing cost of large OFFSETs.Keyset pagination with multiple sort columnsLearn how to use multiple sort columns for PostgreSQL keyset pagination with a unique tie-breaker and row-value cursor comparisons.Navigating forward and backward with keyset paginationLearn how PostgreSQL keyset pagination moves forward and backward using the last or first row as the cursor boundary and adjusting the sort direction.Choosing between offset and keyset paginationCompare offset and keyset pagination in PostgreSQL and learn when to use each for numbered pages, direct jumps, large result sets, and sequential navigation.Why transactions existLearn why PostgreSQL transactions are needed when multiple SQL statements must succeed or fail together as a single unit of work.Using BEGIN, COMMIT, and ROLLBACKLearn how implicit and explicit PostgreSQL transactions work and how to use BEGIN, COMMIT, and ROLLBACK to save or discard changes.Understanding MVCCLearn how PostgreSQL’s MVCC system creates row versions, uses snapshots to determine visibility, and lets readers access committed data without blocking writers.Understanding READ COMMITTEDLearn how PostgreSQL’s default READ COMMITTED isolation level uses snapshots and why two SELECT statements in one transaction can see different data.Preventing race conditionsLearn two ways to prevent PostgreSQL race conditions: lock rows with SELECT FOR UPDATE or move the condition into an atomic UPDATE.Understanding REPEATABLE READ and SERIALIZABLECompare PostgreSQL READ COMMITTED, REPEATABLE READ, and SERIALIZABLE, including transaction snapshots, serialization failures, and application retries.Understanding deadlocksLearn how PostgreSQL deadlocks form, how the database resolves them, why applications may need retries, and how consistent lock ordering reduces risk.Using savepointsLearn how PostgreSQL savepoints let you roll back part of a transaction, preserve earlier work, recover from errors, and continue toward COMMIT.