[00:01] Postgres is such an amazing database as we explore the support for working with vectors using the PG vector extension. This allows us to implement vector similarity search inside of Postgres, which opens up capabilities like
[00:16] recommendations, similarity search or vector search, and even retrieval augmented generation. To be able to do this within Postgres, we need to install an extension called PG vector, and it does just about what you might expect.
[00:29] It gives you the ability to work with a new vector data type, which is a high-dimensional vector, and this allows you to store, index, and query vector data from your Postgres database. So, you don't need a specialized database to
[00:41] do this. You can do it natively within Postgres. Now, there's a PG vector.net organization, and it implements the capabilities for working with vectors and Postgres from C# and .NET. The baseline implementation uses Npgsql,
[00:57] which is the native database client for working with Postgres, but it also has implementations for Dapper, which is what I will show you in this video, and it also works with EF Core, and it also works in F#. Now, fundamentally, it all
[01:11] revolves on this new data type called a vector, and it represents your high-dimensional array. Now, I want to show you a practical example of using we're going to need something to
[01:24] generate the actual embeddings. And for this, I will be using the Qwen 3 embedding model, and I will run it using Ollama. Now, there are a couple of explore, and they come with different parameter sizes, but also differ in
[01:37] their output vector dimensions. For this demo, I'll be using the Qwen 3 embedding .6B model, which outputs embeddings that have 1,024 dimensions. So, let's see how we can connect all of this together from our .NET application. So, I'll be using
[01:52] Aspire to orchestrate my application, and I want to run a Postgres container, and an instance of Ollama using Aspire. So, let's install some NuGet packages that we're going to need. I'll look for Aspire Postgres, and I want to install
[02:06] the Aspire Hosting PostgreSQL NuGet package. And likewise, let's also look for Aspire Ollama, and the library I want to install is the Community Toolkit Aspire Hosting Ollama package. So, let's go ahead and also add that. I will close
[02:21] this down, and now let's add these two as resources in app host. So, first I'll create an Ollama resource, and I'll say builder and Ollama, let's call it Ollama
[02:33] as well. Let's define our resource lifetime, and I want to use persistent, just so that we don't have to regenerate our embedding model every time we restart our Aspire app host. Let's also add a data volume, and if you've got a
[02:46] good GPU, I also recommend using with GPU support. Then we're going to create our embedding model, and we can do that by saying Ollama add model, and we have to pass in the name of our model, which is going to be quantfreeembedding.6b.
[03:01] So, now I can add this as a resource in my API project, and I'll say with and let's also wait for it to be available. Then for my database instance, I can say builder add Postgres, let's give it the name of
[03:15] Postgres. I'll expose a port where I can connect to this locally on port 6432. I also want to run this with a consistent lifetime, just to make the performance
[03:27] faster when we restart this. I'll give it a data volume, and then it's important to talk about which image we want to use to run this. So, Postgres by default doesn't come with pgvector installed. We would have to install this
[03:40] extension ourselves, or we can use the existing pgvector image, which is available as pgvector/pgvector. And we also need to to that, let's say I want to run this as Postgres 17. So, this is now going to
[03:54] run a Postgres instance that comes with PG Vector pre-installed. And let's also add our database, and I will call it articles. So, we'll get back our database instance, and I can finally say with reference, pass in our database,
[04:09] and let's also wait for it to be available before our API project starts. So, that's wiring up all the dependencies. Now, we have to connect to these from our client application. This is going to be my PG Vector articles
[04:21] API. So, let's also install some NuGet packages here. I'll look for Aspire.Npgsql as this is going to make it super easy to connect to our Postgres data source. Then, let's look for Aspire.Ollama, and
[04:35] here we want to install CommunityToolkit.Aspire.OllamaSharp. Let's go ahead and add that. And lastly, let's look for PG Vector, and you've got a couple of options here. EF Core is probably the most popular one, but I
[04:48] want to use PG Vector.Dapper. So, let's go ahead and install that one. So, now I can close this down, and then in my service registrations, I can say builder.AddOllamaApiClient. I need to provide my connection string
[05:03] OllamaQuantryEmbedding. I'll show you this when we also start this up, and you'll be able to see it in the Aspire dashboard. Now, I also want to register an IEmbeddingGenerator from Microsoft.Extensions.AI. This just makes
[05:17] embeddings, and this is what we're going to store inside of our Postgres builder.AddNpgsqlDataSource. I need to pass in my connection string name, which is going to be articles, and we also want to additionally
[05:35] that we can pass in to configure data source builder. And here, what we want to do is tell it to call the UseVector method, as this is important to enable type when writing to Postgres.
[05:49] Additionally, to make this work with Dapper, you have to call SQL mapper at type handler and add a new instance of the vector type handler. So, now let's talk about what we're actually going to build here. I have this sitemap URLs
[06:03] document that contains the links to about 180-ish articles on my website and we're going to use the blog service to fetch the title and the content for each article, extract the important bits, and then we're going to generate the
[06:18] embeddings for these using Ollama and our embedding model. We're going to store this inside of Postgres using PG vector, and then we're going to implement support for semantic search using PG vector. So, let's see what we
[06:30] have to do to make this work. I'm going to divide this into three steps. Step number one is going to be initializing our database schema. Step number two is going to be generating the vector embeddings for all of the articles and
[06:43] step number three is going to be performing a similarity search using cosine distance. There are other supported search operations within PG vector and you're welcome to explore the documentation if you want to learn more
[06:57] about all of the available options, but for our demo, let's focus on implementing these three steps. Now, out of all the three, the first step is just going to drop in the implementation and let's comment on what's actually
[07:10] going on here. So, we're using our data source, which we can now inject database injection because we added it as a service in this call here and we'll use it to open up a database connection. Then, we first have to make sure to
[07:23] enable the PG vector extension if it doesn't already exist. So, this is this completes, we're going to tell our connection to reload the type cache and this is important to be able to work with the new vector data type. And
[07:37] finally, we're going to create our database table and this part here is the most important one where we are creating an embedding column and the data type is a vector. This is available through the PG vector extension and we can specify
[07:51] the number of dimensions for this vector and this should exactly match the number of dimensions that's outputted by our embedding model. And in this case that is exactly 1,024. So after this completes, we'll have our database table
[08:04] where we'll be able to store our vector embeddings and then the next part is for our search queries. We're going to define an index using HNSW which is short for hierarchical navigable small world. Now this is just a way for us to
[08:18] neighbor search which is an efficient way to calculate the distance between two vectors and that is basically how vector search works. We've got our embedding vector stored in the database. We calculate the vector for our query
[08:33] the two. The closer they are together makes the distance between them smaller which means they are more similar. And we can use this to extract a bunch of conclusions from this. From performing recommendations to doing semantic search
[08:46] which is what we're going to implement here. So let's move on to our next step where we want to generate the embeddings for all of the articles inside of our sitemap text file. So what we're going to do is first inject a couple of
[09:00] dependencies. We need our blog service. We need an I embedding generator from Microsoft extensions AI. We need our data source to be able to talk with our database. And then we're going to just read all the lines from this file and
[09:12] iterate through the URLs in there one by one. We'll use the blog service to extract the title and the content of each article and then here's the interesting part. So we're going to use the embedding generator to generate the
[09:24] embeddings for the contents of this article. This produces an embedding that this is what we want to store inside of Postgres. So we'll say insert into articles. We want to store the URL, the
[09:37] this is just another parameter that we can pass in using Dapper. If this were EF Core, we would just set this property and the PG vector libraries take care of mapping this to a vector data type in Postgres. So, then we're going to pass
[09:51] in our parameters. And for the embedding, we just create a new instance of a vector, which automatically accepts an array of floating-point numbers. And this is what an embedding is. So, we can say embedding, this is the result of our
[10:03] call to the embedding generator, access the vector, and then call to array to this in the database, and then we'll remaining articles. And then finally, how do we implement a similarity search
[10:16] using PG vector? Well, let's code this one from scratch. So, it's going to be a get endpoint. I'll say map get. Let's say the route is search. And then, what about the parameters and dependencies? So, we're going to have a query as a
[10:30] IEmbeddingGenerator that takes in a string and returns an embedding containing the floating-point numbers. I will call this the embedding generator. Then we need our Npgsql data source. And
[10:44] lastly, we're going to have an integer representing how many results we're going to return from our endpoint. And then inside of the endpoint, we go through a similar process. We use our embedding generator to create a search
[10:56] embedding from our search query parameter. Then we're going to open up a database connection using our data source. We have to make sure to reload the type cache. And then, let's create an embedding vector by saying new
[11:09] vector, and I'll use my search embedding to populate this value. And this is going to be our query parameter for performing a similarity search. I'll drop in a type here to represent my search result. And this is what we're
[11:22] going to return from our database. So, I can say connection query async, and we want to return the search results. Of course, I'll await this call. And then we have to write our SQL query. So, this is going to be a simple select. That's
[11:35] how you do vector search in SQL. And we want to select the title, the URL of the article, and the embedding. Now, how do we actually perform a vector search? Well, there's this new operator that calculates the distance between your two
[11:49] vectors, and we want to compare this to our embedding parameter, and I want to return this as a distance between the embedding stored in the database and the parameter values that we are passing in through our query. We want to select
[12:02] this from the articles table, and we want to order this by the vector embedding itself. So, we're going to just repeat the distance operator, and we want to make sure that we limit the number of results. So, we're going to
[12:14] pass in our two parameters. We'll pass in the embedding, and Dapr will know how to map a vector data type into a vector within our database, and we have PG Vector to perform the distance calculation between these two vectors.
[12:26] So, then we can return our result object. I'll say return results.Okay, and let's return the original query as well as the results of the search. So, we wrote a lot of code, but let's actually see how all of this works. In
[12:40] the Aspire dashboard, you can see our resources starting up. So, we have our Ollama instance, and it's downloading our embedding model, and then it will populate the respective connection string. We've also got our Postgres
[12:52] database with PG Vector installed. It's also up and running. And then we've got our two connection strings to our database and also to our embedding model. So, now I can jump into something like Postman and send the first request
[13:05] to initialize our database. And we'll hit the breakpoint inside of our minimal API endpoint, where we're going to open up a database connection, enable the PG Vector extension, create our table that contains our vector embeddings if it
[13:19] doesn't exist, and also the index for performing our search. So, let's hit continue here. We'll get a result back in Postman. And if we take a look at our database, we can see the articles table has been created, contains our embedding
[13:33] column with the vector data type. There's also the articles embedding index, which is going to be useful for our search. Now, let's walk through the next step, which is generating the vector embeddings. We're going to hit
[13:46] our second breakpoint, and here is where the interesting part starts. We'll iterate through our articles, fetch the contents of each articles, and then generate the embeddings using our embedding model. And this gives us a
[13:59] vector array that looks something like this. It's just a bunch of numbers encoding the meaning between the content that you are embedding. So, we're going to store this inside of Postgres, and I'll hit continue here. Now, this may
[14:11] take a couple of minutes as we have to go through a lot of articles, and this is typically a process that you'll probably run behind the scenes and not in a typical API endpoint as this is populating some static data inside of
[14:23] our database, but if I refresh my database table, you can see we've now got some articles here along with their vector embeddings. The embedding column for each articles contains 1,024 floating-point numbers encoding the
[14:36] semantic meaning of the contents of this article. This is going to take about a minute or two to complete, and you can see I've got a distributed trace here that is growing in the number of spans inside, but if we take a look, you can
[14:48] see that that's just a series of requests to Ollama, and then an insert into the database. The Ollama request is what's actually performing the embeddings. It's calling our Ollama API and the embed endpoint, and then it's
[15:01] inserting the results of this operation into our articles table, which is where we store the vector embeddings. So, now that this has completed, we can proceed to perform our similarity search, and this is a very powerful feature that you
[15:14] can add to your applications. So, I can send a query saying I want to learn about a pattern to to distributed messaging reliability, and this is a completely valid query when performing vector search. So, if we send this,
[15:27] we'll hit our breakpoint in the endpoint and generate the embeddings using the embeddings in our database. It's important that these models are either identical or at least compatible when it comes to performing similarity search.
[15:42] So, now we're going to send this vector inside of our select query, and we'll get back a set of results that are the closest to our query vector. So, if we that we get the article, but also the distance between our input query and the
[15:56] data stored in our database. If I press continue, we'll get this as a JSON response in Postman, and you can see that the results contain an article about the Outbox pattern, the Item Potent Consumer pattern, the Saga
[16:09] in a distributed system. So, these are all similarity matches to our input query. If I update this to say I want to learn about CQRS, and I send this query, you can see first of all that we get the result relatively fast in about 100
[16:24] milliseconds, and the results inside are all articles about CQRS. And lastly, let's say I want to learn about software architecture, and if we send this, we're going to get the top five results, with the first one being the article about
[16:37] the Missing Chapter of Clean Architecture, How to Enforce Software Architecture with Architecture Tests, and a couple of other matches. If we explore the distributed trace for our vector search, you can see the first
[16:49] span being the embed request for our query vector, then we're opening up the connection to our database, and the vector search itself is very efficient, completes quickly, and gives us back the desired results. If you want to grab the
[17:02] it completely for free from the pinned comment right below. And if you want to see why Postgres is awesome and how you can also use it as a cache, for example, instead of Redis, then go ahead and take a look at this video next. If you
[17:16] enjoyed this video, consider gently tapping the like button to let me know. Thanks a lot for watching and until next time stay awesome.