Designing a search feature: patterns, pitfalls and fixes

Designing a search feature

I've watched teams reach for Elasticsearch before their first user has even searched. I've also watched teams stuck with LIKE queries that timeout on production data. Designing a search feature is really about knowing when to stay simple and when to invest in infrastructure—and more importantly, understanding what your users actually need to find.

Over the years shipping e-commerce platforms, ride-hailing systems, and SaaS products, I've learned that search doesn't have a one-size-fits-all answer. It starts at the database level and evolves as your requirements change. Let me walk you through the journey, from your first LIKE clause to a real search engine.

Start in the database — PostgreSQL full-text search handles most early needs

When you're at Day 32 of building something real, you probably don't have a search engine yet. And you shouldn't. PostgreSQL's built-in full-text search is genuinely powerful for early-stage products and solves the majority of search problems without adding operational complexity.

Basic LIKE queries are seductive because they're simple:

SELECT * FROM products WHERE name LIKE '%laptop%'

But they're also slow. They don't understand word boundaries, they can't rank results, and they're case-sensitive by default. Full-text search in PostgreSQL does all three.

Here's what a real implementation looks like. I'll use a product catalog as our example:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(100),
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', name), 'A') ||
    setweight(to_tsvector('english', description), 'B') ||
    setweight(to_tsvector('english', category), 'C')
  ) STORED
);

CREATE INDEX idx_search_vector ON products USING GIN (search_vector);

The setweight function lets you prioritize fields. A match in the product name ranks higher than a match in the description. When you query, you use the @@ operator:

SELECT 
  id, 
  name, 
  ts_rank(search_vector, query) as relevance
FROM products,
plainto_tsquery('english', 'wireless headphones') as query
WHERE search_vector @@ query
ORDER BY relevance DESC
LIMIT 20;

This ranks results by relevance. The GIN index makes it fast. On my last e-commerce project, this handled 50,000 products and thousands of daily searches without any issues.

The trade-off: PostgreSQL full-text search doesn't handle typos, synonym expansion, or faceted navigation. But for your MVP, you don't need those. You need something that works and doesn't add operational burden.

Move to a search engine when needed — Elasticsearch or OpenSearch for relevance tuning, facets and scale

You'll know when you've outgrown PostgreSQL. Your search result relevance becomes a competitive problem. Your product team wants filters and facets. Your data grows beyond what reasonable indexes can handle. That's when you move to a dedicated search engine.

I've used both Elasticsearch and OpenSearch. OpenSearch is the open-source fork of Elasticsearch before the licensing change, and it's becoming my default for new projects because there's less licensing drama. Both work the same way conceptually.

A search engine is really a document store optimized for relevance. Instead of tables and rows, you think in documents and indexes. Here's how you'd model that product catalog:

// Indexing a product
const client = new Client({ node: 'http://localhost:9200' });

await client.index({
  index: 'products',
  id: product.id,
  body: {
    name: product.name,
    description: product.description,
    category: product.category,
    price: product.price,
    rating: product.rating,
    inStock: product.inStock
  }
});

Querying is where the power emerges. You can combine multiple scoring strategies:

const results = await client.search({
  index: 'products',
  body: {
    query: {
      bool: {
        must: [
          {
            multi_match: {
              query: 'wireless headphones',
              fields: ['name^3', 'description', 'category'],
              fuzziness: 'AUTO'
            }
          }
        ],
        filter: [
          { term: { inStock: true } },
          { range: { price: { lte: 200 } } }
        ]
      }
    },
    aggs: {
      categories: {
        terms: { field: 'category.keyword' }
      },
      priceRanges: {
        range: {
          field: 'price',
          ranges: [
            { to: 50 },
            { from: 50, to: 150 },
            { from: 150 }
          ]
        }
      }
    },
    size: 20
  }
});

See what happened? The query found matches, ranked them by relevance (name matches score higher due to the ^3 boost), handled typos with fuzzy matching, filtered by availability and price, and returned facet counts for the UI. This is production search.

The operational cost is real though. You're running another service. You need cluster management, backups, monitoring. On the CRM I built last year, we had three nodes in production. It was worth it because search was core to the product. For a simpler product, it might be overkill.

Keep the index in sync — Change data capture or events update the index after writes

Here's where many teams stumble. Your search index and your database diverge. A product price changes in PostgreSQL but the old price stays in Elasticsearch. Users see stale results. Frustration compounds.

You have two main approaches: polling and events.

Polling (change data capture) means periodically querying your database for changes. It's simple but has inherent latency. Here's a basic implementation:

// Run this on a schedule (every 30 seconds)
async function syncChanges() {
  const lastSync = await getLastSyncTime();
  
  const changedProducts = await db.query(
    'SELECT * FROM products WHERE updated_at > $1',
    [lastSync]
  );
  
  for (const product of changedProducts) {
    await searchClient.index({
      index: 'products',
      id: product.id,
      body: product
    });
  }
  
  await setLastSyncTime(new Date());
}

This works fine if you can tolerate 30-second staleness. Most products can.

Events give you real-time updates. When a product is created or updated, you emit an event that gets consumed by a search index worker:

// After updating a product in your API
await db.update('products', { id, name, price });

// Emit the event immediately
await eventBus.publish('product.updated', {
  productId: id,
  name,
  price,
  timestamp: new Date()
});

// A worker listens and indexes
eventBus.subscribe('product.updated', async (event) => {
  await searchClient.index({
    index: 'products',
    id: event.productId,
    body: event
  });
});

I prefer events when you have the infrastructure for it (Kafka, RabbitMQ, or even Redis streams). The search service stays loosely coupled from your main database. If the search service is slow or down, it doesn't block writes.

One critical detail: handle deletes. When a product is removed, you need to delete it from the search index too, or your search results will surface deleted items.

eventBus.subscribe('product.deleted', async (event) => {
  await searchClient.delete({
    index: 'products',
    id: event.productId
  });
});

Measure relevance — Track searches with no results and refine

Ship your search feature. Now observe what happens in production.

The most actionable metric: searches that return zero results. These are your blind spots. Someone searched for something and found nothing, even though you probably have relevant products. This is where relevance breaks down.

Log every search query and its result count:

const searchResults = await client.search({
  index: 'products',
  body: { query: { /* ... */ } }
});

await db.insert('search_log', {
  query: userQuery,
  resultCount: searchResults.hits.total.value,
  userId: user.id,
  timestamp: new Date()
});

if (searchResults.hits.total.value === 0) {
  console.warn(`Zero results for: "${userQuery}"`);
}

Run a weekly report of zero-result queries. These often reveal missing products, synonyms you haven't handled, or typos in your data. I've found that the top 10 zero-result queries usually point to either real gaps in inventory or simple fixes like adding product aliases.

Track click-through rate too. If users search and then click the first result every time, your ranking is probably good. If they skip to page 2, you might be burying the right answer.

Refine iteratively. Adjust field weights. Add synonyms. Boost certain product categories during seasonal events. Search is never "done"—it's a continuous loop of measurement and improvement.

Common mistakes

• Jumping to Elasticsearch too early. PostgreSQL full-text search is good. Use it first. Elasticsearch adds operational complexity. Migrate only when you have a specific problem it solves.
• Ignoring stale indexes. A search engine that returns deleted products or wrong prices damages trust more than no search at all. Invest in sync reliability from day one.
• Not measuring. You can't improve what you don't measure. Track zero-result queries and act on them. This single metric has more impact than any algorithm.
• Treating search as a one-time feature. Ship a basic version and iterate. Your first search is rarely your best search. Real insight comes from production usage.
• Over-engineering the schema. Start with simple fields. Add complexity only when you need faceted navigation or complex filtering. Premature optimization wastes time.

Key takeaways

• PostgreSQL full-text search with GIN indexes handles 80% of search problems. Start there before adding infrastructure.
• Move to Elasticsearch or OpenSearch when you need advanced relevance tuning, faceted navigation, or reach scale where database indexing becomes a bottleneck.
• Keep your search index in sync using either scheduled polling or event-driven indexing. Events give real-time updates; polling is simpler to reason about.
• Measure relentlessly. Track zero-result queries and click-through rates. These metrics guide your refinements far better than guesses.
• Search is iterative. Your first implementation will be wrong in ways you can't predict. Ship, observe, refine.