Practical notes: Semantic Search with PostgreSQL: Pragmatism Beats Hype — Most

Use this as an operator-facing rebuild of the ideas in “Semantic Search with PostgreSQL: Pragmatism Beats Hype – Most of the Time”: clear stages, ordered code slots, and recovery notes that survive a handoff. The Overview stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

Semantic Search in One Sentence

For the Semantic Search in One stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

The Most Important Design Decision: The Embedding Model

For the The Most Important Design stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Prefer structured outputs with schema validation over free-form prose when the next step is code or a tool call.

Installing pgvector

For the Installing pgvector stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap. For the Installing pgvector stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

CREATE EXTENSION IF NOT EXISTS vector;

Schema: Store Chunks, Not Just Documents

When working through the Schema Store Chunks Not stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    source      TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE document_chunks (
    id           BIGSERIAL PRIMARY KEY,
    document_id  BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index  INTEGER NOT NULL,
    content      TEXT NOT NULL,
    embedding    vector(1536) NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (document_id, chunk_index)
);

Integration in .NET with Npgsql

When working through the Integration in NET with stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

dotnet add package Npgsql
dotnet add package Pgvector

dotnet add package Pgvector.Dapper

using Npgsql;
using Pgvector;

var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.UseVector();

await using var dataSource = dataSourceBuilder.Build();

Generating Embeddings with OpenAI

When working through the Generating Embeddings with OpenAI stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface. When working through the Generating Embeddings with OpenAI stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

using OpenAI.Embeddings;

var embeddingClient = new EmbeddingClient("text-embedding-3-small", apiKey);

async Task<float[]> GetEmbeddingAsync(string text)
{
    var result = await embeddingClient.GenerateEmbeddingAsync(text);
    return result.Value.ToFloats().ToArray();
}

Storing a Document Chunk

The Storing a Document Chunk stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move.

async Task InsertChunkAsync(
    long documentId,
    int chunkIndex,
    string content,
    CancellationToken cancellationToken = default)
{
    var embedding = await GetEmbeddingAsync(content);
    var vector = new Vector(embedding);

    await using var conn = await dataSource.OpenConnectionAsync(cancellationToken);
    await using var cmd = new NpgsqlCommand("""
        INSERT INTO document_chunks (document_id, chunk_index, content, embedding)
        VALUES (@documentId, @chunkIndex, @content, @embedding)
        ON CONFLICT (document_id, chunk_index)
        DO UPDATE SET
            content = EXCLUDED.content,
            embedding = EXCLUDED.embedding
        """, conn);

    cmd.Parameters.AddWithValue("documentId", documentId);
    cmd.Parameters.AddWithValue("chunkIndex", chunkIndex);
    cmd.Parameters.AddWithValue("content", content);
    cmd.Parameters.AddWithValue("embedding", vector);

    await cmd.ExecuteNonQueryAsync(cancellationToken);
}

Semantic Search

The Semantic Search stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move.

public sealed record SearchResult(
    long DocumentId,
    long ChunkId,
    string Title,
    string Content,
    double Distance);

async Task<List<SearchResult>> SearchAsync(
    string query,
    int limit = 5,
    CancellationToken cancellationToken = default)
{
    var queryEmbedding = await GetEmbeddingAsync(query);
    var queryVector = new Vector(queryEmbedding);

    await using var conn = await dataSource.OpenConnectionAsync(cancellationToken);
    await using var cmd = new NpgsqlCommand("""
        SELECT d.id,
               c.id,
               d.title,
               c.content,
               c.embedding <=> @queryVector AS distance
        FROM document_chunks c
        JOIN documents d ON d.id = c.document_id
        ORDER BY c.embedding <=> @queryVector
        LIMIT @limit
        """, conn);

    cmd.Parameters.AddWithValue("queryVector", queryVector);
    cmd.Parameters.AddWithValue("limit", limit);

    var results = new List<SearchResult>();

    await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
    while (await reader.ReadAsync(cancellationToken))
    {
        results.Add(new SearchResult(
            DocumentId: reader.GetInt64(0),
            ChunkId: reader.GetInt64(1),
            Title: reader.GetString(2),
            Content: reader.GetString(3),
            Distance: reader.GetDouble(4)));
    }

    return results;
}

Distance Operators

The Distance Operators stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move. The Distance Operators stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

Creating an Index with HNSW

For the Creating an Index with stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

CREATE INDEX document_chunks_embedding_hnsw_idx
ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 100;

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT ...
COMMIT;

IVFFlat as an Alternative

For the IVFFlat as an Alternative stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

CREATE INDEX document_chunks_embedding_ivfflat_idx
ON document_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

SET ivfflat.probes = 10;

Filtering and Hybrid Search

For the Filtering and Hybrid Search stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap. For the Filtering and Hybrid Search stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

SELECT d.id, d.title, c.content, c.embedding <=> @queryVector AS distance
FROM document_chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.created_at > NOW() - INTERVAL '30 days'
  AND d.source = 'documentation'
ORDER BY c.embedding <=> @queryVector
LIMIT 10;

WITH semantic AS (
    SELECT c.id,
           row_number() OVER (ORDER BY c.embedding <=> @queryVector) AS semantic_rank
    FROM document_chunks c
    LIMIT 100
),
keyword AS (
    SELECT c.id,
           row_number() OVER (
               ORDER BY ts_rank_cd(
                   to_tsvector('english', c.content),
                   plainto_tsquery('english', @query)
               ) DESC
           ) AS keyword_rank
    FROM document_chunks c
    WHERE to_tsvector('english', c.content) @@ plainto_tsquery('english', @query)
    LIMIT 100
)
SELECT d.id AS document_id,
       c.id AS chunk_id,
       d.title,
       c.content,
       COALESCE(1.0 / (60 + semantic.semantic_rank), 0) +
       COALESCE(1.0 / (60 + keyword.keyword_rank), 0) AS score
FROM semantic
FULL OUTER JOIN keyword ON keyword.id = semantic.id
JOIN document_chunks c ON c.id = COALESCE(semantic.id, keyword.id)
JOIN documents d ON d.id = c.document_id
ORDER BY score DESC
LIMIT 10;

When pgvector Is the Right Choice

When working through the When pgvector Is the stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

When a Dedicated Vector Store May Be Better

When working through the When a Dedicated Vector stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Complete ASP.NET Core Example

When working through the Complete ASP NET Core stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

app.MapGet("/search", async (
    string q,
    NpgsqlDataSource db,
    CancellationToken cancellationToken) =>
{
    var queryEmbedding = await GetEmbeddingAsync(q);
    var queryVector = new Vector(queryEmbedding);

    await using var conn = await db.OpenConnectionAsync(cancellationToken);
    await using var cmd = new NpgsqlCommand("""
        SELECT d.id,
               c.id,
               d.title,
               c.content,
               c.embedding <=> @v AS distance
        FROM document_chunks c
        JOIN documents d ON d.id = c.document_id
        ORDER BY c.embedding <=> @v
        LIMIT 5
        """, conn);

    cmd.Parameters.AddWithValue("v", queryVector);

    var results = new List<object>();

    await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
    while (await reader.ReadAsync(cancellationToken))
    {
        results.Add(new
        {
            documentId = reader.GetInt64(0),
            chunkId = reader.GetInt64(1),
            title = reader.GetString(2),
            content = reader.GetString(3),
            distance = reader.GetDouble(4)
        });
    }

    return Results.Ok(results);
});

Conclusion

When working through the Conclusion stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

References

When working through the References stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Operational checklist

When working through the Operational checklist stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest.

Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Pin dependency versions and record the image digest that ran the demo. Reproducibility beats tribal knowledge.

Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Before promoting the stack, freeze versions, capture a golden transcript for the critical path, and confirm rollback steps. Shared environments need rate limits, tenancy checks, and a clear owner for secret rotation. Prefer boring reliability over clever one-off demos.

Batch note for 5e57ac6d3d33: keep provider keys out of the repo, set a per-session token ceiling, and store transcripts next to the eval fixtures so later model swaps stay comparable.