Full-Text Search: PostgreSQL tsvector, Elasticsearch, and Meilisearch Compared

Editorial Team · · 3 min read

Three Approaches, One Problem

Full-text search seems simple until you try to implement it. Users expect Google-quality results from a search box that queries your application's data. The gap between WHERE title LIKE '%search term%' and a proper full-text search engine is enormous — relevance ranking, stemming, typo tolerance, faceted filtering, and sub-50ms response times.

There are three main approaches: use what your database already has (PostgreSQL's built-in tsvector), deploy a dedicated search engine (Elasticsearch), or use a newer lightweight alternative (Meilisearch). Each hits a different sweet spot.

PostgreSQL tsvector: Good Enough for Most

PostgreSQL's full-text search is surprisingly capable. It handles stemming, ranking, phrase matching, and boolean queries. And since it's built into your existing database, there's no additional infrastructure to manage.

-- Add a tsvector column and GIN index
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(excerpt, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'C');

CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

-- Keep it updated with a trigger
CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.excerpt, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(NEW.body, '')), 'C');
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trig_search_vector
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

The weight assignments (A, B, C, D) let you rank title matches higher than body matches. Searching:

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgresql performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Strengths: No extra infrastructure, ACID consistency with your data, joins with other tables in the same query, decent performance up to a few million documents.

Limitations: No typo tolerance, limited language support compared to Elasticsearch, no built-in autocomplete/suggestions, ranking algorithm is basic.

For an internal tool, a blog, or a SaaS app with under a million searchable documents, PostgreSQL full-text search is the right starting point. You can always migrate to a dedicated engine later.

Elasticsearch: The Enterprise Choice

Elasticsearch is built on Apache Lucene and is the industry standard for full-text search at scale. It handles billions of documents, supports complex aggregations, and has a rich query DSL.

The tradeoff: it's a separate distributed system that needs its own cluster, monitoring, and expertise. Elasticsearch clusters require careful capacity planning, and misconfigurations can lead to slow queries or cluster instability.

Strengths: Extremely powerful query language, typo tolerance (fuzzy matching), faceted search, aggregations, scales horizontally to billions of docs, extensive language analyzers.

Weaknesses: Operational complexity (JVM tuning, shard management, rolling upgrades), eventual consistency (there's a refresh interval before new documents are searchable, default 1 second), expensive memory requirements (plan for 50% of your index size as heap).

Use Elasticsearch when: you're indexing more than 10 million documents, you need complex aggregations for analytics, you're building faceted search for an e-commerce catalog, or you need features like percolation queries, geospatial search, or ML-based relevance tuning.

Meilisearch: The Developer-Friendly Alternative

Meilisearch positions itself as the "search Elasticsearch could've been if it were designed for developers." It's a single binary written in Rust, requires near-zero configuration, and provides typo-tolerant search out of the box.

# Start Meilisearch
./meilisearch --master-key="your-secret-key"

# Index documents
curl -X POST 'http://localhost:7700/indexes/articles/documents'   -H 'Content-Type: application/json'   -H 'Authorization: Bearer your-secret-key'   --data-binary @articles.json

# Search (with typo tolerance, instant)
curl 'http://localhost:7700/indexes/articles/search'   -H 'Authorization: Bearer your-secret-key'   --data '{"q": "postgrsql perfomance", "limit": 10}'
  # Note the typos — Meilisearch handles them

Strengths: Typo tolerance by default, instant search (typically under 50ms), dead simple setup, excellent documentation, built-in filtering and faceting, prefix search works great for autocomplete.

Weaknesses: Single-node only (no clustering), the entire index must fit in RAM, limited query expressiveness compared to Elasticsearch, no aggregation pipeline.

Meilisearch is ideal for: user-facing search boxes, product catalogs under 10 million documents, documentation search, and any case where developer experience and time-to-deploy matter more than raw scalability.

Quick Comparison

For a dataset of 500,000 documents with typical web content:

  • PostgreSQL tsvector — search latency ~10-50ms, no typo tolerance, zero additional infrastructure
  • Meilisearch — search latency ~5-20ms, typo tolerant, needs one extra process
  • Elasticsearch — search latency ~5-30ms, typo tolerant, needs a cluster (minimum 3 nodes for production)

For most web applications, start with PostgreSQL. If you need typo tolerance or instant-search UX, try Meilisearch. Reach for Elasticsearch when you need its specific capabilities at scale.