Skip to content
HeliosDB HeliosDB Full

HeliosDB Full-Text Search Tuning Guide

HeliosDB Full-Text Search Tuning Guide

Version: 1.0 Last Updated: 2025-11-30

Quick Start

-- Create full-text search index
CREATE INDEX idx_documents_fts ON documents USING FULLTEXT(title, content);


-- Basic search
SELECT id, title, ts_rank(document, query) as rank
FROM documents,
     websearch_to_tsquery('english', 'database optimization') as query
WHERE document @@ query
ORDER BY rank DESC;

Full-Text Indexes

Creating Indexes

-- Single column
CREATE INDEX idx_articles_fts ON articles USING FULLTEXT(content);


-- Multiple columns
CREATE INDEX idx_docs_fts ON documents USING FULLTEXT(title, body, tags);


-- With custom tokenizer
CREATE INDEX idx_multi_language ON articles USING FULLTEXT(content)
WITH (language = 'auto', tokenizer = 'unicode');

Index Configuration

-- Full-text parameters
ALTER TABLE articles SET SEARCH_PARAMS (
  language = 'english',
  tokenizer = 'standard',
  stemming = true,
  stop_words = 'english',
  min_token_length = 3
);

Search Queries

-- Keyword search
SELECT id, title, ts_rank(document, query) as relevance
FROM documents,
     plainto_tsquery('english', 'database performance') as query
WHERE document @@ query
ORDER BY relevance DESC;
-- Phrase search
SELECT * FROM documents
WHERE content @@ phraseto_tsquery('english', 'query optimization');


-- Boolean search
SELECT * FROM documents
WHERE content @@ to_tsquery('english', 'database & (optimization | tuning)');


-- Wildcard search
SELECT * FROM documents
WHERE content @@ to_tsquery('english', 'optim:*');  -- optim*, optimization, etc

Ranking & Relevance

-- Rank by relevance
SELECT
  id, title,
  ts_rank(document, query) as rank,
  ts_rank_cd(document, query) as rank_detailed
FROM documents,
     plainto_tsquery('english', 'search terms') as query
WHERE document @@ query
ORDER BY rank DESC;


-- Custom ranking
SELECT
  id, title,
  (ts_rank(document, query) * popularity_score) as custom_rank
FROM documents,
     plainto_tsquery('english', 'search') as query
WHERE document @@ query
ORDER BY custom_rank DESC;

Optimization Techniques

Partial Indexes

-- Index only published articles
CREATE INDEX idx_published_fts ON articles USING FULLTEXT(content)
WHERE status = 'PUBLISHED';

Covering Indexes

-- Include frequently accessed columns
CREATE INDEX idx_search_covering ON documents USING FULLTEXT(content)
INCLUDE (title, author, publish_date);

Batch Indexing

-- Disable index during bulk insert
ALTER INDEX idx_documents_fts DISABLE;
INSERT INTO documents (title, content) SELECT ...;
ALTER INDEX idx_documents_fts ENABLE;
ANALYZE documents;

Basic Similarity

-- Find similar documents
SELECT id, title, similarity(content, 'search text') as sim
FROM documents
WHERE similarity(content, 'search text') > 0.5
ORDER BY sim DESC;

Fuzzy Matching

-- Handle misspellings
SELECT id, title, levenshtein(title, 'databaes') as distance
FROM documents
WHERE levenshtein(title, 'databaes') < 3
ORDER BY distance;

Performance Tuning

Configuration

-- Tune search performance
ALTER TABLE documents SET SEARCH_PARAMS (
  index_threads = 4,
  batch_size = 1000,
  update_interval = 60
);


-- Aggressive caching
ALTER TABLE documents ENABLE SEARCH_CACHE;
ALTER TABLE documents SET CACHE_PARAMS (
  max_entries = 10000,
  ttl_seconds = 3600
);

Monitoring

-- Search performance metrics
SELECT
  query_text,
  execution_count,
  avg_time_ms,
  cache_hit_rate
FROM search_statistics
ORDER BY execution_count DESC;
-- Auto-detect language
CREATE INDEX idx_multilang_fts ON articles USING FULLTEXT(content)
WITH (language = 'auto');


-- Search specific language
SELECT * FROM articles
WHERE content @@ to_tsquery('spanish', 'búsqueda');

Best Practices

  1. Create indexes on columns you search frequently
  2. Use phrase queries for exact matches
  3. Configure language-specific tokenizers
  4. Monitor index size and performance
  5. Rebuild indexes periodically
  6. Use covering indexes for faster retrieval

Related Documentation: