Skip to content
Nizar Abubaker
العربية
العربية

Note1 min read

Try PostgreSQL search before adding a search engine

A generated tsvector column, a GIN index and a prefix query cover a surprising amount of ground — in two languages.

Before adding a search cluster, check what the database you already run can do. PostgreSQL ships text-search configurations for both English and Arabic, and a generated column keeps the index in sync without triggers:

sql
search_vector tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector(CASE WHEN locale = 'ar' THEN 'arabic'::regconfig
                             ELSE 'english'::regconfig END, coalesce(title, '')), 'A') ||
  setweight(to_tsvector(CASE WHEN locale = 'ar' THEN 'arabic'::regconfig
                             ELSE 'english'::regconfig END, coalesce(summary, '')), 'B')
) STORED

Queries are built from user input by keeping only letters and digits and adding a prefix operator to each word — enter integ becomes enter:* & integ:* — so typing feels instant and nothing the user types can inject query syntax.

The search sits behind a small interface. If the site ever needs typo tolerance or faceting at scale, a dedicated engine can replace it without touching the pages that call it.