Full-Text Search Tools: Elasticsearch, Meilisearch, Typesense, SQLite FTS5, and PostgreSQL

Full-Text Search Tools: Elasticsearch, Meilisearch, Typesense, SQLite FTS5, and PostgreSQL

Adding search to your application starts with a simple question: do you need a dedicated search engine, or can your existing database handle it? For many applications, PostgreSQL or SQLite's built-in full-text search is enough. For others, a purpose-built search engine like Meilisearch or Elasticsearch is the right call. This guide covers all five options with honest tradeoffs, setup instructions, and working code.

Quick Comparison

PostgreSQL Full-Text Search

If you're already using PostgreSQL, start here. PostgreSQL's full-text search is surprisingly capable and eliminates the need to sync data to a separate search engine.

Setup

-- Add a tsvector column for search
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Populate it from your text columns
UPDATE articles SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B') ||
  setweight(to_tsvector('english', coalesce(tags::text, '')), 'C');

-- Create a GIN index for fast lookups
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Keep it updated automatically
CREATE OR REPLACE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.tags::text, '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Querying

-- Basic search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'docker networking') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- Phrase search
SELECT title
FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'container orchestration');

-- Highlight matching terms
SELECT title,
  ts_headline('english', body, plainto_tsquery('english', 'kubernetes'),
    'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
  ) AS snippet
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'kubernetes')
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'kubernetes')) DESC;

Application Code

// Using Prisma with raw SQL for full-text search
async function searchArticles(query: string, limit = 20) {
  return prisma.$queryRaw`
    SELECT id, title,
      ts_headline('english', body, plainto_tsquery('english', ${query}),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=35') AS snippet,
      ts_rank(search_vector, plainto_tsquery('english', ${query})) AS rank
    FROM articles
    WHERE search_vector @@ plainto_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT ${limit}
  `;
}

When PostgreSQL FTS Is Enough

• Your dataset is under a few million rows
• You don't need typo tolerance
• Search is a secondary feature, not the core product
• You want to avoid syncing data to a separate system

When to Upgrade

• You need typo tolerance ("devlopment" should match "development")
• You need instant search (search-as-you-type)
• You need faceted filtering (filter by category, date range, price)
• Relevance quality needs to be tuned beyond basic ranking

SQLite FTS5

If your application uses SQLite, FTS5 is a virtual table module that provides fast full-text search. It's ideal for desktop apps, mobile apps, CLI tools, and small web applications.

Setup

-- Create an FTS5 virtual table
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  body,
  tags,
  content='articles',     -- sync with the articles table
  content_rowid='id'
);

-- Populate from existing data
INSERT INTO articles_fts(rowid, title, body, tags)
  SELECT id, title, body, tags FROM articles;

-- Keep it in sync with triggers
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body, tags)
    VALUES (new.id, new.title, new.body, new.tags);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body, tags)
    VALUES ('delete', old.id, old.title, old.body, old.tags);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body, tags)
    VALUES ('delete', old.id, old.title, old.body, old.tags);
  INSERT INTO articles_fts(rowid, title, body, tags)
    VALUES (new.id, new.title, new.body, new.tags);
END;

Querying

-- Basic search with BM25 ranking
SELECT articles.*, rank
FROM articles_fts
JOIN articles ON articles.id = articles_fts.rowid
WHERE articles_fts MATCH 'docker AND networking'
ORDER BY rank;

-- Column-weighted search (title matches rank higher)
SELECT *, bm25(articles_fts, 10.0, 1.0, 5.0) AS rank
FROM articles_fts
WHERE articles_fts MATCH 'kubernetes'
ORDER BY rank;

-- Snippet extraction
SELECT highlight(articles_fts, 1, '<mark>', '</mark>') AS snippet
FROM articles_fts
WHERE articles_fts MATCH 'deployment strategies';

// Using better-sqlite3
import Database from "better-sqlite3";

const db = new Database("app.db");

function search(query: string, limit = 20) {
  return db.prepare(`
    SELECT articles.*, bm25(articles_fts, 10.0, 1.0, 5.0) AS rank,
      snippet(articles_fts, 1, '<mark>', '</mark>', '...', 32) AS snippet
    FROM articles_fts
    JOIN articles ON articles.id = articles_fts.rowid
    WHERE articles_fts MATCH ?
    ORDER BY rank
    LIMIT ?
  `).all(query, limit);
}

Meilisearch

Meilisearch is a search engine designed for user-facing search with instant results, typo tolerance, and faceted filtering out of the box. It's the best option when you need a "search bar" experience with minimal configuration.

Local Dev Setup

services:
  meilisearch:
    image: getmeili/meilisearch:v1.7
    ports:
      - "7700:7700"
    environment:
      MEILI_MASTER_KEY: "dev-master-key"
      MEILI_ENV: development
    volumes:
      - meili_data:/meili_data

volumes:
  meili_data:

Indexing Documents

import { MeiliSearch } from "meilisearch";

const client = new MeiliSearch({
  host: "http://localhost:7700",
  apiKey: "dev-master-key",
});

// Create index and configure it
const index = client.index("articles");

await index.updateSettings({
  searchableAttributes: ["title", "body", "tags"],
  filterableAttributes: ["category", "publishedAt", "author"],
  sortableAttributes: ["publishedAt", "title"],
  rankingRules: [
    "words",
    "typo",
    "proximity",
    "attribute",
    "sort",
    "exactness",
  ],
});

// Index documents
await index.addDocuments([
  {
    id: 1,
    title: "Getting Started with Docker",
    body: "Docker containers package your application...",
    category: "DevOps",
    tags: ["docker", "containers"],
    author: "Alice",
    publishedAt: "2026-01-15",
  },
  // ... more documents
]);

Searching

// Basic search with typo tolerance
const results = await index.search("dokcer networking", {
  limit: 20,
  attributesToHighlight: ["title", "body"],
  highlightPreTag: "<mark>",
  highlightPostTag: "</mark>",
});
// Finds "Docker networking" despite the typo

// Filtered search with facets
const results = await index.search("deployment", {
  filter: ['category = "DevOps"', "publishedAt > 2026-01-01"],
  facets: ["category", "author"],
  sort: ["publishedAt:desc"],
});

console.log(results.facetDistribution);
// { category: { DevOps: 12, Backend: 5 }, author: { Alice: 8, Bob: 9 } }

When to Pick Meilisearch

• You need instant search with typo tolerance for a user-facing search bar
• You want great search quality with minimal configuration
• Your dataset is up to a few million documents
• You want faceted filtering and sorting out of the box

Typesense

Typesense is similar to Meilisearch in goals (instant, typo-tolerant search) but takes a schema-first approach. You define your document structure upfront, which catches data issues early.

Setup and Schema Definition

services:
  typesense:
    image: typesense/typesense:26.0
    ports:
      - "8108:8108"
    environment:
      TYPESENSE_API_KEY: dev-api-key
      TYPESENSE_DATA_DIR: /data
    volumes:
      - typesense_data:/data

volumes:
  typesense_data:

import Typesense from "typesense";

const client = new Typesense.Client({
  nodes: [{ host: "localhost", port: 8108, protocol: "http" }],
  apiKey: "dev-api-key",
});

// Schema-first: define your collection structure
await client.collections().create({
  name: "articles",
  fields: [
    { name: "title", type: "string" },
    { name: "body", type: "string" },
    { name: "category", type: "string", facet: true },
    { name: "tags", type: "string[]", facet: true },
    { name: "author", type: "string", facet: true },
    { name: "published_at", type: "int64", sort: true },
  ],
  default_sorting_field: "published_at",
});

// Index documents
await client.collections("articles").documents().import(documents, {
  action: "upsert",
});

Searching

const results = await client.collections("articles").documents().search({
  q: "kubernetes deployment",
  query_by: "title,body,tags",
  query_by_weights: "3,1,2",
  filter_by: "category:=DevOps",
  sort_by: "published_at:desc",
  facet_by: "category,author",
  highlight_full_fields: "title",
  per_page: 20,
});

Meilisearch vs Typesense

Both are excellent for user-facing search. The differences:

• Schema: Typesense requires upfront schema definition. Meilisearch infers schemas from documents.
• Geo search: Both support it. Typesense's implementation is more mature.
• Analytics: Typesense has built-in search analytics. Meilisearch requires external tracking.
• Clustering: Typesense supports multi-node clusters (via Raft). Meilisearch is single-node (multi-node is experimental).
• Community: Both have active communities. Meilisearch has a larger GitHub presence.

For most use cases, either will serve you well. Try both and pick the one whose API feels better for your team.

Elasticsearch

Elasticsearch is the most powerful and most complex option. It's a distributed search and analytics engine built on Apache Lucene. Use it when you need advanced relevance tuning, complex aggregations, or petabyte-scale search.

Local Dev Setup

services:
  elasticsearch:
    image: docker.elastic.co/elasticsearch/elasticsearch:8.13.0
    ports:
      - "9200:9200"
    environment:
      - discovery.type=single-node
      - xpack.security.enabled=false
      - "ES_JAVA_OPTS=-Xms512m -Xmx512m"
    volumes:
      - es_data:/usr/share/elasticsearch/data

volumes:
  es_data:

Indexing and Mapping

import { Client } from "@elastic/elasticsearch";

const client = new Client({ node: "http://localhost:9200" });

// Define mapping (optional but recommended)
await client.indices.create({
  index: "articles",
  body: {
    mappings: {
      properties: {
        title: {
          type: "text",
          analyzer: "english",
          boost: 2.0,
        },
        body: { type: "text", analyzer: "english" },
        category: { type: "keyword" },
        tags: { type: "keyword" },
        author: { type: "keyword" },
        publishedAt: { type: "date" },
      },
    },
  },
});

// Index a document
await client.index({
  index: "articles",
  id: "1",
  body: {
    title: "Getting Started with Docker",
    body: "Docker containers package your application...",
    category: "DevOps",
    tags: ["docker", "containers"],
    author: "Alice",
    publishedAt: "2026-01-15",
  },
});

Advanced Querying

// Multi-match with boosting and highlighting
const results = await client.search({
  index: "articles",
  body: {
    query: {
      bool: {
        must: {
          multi_match: {
            query: "docker networking best practices",
            fields: ["title^3", "body", "tags^2"],
            type: "best_fields",
            fuzziness: "AUTO",
          },
        },
        filter: [
          { term: { category: "DevOps" } },
          { range: { publishedAt: { gte: "2026-01-01" } } },
        ],
      },
    },
    highlight: {
      fields: {
        title: {},
        body: { fragment_size: 150, number_of_fragments: 3 },
      },
    },
    aggs: {
      categories: { terms: { field: "category" } },
      authors: { terms: { field: "author" } },
    },
    size: 20,
  },
});

When to Pick Elasticsearch

• You need complex relevance tuning (custom scoring, function scores, boosting)
• Your dataset is very large (hundreds of millions of documents)
• You need aggregations and analytics alongside search
• You need multi-language support with language-specific analyzers
• You're also using it for log aggregation (ELK stack)

When to Avoid Elasticsearch

• Your dataset is small (under a few million documents)
• You want simple setup and low operational overhead
• You don't need advanced relevance tuning
• You're looking for a search bar, not a search platform

Decision Framework

Start with your existing database (PostgreSQL FTS or SQLite FTS5) if search is a secondary feature and you can live without typo tolerance. This avoids the complexity of syncing data to a separate system.

Pick Meilisearch or Typesense when you need a user-facing search bar with typo tolerance, instant results, and faceted filtering. These are the sweet spot for most applications. They're easy to set up, fast, and deliver great search quality with minimal tuning.

Pick Elasticsearch when you need the full power of a search platform: complex scoring, heavy aggregations, multi-language support, or petabyte-scale data. Be prepared for significant operational investment.

The most common mistake is jumping straight to Elasticsearch when Meilisearch or even PostgreSQL FTS would have been sufficient. Start simple, measure, and upgrade when you hit a concrete limitation.