Hybrid Search in PostgreSQL | VectorChord Setup

# Hybrid Search on PostgreSQL and VectorChord: Infrastructure Setup

Hybrid search, combining vector and full-text methods, is becoming the standard for modern search systems. In this article, we'll dive into setting up the infrastructure based on PostgreSQL and VectorChord — a solution that simplifies hybrid search implementation without the need for manual extension installation and complex configuration.

Environment Setup: Docker and PostgreSQL

To get started, we'll need a containerized environment. We'll use Docker Compose to deploy PostgreSQL with pre-installed VectorChord extensions. Note: in the configuration, we don't specify the extensions explicitly — they will be added automatically on the first connection via VechordRegistry.

Example docker-compose-dev.yml file:

services:
  postgres:
    image: tensorchord/vchord-suite:pg18-latest
    environment:
      POSTGRES_DB: ${DB__NAME}
      POSTGRES_USER: ${DB__USER}
      POSTGRES_PASSWORD: ${DB__PASSWORD}
    volumes:
      - pgdata:/var/lib/postgresql
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${DB__USER} -d ${DB__NAME}"]
      interval: 5s
      timeout: 5s
      retries: 5
volumes:
  pgdata:

Key point: the tensorchord/vchord-suite image already contains all necessary components, but extensions are activated only on the first access via VechordRegistry. This avoids manual setup and ensures version compatibility.

Designing Tables for Hybrid Search

The data structure is built on three tables. The base table BaseTable contains common fields:

from datetime import datetime, timezone
from functools import partial
import msgspec
from psycopg.types.json import Jsonb
from vechord import Table

class BaseTable(Table, kw_only=True):
    metadata: Jsonb
    created_at: datetime = msgspec.field(
        default_factory=partial(datetime.now, timezone.utc))
    updated_at: datetime = msgspec.field(
        default_factory=partial(datetime.now, timezone.utc))

The metadata field stores arbitrary data in JSONB format, and timestamps are updated automatically upon record creation. Note: updated_at is not updated automatically on record changes — this must be done manually in update methods.

The Document table:

import msgspec
from vechord.spec import PrimaryKeyUUID
from .base import BaseTable

class Document(BaseTable, kw_only=True):
    uid: PrimaryKeyUUID = msgspec.field(default_factory=PrimaryKeyUUID.factory)
    title: str
    text: str

UUID is used for the primary key. The Chunk table is linked to the document via a foreign key:

from vechord.spec import ForeignKey, Keyword, PrimaryKeyUUID, Vector

class Chunk(BaseTable, kw_only=True):
    uid: PrimaryKeyUUID = msgspec.field(default_factory=PrimaryKeyUUID.factory)
    doc_id: Annotated[UUID, ForeignKey[Document.uid]]
    content: str
    content_tsv: Keyword
    embedding: DenseVector
    chunk_index: int

Here, content_tsv is intended for full-text search (BM25), and embedding stores the vector representation of the chunk. The foreign key doc_id automatically implements cascade deletion.

Important: attempting to move uid to the base class causes an error, as ForeignKey starts looking for the field in BaseTable rather than the specific table. Therefore, the primary key is duplicated in each table.

VechordRegistry: Centralized Database Management

The VechordRegistry class serves as a single interface for working with the database. It automates:

• Extension installation (vchord, vchord_bm25, pg_tokenizer)
• Table and tokenizer creation
• CRUD operations and search

Initialization:

from vechord import VechordRegistry
from core import settings
from .document import Document
from .doc_chunk import Chunk

vr = VechordRegistry(
    namespace=settings.db.namespace,
    url=settings.db.db_url,
    tables=[Document, Chunk]
)

On first use (in the async with vr: context), the init_extension method runs, installing extensions and configuring search_path. This eliminates the need for manual setup and minimizes configuration errors.

Example usage for inserting a document:

async with vr:
    doc = Document(title='Note', text='Some text', metadata=Jsonb({}))
    await vr.insert(doc)

After running this code, all necessary extensions, tables, and the record will appear in the DB.

Data Processing Pipelines

For complex operations like creating a document followed by chunking, VechordPipeline is used. It allows combining multiple steps into a pipeline, where each stage automatically saves results to the DB.

Step 1: Document creation. The @vr.inject(output=Document) decorator ensures object persistence:

@vr.inject(output=Document)
async def _create_document(doc_data: DocumentCreate) -> Document:
    doc = Document(
        title=doc_data.title,
        text=doc_data.text,
        metadata=Jsonb(doc_data.metadata)
    )
    return doc

Step 2: Chunk generation. Takes data from the Document table and returns a list of chunks:

@vr.inject(input=Document, output=Chunk)
async def create_chunks(uid: UUID, text: str) -> list[Chunk]:
    chunks = await _chunker.segment(text)
    return [
        Chunk(
            doc_id=uid,
            content=chunk,
            content_tsv=Keyword(chunk),
            embedding=DenseVector(await _embedder.vectorize_chunk(chunk)),
            metadata=Jsonb({}),
            chunk_index=i
        )
        for i, chunk in enumerate(chunks, start=1)
    ]

Pipeline assembly:

from db_models import vr
from .utils import create_chunks, create_document

class DocumentService:
    async def create_document(self, doc_data: DocumentCreate):
        pipeline = vr.pipeline(
            _create_document,
            create_chunks
        )
        await pipeline(doc_data)

This approach ensures operation atomicity and minimizes DB accesses.

Key Points: Essential Implementation Aspects

When implementing hybrid search based on VectorChord, pay attention to:

• Automatic extension initialization: VechordRegistry installs all necessary components on first connection, simplifying deployment.
• Table structure: separating documents and chunks is critical for efficient search; foreign keys ensure data integrity.
• Processing pipelines: using VechordPipeline encapsulates complex operations and ensures data consistency.

These elements form the foundation for a scalable search system combining the precision of vector search and the flexibility of full-text search.

— Editorial Team