Implementing Search: PostgreSQL Full-Text vs Algolia vs Meilisearch

Source: Dev.to

Search Is Harder Than It Looks

LIKE '%query%' works until:

• Your table has 100 k 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 (Prisma + TypeScript)
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 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, 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

Search integration with PostgreSQL FTS and Meilisearch adapters: Whoff Agents AI SaaS Starter Kit.