Full-Text Search Implementation — PostgreSQL, MySQL, and Beyond

Why LIKE queries break down#

SELECT * FROM articles WHERE body LIKE '%kubernetes deployment%';

This scans every row. No index helps. At 100K rows it is slow. At 10M rows it is unusable. It misses "deploy" when searching "deployment." It cannot rank results by relevance.

Full-text search solves all of this.

PostgreSQL full-text search#

PostgreSQL has built-in full-text search that is surprisingly powerful.

tsvector and tsquery#

A tsvector is a sorted list of lexemes (normalized words). A tsquery is a search expression.

-- Convert text to tsvector
SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs');
-- Result: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- Search with tsquery
SELECT to_tsquery('english', 'fox & jump');
-- Result: 'fox' & 'jump'

Notice: "foxes" becomes "fox", "jumped" becomes "jump", "the" is removed. This is stemming and stop-word removal.

Adding a search column#

-- Add a generated tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

-- Create a GIN index
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

Weighting with setweight means title matches rank higher than body matches.

Searching#

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'kubernetes & deployment') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

This uses the GIN index, returns ranked results, and handles stemming automatically.

Phrase search#

-- Find "container orchestration" as a phrase
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'container orchestration');

Prefix search (autocomplete)#

-- Match words starting with "kube"
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'kube:*');

MySQL FULLTEXT search#

MySQL supports FULLTEXT indexes on CHAR, VARCHAR, and TEXT columns (InnoDB and MyISAM).

Creating a FULLTEXT index#

ALTER TABLE articles ADD FULLTEXT INDEX ft_articles (title, body);

Natural language mode#

SELECT title, MATCH(title, body) AGAINST('kubernetes deployment') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('kubernetes deployment')
ORDER BY score DESC;

MySQL ranks by TF-IDF relevance automatically.

Boolean mode#

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+kubernetes -docker +deployment' IN BOOLEAN MODE);

• + means must include
• - means must exclude
• No prefix means optional (boosts rank)

MySQL limitations#

• No stemming by default (searching "deploy" won't match "deployment")
• Minimum word length is 3 characters (configurable via ft_min_word_len)
• Stop word list is fixed unless you customize it
• No phrase proximity search
• No field weighting (title vs body)

Trigram indexes for fuzzy matching#

When users misspell words, stemming does not help. Trigram indexes do.

PostgreSQL pg_trgm#

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_articles_title_trgm ON articles
  USING GIN(title gin_trgm_ops);

-- Fuzzy search
SELECT title, similarity(title, 'kuberntes') AS sim
FROM articles
WHERE title % 'kuberntes'
ORDER BY sim DESC;

The % operator finds similar strings. "kuberntes" matches "kubernetes" because they share enough trigrams.

Combining tsvector + trigram#

Use tsvector for precise full-text search. Use trigram as a fallback for "did you mean?" suggestions.

-- Primary search
SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'kuberntes');
-- No results? Fall back to trigram
SELECT title FROM articles WHERE title % 'kuberntes' ORDER BY similarity(title, 'kuberntes') DESC LIMIT 5;

Search result highlighting#

Show users where their query matched.

PostgreSQL ts_headline#

SELECT title,
  ts_headline('english', body, to_tsquery('english', 'kubernetes'),
    'StartSel=<mark>, StopSel=</mark>, MaxFragments=3, MaxWords=30')
  AS snippet
FROM articles
WHERE search_vector @@ to_tsquery('english', 'kubernetes');

This returns fragments of the body with matches wrapped in mark tags.

MySQL snippet equivalent#

MySQL does not have built-in highlighting. You must implement it in application code or use a search engine.

Building autocomplete#

Autocomplete needs sub-50ms response times.

Approach 1 — Prefix search with tsvector#

SELECT DISTINCT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'kube:*')
LIMIT 10;

Fast with a GIN index, but returns full documents, not suggestions.

Approach 2 — Separate suggestions table#

CREATE TABLE search_suggestions (
  term TEXT PRIMARY KEY,
  frequency INT DEFAULT 1
);

CREATE INDEX idx_suggestions_trgm ON search_suggestions USING GIN(term gin_trgm_ops);

-- Query as user types
SELECT term FROM search_suggestions
WHERE term LIKE 'kube%'
ORDER BY frequency DESC
LIMIT 10;

Populate this table from search logs, article titles, and tags.

Approach 3 — Materialized view#

CREATE MATERIALIZED VIEW search_terms AS
SELECT DISTINCT unnest(string_to_array(title, ' ')) AS term
FROM articles;

REFRESH MATERIALIZED VIEW CONCURRENTLY search_terms;

When to use Elasticsearch instead#

PostgreSQL full-text search is excellent up to a point. Consider Elasticsearch or OpenSearch when:

Rule of thumb: if you are already on PostgreSQL and have fewer than 5M searchable documents, start with built-in FTS. Add Elasticsearch when you outgrow it.

Performance tips#

1. Always use GIN indexes — GiST is slower for text search
2. Store tsvector as a generated column — avoid computing it at query time
3. Limit result sets — use LIMIT even with indexes
4. Use websearch_to_tsquery for user input — it handles quoted phrases and operators safely
5. Vacuum regularly — GIN indexes need maintenance
6. Partition large tables — search within partitions for faster results

Visualize your search architecture#

Map out your search pipeline from ingestion to query — try Codelit to generate an interactive architecture diagram.

Key takeaways#

1. PostgreSQL tsvector/tsquery is production-ready full-text search with stemming, ranking, and indexing
2. MySQL FULLTEXT works for basic search but lacks stemming and field weighting
3. Trigram indexes handle typos and fuzzy matching — combine with tsvector for the best experience
4. Highlight results with ts_headline to show users where matches occurred
5. Autocomplete needs a dedicated approach — prefix search, suggestions table, or materialized views
6. Elasticsearch is worth the operational cost when you outgrow database-native search
7. Start simple — built-in database search covers more use cases than most teams realize

Article #405 in the Codelit engineering series. Explore our full library of system design, infrastructure, and architecture guides at codelit.io.