Skip to content

PGVector (PostgreSQL + pgvector)

Example Code: examples/knowledge/vectorstores/postgres

PGVector is a vector store implementation based on PostgreSQL + pgvector extension, supporting hybrid retrieval (vector similarity + text relevance).

Basic Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
import (
    "trpc.group/trpc-go/trpc-agent-go/knowledge"
    vectorpgvector "trpc.group/trpc-go/trpc-agent-go/knowledge/vectorstore/pgvector"
)

// PostgreSQL + pgvector
pgVS, err := vectorpgvector.New(
    vectorpgvector.WithPGVectorClientDSN("postgres://postgres:your-password@127.0.0.1:5432/your-database?sslmode=disable"),
    // Set index dimension based on embedding model (text-embedding-3-small is 1536)
    vectorpgvector.WithIndexDimension(1536),
    // vectorpgvector.WithEnableTSVector(true), // Enable full-text search for Keyword/Hybrid search
)
if err != nil {
    // Handle error
}

kb := knowledge.New(
    knowledge.WithVectorStore(pgVS),
    knowledge.WithEmbedder(embedder), // Configure embedder
)

Configuration Options

Connection Configuration

PGVector supports two connection configuration methods (in priority order from high to low):

1. Direct Connection Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
// Option 1: Use DSN connection string (recommended)
pgVS, err := vectorpgvector.New(
    vectorpgvector.WithPGVectorClientDSN("postgres://user:password@localhost:5432/mydb?sslmode=disable"),
    vectorpgvector.WithIndexDimension(1536),
)

// Option 2: Use individual connection parameters
// pgVS, err := vectorpgvector.New(
//     vectorpgvector.WithHost("localhost"),
//     vectorpgvector.WithPort(5432),
//     vectorpgvector.WithUser("postgres"),
//     vectorpgvector.WithPassword("your-password"),
//     vectorpgvector.WithDatabase("mydb"),
//     vectorpgvector.WithSSLMode("disable"),
//     vectorpgvector.WithTable("documents"),
//     vectorpgvector.WithIndexDimension(1536),
// )

2. Use Registered Instance

Reuse a PostgreSQL instance already registered in storage/postgres, suitable for scenarios where multiple components share the same database connection.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
import "trpc.group/trpc-go/trpc-agent-go/storage/postgres"

// Step 1: Register instance
postgres.RegisterPostgresInstance("my-postgres",
    postgres.WithClientConnString("postgres://user:password@localhost:5432/mydb?sslmode=disable"),
)

// Step 2: Use registered instance
pgVS, err := vectorpgvector.New(
    vectorpgvector.WithPostgresInstance("my-postgres"),
    vectorpgvector.WithIndexDimension(1536),
)

Priority Rules: - WithPGVectorClientDSN() / WithHost()... > WithPostgresInstance() - If multiple methods are specified simultaneously, the higher priority takes effect

Option Description Default Value
WithPGVectorClientDSN(dsn) PostgreSQL connection string -
WithHost(host) Database host address "localhost"
WithPort(port) Database port 5432
WithUser(user) Database username -
WithPassword(password) Database password -
WithDatabase(database) Database name "trpc_agent_go"
WithTable(table) Table name "documents"
WithSSLMode(mode) SSL mode "disable"
WithPostgresInstance(name) Use registered PostgreSQL instance -

Vector Configuration

Option Description Default Value
WithIndexDimension(dim) Vector dimension (must match embedding model) 1536
WithVectorIndexType(type) Vector index type (VectorIndexHNSW / VectorIndexIVFFlat) VectorIndexHNSW
WithHNSWIndexParams(params) HNSW index parameters (M, EfConstruction) M=16, EfConstruction=64
WithIVFFlatIndexParams(params) IVFFlat index parameters (Lists) Lists=100

Hybrid Search Configuration

Option Description Default Value
WithEnableTSVector(enabled) Enable text search vector true
WithHybridSearchWeights(vector, text) Hybrid search weights (vector/text), only applies in Weighted mode 0.7, 0.3
WithHybridFusionMode(mode) Hybrid search fusion mode (HybridFusionWeighted / HybridFusionRRF) HybridFusionWeighted
WithRRFParams(params) RRF parameters (K and CandidateRatio), only applies in RRF mode K=60, CandidateRatio=3
WithLanguageExtension(lang) Text tokenization language extension (e.g., zhparser/jieba) "english"

Search Configuration

Option Description Default Value
WithMaxResults(n) Default search result count 10
WithDocBuilder(builder) Custom document builder Default builder
WithExtraOptions(opts...) Inject custom PostgreSQL ClientBuilder config, no need to care by default -

Field Mapping (Advanced)

Option Description Default Value
WithIDField(field) ID field name "id"
WithNameField(field) Name field name "name"
WithContentField(field) Content field name "content"
WithEmbeddingField(field) Embedding field name "embedding"
WithMetadataField(field) Metadata field name "metadata"
WithCreatedAtField(field) Created at field name "created_at"
WithUpdatedAtField(field) Updated at field name "updated_at"

Full-Text Search

PGVector supports full-text search (TSVector), which can be used for Keyword Search and Hybrid Search.

⚠️ Important: Keyword Search and Hybrid Search require enabling PostgreSQL full-text search functionality via WithEnableTSVector(true).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
// Option 1: Weighted fusion (default)
pgVS, err := vectorpgvector.New(
    vectorpgvector.WithPGVectorClientDSN(dsn),
    vectorpgvector.WithIndexDimension(1536),
    vectorpgvector.WithEnableTSVector(true),           // ✅ Enable full-text search
    vectorpgvector.WithHybridSearchWeights(0.7, 0.3),  // Set hybrid search weights (70% vector + 30% text)
    vectorpgvector.WithLanguageExtension("english"),   // Set tokenization language (Chinese requires zhparser/jieba)
)

// Option 2: Reciprocal Rank Fusion (RRF)
pgVS, err := vectorpgvector.New(
    vectorpgvector.WithPGVectorClientDSN(dsn),
    vectorpgvector.WithIndexDimension(1536),
    vectorpgvector.WithEnableTSVector(true),
    vectorpgvector.WithHybridFusionMode(vectorpgvector.HybridFusionRRF),
    vectorpgvector.WithRRFParams(&vectorpgvector.RRFParams{
        K:              60, // RRF constant, smaller = more weight to top results
        CandidateRatio: 3,  // fetch 3x candidates from each sub-search
    }),
)

Search Mode Support

Search Mode Requires TSVector Description
Vector Search ❌ Uses vector index only
Keyword Search ✅ Required Depends on PostgreSQL tsvector full-text index
Hybrid Search ✅ Required Uses both vector index and tsvector index
Filter Search ❌ Metadata filtering only

If WithEnableTSVector(true) is not enabled:

The system will automatically downgrade the search mode without error: - When attempting Keyword/Hybrid search → Automatically downgrades to Vector Search (if vector available) or Filter Search (if no vector) - INFO logs will be output indicating the downgrade reason

Note: The default call chain from SearchTool to VectorStore does not actively specify SearchModeKeyword, so Keyword Search is typically not triggered; instead, the default Vector or Hybrid search is used.

Search Modes and Score Normalization

💡 Note: This section covers score calculation details. Users typically don't need to worry about this. PGVector automatically handles score normalization for all search modes.

PGVector supports multiple search modes, all scores are normalized to the [0, 1] range, with higher scores indicating stronger relevance.

SQL Template (using subquery to avoid duplicate calculations): 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT *, vector_score as score
FROM (
  SELECT *, 
         (1.0 - (embedding <=> $1) / 2.0) as vector_score,
         0.0 as text_score
  FROM table_name
  WHERE ...
  ORDER BY embedding <=> $1
  LIMIT 10
) subq

Normalization Formula: 

1
vector_score = 1.0 - (cosine_distance / 2.0)

Mathematical Principle: - PGVector <=> operator returns Cosine Distance: d ∈ [0, 2] - d = 0: Vectors are identical - d = 1: Vectors are orthogonal - d = 2: Vectors are opposite - Cosine Similarity: s = 1 - d ∈ [-1, 1] - Normalized to [0, 1]: score = (s + 1) / 2 = (2 - d) / 2 = 1 - d/2

Examples: - distance = 0.2 → score = 1 - 0.2/2 = 0.90 (highly similar) - distance = 1.0 → score = 1 - 1.0/2 = 0.50 (orthogonal) - distance = 1.8 → score = 1 - 1.8/2 = 0.10 (nearly opposite)

SQL Template (using subquery to avoid duplicate calculations): 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT *, text_score as score
FROM (
  SELECT *, 
         0.0 as vector_score,
         (ts_rank(to_tsvector('english', content), websearch_to_tsquery('english', $1)) 
          / (ts_rank(to_tsvector('english', content), websearch_to_tsquery('english', $1)) + 0.1)) as text_score
  FROM table_name
  WHERE to_tsvector('english', content) @@ websearch_to_tsquery('english', $1)
  ORDER BY (ts_rank(...) / (ts_rank(...) + 0.1)) DESC, created_at DESC
  LIMIT 10
) subq

Normalization Formula: 

1
text_score = rank / (rank + c)
Where c = 0.1 (sparseNormConstant)

Mathematical Principle: - PostgreSQL ts_rank() returns raw text relevance score with unbounded range (typically [0, ∞)) - Uses hyperbolic normalization: f(x) = x / (x + c) to map to [0, 1) - Parameter c controls sensitivity: - Smaller c: More sensitive to small rank values, higher discrimination - Larger c: More tolerant to large rank values, tends to saturate

Examples (c = 0.1): - rank = 1.0 → score = 1.0 / 1.1 = 0.909 - rank = 0.5 → score = 0.5 / 0.6 = 0.833 - rank = 0.1 → score = 0.1 / 0.2 = 0.500 - rank = 0.01 → score = 0.01 / 0.11 = 0.091

Computation Flow Example:

Assume user query is "machine learning", and the database has a document with content "Machine learning enables intelligent systems..."

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- Step 1: Convert query to tsquery
websearch_to_tsquery('english', 'machine learning')
→ 'machin' & 'learn'  -- (automatic stemming: machine → machin, learning → learn)

-- Step 2: Convert document content to tsvector
to_tsvector('english', 'Machine learning enables intelligent systems...')
→ 'enabl':3 'intellig':4 'learn':2 'machin':1 'system':5

-- Step 3: Check match (@@)
to_tsvector(...) @@ websearch_to_tsquery(...)
→ true  -- (contains both 'machin' and 'learn' stems)

-- Step 4: Calculate relevance score
ts_rank(to_tsvector(...), websearch_to_tsquery(...))
→ 2.5  -- (raw rank value)

-- Step 5: Normalize to [0, 1)
text_score = 2.5 / (2.5 + 0.1) = 0.961

Core Functions: - to_tsvector(): Text → searchable vector (tokenization, stemming) - websearch_to_tsquery(): User query → search expression (supports "quotes", OR, -exclusion) - @@: Match check (true/false) - ts_rank(): Calculate relevance score

Hybrid Search supports two fusion modes, switchable via WithHybridFusionMode().

3a. Weighted Fusion (default)

SQL Template (using subquery to avoid duplicate calculations): 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT *, (vector_score * 0.700 + text_score * 0.300) as score
FROM (
  SELECT *,
         (1.0 - (embedding <=> $1) / 2.0) as vector_score,
         (COALESCE(ts_rank(to_tsvector('english', content),
                           websearch_to_tsquery('english', $2)), 0)
          / (COALESCE(ts_rank(...), 0) + 0.1000)) as text_score
  FROM documents
  WHERE ...
  ORDER BY ((1.0 - (embedding <=> $1) / 2.0) * 0.700
            + (ts_rank_expr / (ts_rank_expr + 0.1000)) * 0.300) DESC
  LIMIT 10
) subq

Normalization Formula: 

1
hybrid_score = vector_score × w_v + text_score × w_t
Where default weights: w_v = 0.7, w_t = 0.3

Mathematical Principle: - Calculate vector_score and text_score separately (as in the two modes above) - Use linear weighted combination, weights configurable via WithHybridSearchWeights() - Since both scores are in [0, 1] range and w_v + w_t = 1, the final hybrid_score ∈ [0, 1] - Important: Documents without text match are not forcibly filtered out, because vector similarity has higher weight (0.7), even without text match, high-quality results may still be returned

Examples: 

1
2
3
4
5
6
7
Case 1: Both vector and text match well
  vector_score = 0.85, text_score = 0.90
  hybrid_score = 0.85 × 0.7 + 0.90 × 0.3 = 0.865

Case 2: High vector similarity but no text match
  vector_score = 0.95, text_score = 0.0
  hybrid_score = 0.95 × 0.7 + 0.0 × 0.3 = 0.665  -- Still returns high-quality result

3b. Reciprocal Rank Fusion (RRF)

RRF is a rank-based fusion strategy that does not rely on score normalization. Instead, it computes the final score based on each document's rank in the sub-searches. It is suitable for scenarios where vector scores and text scores have very different scales and are hard to combine via direct weighting.

Execution Flow (parallel sub-queries + Go-level fusion):

RRF mode splits vector search and text search into two independent SQL queries executed in parallel, then performs RRF score fusion in Go code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- Query 1: Vector rank sub-search (parallel)
SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) as rank
FROM documents WHERE ... LIMIT 30  -- limit * CandidateRatio

-- Query 2: Text rank sub-search (parallel)
SELECT id, ROW_NUMBER() OVER (
  ORDER BY ts_rank(to_tsvector('english', content),
                   websearch_to_tsquery('english', $1)) DESC
) as rank
FROM documents
WHERE ... AND to_tsvector('english', content) @@ websearch_to_tsquery('english', $1)
LIMIT 30

-- Query 3: Fetch full documents by fused top-N IDs
SELECT * FROM documents WHERE id IN ($1, $2, ...)

1
2
3
4
5
// Go-level RRF fusion:
// 1. Merge (id, rank) pairs from both sub-searches
// 2. Compute RRF score: score(d) = 1/(k + rank_v) + 1/(k + rank_t)
// 3. Sort by score DESC, take top N
// 4. Fetch full documents by IDs

RRF Formula: 

1
score(d) = sum(1 / (k + rank_i))  for each ranking list i

Parameters:

Parameter Description Default
K RRF constant, must be > 0. Smaller values give more weight to top-ranked results 60
CandidateRatio Candidate multiplier, must be > 0. Each sub-search fetches limit × CandidateRatio candidates 3

Effect of K: - K=1: Rank 1 scores 1/2 = 0.500, rank 10 scores 1/11 ≈ 0.091, gap ~5.5x - K=60 (default): Rank 1 scores 1/61 ≈ 0.016, rank 10 scores 1/70 ≈ 0.014, gap ~1.15x

K=60 is the widely used default in academic literature, making fusion more "democratic" by not over-favoring results that rank very high in a single ranking list.

Notes: - In RRF mode, WithHybridSearchWeights() has no effect; RRF scores from both ranking lists are summed directly - In RRF mode, MinScore has no effect. RRF scores are rank-based (max ~0.033 for K=60) and are incompatible with the [0,1] similarity score semantics - The two sub-queries execute in parallel, latency = max(vector_latency, text_latency), more efficient than a single complex CTE

SQL Template: 

1
2
3
4
5
6
7
8
SELECT *, 
       0.0 as vector_score,
       0.0 as text_score,
       1.0 as score
FROM table_name
WHERE [metadata filters]
ORDER BY created_at DESC
LIMIT 10

Description: - Pure metadata filtering, no vector or text similarity involved - All results have score = 1.0 (as they all satisfy the filter conditions) - Sorted by creation time in descending order