Blog Article Search with Ranking

advanced

Blog platform needs to search articles by keywords. Traditional approach: LIKE operator (slow, no ranking). Problem: LIKE is slow for large tables, no relevance ranking. Solution: Use PostgreSQL Full-Text Search with GIN index and ranking.

-- Problem: LIKE operator approach

CREATE TABLE articles_traditional (

article_id SERIAL PRIMARY KEY,

title VARCHAR(200),

content TEXT

);

-- Query with LIKE (slow, no ranking)

SELECT title, content

FROM articles_traditional

WHERE content LIKE '%postgresql%' OR content LIKE '%database%';

-- Problems: (1) Sequential scan (slow), (2) No ranking, (3) No stemming (misses "databases")

-- Solution: Full-Text Search with GIN index

CREATE TABLE articles (

article_id SERIAL PRIMARY KEY,

title VARCHAR(200),

content TEXT,

created_at TIMESTAMP DEFAULT NOW()

);

-- Add generated column for tsvector (better performance)

ALTER TABLE articles

ADD COLUMN search_vector tsvector

GENERATED ALWAYS AS (

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

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

) STORED;

-- Weight A for title (higher priority), Weight B for content

-- Create GIN index

CREATE INDEX idx_articles_search_vector

ON articles

USING GIN (search_vector);

-- Insert sample articles

INSERT INTO articles (title, content) VALUES

('PostgreSQL Full-Text Search Tutorial', 'Learn how to implement full-text search in PostgreSQL database. PostgreSQL provides powerful search capabilities.'),

('MySQL vs PostgreSQL Comparison', 'Compare MySQL and PostgreSQL databases. Both are popular relational databases.'),

('Database Design Best Practices', 'Best practices for designing databases. Learn about normalization and indexing.');

-- Search for "postgresql database"

SELECT