Data modeling for search: a hands-on guide for engineers

The search index is not your database

When we think about data modeling in traditional relational databases, we optimize for normalization: eliminate redundancy, enforce constraints, and organize data into separate tables linked by foreign keys. That mindset doesn't translate to search. A data modeling for search: a hands-on guide for engineers starts with a fundamentally different principle: denormalize ruthlessly, duplicate strategically, and structure documents for query patterns, not storage efficiency.

Over the last 29 days, we've built indices, written queries, and debugged analyzers. Now we're stepping back to ask the harder question: how do we shape our documents so they answer the questions our users actually ask? The answer lies in understanding that your search index and your primary database serve different masters. The database wants consistency and normalized structure. The search index wants speed and relevance.

Denormalize — Copy related fields into search documents

In a traditional e-commerce database, a product lives in one table, categories in another, suppliers in a third. When you need product details with category names and supplier information, you join them at query time. This is efficient for storage and maintains a single source of truth.

In Elasticsearch, you'd copy those category and supplier fields directly into the product document. Yes, this duplicates data. Yes, it violates normalization principles. That's the point.

Consider a product search use case. A user types "wireless headphones from Sony" and expects results instantly. Your document might look like this:

{
  "id": "sku-12345",
  "name": "WH-1000XM5 Headphones",
  "brand": "Sony",
  "brand_id": "brand-sony",
  "category_name": "Audio",
  "category_id": "cat-audio",
  "supplier_name": "Sony Electronics",
  "supplier_id": "supplier-sony-elec",
  "price": 399.99,
  "in_stock": true,
  "rating": 4.8,
  "review_count": 2341,
  "tags": ["wireless", "noise-cancelling", "Bluetooth 5.3"]
}

Instead of storing just category_id and fetching the category name from a join, you store both. When a product moves to a new category, you update both the database and the search document. When you query, you have category_name immediately available for filtering, boosting, and display without a second lookup.

The cost is synchronization complexity. You now own the responsibility of keeping denormalized fields in sync. We'll address that later. For now, understand that this is intentional: we're trading update complexity for query speed and simplicity.

One index per use case — Search index separate from analytics index

Teams often assume a single index serves all purposes: product search, analytics, recommendations, admin dashboards. That's where the trouble starts.

A search index optimized for customer queries needs fast retrieval, text analysis, and fuzzy matching. An analytics index needs to count, aggregate, and drill down across many dimensions. An admin index might need raw, unanalyzed data for exact matching and audit trails. These have incompatible requirements.

Let's say you index your products for both search and analytics in a single index. Your search mapping includes:

{
  "mappings": {
    "properties": {
      "name": {
        "type": "text",
        "analyzer": "standard",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      },
      "description": {
        "type": "text",
        "analyzer": "english"
      }
    }
  }
}

This is excellent for search. But when analytics queries need to count products by exact name, they hit the analyzed text field and get unexpected results. Or you disable analysis to satisfy analytics, and search becomes unreliable.

The solution: maintain separate indices. A products_search index optimized for relevance, with text fields, synonyms, and fuzzy matching. A products_analytics index with keyword fields, numeric precision, and rich aggregation support. Both read from the same database source, but each is shaped for its purpose.

In practice, this means your indexing pipeline splits at the source. When a product updates in your primary database, you write to both indices through parallel Kafka topics or background jobs. The operational overhead is real but manageable, and it eliminates months of friction from conflicting requirements.

Keep documents small — Index what you search and display

Elasticsearch charges by storage and compute. Large documents consume more disk, take longer to retrieve, and slow down aggregations. Every field you index is a field you maintain, version, and pay for in cluster overhead.

Many teams index everything: full product descriptions, complete user profiles, entire order histories. Then they're surprised when storage balloons and query latency creeps upward.

A practical rule: index fields you either search on or display in results. Anything else belongs in your database or a secondary service.

For a product search index, you might keep:

• id, sku, name — for display and uniqueness
• category, brand — for filtering
• price — for sorting and range queries
• rating — for relevance signals
• tags — for search and faceting

You don't need the full 5,000-character marketing description, detailed manufacturing specs, or the complete supplier contract history. Those live in your database. If a user needs them, you fetch the document ID from search results and query your database for details.

This keeps your index lean. A 1 MB index containing essential fields queries faster and stores cheaper than a 10 MB index with everything ever written about a product.

{
  "id": "sku-12345",
  "name": "WH-1000XM5 Headphones",
  "brand": "Sony",
  "category": "Audio",
  "price": 399.99,
  "rating": 4.8,
  "tags": ["wireless", "noise-cancelling"]
}

When a customer clicks on a result, you have the id. Your frontend requests full details from your API, which retrieves from the database. Search returns fast. Details are rich. Everyone's happy.

Sync from the source of truth — Database stays primary

Your database is the source of truth. Always. The search index is a view, a projection, a read replica optimized for a specific workload. Never let the index become the authority.

This means your update pipeline flows in one direction: database changes first, then those changes propagate to search indices. A product price changes in PostgreSQL. Your change event system (Kafka, CDC, or scheduled jobs) picks it up and updates Elasticsearch.

For a typical architecture, you might use Kafka:

{
  "event": "product.updated",
  "product_id": "sku-12345",
  "timestamp": "2025-01-30T14:23:00Z",
  "changes": {
    "price": 399.99,
    "in_stock": true,
    "rating": 4.8
  }
}

A consumer listens to this topic and updates your Elasticsearch documents in near-real time. A user searches and sees current pricing. When analytics queries the database later, numbers match search display.

The risk is consistency windows. Between the time a product updates in the database and when it reaches search, there's a lag. For most use cases (hours to seconds), this is acceptable. If you need absolute consistency, you need to accept higher latency or more complex eventual consistency patterns.

Never update the search index directly as a "shortcut" to update faster. That creates a second source of truth, and you'll spend months chasing sync bugs. Let the database be primary, and accept the small consistency window that comes with asynchronous indexing.

Common mistakes

• Indexing normalized relationships: Storing only IDs without denormalizing names or categories forces you to either join in the application (slow) or make secondary queries (also slow). Denormalize from the start.
• Treating the index like a database: Writing directly to Elasticsearch, then syncing back to the database creates two sources of truth. The mental overhead isn't worth the marginal speed gain.
• Inflating document size: Including every field "just in case" bloats storage and query time. Index for known access patterns; fetch details from the database.
• One index for everything: Conflicting requirements (search speed vs. analytics precision) force you to compromise on both. Maintain separate indices for separate purposes.
• Ignoring update frequency: If a field changes constantly but you're syncing hourly, your index will feel stale. Match sync frequency to update velocity.

Key takeaways

• Design search documents for query patterns and display, not database normalization.
• Denormalize strategically: copy fields you'll search or filter into the document to avoid joins.
• Maintain separate indices for separate use cases (search, analytics, admin) to optimize each independently.
• Index only what you search or display; keep documents small and fast.
• The database is your source of truth. The search index is a view. Sync flows one direction: database → index.
• Accept small consistency windows. The speed gain far outweighs eventual consistency in almost every production scenario.