---
# === IDENTITY ===
id: software/patterns/sql-fulltext-search/2026
canonical_question: "How do I implement full-text search in PostgreSQL and MySQL?"
aliases:
  - "PostgreSQL full-text search tsvector tsquery"
  - "MySQL FULLTEXT index search"
  - "pg_trgm trigram fuzzy search"
  - "SQL full-text search GIN index"
  - "PostgreSQL vs MySQL text search comparison"
  - "database full-text search without Elasticsearch"
  - "tsvector tsquery GIN index tutorial"
  - "MySQL boolean mode full-text search"
  - "PostgreSQL generated column tsvector"
  - "SQL text search ranking relevance"
entity_type: software_reference
domain: software > patterns > sql_fulltext_search
region: global
jurisdiction: global
temporal_scope: 2015-2026

# === VERIFICATION ===
last_verified: 2026-02-23
confidence: 0.94
version: 1.0
first_published: 2026-02-23

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: "PostgreSQL 12 — generated columns for tsvector (2019-10); MySQL 5.7 — InnoDB FULLTEXT support stable (2015-10)"
  next_review: 2026-08-22
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "PostgreSQL tsvector requires a text search configuration (language dictionary) — using the wrong one silently drops valid tokens"
  - "MySQL InnoDB FULLTEXT minimum token size is 3 characters by default (innodb_ft_min_token_size) — short words like 'Go', 'AI', 'C#' are silently excluded"
  - "GIN indexes are faster to query but slower to update than GiST — never use GiST for read-heavy full-text search workloads"
  - "MySQL FULLTEXT in NATURAL LANGUAGE MODE ignores words present in >50% of rows — high-frequency terms return zero results"
  - "PostgreSQL ts_rank does NOT use the GIN index — ranking always requires a sequential scan of matched rows to read tsvector values"
  - "pg_trgm similarity threshold defaults to 0.3 — failing to tune this produces either too many or too few fuzzy matches"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Need to search across multiple services or >100M documents with sub-50ms latency"
    use_instead: "Dedicated search engine (Elasticsearch, Typesense, Meilisearch) — this unit covers database-native search"
  - condition: "Looking for semantic/vector similarity search (embeddings, kNN)"
    use_instead: "software/patterns/vector-similarity-search/2026"
  - condition: "Debugging slow PostgreSQL queries in general (not search-specific)"
    use_instead: "software/debugging/postgresql-slow-queries/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: database
    question: "Which database engine are you using?"
    type: choice
    options: ["PostgreSQL", "MySQL", "Both/migrating"]
  - key: search_type
    question: "What kind of text matching do you need?"
    type: choice
    options: ["Exact keyword search", "Natural language search with ranking", "Fuzzy/typo-tolerant search", "All of the above"]
  - key: dataset_size
    question: "How large is the text corpus being searched?"
    type: choice
    options: ["Small (<100K rows)", "Medium (100K-10M rows)", "Large (>10M rows)"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/patterns/sql-fulltext-search/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-23)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "PostgreSQL Slow Query Debugging"
    - id: "software/system-design/search-engine/2026"
      label: "Search Engine Architecture"
    - id: "software/debugging/mysql-deadlocks/2026"
      label: "MySQL Deadlock Debugging"
  alternative_to:
    - id: "software/system-design/search-engine/2026"
      label: "Full Search Engine Architecture (Elasticsearch/Solr)"
  often_confused_with:
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "General PostgreSQL Query Optimization (not search-specific)"

# === SOURCES (7 authoritative sources) ===
sources:
  - id: src1
    title: "PostgreSQL Documentation: Full Text Search — Tables and Indexes"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/textsearch-tables.html
    type: official_docs
    published: 2024-11-21
    reliability: authoritative
  - id: src2
    title: "PostgreSQL Documentation: Controlling Text Search (ts_rank, Weighting)"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/textsearch-controls.html
    type: official_docs
    published: 2024-11-21
    reliability: authoritative
  - id: src3
    title: "PostgreSQL Documentation: pg_trgm — Trigram Matching"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/pgtrgm.html
    type: official_docs
    published: 2024-11-21
    reliability: authoritative
  - id: src4
    title: "MySQL 8.4 Reference Manual: Boolean Full-Text Searches"
    author: Oracle Corporation
    url: https://dev.mysql.com/doc/refman/8.4/en/fulltext-boolean.html
    type: official_docs
    published: 2024-04-30
    reliability: authoritative
  - id: src5
    title: "MySQL 8.4 Reference Manual: Fine-Tuning MySQL Full-Text Search"
    author: Oracle Corporation
    url: https://dev.mysql.com/doc/refman/8.4/en/fulltext-fine-tuning.html
    type: official_docs
    published: 2024-04-30
    reliability: authoritative
  - id: src6
    title: "Comparing Postgres, ElasticSearch, and pg_search for Full-Text Search"
    author: Neon
    url: https://neon.com/blog/postgres-full-text-search-vs-elasticsearch
    type: technical_blog
    published: 2024-06-15
    reliability: moderate_high
  - id: src7
    title: "Optimizing Full Text Search with Postgres tsvector Columns and Triggers"
    author: thoughtbot
    url: https://thoughtbot.com/blog/optimizing-full-text-search-with-postgres-tsvector-columns-and-triggers
    type: technical_blog
    published: 2023-09-12
    reliability: moderate_high
---

# SQL Full-Text Search: PostgreSQL tsvector, MySQL FULLTEXT, and pg_trgm

## TL;DR

- **Bottom line**: PostgreSQL and MySQL both offer built-in full-text search that eliminates the need for Elasticsearch in small-to-medium workloads; PostgreSQL's tsvector/tsquery with GIN indexes is more powerful, while MySQL's FULLTEXT is simpler to set up.
- **Key tool/command**: `CREATE INDEX idx ON table USING GIN (to_tsvector('english', column));`
- **Watch out for**: MySQL silently drops words shorter than 3 characters and words appearing in >50% of rows in NATURAL LANGUAGE MODE.
- **Works with**: PostgreSQL 12+ (generated columns), PostgreSQL 9.1+ (basic tsvector), MySQL 5.7+ (InnoDB FULLTEXT), MySQL 5.6+ (MyISAM FULLTEXT).

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- PostgreSQL tsvector requires specifying a text search configuration (e.g., `'english'`) — using the wrong language dictionary silently drops valid stemmed tokens
- MySQL InnoDB FULLTEXT minimum token size is 3 characters by default — words like "Go", "AI", "C#" are excluded unless `innodb_ft_min_token_size` is changed (requires index rebuild)
- GIN indexes are read-optimized; they slow down writes — never use GIN on a table with >10K inserts/second without `fastupdate=on` (default) or consider GiST
- MySQL FULLTEXT in NATURAL LANGUAGE MODE silently returns zero results for terms in >50% of rows (the 50% threshold) — use BOOLEAN MODE to bypass this
- PostgreSQL `ts_rank()` does NOT use the GIN index for scoring — it reads the tsvector column for every matched row, so ranking always has I/O cost proportional to result set size
- pg_trgm similarity threshold defaults to 0.3 — failing to tune `pg_trgm.similarity_threshold` produces either too many false positives or too few results

## Quick Reference

| Scenario | Pattern | Index Type | Strengths | Limitations | Best For |
|---|---|---|---|---|---|
| PostgreSQL keyword search | `to_tsvector()` + `@@` + `to_tsquery()` | GIN | Stemming, ranking, language-aware, boolean ops | No fuzzy/typo tolerance | Structured natural-language queries |
| PostgreSQL phrase search | `to_tsvector()` + `@@` + `phraseto_tsquery()` | GIN | Word proximity, phrase matching | Exact phrase order required | "Near me" or multi-word lookups |
| PostgreSQL fuzzy search | `pg_trgm` `%` operator | GIN (gin_trgm_ops) | Typo-tolerant, LIKE/ILIKE acceleration | Higher index size, no stemming | Search-as-you-type, user input with typos |
| PostgreSQL combined | tsvector + pg_trgm fallback | GIN (both) | Best coverage: stemmed + fuzzy | Two indexes, more storage | Production search with autocomplete |
| PostgreSQL weighted search | `setweight()` + `ts_rank()` | GIN | Title > body boosting (A/B/C/D weights) | Ranking not index-accelerated | Relevance-ranked results |
| PostgreSQL generated column | `GENERATED ALWAYS AS (to_tsvector(...)) STORED` | GIN | No trigger needed, auto-maintained | PostgreSQL 12+ only | Clean, maintainable tsvector storage |
| MySQL natural language | `MATCH(...) AGAINST('...')` | FULLTEXT | Simple setup, built-in relevance | 50% threshold, 3-char minimum | Basic search on InnoDB tables |
| MySQL boolean mode | `MATCH(...) AGAINST('...' IN BOOLEAN MODE)` | FULLTEXT | +/- operators, no 50% threshold | No relevance ranking by default | Precise inclusion/exclusion queries |
| MySQL query expansion | `MATCH(...) AGAINST('...' WITH QUERY EXPANSION)` | FULLTEXT | Finds related terms automatically | Can return noisy results | Expanding vague search queries |
| MySQL multi-column | `FULLTEXT(col1, col2, col3)` | FULLTEXT | Single index across columns | All columns must be same table | Cross-field search |
| Elasticsearch (comparison) | REST API + inverted index | Lucene segments | Sub-10ms on billions of docs, fuzzy, facets | Separate infrastructure, data sync | >10M docs, complex relevance tuning |
| pg_trgm word similarity | `word_similarity()` + `<%` operator | GIN/GiST | Matches substrings, better for prefix search | PostgreSQL 9.6+ only | Autocomplete, partial word matching |

## Decision Tree

> Full script: [decision-tree.txt](scripts/decision-tree.txt) (28 lines)

```
START
├── Using PostgreSQL?
│   ├── YES
│   │   ├── Need typo/fuzzy tolerance?
│   │   │   ├── YES → Use pg_trgm with GIN(gin_trgm_ops) index (see Code Example 3)
# ... (see full script)
```

## Step-by-Step Guide

### 1. Create a tsvector column with GIN index (PostgreSQL)

Use a generated column (PostgreSQL 12+) to automatically maintain the tsvector. This avoids triggers and ensures the search vector stays in sync with source columns. [src1]

```sql
-- Step 1a: Add generated tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english',
      coalesce(title, '') || ' ' ||
      coalesce(body, '')
    )
  ) STORED;

-- Step 1b: Create GIN index for fast lookups
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
```

**Verify**: `SELECT count(*) FROM articles WHERE search_vector IS NOT NULL;` --> should equal total row count

### 2. Query with tsquery and rank results (PostgreSQL)

Use `websearch_to_tsquery()` (PostgreSQL 11+) for Google-style query parsing, or `plainto_tsquery()` for simpler input. Rank with `ts_rank()`. [src2]

```sql
SELECT
  id, title,
  ts_rank(search_vector, query) AS rank
FROM articles,
  websearch_to_tsquery('english', 'full text search postgresql') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
```

**Verify**: `EXPLAIN ANALYZE` on the query --> should show "Bitmap Index Scan" on `idx_articles_search`

### 3. Add field weighting for relevance (PostgreSQL)

Use `setweight()` to boost title matches (weight A) over body matches (weight D). Default weights: A=1.0, B=0.4, C=0.2, D=0.1. [src2]

```sql
-- Replace the generated column with weighted version
ALTER TABLE articles DROP COLUMN search_vector;
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'D')
  ) STORED;

-- Recreate GIN index
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
```

**Verify**: `SELECT ts_rank(search_vector, to_tsquery('english', 'search')) FROM articles WHERE title ILIKE '%search%' LIMIT 5;` --> ranks should be higher (~0.6+) than body-only matches (~0.1)

### 4. Create a FULLTEXT index and query (MySQL)

MySQL FULLTEXT works on VARCHAR, CHAR, and TEXT columns. InnoDB supports FULLTEXT from MySQL 5.7+. [src4]

```sql
-- Step 4a: Create FULLTEXT index
ALTER TABLE articles ADD FULLTEXT INDEX ft_articles (title, body);

-- Step 4b: Natural language search (default mode)
SELECT id, title,
  MATCH(title, body) AGAINST('full text search') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('full text search')
ORDER BY relevance DESC
LIMIT 20;
```

**Verify**: `EXPLAIN SELECT ... WHERE MATCH(title, body) AGAINST('full text search');` --> should show "fulltext" in type column

### 5. Use boolean mode for precise control (MySQL)

Boolean mode supports `+` (must include), `-` (must exclude), `*` (wildcard), and `"..."` (exact phrase). No 50% threshold applies. [src4]

```sql
-- Must contain 'postgresql', must not contain 'mysql', wildcard 'index*'
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+postgresql -mysql +index*' IN BOOLEAN MODE)
ORDER BY id DESC
LIMIT 20;
```

**Verify**: Results should contain 'postgresql' and not contain 'mysql' in title or body

### 6. Enable pg_trgm for fuzzy search (PostgreSQL)

The pg_trgm extension provides typo-tolerant search using trigram matching. Install the extension and create a GIN index with `gin_trgm_ops`. [src3]

```sql
-- Step 6a: Enable extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Step 6b: Create trigram index
CREATE INDEX idx_articles_trgm ON articles
  USING GIN (title gin_trgm_ops);

-- Step 6c: Fuzzy search query
SELECT id, title, similarity(title, 'postgrsql') AS sim
FROM articles
WHERE title % 'postgrsql'
ORDER BY sim DESC
LIMIT 10;
```

**Verify**: `SHOW pg_trgm.similarity_threshold;` --> default 0.3. `SELECT similarity('postgresql', 'postgrsql');` --> should return ~0.5+

## Code Examples

### PostgreSQL: Weighted multi-column search with ranking

```sql
-- Input:  Table with title and body text columns
-- Output: Ranked search results with relevance score

-- Create weighted 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, '')), 'D')
  ) STORED;

CREATE INDEX idx_fts ON articles USING GIN (search_vector);

-- Search with ranking
SELECT id, title,
  ts_rank(search_vector, q) AS rank,
  ts_headline('english', body, q,
    'StartSel=<b>, StopSel=</b>, MaxWords=35, MinWords=15'
  ) AS snippet
FROM articles,
  websearch_to_tsquery('english', 'database full text search') AS q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;
```

### MySQL: Boolean mode with relevance sorting

```sql
-- Input:  Table with FULLTEXT index on title and body
-- Output: Filtered results with relevance score

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

-- Boolean search with relevance score
SELECT id, title,
  MATCH(title, body) AGAINST('+"full text" +search -elasticsearch' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('+"full text" +search -elasticsearch' IN BOOLEAN MODE)
HAVING score > 0
ORDER BY score DESC
LIMIT 20;
```

### PostgreSQL: Combined tsvector + pg_trgm fallback search

```sql
-- Input:  User search query (may contain typos)
-- Output: Best results from exact FTS, falling back to fuzzy trigram

-- Requires both indexes exist (GIN on search_vector + GIN gin_trgm_ops on title)
WITH fts_results AS (
  SELECT id, title, ts_rank(search_vector, q) AS rank, 'fts' AS source
  FROM articles, websearch_to_tsquery('english', $1) AS q
  WHERE search_vector @@ q
  ORDER BY rank DESC LIMIT 20
),
trgm_results AS (
  SELECT id, title, similarity(title, $1) AS rank, 'trgm' AS source
  FROM articles
  WHERE title % $1
    AND id NOT IN (SELECT id FROM fts_results)
  ORDER BY rank DESC LIMIT 10
)
SELECT * FROM fts_results
UNION ALL
SELECT * FROM trgm_results
ORDER BY rank DESC;
```

### Python (psycopg2): Parameterized full-text search

```python
# Input:  User search string
# Output: Ranked search results as list of dicts

import psycopg2

def search_articles(conn, query: str, limit: int = 20) -> list[dict]:
    """Full-text search with parameterized query (SQL injection safe)."""
    sql = """
        SELECT id, title,
          ts_rank(search_vector, websearch_to_tsquery('english', %s)) AS rank,
          ts_headline('english', body, websearch_to_tsquery('english', %s),
            'StartSel=<b>, StopSel=</b>, MaxWords=35') AS snippet
        FROM articles
        WHERE search_vector @@ websearch_to_tsquery('english', %s)
        ORDER BY rank DESC
        LIMIT %s
    """
    with conn.cursor() as cur:
        cur.execute(sql, (query, query, query, limit))
        cols = [desc[0] for desc in cur.description]
        return [dict(zip(cols, row)) for row in cur.fetchall()]
```

## Anti-Patterns

### Wrong: Using LIKE '%term%' for text search

```sql
-- BAD -- full table scan on every query, no ranking, no stemming
SELECT * FROM articles
WHERE body LIKE '%database%' OR body LIKE '%search%';
```

### Correct: Use tsvector with GIN index

```sql
-- GOOD -- uses GIN index, supports stemming and ranking
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & search');
```

### Wrong: Computing tsvector inline on every query

```sql
-- BAD -- recomputes tsvector for every row on every query
SELECT *, ts_rank(to_tsvector('english', body), to_tsquery('english', 'search')) AS rank
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'search');
```

### Correct: Use a stored tsvector column with index

```sql
-- GOOD -- reads precomputed tsvector from stored column, GIN index filters rows
SELECT *, ts_rank(search_vector, to_tsquery('english', 'search')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'search');
```

### Wrong: MySQL NATURAL LANGUAGE MODE for high-frequency terms

```sql
-- BAD -- returns zero results if 'the' or common terms appear in >50% of rows
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('the best database' IN NATURAL LANGUAGE MODE);
```

### Correct: Use BOOLEAN MODE to bypass the 50% threshold

```sql
-- GOOD -- boolean mode has no frequency threshold
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+best +database' IN BOOLEAN MODE);
```

### Wrong: Using pg_trgm without setting similarity threshold

```sql
-- BAD -- default threshold 0.3 returns too many false positives for short strings
SELECT * FROM products WHERE name % 'cat';
-- Returns: "catalog", "category", "caterpillar", "concatenate" ...
```

### Correct: Tune the similarity threshold before querying

```sql
-- GOOD -- raise threshold for short queries to reduce noise
SET pg_trgm.similarity_threshold = 0.5;
SELECT *, similarity(name, 'cat') AS sim FROM products
WHERE name % 'cat' ORDER BY sim DESC;
```

### Wrong: Using plainto_tsquery for user input with operators

```sql
-- BAD -- user types "postgresql -mysql" but plainto_tsquery treats '-' as text
SELECT * FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql -mysql');
-- This searches for 'postgresql' AND 'mysql', ignoring the minus sign
```

### Correct: Use websearch_to_tsquery for Google-style input

```sql
-- GOOD -- websearch_to_tsquery handles -, "quotes", and OR naturally
SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'postgresql -mysql');
-- Correctly excludes rows containing 'mysql'
```

## Common Pitfalls

- **MySQL 3-character minimum**: Words shorter than 3 characters (InnoDB) or 4 characters (MyISAM) are silently dropped from the index. Fix: Set `innodb_ft_min_token_size = 1` in `my.cnf` and rebuild the FULLTEXT index with `ALTER TABLE t DROP INDEX ft_idx, ADD FULLTEXT INDEX ft_idx (col)`. [src5]
- **PostgreSQL wrong language config**: Using `'simple'` instead of `'english'` disables stemming, so "running" won't match "run". Using `'english'` on non-English text drops valid tokens. Fix: Match the config to your content language, or use `'simple'` for multilingual content where stemming is not needed. [src1]
- **MySQL 50% threshold in NATURAL LANGUAGE MODE**: If a search term appears in more than half the rows, MySQL returns zero results. Fix: Use `IN BOOLEAN MODE` instead, or filter with `WHERE` after `MATCH`. [src4]
- **GIN index bloat after bulk deletes**: GIN indexes accumulate dead entries that are not cleaned up by regular `VACUUM`. Fix: Run `REINDEX INDEX idx_name` or `VACUUM FULL table_name` periodically on tables with heavy churn. [src1]
- **Missing COALESCE on nullable columns**: `to_tsvector('english', NULL)` returns NULL, which silently excludes the row from all search results. Fix: Always wrap nullable columns in `coalesce(column, '')`. [src7]
- **ts_headline performance trap**: `ts_headline()` re-parses the original text to generate snippets — calling it on large text columns for hundreds of results is extremely slow. Fix: Apply `ts_headline()` only to the final limited result set, not in a subquery that processes all matches. [src2]
- **MySQL FULLTEXT on JOINs**: MySQL cannot use a FULLTEXT index if the query involves a JOIN and the optimizer chooses a different access path. Fix: Use a subquery to isolate the FULLTEXT search: `SELECT * FROM (SELECT id FROM articles WHERE MATCH(...) AGAINST(...)) AS sub JOIN details ON sub.id = details.article_id`. [src4]
- **pg_trgm index size**: Trigram GIN indexes are 2-5x larger than tsvector GIN indexes because they index all character trigrams, not just lexemes. Fix: Create trigram indexes only on columns users search by (e.g., `title`), not on large `body` columns. [src3]

## Diagnostic Commands

```sql
-- Check if GIN index is being used (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'search');
-- Look for: "Bitmap Index Scan on idx_articles_search"

-- Inspect tsvector content for a row (PostgreSQL)
SELECT to_tsvector('english', 'The quick brown foxes jumped') AS vector;
-- Output: 'brown':3 'fox':4 'jump':5 'quick':2

-- Check MySQL FULLTEXT index status
SHOW INDEX FROM articles WHERE Index_type = 'FULLTEXT';

-- View PostgreSQL text search configuration
\dF+ english
-- Or: SELECT * FROM ts_debug('english', 'running databases');

-- Check pg_trgm similarity between two strings
SELECT similarity('postgresql', 'postgrsql');
-- Output: ~0.53

-- Check MySQL InnoDB FULLTEXT settings
SHOW VARIABLES LIKE 'innodb_ft%';

-- View PostgreSQL GIN index size
SELECT pg_size_pretty(pg_relation_size('idx_articles_search')) AS index_size;
```

## Version History & Compatibility

| Version | Status | Key Features | Notes |
|---|---|---|---|
| PostgreSQL 17 (2024) | Current | Incremental JSON path, general improvements | FTS unchanged from 16 |
| PostgreSQL 16 (2023) | Supported | `websearch_to_tsquery` improvements | — |
| PostgreSQL 15 (2022) | Supported | MERGE command, JSON improvements | FTS stable |
| PostgreSQL 14 (2021) | Supported | Multirange types | FTS stable |
| PostgreSQL 12 (2019) | Supported | Generated columns for tsvector | Major FTS ergonomic improvement |
| PostgreSQL 11 (2018) | EOL | `websearch_to_tsquery()` added | Google-style query parsing |
| PostgreSQL 9.6 (2016) | EOL | `word_similarity()` in pg_trgm | Improved substring matching |
| MySQL 8.4 (2024) | LTS | InnoDB FULLTEXT stable | Recommended version |
| MySQL 8.0 (2018) | GA | Improved InnoDB FTS, CTE support | — |
| MySQL 5.7 (2015) | EOL | InnoDB FULLTEXT support (stable) | Previously MyISAM only |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Dataset < 10M rows and full-text is not the primary product feature | Search is the core product (e.g., document search engine) | Elasticsearch, Typesense, or Meilisearch |
| Already using PostgreSQL/MySQL and want to avoid operational complexity | Need sub-10ms p99 latency on >100M documents | Dedicated search infrastructure |
| Need stemming, ranking, and boolean operators in PostgreSQL | Need cross-language stemming with automatic language detection | Elasticsearch with language analyzers |
| Simple search page or admin filter on an existing application | Need faceted navigation, aggregations, or typeahead suggestions at scale | Elasticsearch or Algolia |
| Need typo-tolerant search on short fields (names, titles) in PostgreSQL | Need fuzzy search on large text bodies (articles, documents) | pg_trgm on titles + tsvector on body (hybrid) |
| MySQL application needing basic keyword search with boolean operators | Need phrase proximity search or field-level boosting in MySQL | PostgreSQL tsvector or Elasticsearch |

## Important Caveats

- PostgreSQL tsvector is language-dependent: switching from `'english'` to `'simple'` or another language config changes which tokens are indexed, potentially breaking existing queries — always rebuild indexes after changing the config
- MySQL FULLTEXT indexes cannot span multiple tables — a composite search across joined tables requires either denormalization or a subquery pattern
- GIN index updates use a "fastupdate" pending list by default — under heavy write load, the pending list can grow large and cause occasional slow searches; monitor with `SELECT * FROM pg_stat_user_indexes WHERE indexrelname = 'idx_name'`
- pg_trgm is not a replacement for tsvector — trigrams do not understand word boundaries, stemming, or stop words; use tsvector for natural language search and pg_trgm for fuzzy matching and autocomplete
- MySQL FULLTEXT relevance scores are not comparable across different queries — scores are query-specific and should only be used for ordering within a single result set

## Related Units

- [PostgreSQL Slow Query Debugging](/software/debugging/postgresql-slow-queries/2026)
- [Search Engine Architecture](/software/system-design/search-engine/2026)
- [MySQL Deadlock Debugging](/software/debugging/mysql-deadlocks/2026)