Adding Full-Text Search to Your Portfolio with PostgreSQL

Implementing full-text search can significantly enhance the user experience of your portfolio or blog. This post details how to add PostgreSQL-powered full-text search to a Laravel application, focusing on accent-insensitive prefix searching across multiple fields.

The Problem: Basic Search Limitations

Simple LIKE queries in SQL often fall short when dealing with complex search requirements. They are case-sensitive, don't handle stemming or synonyms, and can be slow on large datasets. For a portfolio site, users expect to find relevant posts quickly, even with typos or partial keywords.

The Solution: PostgreSQL Full-Text Search

PostgreSQL offers robust full-text search capabilities that address these limitations. The key components are:

• tsvector: A data type representing a document optimized for text search.
• GIN index: An index that speeds up searches on tsvector columns.
• to_tsquery: A function that parses a search query into a tsquery value, which can be used to match documents.
• unaccent: A function that removes accents from text, enabling accent-insensitive searches.

Implementation Steps

1. Add a searchable column:

   Add a searchable column of type tsvector to your posts table. This column will store the indexed text data.

2. Create a GIN index:

   Create a GIN index on the searchable column to enable fast searches:

   CREATE INDEX posts_searchable_index ON posts USING GIN (searchable);
   

3. Create a trigger:

   Create a trigger that automatically updates the searchable column whenever a post is created or updated. This trigger combines the title and content of the post into a single tsvector:

   CREATE OR REPLACE FUNCTION update_post_searchable() RETURNS TRIGGER AS $$
   BEGIN
     NEW.searchable := to_tsvector('simple', public.unaccent(NEW.title || ' ' || NEW.content));
     RETURN NEW;
   END
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER posts_searchable_update BEFORE INSERT OR UPDATE
   ON posts FOR EACH ROW EXECUTE PROCEDURE update_post_searchable();
   

   This trigger uses the to_tsvector function with the simple configuration (which provides basic tokenization) and the unaccent function to remove accents. It concatenates the title and content fields for a comprehensive search.

4. Implement the search scope in Laravel:

   Add a scope to your Post model that uses the full-text search capabilities:

   use Illuminate\Database\Eloquent\Builder;
   
   public function scopeFullTextSearch(Builder $query, string $search): Builder
   {
       $search = trim($search);
   
       if ($search === '') {
           return $query;
       }
   
       $sanitized = preg_replace('/[^\p{L}\p{N}\s]/u', '', $search) ?? '';
       /** @var array<string> $words */
       $words = array_filter(
           preg_split('/\s+/', $sanitized) ?: [],
           fn (string $word): bool => $word !== '',
       );
   
       if ($words === []) {
           return $query;
       }
   
       $tsquery = implode(' & ', array_map(fn (string $word): string => $word.':*', $words));
   
       return $query->whereRaw(
           'searchable @@ to_tsquery(\'simple\', public.unaccent(?))',
           [$tsquery]
       );
   }
   

   This scope takes a search string, sanitizes it, splits it into words, and then uses the to_tsquery function to create a search query. The @@ operator is used to match the searchable column against the query.

5. Integrate search UI:

   Integrate the search functionality into your portfolio's UI. Ensure that the search input field triggers the fullTextSearch scope in your Laravel controller.

Benefits

• Improved Search Accuracy: Full-text search provides more relevant results compared to simple LIKE queries.
• Accent-Insensitive Search: The unaccent function ensures that searches work regardless of accents.
• Prefix Search: The :* operator in the tsquery allows for prefix-based searches.
• Performance: The GIN index significantly speeds up search queries.

Conclusion

By leveraging PostgreSQL's full-text search capabilities, you can provide a superior search experience for your portfolio or blog users. This approach offers improved accuracy, accent insensitivity, and performance, leading to a more engaging and user-friendly experience. Remember to keep the searchable column updated using a trigger to ensure that your search results are always accurate.