PostgreSQL Full-Text Search

Learn tsvector, tsquery, GIN indexes, ranking, and multi-language search.

Core

1. tsvector (document)

• Tokenizes text
• Removes stop words (the, is, a)
• Applies stemming

SELECT to_tsvector('english', 'PostgreSQL is powerful');
-- 'postgresql':1 'power':3

2. tsquery (search query)

SELECT to_tsquery('english', 'power & postgres');

Operators:

& → AND
| → OR
! → NOT

3. Matching (@@)

SELECT to_tsvector('english', 'PostgreSQL is powerful')
@@ to_tsquery('english', 'power');
-- true

4. GIN Index (performance)

CREATE INDEX idx_search ON articles
USING GIN(to_tsvector('english', content));

5. Ranking results

SELECT title,
ts_rank(to_tsvector(content), to_tsquery('search')) AS rank
FROM articles
ORDER BY rank DESC;

6. Multi-language support

SELECT to_tsvector('french', 'bonjour le monde');

Configs:

• english
• simple
• french
• german
• custom

Example

-- Table
CREATE TABLE articles (
  id SERIAL,
  title TEXT,
  content TEXT
);

-- Index
CREATE INDEX idx_fts ON articles
USING GIN(to_tsvector('english', content));

-- Search
SELECT id, title
FROM articles
WHERE to_tsvector('english', content)
      @@ to_tsquery('english', 'database & performance');

Best Practice

• Use GIN index (not sequential scan)
• Store precomputed tsvector column for large data
• Use plainto_tsquery() for user input (safe parsing)
• Combine with ranking (ts_rank)
• Use correct language config
• Avoid calling to_tsvector() repeatedly in queries