Semantic Kernel vector store tutorial: pgvector setup

This tutorial walks through a complete semantic kernel postgres pgvector setup tutorial from a blank PostgreSQL instance to a working vector search pipeline. You will provision the database, enable the extension, define a collection schema, ingest embeddings, and run similarity queries — all with code you can copy into a real project.

Prerequisites

Before starting, ensure you have:

• PostgreSQL 15+ with the pgvector extension installed (version 0.5.0 or later)
• .NET 8 SDK or later
• An OpenAI-compatible embeddings endpoint — you need an API key and a model name (for example, text-embedding-3-small at 1536 dimensions)
• NuGet packages: Microsoft.SemanticKernel, Microsoft.SemanticKernel.Connectors.PgVector, Microsoft.Extensions.VectorData, Npgsql

If you are running PostgreSQL locally via Docker, this command starts a ready-to-use instance:

docker run -d \
  --name pgvector \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=skvectors \
  -p 5432:5432 \
  pgvector/pgvector:pg16

Wait for the container to report “database system is ready to accept connections” before proceeding.

Enable the pgvector extension

Connect to the database and create the extension. You only need to do this once per database.

\c skvectors
CREATE EXTENSION IF NOT EXISTS vector;

Verify the extension is active:

SELECT * FROM pg_extension WHERE extname = 'vector';

Expected output:

  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 16384 | vector  |       10 |         2200 | f              | 0.5.1      |           | 
(1 row)

Create the .NET project

Initialize a console application and add the required packages:

dotnet new console -n SkPgVectorDemo
cd SkPgVectorDemo
dotnet add package Microsoft.SemanticKernel
dotnet add package Microsoft.SemanticKernel.Connectors.PgVector
dotnet add package Microsoft.Extensions.VectorData
dotnet add package Npgsql

Configure the vector store

Open Program.cs and replace the template with the following. This sets up the kernel, registers the pgvector connector, and defines a record type that maps to a PostgreSQL table.

using Microsoft.Extensions.VectorData;
using Microsoft.SemanticKernel;
using Microsoft.SemanticKernel.Connectors.PgVector;
using Microsoft.SemanticKernel.Embeddings;
using Npgsql;

// ---- Configuration ---------------------------------------------------------
const string ConnectionString = "Host=localhost;Port=5432;Database=skvectors;Username=postgres;Password=postgres";
const string CollectionName = "documents";
const string EmbeddingModel = "text-embedding-3-small"; // 1536 dimensions
const string ApiKey = Environment.GetEnvironmentVariable("OPENAI_API_KEY") ?? "your-api-key-here";
const string Endpoint = "https://api.openai.com/v1"; // or your OpenRouter-compatible gateway

// ---- Record definition -----------------------------------------------------
// Each property maps to a column. VectorStoreRecordVectorPropertyAttribute
// tells the connector which column holds the embedding and its dimensions.
public sealed class DocumentRecord
{
    [VectorStoreRecordKey]
    public string Id { get; set; } = Guid.NewGuid().ToString();

    [VectorStoreRecordData(IsFilterable = true)]
    public string Title { get; set; } = string.Empty;

    [VectorStoreRecordData(IsFilterable = true)]
    public string Category { get; set; } = string.Empty;

    [VectorStoreRecordData]
    public string Content { get; set; } = string.Empty;

    [VectorStoreRecordVector(1536, DistanceFunction = DistanceFunction.CosineSimilarity, IndexKind = IndexKind.Hnsw)]
    public ReadOnlyMemory<float> Embedding { get; set; }
}

// ---- Main ------------------------------------------------------------------
var builder = Kernel.CreateBuilder();

// Register an embedding generation service. Replace with your provider if not using OpenAI.
builder.Services.AddOpenAITextEmbeddingGeneration(
    modelId: EmbeddingModel,
    apiKey: ApiKey,
    httpClient: new HttpClient { BaseAddress = new Uri(Endpoint) }
);

var kernel = builder.Build();

// Get the vector store from the pgvector connector
var vectorStore = new PgVectorStore(ConnectionString);

// Get or create the collection. This creates the table if it doesn't exist.
var collection = vectorStore.GetCollection<string, DocumentRecord>(CollectionName);
await collection.CreateCollectionIfNotExistsAsync();

Console.WriteLine($"Collection '{CollectionName}' ready.");

// Ingest sample data
await IngestSampleDataAsync(kernel, collection);

// Run a similarity search
await SearchAsync(kernel, collection, "How do I optimize PostgreSQL indexes?");

static async Task IngestSampleDataAsync(Kernel kernel, IVectorStoreRecordCollection<string, DocumentRecord> collection)
{
    var embeddingGenerator = kernel.GetRequiredService<ITextEmbeddingGenerationService>();

    var documents = new[]
    {
        new DocumentRecord
        {
            Title = "PostgreSQL Index Types",
            Category = "database",
            Content = "PostgreSQL supports B-tree, Hash, GiST, SP-GiST, GIN, and BRIN indexes. B-tree is the default and works well for equality and range queries. GIN indexes are ideal for full-text search and jsonb containment. BRIN indexes suit very large tables with natural correlation."
        },
        new DocumentRecord
        {
            Title = "HNSW Index in pgvector",
            Category = "vector-search",
            Content = "The HNSW (Hierarchical Navigable Small World) index in pgvector provides approximate nearest neighbor search with high recall. Create it with CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops). Tune m and ef_construction for build speed vs recall trade-offs."
        },
        new DocumentRecord
        {
            Title = "Connection Pooling with Npgsql",
            Category = "dotnet",
            Content = "Npgsql enables connection pooling by default. Configure pool size with 'Pooling=true;Minimum Pool Size=2;Maximum Pool Size=100' in the connection string. Avoid opening/closing connections in tight loops; reuse a single NpgsqlDataSource."
        }
    };

    foreach (var doc in documents)
    {
        // Generate embedding for the content
        var embedding = await embeddingGenerator.GenerateEmbeddingAsync(doc.Content);
        doc.Embedding = embedding;

        // Upsert the record
        await collection.UpsertAsync(doc);
        Console.WriteLine($"Upserted: {doc.Title} (id={doc.Id})");
    }
}

static async Task SearchAsync(Kernel kernel, IVectorStoreRecordCollection<string, DocumentRecord> collection, string query)
{
    var embeddingGenerator = kernel.GetRequiredService<ITextEmbeddingGenerationService>();
    var queryEmbedding = await embeddingGenerator.GenerateEmbeddingAsync(query);

    // Vector search with a filter on category
    var results = collection.SearchAsync(
        queryEmbedding,
        top: 3,
        new VectorSearchOptions<DocumentRecord>
        {
            Filter = r => r.Category == "vector-search" || r.Category == "database"
        }
    );

    Console.WriteLine($"\nSearch query: \"{query}\"");
    Console.WriteLine(new string('-', 60));

    int rank = 1;
    await foreach (var result in results)
    {
        var record = result.Record;
        Console.WriteLine($"#{rank++}  Score: {result.Score:F4}  Category: {record.Category}");
        Console.WriteLine($"     Title: {record.Title}");
        Console.WriteLine($"     Content: {record.Content[..Math.Min(120, record.Content.Length)]}...");
        Console.WriteLine();
    }
}

Run the application

Set your API key and execute:

export OPENAI_API_KEY=sk-...   # or set via your shell
dotnet run

Expected output (scores will vary slightly):

Collection 'documents' ready.
Upserted: PostgreSQL Index Types (id=...)
Upserted: HNSW Index in pgvector (id=...)
Upserted: Connection Pooling with Npgsql (id=...)

Search query: "How do I optimize PostgreSQL indexes?"
------------------------------------------------------------
#1  Score: 0.8421  Category: database
     Title: PostgreSQL Index Types
     Content: PostgreSQL supports B-tree, Hash, GiST, SP-GiST, GIN, and BRIN indexes. B-tree is the default and works well for equality and range queries. GIN indexes are ideal for full-text search and jsonb containment. BRIN indexes suit very large tables with natural correlation...

#2  Score: 0.7913  Category: vector-search
     Title: HNSW Index in pgvector
     Content: The HNSW (Hierarchical Navigable Small World) index in pgvector provides approximate nearest neighbor search with high recall. Create it with CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops). Tune m and ef_construction for build speed vs recall trade-offs...

#3  Score: 0.6124  Category: dotnet
     Title: Connection Pooling with Npgsql
     Content: Npgsql enables connection pooling by default. Configure pool size with 'Pooling=true;Minimum Pool Size=2;Maximum Pool Size=100' in the connection string. Avoid opening/closing connections in tight loops; reuse a single NpgsqlDataSource...

The filter restricted results to database and vector-search categories, so the connection pooling document ranks lower despite mentioning indexes.

Inspect the created schema

Connect to PostgreSQL and examine what the connector built:

\d documents

Output:

                           Table "public.documents"
   Column   |          Type          | Collation | Nullable | Default 
------------+------------------------+-----------+----------+---------
 id         | character varying(256) |           | not null |
 title      | text                   |           |          |
 category   | text                   |           |          |
 content    | text                   |           |          |
 embedding  | vector(1536)           |           |          |
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)
    "documents_embedding_idx" hnsw (embedding vector_cosine_ops)

The connector created:

• A primary key on id
• An HNSW index on the embedding column using cosine distance (vector_cosine_ops)
• Columns matching each [VectorStoreRecordData] and [VectorStoreRecordVector] property

Tune the HNSW index for production

The default HNSW parameters (m=16, ef_construction=64) work for small datasets. For larger workloads, recreate the index with tuned values:

DROP INDEX IF EXISTS documents_embedding_idx;

CREATE INDEX documents_embedding_idx ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 32, ef_construction = 128);

Higher m increases graph connectivity (better recall, larger index). Higher ef_construction improves build-time recall at the cost of longer index creation. For a 1M-row table, expect index build times of several minutes with these settings.

At query time, control the search width with SET hnsw.ef_search = 100; before running similarity queries. Higher values improve recall; lower values reduce latency.

Use a custom distance function

The example uses DistanceFunction.CosineSimilarity. pgvector also supports vector_l2_ops (Euclidean) and vector_ip_ops (inner product). Change the attribute on the Embedding property:

[VectorStoreRecordVector(1536, DistanceFunction = DistanceFunction.EuclideanDistance, IndexKind = IndexKind.Hnsw)]
public ReadOnlyMemory<float> Embedding { get; set; }

Then drop and recreate the index with the matching operator class:

CREATE INDEX documents_embedding_idx ON documents
USING hnsw (embedding vector_l2_ops)
WITH (m = 32, ef_construction = 128);

Inner product (vector_ip_ops) is useful when embeddings are normalized and you want maximum throughput — it avoids the square root in cosine distance.

Batch ingestion for larger datasets

The sample upserts one record at a time. For production volumes, batch the upserts:

static async Task BatchIngestAsync(
    Kernel kernel,
    IVectorStoreRecordCollection<string, DocumentRecord> collection,
    IEnumerable<DocumentRecord> documents,
    int batchSize = 100)
{
    var embeddingGenerator = kernel.GetRequiredService<ITextEmbeddingGenerationService>();
    var batch = new List<DocumentRecord>(batchSize);

    foreach (var doc in documents)
    {
        doc.Embedding = await embeddingGenerator.GenerateEmbeddingAsync(doc.Content);
        batch.Add(doc);

        if (batch.Count >= batchSize)
        {
            await collection.UpsertBatchAsync(batch);
            batch.Clear();
        }
    }

    if (batch.Count > 0)
    {
        await collection.UpsertBatchAsync(batch);
    }
}

UpsertBatchAsync sends a single INSERT ... ON CONFLICT statement per batch, reducing round trips significantly.

Clean up

To remove the demo data and collection:

await collection.DeleteCollectionAsync();

This drops the table and its indexes.

Next steps

• Hybrid search: Combine vector similarity with full-text search using tsvector and websearch_to_tsquery in a single query. The pgvector connector does not yet expose a built-in hybrid API, so you will write raw SQL or use Npgsql directly for that layer.
• Multi-tenancy: Add a tenant_id column with [VectorStoreRecordData(IsFilterable = true)] and filter every query by tenant.
• Observability: Wrap the NpgsqlDataSource with OpenTelemetry to trace query latency and connection pool saturation.
• Managed pgvector: If you prefer not to operate PostgreSQL, consider Neon, Supabase, Timescale, or Azure Cosmos DB for PostgreSQL — all support pgvector and HNSW indexes.

You now have a working Semantic Kernel vector store backed by pgvector. The same pattern scales to millions of vectors when you tune the HNSW parameters, batch your writes, and size your connection pool appropriately.