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 JSON
Intro to JSON

Full Course

$
349
$399
USD, one-time fee
Like many, I only dealt with databases when necessary. This course changed that, making me feel confident as my team's 'Database Expert'! Aaron is an engaging and insightful educator, making this a 10 out of 10 course. I'll definitely buy more courses from him. Highly recommend—no regrets!
Kyle Bennett
Kyle Bennett

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

Learn when to use JSON in PostgreSQL and when it's better to create regular tables and columns instead. If you rarely query the data inside a JSON blob, storing it as is can be fine, but if you frequently access its contents, breaking it out into individual columns is more efficient. The video also covers how to manage and optimize JSON blobs with indexing and generated columns if your needs evolve over time.

Links

JSON functions documentation

Video Transcript

It's time to do some stuff with JSON. In fact, it's time to do a lot of stuff with JSON. PostgreSQL has a ton of JSON affordances and we're going to look at a lot of them. I'll leave links down in the notes because there are many dozens of functions and we're not going to look at them all.

Before we do any of that, I just want to do a quick refresh on when you might use JSON in a relational database and when you might not. There are some people that would tell you never, never, ever, ever use a JSON Blob. I don't buy that. I don't think that that has enough nuance as a piece of advice. Here are a couple of things that you can look for when you're deciding should I use JSON Blob, or should I create some sort of top level table schema? The first thing is, if you are infrequently querying into the blob of JSON, so this would be something like potentially logs or web hooks or request response logging, something like that. You're very rarely breaking that blob apart from SQL, and you're trying to get at it from the outside. I would say that's fine. Store it as a blob. That falls into the, maybe the more historical artifact, where you're keeping the JSON Blob around just to have it. That's great. I love it.

On the other hand, if you have a big old blob, and you're frequently querying into the blob, so you have a bunch of keys in there and your queries are oftentimes hitting those keys inside of the blob, that's a point, maybe in the other direction of, let's break that out into multiple columns. Another point in that direction would be, does the JSON have a rigid schema? Or rather, should the JSON have a rigid schema? Am I trying to enforce that a JSON Blob is a certain shape? If so, that might be a hint that you should use PostgreSQL for what PostgreSQL is used for, which is creating columns and tables and giving each column a data type and letting PostgreSQL do some enforcement of that, rather than trying to validate this amorphous blob of JSON. Those are some rules of thumb. I don't think anything is a hard and fast rule. I wouldn't go all the way to having a primary key next to a blob, and treating it like a Mongo database.

I also wouldn't go all the way to just ruling out, explicitly saying, you can't have any JSON in the database. I think both of those are a little bit extreme, and a little bit silly, in my opinion. I would consider those rules of thumb when deciding should we break it out or not. Now if you end up in a situation where you've got a JSON Blob, and then later on, you find yourself querying into that blob quite often, that's okay. That is totally okay. There are ways to bring some of those key values out to top level columns.

We'll look at that here in a little bit, using something like a generated column. There are also ways to index the entire blob and specific parts of a JSON Blob or document. You're not going to paint yourself into a corner, so don't fret, but maybe consider those guidelines, and then we can adapt as we go.