Full-Text Search in PostgreSQL

tsvector, tsquery, ranking, trigram similarity, and search indexing

What You’ll Build

After following this guide, you will have a working implementation of full-text search in postgresql in your project. Build powerful full-text search directly in PostgreSQL — no Elasticsearch needed for most use cases. Covers tsvector/tsquery for full-text matching, pg_trgm for fuzzy/typo-tolerant search, ranking results by relevance, and GIN indexes for fast queries.

Use Cases & Problems Solved

• Set up a production-ready database layer with type-safe queries
• Manage schema changes through migrations without data loss
• Avoid raw SQL injection risks and inconsistent data access patterns

Prerequisites

• PostgreSQL 12+
• Table with text content to search

Step-by-Step Implementation

Enable extensions and add search column

The following snippet shows how to enable extensions and add search column. Copy this into your project and adjust the values for your environment.

-- Enable trigram extension for fuzzy search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Add a tsvector column for full-text search
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Populate the search vector
UPDATE posts SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(content, '')), 'B');

-- Create GIN index for fast search
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Auto-update on insert/update
CREATE TRIGGER posts_search_update BEFORE INSERT OR UPDATE
  ON posts FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

Search queries with ranking

The following snippet shows how to search queries with ranking. Copy this into your project and adjust the values for your environment.

-- Full-text search with relevance ranking
SELECT id, title,
  ts_rank(search_vector, plainto_tsquery('english', 'react hooks')) AS rank
FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'react hooks')
ORDER BY rank DESC
LIMIT 20;

-- Fuzzy search with trigram similarity (handles typos)
SELECT title, similarity(title, 'javscript') AS sim
FROM posts
WHERE title % 'javscript'  -- % operator uses pg_trgm
ORDER BY sim DESC LIMIT 10;

⚠️ Don’t Do This

-- EXTREMELY slow on large tables — full table scan every time!
SELECT * FROM posts WHERE content LIKE '%javascript%';

-- Uses the GIN index, handles word stemming, ranks by relevance
SELECT * FROM posts
WHERE search_vector @@ plainto_tsquery('english', 'javascript')
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'javascript')) DESC;

Testing

Verify your implementation with these tests:

// __tests__/full-text-search-in-postgresql.test.ts
import { describe, it, expect } from 'vitest';

describe('Full-Text Search in PostgreSQL', () => {
  it('should initialize without errors', () => {
    // Test that the setup completes successfully
    expect(() => setup()).not.toThrow();
  });

  it('should handle the primary use case', async () => {
    const result = await execute();
    expect(result).toBeDefined();
    expect(result.success).toBe(true);
  });

  it('should handle edge cases', async () => {
    // Test with empty/null input
    const result = await execute(null);
    expect(result.error).toBeDefined();
  });
});

Verification

-- Insert test data and search:
INSERT INTO posts (title, content) VALUES
  ('React Hooks Guide', 'Learn useState and useEffect...'),
  ('JavaScript Basics', 'Variables, functions, and loops...');

-- Search:
SELECT * FROM posts WHERE search_vector @@ plainto_tsquery('hooks');
-- Should return the React Hooks Guide