Full-Text Search: PostgreSQL, Elasticsearch, Meilisearch Implementation

Imagine an e-commerce store with 50,000 products where users can't find "wireless headphones" because only exact title matches are returned. We solve this with full-text search (FTS). For an electronics catalog of 120,000 SKUs, we implemented Elasticsearch with a Russian stemmer and facets. Average search time dropped from 3s to 0.1s, conversion increased by 30%. Over 5 years, we've completed 15+ FTS projects, reducing search time 10x and boosting conversion by 25%. Infrastructure cost savings reach 30% by offloading the database. Below, we break down how to choose and implement full-text search—from PostgreSQL FTS to external engines.

What problems does full-text search solve?

On large volumes, LIKE queries cause full table scans. With 100,000 products, response time exceeds 5 seconds, losing up to 40% of users. FTS uses inverted indexes to find records in milliseconds. Additionally, FTS ranks results by relevance: titles get more weight than descriptions, fresh records rank higher. This increases search conversion by 25% and reduces database load.

Which architecture to choose: built-in FTS or external engine?

The choice depends on data volume and feature requirements. For a typical catalog up to 100,000 records, PostgreSQL FTS suffices. For complex scenarios (fuzzy search, facets, synonyms), Elasticsearch or Meilisearch is needed.

PostgreSQL FTS: built-in option

Schema setup:

ALTER TABLE products ADD COLUMN search_vector TSVECTOR GENERATED ALWAYS AS ( to_tsvector('russian', coalesce(title, '') || ' ' || coalesce(description, '') || ' ' || coalesce(brand, '') ) ) STORED; CREATE INDEX idx_products_fts ON products USING GIN (search_vector); 

GENERATED ALWAYS AS ... STORED — the column updates automatically on INSERT/UPDATE, no trigger needed.

Search with ranking and snippets:

-- Simple query SELECT id, title, ts_rank(search_vector, query) AS rank, ts_headline('russian', description, query, 'MaxWords=30, MinWords=15, StartSel=<b>, StopSel=</b>' ) AS excerpt FROM products, plainto_tsquery('russian', 'беспроводные наушники') AS query WHERE search_vector @@ query ORDER BY rank DESC LIMIT 20; -- websearch_to_tsquery: supports "phrases", -exclusions, OR SELECT id, title FROM products WHERE search_vector @@ websearch_to_tsquery('russian', '"беспроводные наушники" -проводные') ORDER BY ts_rank(search_vector, websearch_to_tsquery('russian', '"беспроводные наушники" -проводные')) DESC; 

ts_headline generates a snippet with highlighted matches. More details in PostgreSQL.

Elasticsearch: when an external engine is needed

PostgreSQL FTS has limitations: no fuzzy search, no built-in synonyms, no faceted aggregations. If these are needed, use Elasticsearch or OpenSearch.

Index schema with Russian analyzer:

PUT /products { "settings": { "analysis": { "analyzer": { "russian_analyzer": { "type": "custom", "tokenizer": "standard", "filter": ["lowercase", "russian_stop", "russian_stemmer"] } }, "filter": { "russian_stop": { "type": "stop", "stopwords": "_russian_" }, "russian_stemmer": { "type": "stemmer", "language": "russian" } } } }, "mappings": { "properties": { "title": { "type": "text", "analyzer": "russian_analyzer", "boost": 3 }, "brand": { "type": "text", "analyzer": "russian_analyzer", "boost": 2 }, "description": { "type": "text", "analyzer": "russian_analyzer" }, "category_id": { "type": "keyword" }, "price": { "type": "double" }, "status": { "type": "keyword" }, "created_at": { "type": "date" } } } } 

Search with facets and filters:

POST /products/_search { "query": { "bool": { "must": { "multi_match": { "query": "беспроводные наушники", "fields": ["title^3", "brand^2", "description"], "type": "best_fields", "fuzziness": "AUTO" } }, "filter": [ { "term": { "status": "published" } }, { "range": { "price": { "gte": 1000, "lte": 15000 } } } ] } }, "aggs": { "by_brand": { "terms": { "field": "brand.keyword", "size": 20 } }, "price_stats": { "stats": { "field": "price" } } }, "highlight": { "fields": { "title": { "number_of_fragments": 0 }, "description": { "fragment_size": 150, "number_of_fragments": 3 } } }, "from": 0, "size": 20 } 

Synchronization with PostgreSQL via CDC (Debezium + Kafka) ensures data reaches ES even during service failures. Documentation at Elasticsearch covers all features.

How to ensure data synchronization between PostgreSQL and Elasticsearch?

For syncing changes, we use Change Data Capture (CDC) with Debezium and Kafka. Debezium monitors PostgreSQL WAL changes and publishes events to Kafka, from which Elasticsearch (via Logstash or a custom consumer) receives updates. Alternatively, a direct API service periodically reads changes by timestamp. CDC is more reliable: it doesn't lose data during failures and requires no extra queries to the database. To set up CDC, enable WAL logging (wal_level = logical) and create a publication for the tables.

Why implement full-text search immediately?

Users leave if they can't find a product within 3 seconds. Full-text search lifts search conversion by 25% and reduces database load 10x. Our Elastic-certified engineers help choose the optimal solution for your stack. Contact us—we'll assess your project for free and recommend the best approach.

How we do it: a case study

We implemented search for a catalog of 120,000 products in 3 days. We used Elasticsearch with a Russian stemmer and facets by brand, price, and status. Result: average search time dropped from 3s to 0.1s, conversion increased by 30%.

Work process: step-by-step plan

Typical mistakes

• Using LIKE for searching large tables (slow, no morphology).
• Not weighting fields (title and description ranked equally).
• Ignoring normalization (handling word endings and cases).

Engine comparison

curl -X POST 'http://localhost:7700/indexes' \ -H 'Content-Type: application/json' \ -d '{ "uid": "products", "primaryKey": "id" }'