Mastering Postgres
Introduction
Introduction to the course
Overview of course structure
Postgres vs. everyone
The psql CLI
Data Types
Introduction to schema
Integers
Numeric
Floating point
Storing money
NaNs and infinity
Casting types
Characters types
Check constraints
Domain types
Charsets and collations
Binary data
UUIDs
Boolean
Enums
Timestamps
Timezones
Dates and times
Advanced Data Types
Intervals
Serial type
Sequences
Identity
Network and mac addresses
JSON
Arrays
Generated columns
Text search types
Bit string
Ranges
Composite types
Nulls
Unique constraints
Exclusion constraints
Foreign key constraints
Indexing
Introduction to indexes
Heaps and CTIDs
B-Tree overview
Primary keys vs. secondary indexes
Primary key types
Where to add indexes
Index selectivity
Composite indexes
Composite range
Combining multiple indexes
Covering indexes
Partial indexes
Index ordering
Ordering nulls in indexes
Advanced Indexing
Functional indexes
Duplicate indexes
Hash indexes
Naming indexes
Understanding Query Plans
Introduction to explain
Explain structure
Scan nodes
Costs and rows
Explain analyze
Generating Results
Introduction to queries
Cross joins
Inner joins
Outer joins
Subqueries
Lateral joins
ROWS FROM
Filling gaps in sequences
Subquery elimination
Combining queries
Set generating functions
Indexing joins
Advanced SQL
Introduction to advanced SQL
Grouping
Grouping sets, rollups, cubes
Window functions
CTEs
CTEs with window functions
Recursive CTE
Hierarchical recursive CTE
Handling nulls
Row value syntax
Views
Materialized views
Removing duplicate rows
Upsert
Returning keyword
COALESCE + generated column
Full Text Search
Introduction to full text search
Searching with LIKE
Vectors, queries, and ranks
Websearch
Ranking
Indexing full text search
Highlighting
Advanced JSON
Intro to JSON
JSON vs JSONB
Validating JSON
Creating JSON objects + arrays
JSON extraction
JSON containment
JSON existence
JSON recordset
Updating JSON
Indexing JSON parts
GIN index
Vectors (pgvector)
Intro to pgvector
Vector embedding columns
Find related articles
Upsert vector embedding
Semantic search
Other operators
Vector indexes
Outro
Thank you
Bonus interviews
Heroku's glory days & Postgres vs the world (with Craig Kerstiens)
Creating a Postgres platform with Monica & Tudor from Xata.io
Bootstrapping an email service provider (with Jesse Hanley)
Locked video

Please purchase the course to watch this video.

Video thumbnail
Advanced SQL
Introduction to advanced SQL

Full Course

$
349
$399
USD, one-time fee
Window function 😊. CTE with window function 😮. Hierarchical recursive CTE 🤯. Recommended all the way. Can’t wait to see the rest of the videos!
M Wildan Zulfikar
M Wildan Zulfikar

PostgreSQL database platform

Shorten dev cycles with branching and zero-downtime schema migrations.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Xata

Summary

We're going to dive into advanced SQL topics such as CTEs, recursive CTEs, and window functions, expanding on the foundational concepts of data types, indexes, and result sets. Consider how these techniques apply to your own work and projects, as doing so will help you retain and effectively use them. If you haven’t already, be sure to review the previous modules, as we're now focusing on applying theory to real-world SQL performance optimization.

Video Transcript

As we've progressed through the modules, we're getting more and more practical. We started out with kind of an intro and then we did some data types, and some indexes, and some reconstructing of result sets, some explains. All of that stuff is very good information to have. It all builds upon itself until we reach this point.

Now, we're out kind of in the real world, in the practical side of things, but it builds on all of that theoretical knowledge that we've gone through so far. If you haven't watched those modules, I would highly encourage you to do that now 'cause we're gonna talk about indexes and explains and joins, but we're not gonna go into super detail because we already did that.

In this one we're gonna look at some advanced SQL, some performance optimizations, that sort of thing. We'll look at CTEs, recursive CTEs, window functions, all kinds of super fun stuff. As you're watching this, I would encourage you to keep your problem set in mind. Keep your domain, your application, your business logic. Keep all of that in mind and see where these examples might apply to what you do day-to-day 'cause that's really gonna help solidify the knowledge. I will try to make the best examples that I can, but it's gonna make a lot more sense to you, or it's going to help you remember these things a lot better if you immediately take it and you apply it to your own area of expertise. Keep that in mind as you're watching these.

This one's gonna be super fun. So here we go.