DEV Community: Ankit Arora

Hybrid Schematic Search in PostgreSQL with Full-Text and Vector Similarity

Ankit Arora — Sun, 04 Oct 2026 16:05:14 +0000

The Search Problem We're Really Solving

If you're building any modern AI application—whether it's a RAG (Retrieval-Augmented Generation) pipeline, a semantic search engine, or an intelligent document retrieval system—you've likely encountered a fundamental tension:

Vector similarity search understands meaning but can miss exact keyword matches.

Full-text search catches exact terms but fails to understand semantic relationships.

What if you could have both?

This is the promise of hybrid search: combining the semantic understanding of vector embeddings with the precision of traditional full-text search, all within a single PostgreSQL database using the pgvector extension.

In this comprehensive guide, we'll build a working hybrid search system from scratch, analyze its performance characteristics, and understand exactly why it works—and when you should consider implementing it in your own AI engineering projects.

Why Hybrid Search Matters for AI Applications
Understanding the Two Pillars of Search
Setting Up Your PostgreSQL Environment
Building the Dataset: From Random Text to Embeddings
Indexing Strategy: GIN and HNSW Explained
Implementing Vector Search
Implementing Full-Text Search
The Magic of Reciprocal Rank Fusion (RRF)
Constructing the Hybrid Search Query
Performance Analysis with EXPLAIN ANALYZE
Production Considerations and Trade-offs
Next Steps and Future Research

1. Why Hybrid Search Matters for AI Applications {#why-hybrid-search-matters}

The RAG Revolution and Its Retrieval Problem

Retrieval-Augmented Generation has become the dominant paradigm for building AI applications that need to access external knowledge. The pattern is deceptively simple:

User asks a question
System retrieves relevant documents
LLM generates an answer using retrieved context

The quality of step 2—retrieval—often determines the entire system's success. And here's the uncomfortable truth: most RAG implementations rely solely on vector similarity search, which has significant blind spots.

When Vector Search Fails

Consider these scenarios where pure vector search disappoints:

Scenario	Vector Search Behavior	Problem
Product codes ("SKU-12345")	Treats as semantic tokens	May miss exact matches
Technical terminology	Averages meaning across context	Loses specificity
Proper nouns	Depends on training data	Inconsistent results
Rare phrases	Embedding space may be sparse	Poor discrimination

When Full-Text Search Fails

Traditional full-text search has complementary weaknesses:

Scenario	Full-Text Search Behavior	Problem
Synonyms ("car" vs "automobile")	No match without thesaurus	Misses relevant docs
Conceptual queries	Requires exact terms	Poor recall
Natural language questions	Word-by-word matching	Ignores intent
Multilingual content	Dictionary-dependent	Inconsistent coverage

The Hybrid Search Solution

Hybrid search combines both approaches, using techniques like Reciprocal Rank Fusion (RRF) to merge results intelligently. The result: better recall, better precision, and more robust retrieval across diverse query types.

2. Understanding the Two Pillars of Search {#understanding-the-two-pillars}

Before diving into implementation, let's establish a clear mental model of what each search method actually does.

Vector Similarity Search

Vector search converts text into high-dimensional embeddings—numerical representations that capture semantic meaning. Similar meanings produce similar vectors, enabling:

Semantic matching: "happy" finds "joyful"
Conceptual search: "how to fix a leak" finds "plumbing repair guide"
Cross-lingual retrieval: With multilingual models, "hello" finds "hola"

The similarity is typically measured using cosine distance, where smaller values indicate greater similarity.

cosine_distance = 1 - cosine_similarity

Full-Text Search

PostgreSQL's full-text search uses:

Tokenization: Breaking text into words
Normalization: Stemming ("running" → "run"), lowercasing
Stop word removal: Eliminating common words ("the", "a", "is")
tsvector creation: A sorted list of normalized tokens with positions
tsquery matching: Boolean operations on search terms

The result is a lexeme-based index that enables fast, precise keyword matching.

The Complementarity Principle

Here's the key insight: vector search and full-text search fail in different ways. When you combine them, failures in one method can be compensated by successes in the other.

                    ┌─────────────────┐
                    │   User Query    │
                    └────────┬────────┘
                             │
              ┌──────────────┴──────────────┐
              │                             │
              ▼                             ▼
    ┌─────────────────┐           ┌─────────────────┐
    │  Vector Search  │           │ Full-Text Search│
    │   (Semantic)    │           │   (Lexical)     │
    └────────┬────────┘           └────────┬────────┘
             │                             │
             │    ┌─────────────────┐      │
             └───►│  RRF Fusion     │◄─────┘
                  │  (Combining)    │
                  └────────┬────────┘
                           │
                           ▼
                  ┌─────────────────┐
                  │ Ranked Results  │
                  └─────────────────┘

3. Setting Up Your PostgreSQL Environment {#setting-up-postgresql}

Requirements

To follow along, you'll need:

PostgreSQL (version 14+ recommended)
pgvector extension (v0.5+, I'll use v0.7.4)
Python 3.8+ with these packages:
- psycopg (PostgreSQL adapter)
- pgvector (Python integration)
- faker (test data generation)
- sentence_transformers (embedding generation)

Installation

# Install pgvector (varies by platform)
# macOS with Homebrew:
brew install pgvector

# Ubuntu/Debian:
sudo apt install postgresql-15-pgvector

# From source:
git clone https://github.com/pgvector/pgvector.git
cd pgvector && make && make install

# Python dependencies
pip install psycopg[binary] pgvector faker sentence-transformers

Database Schema

Let's create our schema with careful attention to production-readiness:

-- Enable the vector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create the products table
CREATE TABLE products (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    description text NOT NULL,
    embedding vector(384) NOT NULL
);

-- Create a helper function for RRF scoring
-- This will be used in our hybrid search query
CREATE OR REPLACE FUNCTION rrf_score(rank int, rrf_k int DEFAULT 50)
RETURNS numeric
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
    SELECT COALESCE(1.0 / ($1 + $2), 0.0);
$$;

Why 384 dimensions? The multi-qa-MiniLM-L6-cos-v1 model produces 384-dimensional embeddings. This is a deliberate choice balancing:

Expressiveness: Enough dimensions to capture semantic nuance
Storage efficiency: Smaller than 768 or 1536-dimensional alternatives
Query speed: Faster distance calculations

4. Building the Dataset: From Random Text to Embeddings {#building-the-dataset}

The Data Generation Strategy

For this demonstration, we'll generate synthetic data using Faker and encode it with a sentence transformer. While the data is artificial, the methodology is production-ready.

from faker import Faker
import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

# Initialize Faker for synthetic data
fake = Faker()

# Generate 50,000 random sentences (50 words each)
# This simulates a product description corpus
sentences = [fake.sentence(nb_words=50) for _ in range(50_000)]

print(f"Generated {len(sentences)} sentences")
print(f"Sample: {sentences[0][:100]}...")

Generating Embeddings

# Load the sentence transformer model
# multi-qa-MiniLM-L6-cos-v1 is optimized for question-answering retrieval
model = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1')

# Generate embeddings for all sentences
# This may take several minutes depending on your hardware
print("Generating embeddings...")
embeddings = model.encode(sentences, show_progress_bar=True)
print(f"Embedding shape: {embeddings.shape}")  # Should be (50000, 384)

Loading Data into PostgreSQL

# Connect to your database
# Replace with your actual connection details
conn = psycopg.connect(
    dbname="your_database",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432",
    autocommit=True
)

# Register the vector type with psycopg
register_vector(conn)

cur = conn.cursor()

# Use COPY for efficient bulk loading
with cur.copy("COPY products (description, embedding) FROM STDIN WITH (FORMAT BINARY)") as copy:
    copy.set_types(["text", "vector"])
    for content, embedding in zip(sentences, embeddings):
        copy.write_row((content, embedding))

print("Data loaded successfully!")
cur.close()
conn.close()

Performance Tip: The COPY command is orders of magnitude faster than individual INSERT statements for bulk loading. For 50,000 rows, this approach typically completes in seconds rather than minutes.

5. Indexing Strategy: GIN and HNSW Explained {#indexing-strategy}

Creating the Indexes

-- Full-text search index using GIN (Generalized Inverted Index)
CREATE INDEX products_description_gin_idx ON products
    USING GIN (to_tsvector('english', description));

-- Vector search index using HNSW (Hierarchical Navigable Small World)
CREATE INDEX products_embeddings_hnsw_idx ON products
    USING hnsw(embedding vector_cosine_ops) WITH (ef_construction=256);

Deep Dive: The GIN Index

The GIN index on to_tsvector('english', description) deserves careful explanation:

Expression Index: We're not indexing the raw description column—we're indexing the output of to_tsvector(). This means:

Storage efficiency: We don't need a separate tsvector column
Automatic consistency: The index is always in sync with the text
Query optimization: Queries using the same expression can use the index

Why 'english'? PostgreSQL requires immutable functions in expression indexes. Since to_tsvector() with a dictionary argument is immutable (the dictionary is fixed), we must specify it explicitly. This ensures:

Consistent tokenization across index builds and queries
Reproducible results
No surprises from session-level configuration changes

Deep Dive: The HNSW Index

HNSW (Hierarchical Navigable Small World) is a graph-based algorithm for approximate nearest neighbor search:

Layer 3 (coarsest):    A ─────────────────── B
                       │                     │
Layer 2:          C ───┼─── D ─── E ──────── F
                  │    │    │     │         │
Layer 1:     G ───┼────┼────┼─────┼────H────┼─── I
             │    │    │    │     │    │    │   │
Layer 0 (finest): All vectors connected to nearest neighbors

Key parameters:

Parameter	Default	Our Setting	Trade-off
`m`	16	16	Higher = better recall, more memory
`ef_construction`	64	256	Higher = better index quality, slower build
`ef_search`	40	40	Higher = better recall, slower queries

Why ef_construction=256? This increases the quality of the graph structure during index construction. The trade-off is longer build time, but better query performance and recall.

6. Implementing Vector Search {#implementing-vector-search}

Generating a Query Embedding

from sentence_transformers import SentenceTransformer

model = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1')
query_embedding = model.encode('travel computer')

The Vector Search Query

SELECT 
    id, 
    description, 
    rank() OVER (ORDER BY $1 <=> embedding) AS rank
FROM products
ORDER BY $1 <=> embedding
LIMIT 10;

Understanding the operators:

<=>: Cosine distance operator (0 = identical, 2 = opposite)
rank() OVER (ORDER BY ...): Window function assigning rank based on distance
$1: Parameterized query placeholder for the embedding vector

Sample Results

  id   | description             | rank 
-------+-------------------------+-------
 10578 | ... travel ... computer |    1
 20763 | ... computer ...        |    2
 20894 | ... computer ...        |    3
   838 | Computer ...            |    4
 11045 | ...computer ...         |    5
 18548 | ... travel computer ... |    6  ← Should be higher!
 16564 | ... computer ...        |    7
 20402 | ...computer ...         |    8
 10346 | ... computer ...        |    9
 11243 | ... travel ... computer |   10

Observation: Record 18548 contains the exact phrase "travel computer" but ranks only 6th. This is the fundamental limitation of pure vector search—it prioritizes overall semantic similarity over exact phrase matching.

7. Implementing Full-Text Search {#implementing-full-text-search}

The Full-Text Search Query

SELECT
    id,
    description,
    rank() OVER (
        ORDER BY ts_rank_cd(
            to_tsvector(description), 
            plainto_tsquery('travel computer')
        ) DESC
    ) AS rank
FROM products
WHERE
    plainto_tsquery('english', 'travel computer') @@ 
    to_tsvector('english', description)
ORDER BY rank
LIMIT 10;

Deconstructing the Query

plainto_tsquery('english', 'travel computer'): Converts plain text to a tsquery
- Result: 'travel' & 'comput' (stemmed, AND-connected)
to_tsvector('english', description): Converts document to searchable form
- Result: Sorted array of stemmed tokens with positions
@@ operator: Tests if tsquery matches tsvector
ts_rank_cd(): Cover density ranking
- Considers proximity of search terms
- Higher scores for terms appearing close together

Sample Results

  id   | description                 | rank 
-------+-----------------------------+------
 18548 | ... travel computer ...     |    1  ← Correct!
  7372 | ... travel computer ...     |    1
 49374 | ... travel computer ...     |    1
 39214 | ... travel computer ...     |    1
 12875 | ... computer travel ...     |    1
  3712 | ... travel computer ...     |    1
 24719 | ... travel ... computer ... |    7  ← Terms far apart
 31607 | ... travel ... computer ... |    7
 13674 | ... travel ... computer ... |    7
 42755 | ... computer ... travel ... |    7

Observation: Full-text search correctly identifies 18548 as a top result, but it returns many results with identical ranks. It lacks the ability to distinguish overall semantic relevance.

8. The Magic of Reciprocal Rank Fusion (RRF) {#rrf-explained}

What is RRF?

Reciprocal Rank Fusion is a rank aggregation method that combines multiple ranked lists into a single ranking. It was introduced by Cormack et al. in 2009 and has become a standard technique in information retrieval.

The Formula

RRF_score(d) = Σ (1 / (k + rank_i(d)))

Where:

d = document
k = smoothing constant (typically 50-60)
rank_i(d) = rank of document d in result list i

Why RRF Works

Scale-independent: Combines rankings, not raw scores
- Vector distances and ts_rank scores have different scales
- Rankings are universally comparable
Robust: Outliers in one list don't dominate
- A single #1 ranking contributes ~0.02
- Multiple high rankings compound
Simple: No training required
- Unlike learning-to-rank approaches
- Deterministic and explainable

The PostgreSQL Implementation

CREATE OR REPLACE FUNCTION rrf_score(rank int, rrf_k int DEFAULT 50)
RETURNS numeric
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
    SELECT COALESCE(1.0 / ($1 + $2), 0.0);
$$;

Why COALESCE? This handles NULL ranks gracefully. If a document appears in only one result list, its "missing" rank is treated as contributing 0 to the sum.

Why IMMUTABLE PARALLEL SAFE?

IMMUTABLE: Same inputs always produce same output (required for index expressions)
PARALLEL SAFE: Can be executed in parallel workers

Score Distribution Analysis

For k=50:

Rank	Score	Contribution
1	1/51	0.0196
2	1/52	0.0192
5	1/55	0.0182
10	1/60	0.0167
40	1/90	0.0111

Key insight: The difference between rank 1 and rank 40 is only about 2x. This means appearing in both lists is more valuable than ranking #1 in just one.

9. Constructing the Hybrid Search Query {#constructing-hybrid-query}

The Complete Query

SELECT
    searches.id,
    searches.description,
    sum(rrf_score(searches.rank)) AS score
FROM (
    -- Vector search subquery
    (
        SELECT
            id,
            description,
            rank() OVER (ORDER BY $1 <=> embedding) AS rank
        FROM products
        ORDER BY $1 <=> embedding
        LIMIT 40
    )
    UNION ALL
    -- Full-text search subquery
    (
        SELECT
            id,
            description,
            rank() OVER (
                ORDER BY ts_rank_cd(
                    to_tsvector(description), 
                    plainto_tsquery('travel computer')
                ) DESC
            ) AS rank
        FROM products
        WHERE
            plainto_tsquery('english', 'travel computer') @@ 
            to_tsvector('english', description)
        ORDER BY rank
        LIMIT 40
    )
) searches
GROUP BY searches.id, searches.description
ORDER BY score DESC
LIMIT 10;

Why 40 Results Per Subquery?

The choice of 40 is strategic:

Default hnsw.ef_search: PostgreSQL's HNSW index defaults to searching 40 candidates
Overlap potential: With 10 final results desired, 40 provides 4x buffer
Performance balance: More results = better fusion but slower queries

Query Execution Flow

┌─────────────────────────────────────────────────────────────┐
│                    Hybrid Search Query                       │
└─────────────────────────────────────────────────────────────┘
                              │
        ┌─────────────────────┴─────────────────────┐
        │                                           │
        ▼                                           ▼
┌───────────────────┐                   ┌───────────────────┐
│  Vector Search    │                   │  Full-Text Search │
│  (HNSW Index)     │                   │  (GIN Index)      │
│                   │                   │                   │
│  Returns 40 rows  │                   │  Returns 40 rows  │
│  with ranks 1-40  │                   │  with ranks 1-40  │
└─────────┬─────────┘                   └─────────┬─────────┘
          │                                       │
          └───────────────┬───────────────────────┘
                          │
                          ▼
              ┌───────────────────────┐
              │    UNION ALL          │
              │  (80 rows total)      │
              └───────────┬───────────┘
                          │
                          ▼
              ┌───────────────────────┐
              │   GROUP BY id         │
              │   SUM(rrf_score(rank))│
              └───────────┬───────────┘
                          │
                          ▼
              ┌───────────────────────┐
              │   ORDER BY score DESC │
              │   LIMIT 10            │
              └───────────────────────┘

Sample Hybrid Results

  id   | description                 |         score          
-------+-----------------------------+------------------------
 18548 | ... travel computer ...     | 0.03746498599439775910  ← Top!
  7372 | ... travel computer ...     | 0.01960784313725490196
 12875 | ... computer travel ...     | 0.01960784313725490196
 10578 | ... travel ... computer ... | 0.01960784313725490196
 39214 | ... travel computer ...     | 0.01960784313725490196
 49374 | ... travel computer ...     | 0.01960784313725490196
  3712 | ... travel computer ...     | 0.01960784313725490196
 20763 | ... computer ...            | 0.01923076923076923077
 20894 | ... computer ...            | 0.01886792452830188679
   838 | Computer ...                | 0.01851851851851851852

Analysis of Results

Record 18548 (the "correct" answer):

Appeared in both result lists
Vector rank: 6 → RRF contribution: 1/(50+6) = 0.0179
FTS rank: 1 → RRF contribution: 1/(50+1) = 0.0196
Total: 0.0375

Records 7372, 12875, etc.:

Appeared in FTS with rank 1 (or tied)
Appeared in vector search with lower ranks
Combined score: ~0.0196

Records 20763, 20894, 838:

Appeared only in vector search (high ranks)
No FTS match
Score: ~0.019

The key insight: Record 18548's appearance in both lists with strong rankings boosted it to the top, validating the hybrid approach.

10. Performance Analysis with EXPLAIN ANALYZE {#performance-analysis}

The Execution Plan

EXPLAIN ANALYZE
SELECT ...;  -- Our hybrid search query

Key Plan Output

Limit  (cost=789.66..789.69 rows=10 width=365) (actual time=8.516..8.519 rows=10 loops=1)
  ->  Sort  (cost=789.66..789.86 rows=80 width=365) (actual time=8.515..8.518 rows=10 loops=1)
        Sort Key: (sum(COALESCE((1.0 / (("*SELECT* 1".rank + 50))::numeric), 0.0))) DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  GroupAggregate  (cost=785.53..787.93 rows=80 width=365) (actual time=8.435..8.495 rows=79 loops=1)
              Group Key: "*SELECT* 1".id, "*SELECT* 1".description
              ->  Sort  (cost=785.53..785.73 rows=80 width=341) (actual time=8.430..8.436 rows=80 loops=1)
                    ->  Append  (cost=84.60..783.00 rows=80 width=341) (actual time=0.877..8.414 rows=80 loops=1)
                          ->  Subquery Scan on "*SELECT* 1"  
                                ->  Limit  
                                      ->  WindowAgg  
                                            ->  Index Scan using products_embeddings_hnsw_idx on products  
                                                  Order By: (embedding <=> '<redacted>'::vector)
                          ->  Subquery Scan on "*SELECT* 2"  
                                ->  Limit  
                                      ->  Sort  
                                            ->  WindowAgg  
                                                  ->  Sort  
                                                        ->  Bitmap Heap Scan on products products_1  
                                                              Recheck Cond: ('''travel'' & ''comput'''::tsquery @@ ...)
                                                              ->  Bitmap Index Scan on products_description_gin_idx  
                                                                    Index Cond: (to_tsvector('english'::regconfig, description) @@ ...)

Planning Time: 0.193 ms
Execution Time: 8.553 ms

Performance Breakdown

Component	Time	Notes
Vector search (HNSW)	~0.9ms	Extremely fast with index
Full-text search (GIN)	~7.3ms	Bitmap heap scan overhead
Sort + Group + Aggregate	~1.1ms	Small result set
Total	8.5ms	Excellent for 50K rows

Index Usage Confirmation

The plan confirms both indexes are utilized:

Index Scan using products_embeddings_hnsw_idx — HNSW vector index
Bitmap Index Scan on products_description_gin_idx — GIN full-text index

Scaling Considerations

For production workloads with millions of rows:

Factor	Impact	Mitigation
HNSW build time	Increases linearly	Build offline, use `maintenance_work_mem`
GIN index size	~30% of text size	Consider partial indexes
Query latency	Sub-linear with HNSW	Tune `ef_search`
Memory	HNSW graph in RAM	Monitor `shared_buffers`

11. Production Considerations and Trade-offs {#production-considerations}

When to Use Hybrid Search

Use Case	Recommendation
RAG pipelines	✅ Strongly recommended
E-commerce search	✅ Recommended
Document retrieval	✅ Recommended
Real-time autocomplete	⚠️ Consider latency
Simple keyword search	❌ Overkill

Tuning Parameters

HNSW Parameters

-- Query-time parameter (higher = better recall, slower)
SET hnsw.ef_search = 100;

-- Index-time parameters (require rebuild)
-- m: connections per node (default 16)
-- ef_construction: candidate list size during build (default 64)

Full-Text Search Parameters

-- Use different dictionaries
to_tsvector('simple', description)  -- No stemming
to_tsvector('english', description) -- English stemming

-- Custom dictionaries for domain-specific terms
CREATE TEXT SEARCH DICTIONARY custom_dict (...);

RRF Parameters

-- k=50 (default): Balanced
-- k=10: Favor top-ranked results more
-- k=100: Flatter score distribution

SELECT sum(rrf_score(rank, 10)) AS score  -- More aggressive

Memory and Storage

Component	Storage (50K rows)	Storage (1M rows)
Raw text	~5MB	~100MB
Vector embeddings (384d)	~75MB	~1.5GB
HNSW index	~100MB	~2GB
GIN index	~2MB	~40MB

Hybrid Search vs. Alternatives

Approach	Pros	Cons
Hybrid (RRF)	Simple, effective, no training	Requires tuning
Learning to Rank	Optimal if trained well	Needs labeled data
Weighted Sum	Simple	Requires score normalization
Cascade	Fast	May miss results

12. Next Steps and Future Research {#next-steps}

Immediate Improvements

Add reranking: Use a cross-encoder model to rerank top results
Query expansion: Expand queries with synonyms before search
Metadata filtering: Combine with structured filters
Caching: Cache frequent query embeddings

Research Directions

Benchmark on ground-truth datasets: Measure recall@k improvements
Compare FTS algorithms: ts_rank vs ts_rank_cd vs custom
Optimize RRF parameters: Grid search for optimal k
Multi-vector approaches: ColBERT-style late interaction

Production Deployment Checklist

□ Set up connection pooling (PgBouncer)
□ Configure maintenance_work_mem for index builds
□ Set up monitoring for query latency
□ Implement query result caching
□ Create partial indexes for common filters
□ Set up replication for read scaling
□ Document tuning parameters
□ Create runbooks for common issues

Complete Python Implementation

import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

class HybridSearch:
    def __init__(self, connection_string: str, model_name: str = 'multi-qa-MiniLM-L6-cos-v1'):
        self.conn = psycopg.connect(connection_string)
        register_vector(self.conn)
        self.model = SentenceTransformer(model_name)

    def search(self, query: str, limit: int = 10, subquery_limit: int = 40) -> list[dict]:
        # Generate query embedding
        embedding = self.model.encode(query)

        # Execute hybrid search
        with self.conn.cursor() as cur:
            cur.execute("""
                SELECT
                    searches.id,
                    searches.description,
                    sum(rrf_score(searches.rank)) AS score
                FROM (
                    (
                        SELECT id, description,
                               rank() OVER (ORDER BY %s <=> embedding) AS rank
                        FROM products
                        ORDER BY %s <=> embedding
                        LIMIT %s
                    )
                    UNION ALL
                    (
                        SELECT id, description,
                               rank() OVER (
                                   ORDER BY ts_rank_cd(
                                       to_tsvector(description),
                                       plainto_tsquery(%s)
                                   ) DESC
                               ) AS rank
                        FROM products
                        WHERE plainto_tsquery('english', %s) @@ 
                              to_tsvector('english', description)
                        ORDER BY rank
                        LIMIT %s
                    )
                ) searches
                GROUP BY searches.id, searches.description
                ORDER BY score DESC
                LIMIT %s
            """, (embedding, embedding, subquery_limit, query, query, subquery_limit, limit))

            results = cur.fetchall()

        return [
            {"id": r[0], "description": r[1], "score": float(r[2])}
            for r in results
        ]

    def close(self):
        self.conn.close()


# Usage
searcher = HybridSearch("postgresql://user:pass@localhost/dbname")
results = searcher.search("travel computer")
for r in results:
    print(f"[{r['score']:.4f}] {r['description'][:80]}...")
searcher.close()

Conclusion: The Hybrid Advantage

Hybrid search represents a pragmatic evolution in retrieval systems. By combining:

Vector search's semantic understanding with
Full-text search's lexical precision using
Reciprocal Rank Fusion's elegant aggregation

...we achieve retrieval quality that exceeds either method alone.

The PostgreSQL implementation demonstrated here is:

Production-ready: Uses battle-tested indexes and query patterns
Performant: Sub-10ms queries on 50K rows
Scalable: HNSW and GIN indexes handle millions of rows
Maintainable: Single database, no external services
Cost-effective: No vector database licensing fees

As RAG systems become more prevalent, hybrid search will transition from "nice to have" to "table stakes" for serious AI applications. The techniques shown here provide a solid foundation for building these systems on PostgreSQL—a database you likely already know and trust.

Resources

Building a ~10M-Parameter GPT-Style Language Model on Simple Home Computer

Ankit Arora — Sat, 01 Aug 2026 21:09:39 +0000

A Complete Guide with Code Examples

Generative AI has transformed the way we build intelligent applications. Large Language Models (LLMs) such as GPT, Llama, and Gemma can write code, answer questions, summarize documents, and even reason through complex problems. While using these existing models is straightforward, understanding how they actually work is a different challenge.

Many engineers begin by fine-tuning existing models or using frameworks like Hugging Face. While these tools are incredibly valuable, they often hide the mechanics that make language models possible. Building a small GPT-style model from scratch is one of the best ways to understand the underlying concepts.

This article walks through the complete process of building a miniature Generative AI model using only PyTorch. By the end, you'll understand every major component of a Transformer-based language model and have a solid foundation for exploring larger architectures.

Although the model we'll build contains around 10 million parameters—tiny compared to today's billion-parameter models—it contains the same fundamental building blocks used in modern LLMs.

What You'll Build

Our goal is to create a character-level GPT model capable of learning patterns from text and generating new text one token at a time.

The journey consists of five major stages:

Build a tokenizer
Create a dataset for next-token prediction
Implement the Transformer architecture
Train the model
Generate text

Each stage builds on the previous one, gradually transforming raw text into an intelligent language model.

Setting Up Your Environment

First, prepare your Mac for machine learning:

# Install Xcode Command Line Tools
xcode-select --install

# Create project directory
mkdir my-gpt-from-scratch && cd my-gpt-from-scratch

# Create virtual environment
python3 -m venv .venv
source .venv/bin/activate

# Install dependencies
pip install torch numpy tqdm

Verify PyTorch can use your Mac's GPU:

import torch
print(f"MPS available: {torch.backends.mps.is_available()}")
print(f"MPS built: {torch.backends.mps.is_built()}")
# Should output: True, True

Project Structure

my-gpt-from-scratch/
│
├── tokenizer.py      # Character-level tokenizer
├── data.py           # Dataset and batching
├── model.py          # GPT architecture
├── train.py          # Training loop
├── generate.py       # Text generation
├── config.py         # Hyperparameters
├── utils.py          # Helper functions
└── data/
    └── input.txt     # Training corpus

Stage 1: Teaching the Computer to Read

Before a neural network can understand language, it must convert text into numbers. This process is called tokenization. For simplicity, we'll use a character-level tokenizer, where every unique character receives a unique integer.

# tokenizer.py

class CharTokenizer:
    def __init__(self, text):
        chars = sorted(list(set(text)))

        self.stoi = {ch:i for i,ch in enumerate(chars)}
        self.itos = {i:ch for ch,i in self.stoi.items()}

        self.vocab_size = len(chars)

    def encode(self, text):
        """Convert text to token IDs"""
        return [self.stoi[c] for c in text]

    def decode(self, ids):
        """Convert token IDs back to text"""
        return ''.join(self.itos[i] for i in ids)

Example Usage:

text = "hello world"
tok = CharTokenizer(text)

ids = tok.encode("hello")
print(ids)  # [1, 0, 2, 2, 3]

print(tok.decode(ids))  # hello

Stage 2: Creating Training Examples

Language models learn a surprisingly simple task: predict the next token.

Consider the sentence: "Machine learning is amazing"

During training, the model repeatedly sees examples like:

Input	Target
Machine	learning
Machine learning	is
Machine learning is	amazing

Instead of feeding the entire dataset every iteration, we'll create small chunks called contexts (or sequences).

# data.py

import torch

class TextDataset:
    def __init__(self, data, block_size):
        """
        data: list of token IDs
        block_size: context length (e.g., 256)
        """
        self.data = torch.tensor(data, dtype=torch.long)
        self.block_size = block_size

    def get_batch(self, batch_size):
        """Get a batch of input-target pairs"""
        # Random starting positions
        ix = torch.randint(
            len(self.data) - self.block_size - 1,
            (batch_size,)
        )

        # Input sequences
        x = torch.stack([
            self.data[i:i+self.block_size]
            for i in ix
        ])

        # Target sequences (shifted by 1)
        y = torch.stack([
            self.data[i+1:i+self.block_size+1]
            for i in ix
        ])

        return x, y

Stage 3: Building the Transformer

Modern language models are built using the Transformer architecture, introduced in the landmark paper "Attention Is All You Need."

Step 3.1: Token Embedding

Neural networks cannot operate directly on integer IDs. Instead, every token is mapped to a dense vector called an embedding.

import torch.nn as nn

# In your model class:
token_embedding = nn.Embedding(
    vocab_size,     # Number of unique tokens
    embedding_dim   # Size of each embedding vector (e.g., 384)
)

Step 3.2: Positional Embedding

Unlike humans, Transformers have no built-in understanding of sequence order. Without positional information, "dog bites man" and "man bites dog" appear identical.

# Positional embedding
position_embedding = nn.Embedding(
    block_size,     # Maximum sequence length
    embedding_dim   # Same as token embedding
)

# In forward pass:
B, T = x.shape  # Batch size, Sequence length

tok_emb = token_embedding(x)  # Shape: (B, T, n_embed)

pos = position_embedding(
    torch.arange(T, device=x.device)
)  # Shape: (T, n_embed)

x = tok_emb + pos  # Add position information

Step 3.3: Single-Head Self-Attention

Self-attention allows the model to weigh the importance of different tokens. Each token asks: "Which previous tokens should I pay attention to?"

class Head(nn.Module):
    def __init__(self, n_embed, head_size, block_size):
        super().__init__()

        self.key = nn.Linear(n_embed, head_size, bias=False)
        self.query = nn.Linear(n_embed, head_size, bias=False)
        self.value = nn.Linear(n_embed, head_size, bias=False)

        # Causal mask (prevents looking at future tokens)
        self.register_buffer(
            "tril",
            torch.tril(torch.ones(block_size, block_size))
        )

    def forward(self, x):
        B, T, C = x.shape

        # Compute key, query, value
        k = self.key(x)    # (B, T, head_size)
        q = self.query(x)  # (B, T, head_size)

        # Compute attention scores
        wei = q @ k.transpose(-2, -1)  # (B, T, T)

        # Scale (stabilizes gradients)
        wei = wei / (k.shape[-1] ** 0.5)

        # Apply causal mask
        wei = wei.masked_fill(
            self.tril[:T, :T] == 0,
            float("-inf")
        )

        # Convert to probabilities
        wei = wei.softmax(dim=-1)  # (B, T, T)

        # Apply attention to values
        v = self.value(x)  # (B, T, head_size)
        out = wei @ v      # (B, T, head_size)

        return out

Step 3.4: Multi-Head Attention

Because language contains many types of relationships, we use multiple attention heads. Different heads can specialize in different patterns.

class MultiHeadAttention(nn.Module):
    def __init__(self, num_heads, n_embed, block_size):
        super().__init__()

        head_size = n_embed // num_heads

        self.heads = nn.ModuleList([
            Head(n_embed, head_size, block_size)
            for _ in range(num_heads)
        ])

        self.proj = nn.Linear(n_embed, n_embed)

    def forward(self, x):
        # Run all heads and concatenate
        out = torch.cat(
            [h(x) for h in self.heads],
            dim=-1
        )

        return self.proj(out)

Step 3.5: Feed-Forward Network

After attention gathers information, each token passes through a small neural network that transforms the representation.

class FeedForward(nn.Module):
    def __init__(self, n_embed):
        super().__init__()

        self.net = nn.Sequential(
            nn.Linear(n_embed, 4 * n_embed),  # Expand
            nn.GELU(),                         # Activation
            nn.Linear(4 * n_embed, n_embed)    # Contract
        )

    def forward(self, x):
        return self.net(x)

Step 3.6: Transformer Block

Layer normalization and residual connections make training stable.

class Block(nn.Module):
    def __init__(self, n_embed, n_head, block_size):
        super().__init__()

        self.sa = MultiHeadAttention(n_head, n_embed, block_size)
        self.ffwd = FeedForward(n_embed)

        self.ln1 = nn.LayerNorm(n_embed)
        self.ln2 = nn.LayerNorm(n_embed)

    def forward(self, x):
        # Attention with residual connection
        x = x + self.sa(self.ln1(x))

        # Feed-forward with residual connection
        x = x + self.ffwd(self.ln2(x))

        return x

Step 3.7: Complete GPT Model

class GPT(nn.Module):
    def __init__(self, vocab_size, n_embed, n_head, n_layer, block_size):
        super().__init__()

        self.token_embedding = nn.Embedding(vocab_size, n_embed)
        self.position_embedding = nn.Embedding(block_size, n_embed)

        self.blocks = nn.Sequential(*[
            Block(n_embed, n_head, block_size)
            for _ in range(n_layer)
        ])

        self.ln = nn.LayerNorm(n_embed)
        self.lm_head = nn.Linear(n_embed, vocab_size)

    def forward(self, idx):
        B, T = idx.shape

        # Get embeddings
        tok_emb = self.token_embedding(idx)
        pos_emb = self.position_embedding(torch.arange(T, device=idx.device))

        x = tok_emb + pos_emb

        # Pass through transformer blocks
        x = self.blocks(x)

        # Final layer norm
        x = self.ln(x)

        # Project to vocabulary
        logits = self.lm_head(x)

        return logits

Stage 4: Training the Model

Configuration

First, define your hyperparameters:

# config.py

# Model architecture
vocab_size = 100     # Character-level vocabulary
block_size = 256     # Context length
n_embed = 384        # Embedding dimension
n_head = 6           # Number of attention heads
n_layer = 8          # Number of transformer layers

# Training
batch_size = 64
learning_rate = 3e-4
max_iters = 10000
eval_interval = 100

# Generation
max_new_tokens = 300
temperature = 0.8

Training Loop

# train.py

import torch
import torch.nn.functional as F
from config import *
from tokenizer import CharTokenizer
from data import TextDataset
from model import GPT

# Load data
with open('data/input.txt', 'r', encoding='utf-8') as f:
    text = f.read()

# Create tokenizer
tokenizer = CharTokenizer(text)
vocab_size = tokenizer.vocab_size

# Create dataset
data = tokenizer.encode(text)
dataset = TextDataset(data, block_size)

# Initialize model
model = GPT(vocab_size, n_embed, n_head, n_layer, block_size)

# Move to GPU if available
device = 'mps' if torch.backends.mps.is_available() else 'cpu'
model = model.to(device)

# Optimizer
optimizer = torch.optim.AdamW(model.parameters(), lr=learning_rate)

# Training loop
for step in range(max_iters):
    # Get batch
    xb, yb = dataset.get_batch(batch_size)
    xb, yb = xb.to(device), yb.to(device)

    # Forward pass
    logits = model(xb)

    # Compute loss
    loss = F.cross_entropy(
        logits.view(-1, vocab_size),
        yb.view(-1)
    )

    # Backward pass
    optimizer.zero_grad()
    loss.backward()
    optimizer.step()

    # Log progress
    if step % eval_interval == 0:
        print(f"Step {step}: loss = {loss.item():.4f}")

# Save model
torch.save(model.state_dict(), 'model.pt')

Stage 5: Generating Text

# generate.py

import torch
from config import *
from tokenizer import CharTokenizer
from model import GPT

def generate(model, tokenizer, start_text, max_new_tokens, temperature=0.8):
    """Generate text from a starting prompt"""
    model.eval()

    # Encode the start text
    idx = torch.tensor([tokenizer.encode(start_text)], dtype=torch.long)
    idx = idx.to(next(model.parameters()).device)

    for _ in range(max_new_tokens):
        # Get predictions
        with torch.no_grad():
            logits = model(idx)
            logits = logits[:, -1, :]  # Get last token's logits

            # Apply temperature
            logits = logits / temperature

            # Convert to probabilities
            probs = logits.softmax(dim=-1)

            # Sample from the distribution
            next_token = torch.multinomial(probs, num_samples=1)

            # Append to sequence
            idx = torch.cat([idx, next_token], dim=1)

    # Decode and return
    generated = tokenizer.decode(idx[0].tolist())
    return generated

# Load model
model = GPT(vocab_size, n_embed, n_head, n_layer, block_size)
model.load_state_dict(torch.load('model.pt'))
model.to(device)

# Generate text
prompt = "Once upon a time"
generated_text = generate(
    model,
    tokenizer,
    prompt,
    max_new_tokens=300,
    temperature=0.8
)
print(generated_text)

Advanced Sampling: Top-K and Top-P

def generate_with_sampling(model, tokenizer, start_text, max_new_tokens, 
                          temperature=0.8, top_k=None, top_p=None):
    """Generate with Top-K or Nucleus (Top-P) sampling"""
    model.eval()

    idx = torch.tensor([tokenizer.encode(start_text)], dtype=torch.long)
    idx = idx.to(next(model.parameters()).device)

    for _ in range(max_new_tokens):
        with torch.no_grad():
            logits = model(idx)
            logits = logits[:, -1, :] / temperature

            # Top-K filtering
            if top_k is not None:
                v, _ = torch.topk(logits, min(top_k, logits.size(-1)))
                logits[logits < v[:, [-1]]] = float('-inf')

            # Top-P (nucleus) filtering
            if top_p is not None:
                sorted_logits, sorted_indices = torch.sort(logits, descending=True)
                cumulative_probs = torch.cumsum(F.softmax(sorted_logits, dim=-1), dim=-1)

                # Remove tokens with cumulative probability above the threshold
                sorted_indices_to_remove = cumulative_probs > top_p
                sorted_indices_to_remove[..., 1:] = sorted_indices_to_remove[..., :-1].clone()
                sorted_indices_to_remove[..., 0] = 0

                indices_to_remove = sorted_indices_to_remove.scatter(1, sorted_indices, sorted_indices_to_remove)
                logits[indices_to_remove] = float('-inf')

            probs = F.softmax(logits, dim=-1)
            next_token = torch.multinomial(probs, num_samples=1)
            idx = torch.cat([idx, next_token], dim=1)

    return tokenizer.decode(idx[0].tolist())

Configuration for a ~10M Parameter Model

A compact GPT configuration that lands near 10 million parameters:

Hyperparameter	Value
Vocabulary	Character-level (~80-200 chars)
Context Length (`block_size`)	256
Embedding Dimension (`n_embed`)	384
Transformer Layers (`n_layer`)	8
Attention Heads (`n_head`)	6
Feed-Forward Hidden Size	1536 (4× embedding)
Batch Size	64
Learning Rate	3e-4
Optimizer	AdamW
Parameters	~10 Million

Performance Benchmarks

With the MPS backend enabled, training a 10M parameter model on a MacBook is surprisingly fast:

M1 Max: ~7.7x faster than CPU training
M2 Pro: A 10M model shows promising results in ~1 hour
M3 Pro: Can train the same model in under an hour

# Move model to MPS
device = torch.device('mps' if torch.backends.mps.is_available() else 'cpu')
model = model.to(device)

# Everything else remains the same!

Where to Go Next

Once you've built a working GPT from scratch, you'll have the foundation to explore more advanced ideas:

Tokenization Improvements

Byte Pair Encoding (BPE): More efficient than character-level
SentencePiece: Handles unicode and multiple languages
Byte-level tokenization: Preserves raw bytes

Architecture Enhancements

Rotary Positional Embeddings (RoPE): Better relative positioning
FlashAttention: Faster attention computation
KV Caching: Faster inference
RMSNorm: Faster alternative to LayerNorm
Weight Tying: Share embeddings between input and output

Training Optimizations

Mixed Precision Training (FP16/BF16)
Gradient Checkpointing
Learning Rate Schedules: Warmup + cosine decay
Gradient Accumulation

Advanced Techniques

LoRA and QLoRA: Efficient fine-tuning
RLHF: Reinforcement Learning from Human Feedback
RAG: Retrieval-Augmented Generation
Mixture of Experts (MoE)

Suggested Learning Roadmap

Build the character tokenizer ✓
Implement batch creation and next-token prediction ✓
Add token and positional embeddings ✓
Implement masked self-attention ✓
Extend to multi-head attention ✓
Add feed-forward network, layer norm, residual connections ✓
Stack multiple transformer blocks ✓
Train using cross-entropy loss and AdamW ✓
Implement autoregressive generation with sampling ✓
Next Steps:
- Add dropout for regularization
- Implement learning rate scheduling
- Add validation metrics and early stopping
- Experiment with different model sizes
- Try BPE tokenization

Example Training Data Sources

For your first model, start with a single text corpus:

Public domain books: Project Gutenberg
Shakespeare plays: Complete works
Wikipedia extracts: First few MB of text
TinyStories: Small story dataset
Code datasets: For building a code model

A few hundred kilobytes of clean text is enough to produce recognizable language patterns with a 10M parameter model.

Final Thoughts

The gap between using Generative AI and truly understanding it is smaller than it appears. Every modern language model—from compact open-source models to frontier-scale systems—relies on the same core ideas: tokenization, embeddings, self-attention, feed-forward networks, normalization, residual connections, and next-token prediction.

Building a small GPT model from scratch won't produce ChatGPT-level intelligence, but it will give you something even more valuable: intuition. You'll understand why Transformers work, how they learn, and where the opportunities lie for optimization, fine-tuning, and innovation.

Scaleable Multifaceted File System Search Architecture

Ankit Arora — Wed, 01 Jul 2026 05:38:41 +0000

To achieve an efficient "fast file lookup search, then deep content graph indexing," the best approach is a layered architecture including knowledge graph construction:

Layer 1 (Instant Lookup): Uses native OS commands to find files by name/metadata in milliseconds.
Layer 2 (Content Extraction): Parses the found files to extract plain text.
Layer 3 (Knowledge Memory): Stores extracted text as vectors and graph relationships, with built-in TTL (Time-To-Live) for automatic expiration.

Complete technical blueprint and architecture design

1. Fast File Lookup (Using OS Native Commands)

To find files instantly without scanning the entire hard drive, you must tap into the OS's pre-built indexes. Do not use os.walk() for the initial lookup.

OS	Command / SDK	How it works
Windows	Everything SDK / `es.exe`	Accesses the USN Journal and MFT (Master File Table). Searches are near-instantaneous (<100ms for millions of files).
macOS	`mdfind`	Uses the Spotlight metadata index. Run `mdfind -name "keyword"` for filenames, or `mdfind "kMDItemContentType == 'pdf'"` for types.
Linux	`plocate` / `mlocate`	Uses a pre-built database updated by `updatedb`. `plocate` is extremely fast and uses minimal I/O.

Python Implementation Snippet:

import subprocess
import platform

def os_file_lookup(query, file_type=None):
    system = platform.system()
    if system == "Windows":
        # Assuming Everything is installed
        cmd = ["es.exe", "-n", "100", query] 
    elif system == "Darwin": # macOS
        cmd = ["mdfind", "-name", query]
    else: # Linux
        cmd = ["plocate", query]

    result = subprocess.run(cmd, capture_output=True, text=True)
    return result.stdout.splitlines()

2. Content Extraction (Parsing PDF, Word, Excel, etc.)

Once you have the file paths from Layer 1, you pass them to an extraction engine. For a unified interface, use these Python libraries:

all2txt or doc-master: Provides a single parse(file_path) function that handles .docx, .xlsx, .pptx, .pdf, .odt, .epub, and .pages. It automatically chooses the right parser behind the scenes.
fscan: A multi-threaded Python search tool. You can borrow its extractor logic for speed.
For more improved performance in C++ *: Use *libpoppler (PDF), libxml2 (DOCX/XLSX are just ZIPs of XML), and xlsxioread (Excel).

3. Knowledge Graph + Vector Memory (With TTL)

To store this as "memory" and remove it after disuse or max capacity, you need a database that supports Graph relationships, Vector embeddings, and TTL (Time-To-Live) indices.

Tool	Why it fits your requirement
Grafeo (with LlamaIndex)	An embedded graph database (single `.db` file). It natively supports Vector Search (for semantic context) and GQL (Graph Query Language). Perfect for a single installable software.
Cognee	Specifically built as an "AI Memory" engine. It automates the extraction of entities and relationships from your extracted text, builds a graph, and automatically prunes (deletes) old data based on usage frequency.
SQLite + sqlite-vec + TTL	A simpler DIY approach: Store chunks of text in SQLite, enable the `sqlite-vec` extension for similarity search, and add a `created_at` column with an index. Run a background scheduler every hour to `DELETE FROM memory WHERE created_at < datetime('now', '-7 days')`.

Memory Management (Your "Remove after unused or max increased" requirement):

Use a LRU (Least Recently Used) cache pattern on the graph nodes.
Store a last_accessed timestamp for every entity. When the database exceeds your defined size limit (e.g., 500MB), run a cleanup job that deletes the oldest 20% of nodes.
Alternatively, assign a "Hotness" score. Files opened via the chat interface get a +1 score; if a node's score drops to 0 and 30 days have passed, it is purged.

4. Hybrid Search Workflow (Efficient "Lookup First" Search)

Your system should not search the heavy graph database for every keystroke. Use this flow:

User types a query in the chat interface.
Fast Path: Fire the OS command (mdfind/es.exe) to get filenames matching the query. Display these immediately (under 200ms).
Parallel Deep Path: Simultaneously, send the query to the Knowledge Graph (Vector + Graph search). This takes a bit longer (1-3 seconds).
Aggregation: Show the user: "Found 15 files (OS lookup). Here are the top 3 knowledge contexts extracted from them (AI lookup)."

5. Suggested System Architecture Diagram

flowchart TD
    UI[User Chat / Editor Interface] --> Router{Query Router};

    Router -->|"Filename/Path Query"| OS_Layer[OS Lookup Layer];
    OS_Layer --> Win[Windows: Everything SDK];
    OS_Layer --> Mac[macOS: mdfind];
    OS_Layer --> Lin[Linux: plocate];
    OS_Layer -->|"Paths found"| Extractor[Content Extractor];

    Router -->|"Semantic / Context Query"| Graph_DB[Knowledge Graph Memory];
    Graph_DB --> Vec[Vector Search for Context];
    Graph_DB --> Rel[Graph Traversal for Relationships];

    Extractor --> Chunker[Text Chunker + Embedder];
    Chunker --> Graph_DB;

    subgraph Memory_Management[Automated Memory Pruning]
        TTL[TTL Scheduler] -->|"Delete old/unused nodes"| Graph_DB;
        Size_Monitor[Size Monitor] -->|"If > 500MB, purge oldest"| Graph_DB;
    end

    OS_Layer --> Aggregator[Result Aggregator];
    Graph_DB --> Aggregator;
    Aggregator --> UI;

6. Roadmap for the Installable Text Editor + Chat Solution

Since you want to eventually build this into a downloadable app in (C++ or Python), here is the recommended tech stack:

Backend Core (Python): Use FastAPI or Starlette to wrap your search and graph logic into a local REST API or WebSocket server.
Frontend UI (Cross-platform): Use Tauri (Rust + HTML/JS) or Electron for the text editor with a chat sidebar. Tauri is lighter, but Electron is easier to integrate Python via child_process.
Packaging: Use PyInstaller (for Python) or CPack (for C++) to bundle the Everything SDK (Windows DLLs) and the database into a single .exe / .dmg / .deb.

Why choose this approach?

Speed: OS commands give you file results in < 100ms, which feels "instant" to users.
Intelligence: The Graph DB provides "contextual memory" (e.g., finding an Excel number mentioned in a PDF).
Self-maintaining: The TTL and size monitors ensure the software won't bloat your user's hard drive, automatically removing memory that is no longer relevant.

DEV Community: Ankit Arora

Hybrid Schematic Search in PostgreSQL with Full-Text and Vector Similarity

The Search Problem We're Really Solving

Table of Contents

1. Why Hybrid Search Matters for AI Applications {#why-hybrid-search-matters}

The RAG Revolution and Its Retrieval Problem

When Vector Search Fails

When Full-Text Search Fails

The Hybrid Search Solution

2. Understanding the Two Pillars of Search {#understanding-the-two-pillars}

Vector Similarity Search

Full-Text Search

The Complementarity Principle

3. Setting Up Your PostgreSQL Environment {#setting-up-postgresql}

Requirements

Installation

Database Schema

4. Building the Dataset: From Random Text to Embeddings {#building-the-dataset}

The Data Generation Strategy

Generating Embeddings

Loading Data into PostgreSQL

5. Indexing Strategy: GIN and HNSW Explained {#indexing-strategy}

Creating the Indexes

Deep Dive: The GIN Index

Deep Dive: The HNSW Index

6. Implementing Vector Search {#implementing-vector-search}

Generating a Query Embedding

The Vector Search Query

Sample Results

7. Implementing Full-Text Search {#implementing-full-text-search}

The Full-Text Search Query

Deconstructing the Query

Sample Results

8. The Magic of Reciprocal Rank Fusion (RRF) {#rrf-explained}

What is RRF?

The Formula

Why RRF Works

The PostgreSQL Implementation

Score Distribution Analysis

9. Constructing the Hybrid Search Query {#constructing-hybrid-query}

The Complete Query

Why 40 Results Per Subquery?

Query Execution Flow

Sample Hybrid Results

Analysis of Results

10. Performance Analysis with EXPLAIN ANALYZE {#performance-analysis}

The Execution Plan

Key Plan Output

Performance Breakdown

Index Usage Confirmation

Scaling Considerations

11. Production Considerations and Trade-offs {#production-considerations}

When to Use Hybrid Search

Tuning Parameters

HNSW Parameters

Full-Text Search Parameters

RRF Parameters

Memory and Storage

Hybrid Search vs. Alternatives

12. Next Steps and Future Research {#next-steps}

Immediate Improvements

Research Directions

Production Deployment Checklist

Complete Python Implementation

Conclusion: The Hybrid Advantage

Resources

Building a ~10M-Parameter GPT-Style Language Model on Simple Home Computer

A Complete Guide with Code Examples

What You'll Build

Setting Up Your Environment

Project Structure

Stage 1: Teaching the Computer to Read

Stage 2: Creating Training Examples

Stage 3: Building the Transformer

Step 3.1: Token Embedding

Step 3.2: Positional Embedding

Step 3.3: Single-Head Self-Attention

Step 3.4: Multi-Head Attention

Step 3.5: Feed-Forward Network

Step 3.6: Transformer Block