pg-hybrid-search

Version: v0.5.0 (Beta) — Open for feedback

🚀 Advanced Hybrid Search Toolkit for PostgreSQL

Seamlessly combine vector similarity, BM25 full-text search, and AI-powered reranking

📋 Table of Contents

• 🌟 Overview
• ✨ Key Features
• 🚀 Quick Start
• 📦 Installation
• ⚙️ Configuration
• 📚 API Reference
• 📖 Complete API Documentation ⭐
• 🖥 CLI Tools
• 🆕 What's New (v0.5.0 Beta)
• 💡 Usage Examples
• 🏗 Database Schema
• ⚡ Performance Optimization
• 🔧 Development
• 🤝 Contributing
• 📄 License

🌟 Overview

pg-hybrid-search is a powerful, production-ready library that brings advanced search capabilities to PostgreSQL applications. Combining the precision of vector similarity search with the versatility of full-text search and the intelligence of AI-powered reranking.

Why Choose pg-hybrid-search?

• 🎯 Best of Both Worlds: Vector similarity + BM25 full-text search
• 🤖 AI-Enhanced: Optional reranking with Voyage AI for superior relevance
• 🚀 Performance Focused: Optimized queries and connection pooling
• 🛡️ Type Safe: Full TypeScript support with comprehensive types
• 🔧 Developer Friendly: Simple CLI tools and intuitive API
• 📈 Production Ready: Battle-tested in real-world applications

✨ Key Features

🔍 Advanced Search Capabilities

• Vector Search: Cosine similarity using OpenAI embeddings
• Full-text Search: PostgreSQL's powerful BM25 algorithm
• Hybrid Search: Intelligent combination with custom weights
• AI Reranking: Voyage Rerank v2 integration
• Multi-Index Support: Isolated document collections for different use cases

🛠️ Developer Experience

• TypeScript First: Complete type safety & IntelliSense
• CLI Tools: Easy schema management
• Flexible API: Simple yet powerful functions
• Well Documented: Comprehensive guides & examples

🚀 Quick Start

RAG Search with AI SDKs (Anthropic/OpenAI)

Perfect for building AI assistants with retrieval-augmented generation:

With Anthropic Claude SDK

import { createClient } from '@brightly/pg-hybrid-search';
import Anthropic from '@anthropic-ai/sdk';

const client = createClient();
const knowledge = client.index("knowledge-base");
const anthropic = new Anthropic({ apiKey: process.env.ANTHROPIC_API_KEY });

// 1. Setup search tool for Claude
const searchTool = {
  name: "search_knowledge",
  description: "Search the knowledge base for relevant information",
  input_schema: {
    type: "object",
    properties: {
      query: { type: "string", description: "Search query" },
      limit: { type: "number", description: "Number of results", default: 5 }
    },
    required: ["query"]
  }
};

// 2. RAG-powered chat function
async function ragChat(userMessage: string) {
  const message = await anthropic.messages.create({
    model: "claude-3-5-sonnet-20241022",
    max_tokens: 1000,
    tools: [searchTool],
    messages: [{
      role: "user",
      content: userMessage
    }]
  });

  // Handle tool calls
  if (message.content[0].type === 'tool_use') {
    const toolCall = message.content[0];
    
    // Execute search
    const searchResults = await knowledge.search({
      query: toolCall.input.query,
      limit: toolCall.input.limit || 5,
      reranking: true
    });

    // Continue conversation with search results
    const followUp = await anthropic.messages.create({
      model: "claude-3-5-sonnet-20241022",
      max_tokens: 1000,
      messages: [
        { role: "user", content: userMessage },
        { role: "assistant", content: message.content },
        {
          role: "user",
          content: [{
            type: "tool_result",
            tool_use_id: toolCall.id,
            content: JSON.stringify(searchResults.map(r => r.raw_content))
          }]
        }
      ]
    });

    return followUp.content[0].text;
  }

  return message.content[0].text;
}

// Usage
const response = await ragChat("What are the latest developments in AI?");
console.log(response);

With OpenAI SDK

import { createClient } from '@brightly/pg-hybrid-search';
import OpenAI from 'openai';

const client = createClient();
const docs = client.index("documentation");
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });

// 1. Define search function for OpenAI
const searchFunction = {
  name: "search_docs",
  description: "Search documentation for relevant information",
  parameters: {
    type: "object",
    properties: {
      query: { type: "string", description: "Search query" },
      limit: { type: "number", description: "Number of results", default: 3 }
    },
    required: ["query"]
  }
};

// 2. RAG chat with function calling
async function ragChatGPT(userMessage: string) {
  const completion = await openai.chat.completions.create({
    model: "gpt-4",
    messages: [{ role: "user", content: userMessage }],
    functions: [searchFunction],
    function_call: "auto"
  });

  const message = completion.choices[0].message;

  if (message.function_call) {
    // Execute search
    const args = JSON.parse(message.function_call.arguments);
    const searchResults = await docs.search({
      query: args.query,
      limit: args.limit,
      reranking: true
    });

    // Continue with search results
    const followUp = await openai.chat.completions.create({
      model: "gpt-4",
      messages: [
        { role: "user", content: userMessage },
        message,
        {
          role: "function",
          name: "search_docs",
          content: JSON.stringify(searchResults.map(r => r.raw_content))
        }
      ]
    });

    return followUp.choices[0].message.content;
  }

  return message.content;
}

// Usage
const answer = await ragChatGPT("How do I implement vector search?");
console.log(answer);

Basic Library Usage

Modern Client API (Recommended)

import { createClient } from '@brightly/pg-hybrid-search';

const client = createClient();

// 1. Insert documents with automatic embedding generation
await client.index("documents").add("Machine learning revolutionizes data analysis");
await client.index("documents").add("PostgreSQL provides excellent full-text search");

// 2. AI-powered semantic search with reranking
const results = await client.index("documents").search({
  query: "AI data analysis", 
  limit: 5,
  reranking: true
});

// 3. Results include relevance scores and original content
results.forEach(result => {
  console.log(`Score: ${result.hybrid_score || result.rerank_score}`);
  console.log(`Content: ${result.raw_content}`);
});

Simplified Functional API (Also Available)

import { add, search } from '@brightly/pg-hybrid-search';

await add("Machine learning revolutionizes data analysis");
const results = await search({ query: "AI data analysis", limit: 5 });

📦 Installation

# Install the package
npm install @brightly/pg-hybrid-search

# Initialize database schema
npx @brightly/pg-hybrid-search init

Prerequisites

| Requirement | Version | Purpose | |-------------|---------|---------| | Node.js | ≥18.0.0 | Runtime environment | | PostgreSQL | ≥15.0.0 | Database with pgvector support | | pgvector | Latest | Vector similarity operations | | OpenAI API | - | Embedding generation | | Voyage AI API | - | Reranking (optional) |

⚙️ Configuration

Environment Variables

Create a .env file in your project root:

# Database Connection (Required)
DATABASE_URL=postgresql://username:password@localhost:5432/your_database

# OpenAI Configuration (Required)
OPENAI_API_KEY=sk-your-openai-api-key-here
EMBED_MODEL=text-embedding-3-small  # Optional: default model

# Voyage AI Configuration (Optional - for reranking)
VOYAGE_API_KEY=pa-your-voyage-api-key-here
RERANK_MODEL=rerank-2.5-lite  # Optional: default rerank model

Database Setup

# One-time schema initialization
npx @brightly/pg-hybrid-search init

# Verify installation
psql -d your_database -c "SELECT COUNT(*) FROM vector_table;"

📚 API Reference

📖 View Complete API Documentation →
Comprehensive guide with sequence diagrams, payload examples, and detailed usage patterns

Modern Client API (Recommended)

Creating a Client

import { createClient } from '@brightly/pg-hybrid-search';

const client = createClient();

Index Operations

// Get an index reference
const index = client.index("your-index-name");

// Add documents
// Optional: set language per document (improves BM25 in multilingual apps)
const documentId = await index.add("Your document content", "indonesian");

// Remove documents  
await index.remove(documentId);

// Destroy an index (delete all rows with this index name)
const deleted = await index.destroy();
console.log(`Index cleared: ${deleted} rows removed`);

Search Operations

// Hybrid search (default)
const results = await index.search({
  query: "your search query",
  limit: 10,                // Optional: number of results (default: 10)
  reranking: true,          // Optional: enable AI reranking (default: false)
  weights: {                // Optional: custom hybrid weights
    vectorW: 0.7,
    textW: 0.3
  },
  topNForRerank: 50        // Optional: candidates for reranking (default: 50)
});

// Pure vector search
const vectorResults = await index.search({
  query: "your search query",
  limit: 10,
  vectorOnly: true         // Enable vector-only mode
});

Search Options Interface

interface ClientSearchOptions {
  query: string;           // Search query text
  limit?: number;          // Number of results to return
  reranking?: boolean;     // Enable AI-powered reranking
  vectorOnly?: boolean;    // Use vector search only (no BM25)
  weights?: SearchWeights; // Custom scoring weights
  topNForRerank?: number;  // Candidates to consider for reranking
}

interface SearchWeights {
  vectorW: number;         // Vector search weight (0-1)
  textW: number;           // Text search weight (0-1)
}

Type Definitions

interface SearchResult {
  id: string;
  raw_content: string;
  cosine_sim?: number;        // Vector similarity score
  ts_score?: number;          // BM25 text search score  
  hybrid_score?: number;      // Combined normalized score
  rerank_score?: number;      // AI reranking score
  created_at?: string;
  updated_at?: string;
}

interface HybridWeights {
  vectorW: number;            // Vector search weight (0-1)
  textW: number;              // Text search weight (0-1)
}

🖥 CLI Tools

The CLI provides essential database management commands:

# Initialize database schema (safe to run multiple times)
npx @brightly/pg-hybrid-search init

# Reset schema (⚠️ destructive - requires confirmation)
npx @brightly/pg-hybrid-search reset -y

# Show help
npx @brightly/pg-hybrid-search help

CLI Command Reference

| Command | Description | Usage | |---------|-------------|-------| | init | Creates tables, indexes, and triggers | pg-hybrid init | | reset -y | ⚠️ Drops all tables and indexes | pg-hybrid reset -y | | help | Shows command documentation | pg-hybrid help |

💡 Usage Examples

Modern Client API Examples

Basic Document Management

import { createClient } from '@brightly/pg-hybrid-search';

const client = createClient();
const movies = client.index("movies");

// Insert documents
const docIds = await Promise.all([
  movies.add("Star Wars: A space opera epic with Jedi knights"),
  movies.add("Blade Runner: Cyberpunk dystopian future with replicants"),
  movies.add("The Matrix: Virtual reality and artificial intelligence thriller")
]);

// AI-powered semantic search with reranking
const results = await movies.search({
  query: "space opera with jedi",
  limit: 5,
  reranking: true
});

console.log(`Found ${results.length} relevant movies`);

// Clean up
await Promise.all(docIds.map(id => movies.remove(id)));

Advanced Search Scenarios

// Semantic-focused search with custom weights
const semanticResults = await movies.search({
  query: "futuristic AI rebellion",
  limit: 10,
  weights: { vectorW: 0.9, textW: 0.1 }
});

// Keyword-focused search
const keywordResults = await movies.search({
  query: "cyberpunk dystopian",
  limit: 10,
  weights: { vectorW: 0.2, textW: 0.8 }
});

// High-precision search with reranking
const precisionResults = await movies.search({
  query: "epic space battles with lightsabers",
  limit: 3,
  reranking: true,
  topNForRerank: 20
});

Pure Vector Search

// Vector similarity only
const vectorResults = await movies.search({
  query: "heroic journey in space",
  limit: 5,
  vectorOnly: true
});

Simplified Functional API Examples

Basic Document Management

import { add, search, remove } from '@brightly/pg-hybrid-search';

// Insert documents
const docIds = await Promise.all([
  add("PostgreSQL is a powerful relational database"),
  add("Vector databases enable semantic search capabilities"),
  add("Full-text search provides keyword-based retrieval")
]);

// Search with hybrid approach (default)
const results = await search({
  query: "database search capabilities",
  limit: 3
});
console.log(`Found ${results.length} relevant documents`);

// Clean up
await Promise.all(docIds.map(id => remove(id)));

Advanced Search Strategies

// Semantic-focused search (higher vector weight)
const semanticResults = await search({
  query: "AI innovation",
  limit: 10,
  weights: { vectorW: 0.9, textW: 0.1 }
});

// Keyword-focused search (higher text weight)
const keywordResults = await search({
  query: "machine learning",
  limit: 10,
  weights: { vectorW: 0.2, textW: 0.8 }
});

// Vector-only search
const vectorResults = await search({
  query: "artificial intelligence",
  vectorOnly: true
});

Enterprise Pipeline with Reranking

import { createClient } from '@brightly/pg-hybrid-search';

async function enterpriseSearch(query: string) {
  const client = createClient();
  const knowledge = client.index('knowledge');
  // High-precision search with AI reranking
  const results = await knowledge.search({
    query,
    limit: 15,
    reranking: true,
    topNForRerank: 100
  });
  return results.map((result, index) => ({
    rank: index + 1,
    id: result.id,
    content: result.raw_content,
    relevanceScore: result.rerank_score ?? result.hybrid_score,
    confidence: result.rerank_score ? 'high' : 'medium'
  }));
}

const enterpriseResults = await enterpriseSearch('sustainable technology solutions');

Batch Operations

import { createClient } from '@brightly/pg-hybrid-search';

// Efficient bulk insertion using Modern Client API
const client = createClient();
const docs = client.index('bulk');

const documents = [
  'Document 1 content...',
  'Document 2 content...',
  'Document 3 content...'
];

const ids = await Promise.all(documents.map(text => docs.add(text, 'english')));
console.log(`Inserted ${ids.length} documents`);

🏗 Database Schema

The library automatically creates and manages the following schema (v0.5.0 Beta):

-- Main table for storing documents and embeddings with multi-index support
CREATE TABLE vector_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  index_name TEXT NOT NULL DEFAULT 'default',
  raw_content TEXT NOT NULL,
  lang TEXT NOT NULL DEFAULT 'simple',
  embedding VECTOR(1536) NOT NULL,
  content_tsv TSVECTOR,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Optimized indexes for performance
CREATE INDEX idx_vector_table_embedding 
  ON vector_table USING ivfflat (embedding vector_cosine_ops) 
  WITH (lists = 100);

CREATE INDEX idx_vector_table_tsv 
  ON vector_table USING GIN (content_tsv);

CREATE INDEX idx_vector_table_index_name
  ON vector_table (index_name);

-- Keep timestamps fresh
CREATE OR REPLACE FUNCTION set_updated_at_pg_hybrid() RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_set_updated_at_pg_hybrid ON vector_table;
CREATE TRIGGER trg_set_updated_at_pg_hybrid BEFORE UPDATE ON vector_table
  FOR EACH ROW EXECUTE FUNCTION set_updated_at_pg_hybrid();

-- Maintain multilingual TSV per-row
CREATE OR REPLACE FUNCTION update_content_tsv_pg_hybrid() RETURNS TRIGGER AS $$
BEGIN
  NEW.content_tsv := to_tsvector(pg_hybrid_safe_regconfig(NEW.lang), coalesce(NEW.raw_content, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_update_content_tsv_pg_hybrid ON vector_table;
CREATE TRIGGER trg_update_content_tsv_pg_hybrid
  BEFORE INSERT OR UPDATE ON vector_table
  FOR EACH ROW EXECUTE FUNCTION update_content_tsv_pg_hybrid();

Schema Features

• UUID Primary Keys: Globally unique identifiers
• Multi-Index Support: Isolated document collections with index_name field
• Vector Storage: 1536-dimensional embeddings (OpenAI standard)
• Multilingual TSVector: Per-row language via lang with GIN index
• Timestamps: Automatic creation and update tracking
• Optimized Indexes: IVFFlat for vectors, GIN for text search, B-tree for index names

Upgrade Guide (pre-0.5.0 → 0.5.0)

If you already have data, run a safe migration:

ALTER TABLE vector_table ADD COLUMN IF NOT EXISTS lang TEXT NOT NULL DEFAULT 'simple';

-- Recreate content_tsv as a regular column if it was generated
DROP INDEX IF EXISTS idx_vector_table_tsv;
ALTER TABLE vector_table DROP COLUMN IF EXISTS content_tsv;
ALTER TABLE vector_table ADD COLUMN content_tsv TSVECTOR;

-- Recreate trigger
-- (Use the function definitions shown in the schema above)

Re-run CLI init (idempotent) to ensure functions/triggers exist:

npx @brightly/pg-hybrid-search init

Query Notes

• Hybrid search computes text score with: websearch_to_tsquery(pg_hybrid_safe_regconfig(lang), $query)
• Provide lang at insert time for better BM25 (e.g., 'english', 'indonesian')

⚡ Performance Optimization

Vector Index Tuning

-- Adjust lists parameter based on your dataset size
-- Rule of thumb: lists = sqrt(total_rows)

-- For datasets < 10K documents
CREATE INDEX CONCURRENTLY idx_vector_small 
  ON vector_table USING ivfflat (embedding vector_cosine_ops) 
  WITH (lists = 50);

-- For datasets > 100K documents  
CREATE INDEX CONCURRENTLY idx_vector_large
  ON vector_table USING ivfflat (embedding vector_cosine_ops) 
  WITH (lists = 500);

Connection Optimization

// The library uses connection pooling by default
// You can access the pool for advanced configuration

import { pool } from '@brightly/pg-hybrid-search';

// Monitor pool status
setInterval(() => {
  console.log(`Active connections: ${pool.totalCount}`);
  console.log(`Idle connections: ${pool.idleCount}`);
}, 30000);

Search Performance Tips

1. Batch Similar Queries: Group related searches to amortize embedding costs
2. Tune Hybrid Weights: Adjust based on your content and query patterns
3. Optimize Rerank Usage: Use topNForRerank between 50-200 for best balance
4. Monitor Query Performance: Use EXPLAIN ANALYZE for slow queries

Debugging

• Set PG_HYBRID_DEBUG=1 or PG_HYBRID_DEBUG_RERANK=1 to print light rerank diagnostics
  • Shows selected rerank model and usage payload when available
  • Useful for tuning topNForRerank and verifying model selection

Example App (Seeding + Isolation)

# Run the bundled example that seeds three indexes (A/B/C)
npm run example

# Shows:
# - Per-index seeding with optional language
# - Isolation checks (searching A won’t return B/C)
# - Rerank demo with topNForRerank=50
# - Hybrid weights customization

🔧 Development

Local Development Setup

# Clone the repository
git clone https://github.com/Brightlyviryaa/pg-hybrid-search.git
cd pg-hybrid-search

# Install dependencies
npm install

# Build the project
npm run build

# Set up test environment
cp .env.example .env
# Edit .env with your database credentials

# Initialize test database
npm run build && node dist/src/cli.js init

Project Structure

pg-hybrid-search/
├── src/
│   ├── cli.ts              # Command-line interface
│   ├── db.ts               # Database connection management
│   ├── embedding.ts        # OpenAI embedding integration  
│   ├── search.ts           # Core search functionality
│   ├── rerank.ts           # Voyage AI reranking
│   ├── index.ts            # Main exports
│   ├── sql/
│   │   ├── init.sql        # Schema initialization
│   │   └── reset.sql       # Schema cleanup
│   └── image/
│       └── PG-HYBRID-SEARCH-LOGO.png
├── dist/                   # Compiled JavaScript
├── package.json
├── tsconfig.json
└── README.md

Building and Testing

# Development build
npm run build

# Test CLI functionality
npm run build && node dist/src/cli.js init

# Test basic operations (requires test database)
node -e "
const { upsertDocument, searchHybrid } = require('./dist/index.js');
upsertDocument('test document').then(id => 
  searchHybrid('test', 1).then(console.log)
);
"

🤝 Contributing

We welcome contributions from the community! Here's how you can help:

Ways to Contribute

• 🐛 Bug Reports: Found an issue? Create an issue
• ✨ Feature Requests: Have an idea? Submit a feature request
• 📝 Documentation: Improve docs, add examples, fix typos
• 🔧 Code: Submit pull requests for bug fixes or new features

Development Workflow

1. Fork the repository
2. Create a feature branch: git checkout -b feature/amazing-feature
3. Commit your changes: git commit -m 'Add amazing feature'
4. Push to your branch: git push origin feature/amazing-feature
5. Submit a Pull Request

Code Standards

• Follow existing TypeScript patterns
• Add tests for new functionality
• Update documentation for API changes
• Ensure backward compatibility when possible

🆘 Support & Community

Need help or want to connect with other users?

• 📖 Documentation: You're reading it! Check the API Reference
• 🐛 Issues: GitHub Issues for bugs and feature requests
• 💬 Discussions: GitHub Discussions for general questions
• 📧 Email: Open an issue for direct support needs

Troubleshooting

Common issues and solutions:

🆕 What's New (v0.5.0 Beta)

• Multilingual BM25 via per-row lang column
  • content_tsv dihitung oleh trigger dengan to_tsvector(pg_hybrid_safe_regconfig(lang), raw_content)
  • Query hybrid memakai websearch_to_tsquery(pg_hybrid_safe_regconfig(lang), query)
• Exposed hybrid weights in search: { weights: { vectorW, textW } }
• Configurable rerank candidate pool: { topNForRerank } (contoh: 50–200)
• Example seeding multi-index + isolasi index (A/B/C): npm run example
• CLI bin diperbaiki untuk npx @brightly/pg-hybrid-search <cmd>

| Issue | Solution | |-------|----------| | pgvector extension not found | Install pgvector: CREATE EXTENSION vector; | | OpenAI API rate limits | Implement request batching and retry logic | | Slow vector searches | Tune IVFFlat index parameters | | Connection pool exhausted | Check for connection leaks, increase pool size |

📊 Benchmarks

Performance characteristics on a standard setup (PostgreSQL 15, 4 CPU cores, 8GB RAM):

| Operation | Documents | Time | Notes | |-----------|-----------|------|-------| | Document insertion | 1,000 | ~2.5s | Including embedding generation | | Vector search | 100K docs | ~50ms | With IVFFlat index | | Hybrid search | 100K docs | ~75ms | Combined vector + text | | Rerank (50 candidates) | - | ~200ms | Voyage API latency |

Benchmarks may vary based on document size, query complexity, and infrastructure.

🗺️ Roadmap

Planned features and improvements:

• [ ] Multi-language Support: Enhanced tokenization for non-English content
• [ ] Custom Embedding Models: Support for Hugging Face and other providers
• [ ] Advanced Filtering: Metadata-based search filtering
• [ ] Batch Reranking: Optimize multiple query reranking
• [ ] Performance Monitoring: Built-in metrics and observability
• [ ] Migration Tools: Version upgrade utilities

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

MIT License

Copyright (c) 2024 Brightly Virya

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

🌟 If this project helps you, please give it a star!

Built with ❤️ for the Indonesian AI & Web3 community

Empowering developers to build intelligent search experiences