Can my DB?

-- Create a GIN index on a tsvector column
ALTER TABLE articles ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;
CREATE INDEX idx_articles_fts ON articles USING gin(search_vec);

-- Full-text search query
SELECT title, ts_rank(search_vec, query) AS rank
FROM articles, plainto_tsquery('english', 'database performance') AS query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 10;

-- Snippet highlighting
SELECT ts_headline('english', content, to_tsquery('database'))
FROM articles
WHERE search_vec @@ to_tsquery('database');

• tsvector stores pre-processed lexemes; tsquery expresses the search condition.
• GIN indexes on tsvector columns enable fast full-text lookups.
• Multiple language configurations available (english, french, german, etc.).
• ts_rank() and ts_rank_cd() compute relevance scores for result ordering.
• phraseto_tsquery() for phrase search; websearch_to_tsquery() for Google-like syntax.
• Unaccent and other text search dictionaries can be configured.

-- Create FULLTEXT index
ALTER TABLE articles ADD FULLTEXT INDEX idx_fts (title, content);

-- Natural language mode (default)
SELECT title, MATCH(title, content) AGAINST('database performance') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('database performance')
ORDER BY score DESC;

-- Boolean mode with operators
SELECT title FROM articles
WHERE MATCH(title, content) AGAINST('+database -slow' IN BOOLEAN MODE);

-- Query expansion (widens search terms)
SELECT title FROM articles
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

• InnoDB and MyISAM both support FULLTEXT indexes.
• Boolean mode supports +, -, *, " " operators for refined searches.
• No stemming by default; searches are word-level.
• innodb_ft_min_token_size controls minimum word length (default 3).
• stopwords list filters common words from indexing.

ALTER TABLE articles ADD FULLTEXT INDEX idx_fts (title, content);

SELECT title, MATCH(title, content) AGAINST('database performance') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('database performance')
ORDER BY score DESC;

• Same FULLTEXT INDEX and MATCH ... AGAINST syntax as MySQL.
• Mroonga storage engine provides additional FTS capabilities including regular expression and proximity search.
• Supports natural language, boolean, and query expansion modes.

-- Create Oracle Text CONTEXT index
CREATE INDEX idx_articles_fts ON articles(content)
  INDEXTYPE IS CTXSYS.CONTEXT;

-- Search with CONTAINS predicate
SELECT title, SCORE(1) AS relevance
FROM articles
WHERE CONTAINS(content, 'database AND performance', 1) > 0
ORDER BY relevance DESC;

-- Stem search
SELECT title FROM articles
WHERE CONTAINS(content, 'STEM(run)') > 0;

-- Proximity search
SELECT title FROM articles
WHERE CONTAINS(content, 'database NEAR performance') > 0;

• Oracle Text (CTXSYS.CONTEXT index type) is Oracle's built-in full-text search.
• SCORE() function returns relevance score for result ranking.
• Supports STEM, FUZZY, ABOUT (conceptual), and proximity (NEAR) operators.
• SYNC ON COMMIT or CTX_DDL.SYNC_INDEX required to keep index current.
• Very mature feature with support for multiple languages, custom lexers, and wordlists.

-- Create full-text catalog and index
CREATE FULLTEXT CATALOG ftc_articles;
CREATE FULLTEXT INDEX ON articles(title, content)
  KEY INDEX pk_articles ON ftc_articles;

-- CONTAINS for precise matching
SELECT title FROM articles
WHERE CONTAINS((title, content), '"database performance"');

-- FREETEXT for natural language search
SELECT title FROM articles
WHERE FREETEXT(content, 'database performance optimization');

-- CONTAINSTABLE for ranking
SELECT a.title, k.RANK
FROM articles a
INNER JOIN CONTAINSTABLE(articles, content, 'database') k
  ON a.id = k.[KEY]
ORDER BY k.RANK DESC;

• Requires Full-Text Search feature installed and SQL Server Full-Text Filter Daemon service.
• CONTAINS for exact terms, phrases, proximity; FREETEXT for natural language.
• CONTAINSTABLE() and FREETEXTTABLE() return ranking scores as joinable result sets.
• Semantic search (SEMANTICSIMILARITYTABLE) enables document similarity scoring.
• Language-aware stemming and stopwords via language IDs.

-- Create FTS5 virtual table
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, content,
  content='articles',
  content_rowid='id'
);

-- Populate
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

-- Search
SELECT rowid, title, rank
FROM articles_fts
WHERE articles_fts MATCH 'database performance'
ORDER BY rank;

-- Snippet and highlight
SELECT snippet(articles_fts, 1, '<b>', '</b>', '...', 15)
FROM articles_fts
WHERE articles_fts MATCH 'database';

• FTS5 is the modern full-text search extension, built into the standard SQLite distribution.
• Uses a virtual table with MATCH operator for queries.
• Supports BM25 relevance ranking, snippets, and highlight functions.
• Porter stemmer available as a tokenizer option.
• Content tables allow FTS5 index to mirror a real table for storage efficiency.

-- Basic pattern matching (no FTS)
SELECT title FROM articles
WHERE content ILIKE '%database performance%';

-- Regex matching
SELECT title FROM articles
WHERE REGEXP_INSTR(content, 'database.{0,20}performance') > 0;

• No native full-text search capability.
• LIKE/ILIKE for basic pattern matching; REGEXP_INSTR/REGEXP_SUBSTR for regex.
• pg_trgm SIMILARITY function (available in PostgreSQL) is not available in Redshift.
• External Amazon OpenSearch Service is the recommended solution for FTS with Redshift data.

-- Enable DB2 Text Search (requires admin setup)
-- db2ts ENABLE DATABASE FOR TEXT

-- Create text search index
CREATE INDEX idx_articles_fts ON articles(content)
  FOR TEXT;

-- Search with CONTAINS predicate
SELECT title, SCORE(content, 'database performance') AS relevance
FROM articles
WHERE CONTAINS(content, 'database performance') = 1
ORDER BY relevance DESC;

• DB2 Text Search is a separate component built on Apache Lucene.
• Must be enabled at the database level (ENABLE DATABASE FOR TEXT).
• CONTAINS() and SCORE() predicates provide search and relevance ranking.
• Supports multiple languages with language-specific analyzers.
• Index must be synchronized after data changes (automatic or manual sync).

-- Enable search optimization on a table
ALTER TABLE articles ADD SEARCH OPTIMIZATION;

-- SEARCH function for cross-column text search
SELECT title FROM articles
WHERE SEARCH((title, content), 'database performance');

-- Equality and LIKE searches benefit from search optimization
SELECT title FROM articles
WHERE title ILIKE '%database%';

• Not a true full-text search - no stemming, relevance ranking, or boolean operators.
• SEARCH() function added for cross-column text matching.
• Search Optimization Service speeds up equality and prefix lookups.
• ALTER TABLE ... ADD SEARCH OPTIMIZATION enables the feature; costs Snowflake credits.
• For full FTS requirements, use external Elasticsearch or OpenSearch.

-- Create a search index for performance
CREATE SEARCH INDEX idx_articles_fts ON articles(ALL COLUMNS);

-- SEARCH function
SELECT title FROM articles
WHERE SEARCH(articles, 'database performance');

-- Phrase search
SELECT title FROM articles
WHERE SEARCH(articles, '"database performance"');

• SEARCH() function added in 2022 for multi-column text search.
• Supports AND/OR operators and quoted phrase matching.
• No language stemming or relevance ranking.
• CREATE SEARCH INDEX greatly improves SEARCH() query performance.
• For full FTS with ranking and stemming, use external Elasticsearch or Vertex AI Search.