PostgreSQL

Runiq.AI.Rag.PostgreSql stores logical indexes, documents, chunks, embeddings, JSONB metadata, and ingestion state. Vector search executes in PostgreSQL, allowing the application to keep its retrieval data across process restarts.

PostgreSQL configuration does not silently fall back to in-memory storage. Missing configuration fails during registration, and connection or migration failures remain provider failures that the application must handle.

Production deployments should provision the vector and pg_trgm extensions and run reviewed migrations before the application starts. Keep InitializeSchema and CreateVectorExtension disabled at runtime unless a controlled local or test environment explicitly owns schema initialization. Creating extensions requires database privileges that many runtime roles should not have.

Install the integration

dotnet add package Runiq.AI.Rag.PostgreSql

The database needs the vector and pg_trgm extensions. vector backs pgvector similarity search. pg_trgm supports lexical search alongside the provider's text-search indexes, so PostgreSQL can serve lexical and hybrid retrieval in addition to durable vector storage.

Register the provider

Read the connection string from host configuration. This example assumes the schema and extensions were provisioned during deployment:

using Runiq.AI.Rag.DependencyInjection;
using Runiq.AI.Rag.PostgreSql.DependencyInjection;

var connectionString = builder.Configuration.GetConnectionString("Rag")
    ?? throw new InvalidOperationException("ConnectionStrings:Rag is required.");

builder.Services.AddRuniqRag();
builder.Services.AddRuniqRagPostgreSql(options =>
{
    options.ConnectionString = connectionString;
    options.InitializeSchema = false;
    options.CreateVectorExtension = false;
});

Select UsePostgreSqlVectorStore() in the named index configuration from Defining indexes. Register the PostgreSQL provider after any default in-memory provider setup: the last provider registration wins. A missing connection string fails at registration; a connection failure does not silently switch to in-memory storage.

Local database setup

From a checkout of the Runiq.AI repository, start the supplied development database:

docker compose -f docker-compose.rag-postgresql.yml up -d --wait

Its development connection string is:

Host=localhost;Port=54329;Database=runiq_rag_dev;Username=runiq_dev;Password=runiq_dev_only

These credentials belong to the local sample. Supply them through ConnectionStrings:Rag for that environment. The volume retains data across container restarts.

For local schema initialization, change the provider options to:

options.InitializeSchema = true;
options.CreateVectorExtension = true;

The database role must have permission to install the required extensions. In production, provision extensions and apply reviewed migrations during deployment, then keep runtime schema initialization disabled. Initialization is opt-in, transactional, idempotent, and non-destructive.

Customize document persistence

The managed ingestion path handles document writes for the configured index. For custom ingestion code, IPostgreSqlRagDocumentStore.UpsertDocumentAsync writes a document aggregate. The following fragment assumes the caller already prepared contentHash and chunkVectors for the logical index:

var outcome = await documentStore.UpsertDocumentAsync(
    new PostgreSqlRagDocumentUpsertRequest
    {
        IndexName = "company-policies",
        DocumentId = "handbook",
        ContentHash = contentHash,
        Version = "handbook-v2",
        Records = chunkVectors,
    }, cancellationToken);

Writes use an advisory lock scoped to the index and document. DeleteDocumentAsync(indexName, documentId) is index-scoped and idempotent; it returns Deleted or NotFound, with related chunks and ingestion state removed through foreign-key cascades.

Understand database search

Metadata equality filters run in SQL before the candidate limit. Results sort by distance, then document and chunk identity. PostgreSQL reports cosine and Euclidean distances as lower-is-better raw values and dot product as higher-is-better.

Exact scan is the default because one table can contain indexes with different vector dimensions and metrics. At scale, evaluate dimension- and metric-specific partial HNSW indexes against real workloads. IPostgreSqlRagHealthCheck reports connectivity, extensions, schema, migration version, and index-table readability.

Lexical search uses provider-managed text-search and trigram structures for exact terms, punctuation-sensitive identifiers, and phrase intent. Hybrid retrieval runs both PostgreSQL vector search and lexical search, then fuses the ranked candidates in the retrieval layer; it is not just vector persistence with a database behind it.

Reference: PostgreSQL package guide.