PostgreSQL Full-Text Search provides built-in search without external services. Real examples: (1) Blog search: Search articles by keywords. Medium uses PostgreSQL FTS for article search (no Elasticsearch). (2) Product search: E-commerce sites search products. Shopify uses PostgreSQL FTS for product search (< 1M products). (3) Documentation search: Search docs by keywords. GitLab uses PostgreSQL FTS for issue/MR search (fast, simple). (4) Job search: Search jobs by title/description. Indeed uses PostgreSQL FTS for job search (stemming, ranking). Trade-offs: PostgreSQL FTS is simpler than Elasticsearch (no separate service) but less powerful (no advanced features like fuzzy search, synonyms). Good for < 1M documents, use Elasticsearch for > 1M documents or advanced features.

Medium: Article Search with Full-Text Search

Medium has millions of articles. Challenge: Provide fast article search without Elasticsearch complexity. Solution: Use PostgreSQL Full-Text Search with GIN indexes and ranking.

Medium uses PostgreSQL FTS for article search: (1) Generated column with weighted search (title weight A, content weight B), (2) GIN index for fast queries (100x faster), (3) ts_rank_cd() for ranking (considers article length), (4) ts_headline() for snippets. Architecture: articles table with search_vector tsvector, GIN index on search_vector. Benefits: No Elasticsearch needed (simpler), fast queries (< 50ms), good for < 1M articles.

CREATE TABLE articles (

article_id BIGSERIAL PRIMARY KEY,

title VARCHAR(200),

content TEXT,

search_vector tsvector GENERATED ALWAYS AS (

setweight(to_tsvector('english', title), 'A') ||

setweight(to_tsvector('english', content), 'B')

) STORED

);

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

SELECT title, ts_rank_cd(search_vector, query) as rank

FROM articles, to_tsquery('english', 'postgresql') query

WHERE search_vector @@ query

ORDER BY rank DESC LIMIT 10;

Millions of articles, < 1M active

PostgreSQL FTS: No Elasticsearch needed

GIN index: 100x faster queries

Weighted search: Title ranked higher

Lesson: PostgreSQL FTS for < 1M documents

PostgreSQL

Shopify: Product Search with Multi-Language Support

Shopify has millions of products in multiple languages. Challenge: Search products with language-specific stemming. Solution: Use language-specific text search configurations.

Shopify uses multi-language FTS for product search: (1) Language column stores product language, (2) Generated column with CASE statement applies language-specific config, (3) GIN index covers all languages, (4) Search filters by language. Architecture: products table with language column, search_vector with CASE for language-specific config. Benefits: Language-specific stemming (proper results), single GIN index (efficient), fast queries.

CREATE TABLE products (

product_id BIGSERIAL PRIMARY KEY,

name VARCHAR(200),

description TEXT,

language VARCHAR(10),

search_vector tsvector GENERATED ALWAYS AS (

CASE language

WHEN 'english' THEN to_tsvector('english', name || ' ' || description)

WHEN 'spanish' THEN to_tsvector('spanish', name || ' ' || description)

ELSE to_tsvector('simple', name || ' ' || description)

END

) STORED

);