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
Outro
Thank you

Full Course

$
349
$399
USD, one-time fee
Really enjoying Aaron Francis' "Mastering Postgres". It's information-dense, very approachable, and well-produced.
Daniel Bachhuber
Daniel Bachhuber

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

Congratulations on completing the Postgres course! I hope you now feel confident to apply your knowledge and excel in your current role or explore new opportunities. Thank you for supporting our videos, and don't forget, I also offer courses on SQLite and recording screencasts. Feel free to reach out via LinkedIn, Twitter, or email to share your thoughts or ask questions—I look forward to seeing you in future courses!

Links

High Performance SQLite
Screencasting.com
AaronFrancis.com

Video Transcript

Y'all, you made it. That's it. You have mastered Postgres. You have made it to the end. I just want to say from the bottom of my heart, thank you. Thank you for taking the course. Thank you for sticking with me throughout the whole thing. I know that is a ton of material. I hope you feel empowered to run your company, to start a startup, to do your job, to get a better job, to get a job in the first place.

I hope this has given you some sense of empowerment. It is really, really wonderful for me to be able to do this for a living. I just want to say thank you, and congratulations! What a huge amount of video you just watched. I am terribly excited to clean off my desk, clean up my office, and not produce video for a little bit. We will continue to make videos, so if I've missed anything let me know, and we'll continue to update videos, especially as new Postgres versions come out. We'll try to keep this up to date for a very, very long time.

If you want to watch other videos, I've got a course on SQLite. I've got a course on how to create video. How to actually record screencasts, everything that I'm doing here, I've got a whole course on that. Regardless, I just want to say thank you so much. I mean, it is just I love that I get to do this for a living, so thank you for supporting our little company, my not so little family. I'm very grateful. Let me know what you think. I would love to hear from you. LinkedIn, Twitter, Bluesky, email, you can probably find me everywhere. I would love to hear what you have to say and what you think about this course, and hopefully, I will see you again soon.