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
Vectors (pgvector)
Vector indexes

Full Course

$
349
$399
USD, one-time fee
Mastering Postgres is single-handedly the most thorough and informative database course I’ve seen. I’ve gone from someone who barely knew the basics of Postgres and how it works to being comfortable with the advanced topics of Postgres.
Ryan Hendrickson
Ryan Hendrickson

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 how to index vectors for better performance by choosing between IVF Flat and HNSW, each offering different trade-offs. IVF Flat is a good general-purpose index, while HNSW delivers higher performance and accuracy, though it is more resource-intensive. It's crucial to understand that indexing can lead to approximate nearest neighbor searches, and by adjusting parameters, you can balance the trade-off between performance and accuracy.

Video Transcript

When it comes time to index these vectors for better performance, you have two options. You have HNSW and IVF flat, and they're slightly different, and we'll talk about that in a second. Indexing vectors breaks one of the cardinal rules of indexing, which is the index should not change the results of the query. That is not true when it comes to vectors. When you are running a query against a vector, embedding without an index, it's performing an exact nearest neighbor search. When you're running that query with an index, it's performing an approximate nearest neighbor search, and you have control, you can tune some of the parameters to control that trade off.

It's not necessarily a bad thing, it is just a thing you need to know. When you add an index, it might come back with a different set of results, which would be very surprising to me if I did not know that, because indexing shouldn't change the results. In this case, it can. That's a trade off that is mostly, we're mostly willing to make for speed.

Let's take a look at these two types of indexes. The first one we're going to look at is probably the best general purpose. It does have a few drawbacks, but we're going to do create index on articles. We'll create index, let's say articles, embedding IVF flat, inverted file flat, which doesn't make a lot of sense to me, but that is the name of it. We're going to say using IVF flat, and then we'll say embedding. Then you can pass a flag here. You can also say with lists equal something. There are a few things. There are a few options that you have here. The first is lists, and this is, it breaks it apart into a certain number of lists, and there's a formula to calculate the general, I guess, best practice for lists. That's up to a million rows. It's the rows divided by a thousand, and then after a million rows, it's just the square root of the rows. That can kind of help you with the lists. Now when you create an IVF flat index, it's best if there's already data in the table. That's a little bit of a drawback.

Then what we're going to do now is we're going to pass through the operator that we intend to use with this index. Right here after the column name, we're going to put vector_l2_ops, and that says we plan to use the l2 operator here. Of course, you could do IP for inner product, you could do co-sign for co-sign, and this is going to tell Postgres or pgvector rather. This is the operator that we're going to use. Please use this index when we use this operator.

The other type of index, the other type of index is called the HNSW. We'll just add HNSW as the end of that, and we'll call this HNSW, and that's what creating an HNSW index would look like. You also have the same option here. You can change it to l2 or inner product, whatever. HNSW is a little bit of a higher quality index you can get some better performance and better accuracy, better recall with an HNSW, but it is a little bit more expensive to maintain. It requires more resources, both in memory and compute. A good general purpose is the IVF flat, but if you're willing to do it, the HNSW can be a little bit higher performance. There are lots of parameters that you can tune on these things, on these indexes, resource allocation recall and accuracy, performance, all of that kind of stuff. Lots of parameters both at the index build time and at the query time.

Now, this is all still very, very much in flux. PG Vector is not even at a 1.0 release at the time of recording. It's at a 0.8, I think. Everybody's using it and it's not even 1.0. It's all changing very, very quickly. As much as I would love to tell you this is exactly what you should do in this case, that's kind of hard. That's kind of hard for me to tell you exactly what you should do here. I'm hoping, hoping that I've given you some right down the middle use cases, some generalized principles, but because of how fast, not only this vector embedding, but also PG Vector itself because of how fast all of that is changing, I'm loathed to give you very strict rules here.

Hopefully I've given you some general principles here. IVF Flat is a great general use case index if you are willing to commit the resources. HNSW can be higher performance and better accuracy at the cost of being a little bit more expensive. If you're just going right down the middle, hopefully now you're able to go out and build something today.