1. Tokenization: Text is split into words or lexemes using the to_tsvector() function.

SELECT to_tsvector('english', 'PostgreSQL is a powerful database system');                        

Result: ‘databas’:5 ‘power’:4 ‘postgresql’:1 ‘system’:6

2. Lexeme Normalization: Stem words to their base form and removes stop words (e.g., “is”, “the”).

3. Storage with tsvector: PostgreSQL stores normalized tokens as a tsvector, optionally with positions.

ALTER TABLE products ADD COLUMN fts_doc tsvector;
UPDATE products SET fts_doc = to_tsvector('english', name || ' ' || description);                 

Now, search queries are much faster!

4.Search with to_tsquery() or websearch_to_tsquery(): Finds matches based on lexemes and operators.

SELECT * FROM products WHERE fts_doc @@ to_tsquery('english', 'table & wood');                    

Finds “wooden table”, “tables made of wood”, etc.

5. Ranking and Indexing: Results are ranked and can be indexed using GIN (for speed) or GiST (for flexibility).

SELECT *, ts_rank(fts_doc, to_tsquery('english', 'table & wood')) AS rank FROM products ORDER BY rank DESC;

Higher-ranked results appear at the top.

• Imagine you have the following input text: When you convert this text into a tsvector (assuming the English text search configuration).

"PostgreSQL is a powerful database system."                                                          

• Converted tsvector:

'databas':5 'power':4 'postgresql':1 'system':6                                                   

• What’s happening here?

1. Words like “is” and “a” are considered stop words and are removed.
2. The remaining important words are:
   • Postgresql
   • Power
   • Database
   • System
3. Each word is stored with its position in the sentence:
   • ‘postgresql’:1 – PostgreSQL is the 1st word
   • ‘power’:4 – Power is the 4th word
   • etc.

1. Create a tsvector Column: Adds a new column named fts_doc to the item table. The column’s data type is tsvector, which is a special format optimized for searching text efficiently.

ALTER TABLE item ADD COLUMN fts_doc tsvector;                                                     

2. Create an Index for Faster Searches: Creates an index using GIN (Generalized Inverted Index) on the fts_doc column. Without an index, searching will require full table scans, making it very slow for large tables.

CREATE INDEX idx_fts_doc ON item USING GIN (fts_doc);                                             

3. Create a function that automatically updates the tsvector on Insert/Update: Creates a PostgreSQL function that auto-generates a tsvector for every new or updated row. It combines multiple text columns (name, description, item_category.name) into one searchable field. COALESCE() ensures NULL values are treated as empty strings to avoid errors.

CREATE OR REPLACE FUNCTION update_fts_doc_item()                                                  
RETURNS TRIGGER AS $$
BEGIN
  NEW.fts_doc := to_tsvector('english',
    COALESCE(NEW.name, '') || ' ' || 
    COALESCE(NEW.description, '') || ' ' || 
    COALESCE((SELECT ic.name FROM item_category ic WHERE ic.id = NEW.item_category), '')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

4. Attach a Trigger to Keep Data Updated: Creates a trigger that fires before every INSERT or UPDATE on the item table. This trigger calls the update_fts_doc_item() function automatically. Ensures that the fts_doc column is always updated whenever the row changes.

CREATE TRIGGER trigger_update_fts                                                                  
BEFORE INSERT OR UPDATE ON item
FOR EACH ROW EXECUTE FUNCTION update_fts_doc_item();

5. Perform a Search: Uses websearch_to_tsquery() to convert the search phrase into a form that can match the fts_doc. @@ is the match operator in PostgreSQL Full-Text Search. You can now perform Google-like natural searches instead of complicated SQL LIKE queries. websearch_to_tsquery() automatically understands AND, OR, and phrase search without needing special syntax.

SELECT * FROM item WHERE fts_doc @@ websearch_to_tsquery('english', 'reclining sofa');             

SELECT * FROM item WHERE fts_doc @@ websearch_to_tsquery('english', 'modern sofa');

No need for &, |, or ! operators!

1. Edit: /usr/share/pgsql/tsearch_data/english.stop(For Linux).
2. Add the words you want to stop in english.stop file.
3. Restart PostgreSQL to apply changes.

furniture                                                                                         
color

1. Create a Synonym Dictionary File in a ‘$SHAREDIR/tsearch_data/my_synonyms.syn’ location (where $SHAREDIR means the PostgreSQL installation’s shared-data directory). The file format is just one line per word to be substituted, with the word followed by its synonym, separated by white space. Blank lines and trailing spaces are ignored. For example:

couch sofa                                                                                       
settee sofa
Knole sofa
seat chair
timepiece clock

3. Define a Synonym Dictionary in PostgreSQL: You can then define a custom dictionary in PostgreSQL that uses this synonym file.

CREATE TEXT SEARCH DICTIONARY synonym_dict(                                                       
	template=synonym, 
	synonyms='my_synonyms'
);

4. Create a Text Search Configuration with the custom Synonym Dictionary and the default dictionary. Next, you create a text search configuration that uses the synonym dictionary.

CREATE TEXT SEARCH CONFIGURATION custom_search_configuration (copy=english);                      

ALTER TEXT SEARCH CONFIGURATION custom_search_configuration 
	ALTER MAPPING FOR asciiword WITH english_stem, synonym_dict;

5. Check your changes:

SELECT ts_lexize('synonym_dict', 'settee');
SELECT to_tsvector('custom_search_configuration', 'this is my timepiece');