Full-Text Search

-- Create a table with a full-text search index
CREATE TABLE articles (
    id        BIGSERIAL PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT NOT NULL,
    author    TEXT,
    published TIMESTAMPTZ DEFAULT now()
);

-- Create a GIN index for full-text search
CREATE INDEX idx_articles_fts
    ON articles USING GIN (to_tsvector('english', title || ' ' || body));

-- BM25 search using the MATCH operator
SELECT id, title, bm25_score(body, 'distributed consensus') AS score
FROM articles
WHERE body MATCH 'distributed consensus'
ORDER BY score DESC
LIMIT 10;

-- Boolean query operators
SELECT id, title
FROM articles
WHERE body MATCH 'consensus AND (raft OR paxos) NOT viewstamped'
ORDER BY bm25_score(body, 'consensus raft paxos') DESC
LIMIT 10;

-- BM25F search with field weights
SELECT id, title,
       bm25f_score(
           fields => ARRAY['title', 'body', 'tags'],
           weights => ARRAY[3.0, 1.0, 2.0],
           query => 'machine learning database'
       ) AS score
FROM articles
WHERE bm25f_match(
    fields  => ARRAY['title', 'body', 'tags'],
    query   => 'machine learning database'
)
ORDER BY score DESC
LIMIT 10;

-- Equivalent using the field-weighted MATCH syntax
SELECT id, title
FROM articles
WHERE MATCH(title * 3, body * 1, tags * 2) AGAINST 'machine learning database'
ORDER BY relevance DESC
LIMIT 10;

SELECT
    id,
    title,
    absdb_snippet(
        body,
        'distributed consensus',
        max_words   => 30,
        start_sel   => '<strong>',
        stop_sel    => '</strong>',
        min_words   => 15
    ) AS snippet
FROM articles
WHERE body MATCH 'distributed consensus'
ORDER BY bm25_score(body, 'distributed consensus') DESC
LIMIT 10;

-- Single-column GIN index
CREATE INDEX idx_articles_body_gin
    ON articles USING GIN (to_tsvector('english', body));

-- Multi-column GIN index (title + body combined)
CREATE INDEX idx_articles_full_gin
    ON articles USING GIN (
        to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
    );

-- GIN index on JSONB for containment queries
CREATE INDEX idx_products_meta_gin
    ON products USING GIN (metadata jsonb_path_ops);

-- GIN index on text array
CREATE INDEX idx_articles_tags_gin
    ON articles USING GIN (tags);

-- Check index usage
EXPLAIN SELECT * FROM articles WHERE body MATCH 'consensus';

-- Exact phrase
SELECT id, title FROM articles
WHERE body MATCH '"raft consensus algorithm"'
ORDER BY bm25_score DESC
LIMIT 10;

-- Prefix wildcard
SELECT id, title FROM articles
WHERE body MATCH 'distrib*'
ORDER BY bm25_score DESC
LIMIT 20;

-- Fuzzy, edit-distance <= 1
SELECT id, title FROM articles
WHERE body MATCH '~databse'   -- matches "database", "databases"
ORDER BY bm25_score DESC
LIMIT 20;

-- Mix forms in one query
SELECT id, title FROM articles
WHERE body MATCH '"state machine" OR ~raft OR log*';

-- Create GIN trigram index
CREATE INDEX idx_products_name_trgm
    ON products USING GIN (name gin_trgm_ops);

-- Compute similarity score between two strings (0.0 to 1.0)
SELECT similarity('apple iphone', 'apple iPhone 15 Pro');
-- Result: 0.62

-- Find similar product names (fuzzy match)
SELECT name, similarity(name, 'iphone') AS sim
FROM products
WHERE name % 'iphone'         -- % operator: similarity > threshold (default 0.3)
ORDER BY sim DESC
LIMIT 10;

-- Set similarity threshold
SET pg_trgm.similarity_threshold = 0.4;

-- Trigram search with ranking
SELECT name, similarity(name, $1) AS score
FROM products
WHERE similarity(name, $1) > 0.25
ORDER BY score DESC
LIMIT 20;

-- Hybrid search: BM25 keyword + dense vector
SELECT id, title
FROM articles
WHERE body MATCH 'consensus algorithm fault tolerance'
ORDER BY embedding <=> EMBED('minilm', 'consensus algorithm fault tolerance')
LIMIT 10;

-- Explicit hybrid search function with weight control
SELECT * FROM absdb_hybrid_search(
    table       => 'articles',
    query       => 'consensus algorithm fault tolerance',
    vec_query   => EMBED('minilm', 'consensus algorithm fault tolerance'),
    bm25_weight => 0.3,
    vec_weight  => 0.7,
    top_k       => 20
);

-- Store per-token ColBERT embeddings (128-dim per token)
CREATE TABLE colbert_articles (
    id       BIGSERIAL PRIMARY KEY,
    title    TEXT,
    body     TEXT,
    tokens   VECTOR(128)[]
);

-- Auto-populate token embeddings on insert
ALTER TABLE colbert_articles
    SET (auto_embed_tokens = true, embed_model = 'colbert-v2');

-- MaxSim search
SELECT id, title,
       absdb_maxsim(tokens, EMBED_TOKENS('colbert-v2', 'fault tolerant consensus')) AS score
FROM colbert_articles
ORDER BY score DESC
LIMIT 10;