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)
Next video (8)
Introduction
Introduction to the course

Full Course

$
349
$399
USD, one-time fee
The course broke down complex subjects into easy-to-understand videos that I've referred back to multiple times since completing it. It strengthened my knowledge by filling gaps and expanding into new areas I hadn't known about—absolutely worth doing!
Lawrence
Lawrence

The PostgreSQL Development Platform

Start your project with a Postgres database, Authentication, instant APIs, Edge Functions, Realtime subscriptions, Storage, and Vector embeddings.

Test Data

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

Download
or
Use on Supabase (coming soon)

Summary

In Mastering Postgres, you'll benefit from nearly 20 years of real-world database experience to help you build faster, more efficient applications and grow your career. The course covers everything from foundational concepts to advanced techniques, all with a focus on pure Postgres—supported by Xata, but vendor-neutral in content. By the end, you'll feel confident stepping up as the database expert on your team, and much of the material is available to explore for free.

Video Transcript

Hey. Welcome to Mastering Postgres. My name is Aaron Francis. I'm gonna be your teacher throughout this course.

I'm a database educator. Been working with databases for almost 20 years at this point, which makes me feel super old, but I love it so, so much. And the reason that I love it so much is no matter how fast your application is, it's going to be as slow as your slowest query.

We spend all this time arguing about tech stacks and frameworks and languages when there's this giant behemoth at the bottom that we kind of treat as like a black box. A lot of people don't want to go down to that level of the stack and there are just massive performance gains that we can find in the database. Learning the things that we're gonna learn in this course, yes, will help you make better, faster, more performant applications, which is awesome, but it's also gonna help you advance in your career.

After you finish this, there's a very high possibility that you become the database expert on your team, and that's very good for your career. We're gonna go through everything from the basics all the way up to very advanced stuff, but we're gonna walk through it one step at a time. And frankly, honestly, I think it's a lot of fun. Hopefully at the end of this, you feel extremely confident.

A lot of this course is free. It has been made freely available thanks to our generous sponsor, Supabase. Supabase is a Postgres platform for builders. If you want to take all of this knowledge, but not actually host your own database, I recommend Supabase. Go put it on Supabase.

Here's the promise that I will make to you. Everything that you're gonna learn here, everything that I'm going to teach you here is going to be honest to goodness, vanilla, raw Postgres. There are some things that Supabase makes easier, and I will absolutely mention them as we're learning. But I'm not here to teach you Supabase.

I am here to teach you Postgres.

I just happen to think Supabase is a good place to put your database, but I want it to be pure and honest Postgres. That's what we're gonna learn here. I'm very excited. I put a ton of work into this. I love teaching. Please reach out, let me know what you think, and I hope you enjoy this course.