Efficient Full-Text Search with `tsvector` and `tsquery` in PostgreSQL

-- Create a sample table with a text column
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector TSVECTOR
);

-- Insert sample data
INSERT INTO documents (title, content) VALUES
('PostgreSQL Features', 'PostgreSQL is a powerful, open-source object-relational database system with an emphasis on extensibility and standards compliance.'),
('Web Development Basics', 'HTML, CSS, and JavaScript are the core technologies for web development. Frameworks like React and Vue simplify development.'),
('Database Indexing', 'Indexing improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space.');

-- Create a GIN index on the search_vector for performance
CREATE INDEX idx_search_vector ON documents USING GIN(search_vector);

-- Update search_vector column using `to_tsvector` function
-- This function parses a document into a list of lexemes (words)
UPDATE documents
SET search_vector = to_tsvector('english', title || ' ' || content);

-- Create a trigger to automatically update search_vector on insert/update
CREATE OR REPLACE FUNCTION update_search_vector_trigger() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector = to_tsvector('english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION update_search_vector_trigger();

-- Perform a full-text search
-- `to_tsquery` converts user input into a search query
-- `@@` is the match operator
SELECT id, title, content
FROM documents
WHERE search_vector @@ to_tsquery('english', 'database & system');

-- Search for "web development" OR "react"
SELECT id, title, content
FROM documents
WHERE search_vector @@ to_tsquery('english', 'web | react');

-- Order results by relevance (rank)
SELECT id, title, ts_rank(search_vector, to_tsquery('english', 'database & indexing')) AS rank
FROM documents
WHERE search_vector @@ to_tsquery('english', 'database & indexing')
ORDER BY rank DESC;