The Challenge of Arabic Search in Syrian E-Commerce

As digital commerce and online services expand in Syria, building a seamless user experience is critical for customer retention. A core element of this experience is the search bar. When a customer searches an e-commerce platform or business directory, they expect immediate, accurate results, regardless of typos, diacritics, or grammatical variations.

However, standard SQL search methods like LIKE '%query%' fall short in Arabic. Standard queries are slow, cannot rank results by relevance, and fail to handle the unique linguistic characteristics of the Arabic language, such as:

  • Orthographic Variations: Users frequently interchange letters like Alif-Hamza variations (أ, إ, آ) with plain Alif (ا), Te-Marbuta (ة) with He (ه), and Ya (ي) with Alif-Maqsura (ى).
  • Diacritics (Tashkeel): Characters like Fatha (َ), Damma (ُ), Kasra (ِ), and Shadda (ّ) are often stored in product catalogs but rarely typed by customers in search fields.
  • Prefixes and Suffixes: Definite articles like "Al-" (ال) or conjunctions attached to nouns alter the word structure, making exact match queries fail.

While dedicated search engines like Elasticsearch or Solr solve these problems, they require significant system memory (RAM) and separate server infrastructure. For Syrian startups and small-to-medium enterprises (SMEs) running on budget-friendly cloud virtual machines (VMs) with 1GB or 2GB of RAM, hosting a Java-based search stack alongside an application server is often cost-prohibitive.

PostgreSQL offers a powerful, built-in alternative: Full-Text Search (FTS). By leveraging PostgreSQL's native FTS capabilities, you can build a fast, scalable, and linguistically accurate Arabic search engine that runs directly within your primary database, requiring zero additional infrastructure.

Here is a step-by-step technical guide to implementing Arabic Full-Text Search in PostgreSQL.

---

Step 1: Writing an Arabic Text Normalization Function

To ensure search terms match database content regardless of formatting differences, we must normalize the Arabic text. Normalization strips diacritics and maps letters with common spelling variations to a unified base form.

Create the following immutable PL/pgSQL function in your PostgreSQL database:

CREATE OR REPLACE FUNCTION normalize_arabic(input_text TEXT)
RETURNS TEXT AS $$
DECLARE
    normalized TEXT;
BEGIN
    IF input_text IS NULL THEN
        RETURN '';
    END IF;

    -- Convert to lowercase and trim whitespace
    normalized := lower(trim(input_text));

    -- 1. Remove Arabic Diacritics (Tashkeel)
    -- Covers Fatha, Damma, Kasra, Fathatayn, Dammatayn, Kasratayn, Sukun, and Shadda
    normalized := regexp_replace(normalized, '[\u064B-\u0652\u0653\u0670]', '', 'g');

    -- 2. Normalize Alif variations (أ, إ, آ to plain ا)
    normalized := regexp_replace(normalized, '[أإآ]', 'ا', 'g');

    -- 3. Normalize Te-Marbuta to He (ة to ه)
    -- Helps match "أجهزة" with "اجهزه"
    normalized := regexp_replace(normalized, 'ة', 'ه', 'g');

    -- 4. Normalize Alif-Maqsura to Ya (ى to ي)
    -- Helps match "مشفى" with "مشفي"
    normalized := regexp_replace(normalized, 'ى', 'ي', 'g');

    -- 5. Strip common prefixes (Optional but helpful for e-commerce)
    -- Strips the definite article "ال" (Al-) if it appears at the start of a word
    -- We use word boundary matching (\y in Postgres regex)
    normalized := regexp_replace(normalized, '\yال([أ-ي]+)', '\1', 'g');

    RETURN normalized;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

> [!TIP] > Marking the function as IMMUTABLE is required. It tells PostgreSQL that the function will always return the same output for a given input, allowing it to be used safely in index expressions and generated columns.

---

Step 2: Creating a Custom Search Configuration

PostgreSQL uses text search configurations to parse document strings into tokens and run them through dictionaries. Since standard PostgreSQL installations might not include a native Arabic stemmer, or your database provider may restrict compiling third-party libraries, we will create a dedicated configuration that inherits the built-in simple parser and applies our custom normalization function.

Run the following SQL commands to define your custom Arabic search configuration:

-- 1. Create a new search configuration copied from 'simple'
-- The simple configuration converts text to lowercase and splits it into tokens
CREATE TEXT SEARCH CONFIGURATION arabic_normalized (COPY = simple);

-- 2. Map standard token types to use the simple dictionary
-- This ensures words are passed through without incorrect English stemming
ALTER TEXT SEARCH CONFIGURATION arabic_normalized
    ALTER MAPPING FOR asciiword, word, numword
    WITH simple;

---

Step 3: Schema Design with Generated Search Vectors

To maintain high performance, you should never run the normalization function or calculate the tsvector on the fly during search queries. Doing so forces the database to perform full table scans, which degrades performance as your product catalog grows.

Instead, store the pre-calculated search vector directly inside your table. Using PostgreSQL 12 or newer, you can implement a GENERATED ALWAYS AS ... STORED column that automatically updates whenever a product row is inserted or updated.

Here is an example schema for an e-commerce products table:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    category VARCHAR(100),
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- Automatically generated search vector combining title, category, and description
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('arabic_normalized', 
            coalesce(normalize_arabic(title), '') || ' ' ||
            coalesce(normalize_arabic(category), '') || ' ' ||
            coalesce(normalize_arabic(description), '')
        )
    ) STORED;
);

For databases running on versions older than PostgreSQL 12, you can achieve the same result using a standard table column and a database trigger:

-- For PostgreSQL < 12
ALTER TABLE products ADD COLUMN search_vector tsvector;

CREATE OR REPLACE FUNCTION products_search_vector_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := to_tsvector('arabic_normalized', 
        coalesce(normalize_arabic(NEW.title), '') || ' ' ||
        coalesce(normalize_arabic(NEW.category), '') || ' ' ||
        coalesce(normalize_arabic(NEW.description), '')
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_search_vector_update
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION products_search_vector_trigger();

---

Step 4: Indexing the Search Vector for Fast Scans

To enable sub-millisecond searches, you must index the search_vector column. The best index type for full-text search is a GIN (Generalized Inverted Index). GIN indexes map individual tokens directly to the rows containing them, allowing the database to bypass row-by-row scanning entirely.

Run the following command to index your table:

CREATE INDEX idx_products_search_vector ON products USING gin(search_vector);

With the GIN index in place, PostgreSQL can retrieve search matches in fractions of a millisecond, even when querying catalogs containing hundreds of thousands of items.

---

Step 5: Implementing Search Queries and Relevance Ranking

When writing search queries, the raw search string entered by the user must pass through the same normalization pipeline before hitting the database.

To construct a safe, well-formatted query, write a helper function that converts space-separated search keywords into a logical AND (&) search term:

CREATE OR REPLACE FUNCTION parse_arabic_query(search_query TEXT)
RETURNS tsquery AS $$
DECLARE
    clean_query TEXT;
    formatted_query TEXT;
BEGIN
    -- Normalize user input
    clean_query := normalize_arabic(search_query);
    
    -- Replace consecutive spaces with the '&' operator
    -- e.g., "اجهزة ذكية" becomes "اجهزه & ذكيه"
    formatted_query := regexp_replace(trim(clean_query), '\s+', ' & ', 'g');
    
    RETURN to_tsquery('arabic_normalized', formatted_query);
EXCEPTION
    -- Fallback in case of syntax issues
    WHEN OTHERS THEN
        RETURN plainto_tsquery('arabic_normalized', search_query);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Now, write your search query using the parsing function, rank the results by match density, and generate dynamic search snippets that highlight the matching terms:

SELECT 
    id,
    sku,
    title,
    price,
    -- Rank matches using Cover Density ranking (higher score for closer terms)
    ts_rank_cd(search_vector, query) AS relevance,
    -- Generate an HTML snippet highlighting the match in the description
    ts_headline('arabic_normalized', description, query, 
        'StartSel = <span class="highlight">, StopSel = </span>, MaxWords=25, MinWords=15'
    ) AS snippet
FROM 
    products, 
    parse_arabic_query('أجهزة ذكية') query
WHERE 
    search_vector @@ query
ORDER BY 
    relevance DESC, 
    created_at DESC
LIMIT 12;

---

Evaluating Search Behavior

Let's compare the performance of standard search query patterns against this optimized system:

| User Query | Match Status (Standard SQL LIKE) | Match Status (Optimized PostgreSQL FTS) | Rationale | | :--- | :--- | :--- | :--- | | اجهزه | ❌ Fails (Stored as الأجهزة with Hamza and "Al-") | ✅ Matches (Both normalized to اجهزه and stripped of "Al-") | Normalizes prefixes and orthographic variations. | | أَجْهِزَةٌ | ❌ Fails (Stored without Tashkeel) | ✅ Matches (Strips diacritics during normalization) | Removes Arabic Tashkeel from both database and query. | | ذكية | ❌ Fails (Stored as ذكيه with He) | ✅ Matches (Normalizes ة to ه) | Addresses spelling inconsistencies at the database level. |

By utilizing this implementation, your search engine handles the intricacies of Arabic orthography seamlessly, ensuring that search results remain highly accurate without imposing strict spelling requirements on your customers.

---

Conclusion and Next Steps

Building a resilient, localized search engine does not require introducing complex, resource-heavy software layers. For applications operating in environments with limited hardware budgets, PostgreSQL's native Full-Text Search provides an efficient, production-ready solution that combines database reliability with advanced linguistic processing.

If you are developing custom software, a business dashboard, or an e-commerce platform in Syria, choosing the right database architecture is key to achieving long-term speed and stability.

Are you looking to optimize your existing database performance, integrate secure local payment gateways, or build a bespoke web application tailored to your business needs? Contact Dragonfly Soft today to consult with our engineering team in Aleppo and plan your next technical project.