Implementing Search: PostgreSQL Full-Text vs Algolia vs Meilisearch

#database #backend #javascript #tutorial

Search Is Harder Than It Looks

LIKE '%query%' works until:

Your table has 100k rows (Seq Scan, slow)
Users misspell words (no fuzzy matching)
Users search in different languages
Users expect relevance ranking

Then you need a real search solution.

Option 1: PostgreSQL Full-Text Search

Good enough for most apps. No extra infrastructure.

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

-- Populate it
UPDATE posts SET search_vector = 
  to_tsvector('english', 
    coalesce(title, '') || ' ' || coalesce(content, '') || ' ' || coalesce(tags::text, '')
  );

-- Keep it updated automatically
CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    coalesce(NEW.title, '') || ' ' || coalesce(NEW.content, '')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

-- GIN index for fast search
CREATE INDEX posts_search_idx ON posts USING GIN(search_vector);

// Search with ranking
async function searchPosts(query: string) {
  return prisma.$queryRaw`
    SELECT
      id,
      title,
      ts_rank(search_vector, plainto_tsquery('english', ${query})) AS rank,
      ts_headline('english', content, plainto_tsquery('english', ${query}),
        'MaxWords=50, MinWords=20'
      ) AS excerpt
    FROM posts
    WHERE search_vector @@ plainto_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT 20
  `;
}

Good: Zero extra infrastructure, transactionally consistent.
Limitations: No typo tolerance, English-centric, limited relevance tuning.

Option 2: Algolia

Managed search. Best-in-class UX.

npm install algoliasearch

import algoliasearch from 'algoliasearch';

const client = algoliasearch(process.env.ALGOLIA_APP_ID!, process.env.ALGOLIA_API_KEY!);
const index = client.initIndex('posts');

// Index a document
await index.saveObject({
  objectID: post.id,
  title: post.title,
  content: post.content.slice(0, 10000), // Algolia has size limits
  author: post.author.name,
  tags: post.tags,
  publishedAt: post.publishedAt.getTime(),
});

// Search
const { hits } = await index.search(query, {
  attributesToRetrieve: ['title', 'author', 'tags'],
  attributesToHighlight: ['title', 'content'],
  hitsPerPage: 20,
  typoTolerance: true, // finds 'recat' → React
});

Good: Incredible speed (<10ms), typo tolerance, great analytics dashboard.
Cost: Free tier: 10k searches/month, 10k records. Gets expensive fast.

Option 3: Meilisearch

Open source Algolia alternative. Self-host or use Meilisearch Cloud.

# Self-host with Docker
docker run -d -p 7700:7700 -v meilidata:/meili_data getmeili/meilisearch

npm install meilisearch

import { MeiliSearch } from 'meilisearch';

const client = new MeiliSearch({ host: 'http://localhost:7700', apiKey: process.env.MEILI_API_KEY });
const index = client.index('posts');

// Configure searchable attributes and ranking
await index.updateSettings({
  searchableAttributes: ['title', 'content', 'tags'],
  rankingRules: [
    'words', 'typo', 'proximity', 'attribute', 'sort', 'exactness'
  ],
  typoTolerance: {
    enabled: true,
    minWordSizeForTypos: { oneTypo: 5, twoTypos: 9 },
  },
});

// Add documents
await index.addDocuments(posts.map(p => ({ id: p.id, ...p })));

// Search
const results = await index.search(query, {
  limit: 20,
  attributesToHighlight: ['title', 'content'],
  highlightPreTag: '<mark>',
  highlightPostTag: '</mark>',
});

Good: Typo tolerance, fast, open source, predictable costs.
Limitations: Less mature than Algolia, smaller ecosystem.

Keeping Search in Sync

// After any post mutation, sync to search index
async function createPost(data: PostInput) {
  const post = await db.posts.create({ data });

  // Async index update (don't block response)
  setImmediate(async () => {
    await searchIndex.saveObject({
      objectID: post.id,
      title: post.title,
      content: post.content,
    });
  });

  return post;
}

// Or use a background job for reliability
await queue.add('sync-search', { postId: post.id, operation: 'upsert' });

Decision Guide

Scenario	Recommendation
< 100k records, basic search	PostgreSQL FTS
Need typo tolerance	Meilisearch (self-hosted)
Need best UX, can pay	Algolia
Open source, predictable cost	Meilisearch Cloud
Multi-language content	Elasticsearch or Meilisearch