Full-Text Search Setup

sql Copy Code

-- PostgreSQL Full-Text Search with ranking and highlights

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Add search vector column
ALTER TABLE articles ADD COLUMN IF NOT EXISTS
    search_vector tsvector;

-- Populate search vector with weighted fields
UPDATE articles SET search_vector =
    setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(description, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(body, '')), 'C') ||
    setweight(to_tsvector('english', COALESCE(array_to_string(tags, ' '), '')), 'B');

-- Create GIN index for fast search
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Trigram index for fuzzy matching
CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);

-- Auto-update trigger
CREATE OR REPLACE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.body, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_articles_search
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

-- Search query with ranking and highlights
SELECT
    id, title,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', body, query,
        'StartSel=<mark>, StopSel=</mark>, MaxWords=35') AS snippet
FROM articles, plainto_tsquery('english', 'node.js authentication') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

full-text-search postgresql search indexing

javascript

MongoDB Connection with Retry

bash

MongoDB Backup Script

sql

User Analytics Query

sql

Efficient Pagination Query