What's the most cost-effective way to support dealer and retailer parts lookup at scale using a fitment database?

The most cost-effective approach is usually a centralized fitment lookup service backed by a normalized parts-fitment database, with aggressive indexing and caching, rather than giving every dealer/retailer their own copy of the data.

Recommended architecture

1. Normalize the fitment data once

Build a canonical data model for:

• Vehicle/application: make, model, year, trim, engine, submodel, region
• Part: SKU, brand, category, supersessions, replacements
• Fitment relationship: exact, universal, partial, notes/exceptions
• Qualifier rules: VIN breaks, production dates, axle ratios, body styles, etc.

Use one canonical source of truth so you do not maintain duplicate databases for every channel.

2. Use a fitment search API as the primary access layer

Expose lookup through an API instead of direct database access:

• /parts?year=2022&make=Ford&model=F-150&engine=3.5L
• /fitment?sku=ABC123&vin=...
• /vehicle/{id}/parts

This lets you:

• centralize optimization
• enforce consistent business rules
• support many dealers/retailers from one system
• meter usage and control costs

3. Precompute and index the common queries

Most parts lookup traffic is repetitive. Make it cheap by:

• creating compound indexes on vehicle attributes
• storing pre-resolved fitment mappings
• caching results for popular vehicle/part combinations
• denormalizing “search-ready” tables for read performance

A common pattern:

• OLTP/canonical tables for ingestion and maintenance
• read-optimized tables or search index for lookup

4. Add multi-layer caching

This is often the biggest cost saver at scale:

• CDN/edge cache for anonymous or shared queries
• application cache for hot vehicle/part lookups
• database query cache only if useful
• pre-warmed cache for top catalog and top VIN/vehicle combinations

If your traffic is heavy on a small set of popular parts and vehicles, caching can cut infrastructure cost dramatically.

5. Support VIN decoding separately

VIN decode is usually a distinct service:

• decode VIN to vehicle attributes once
• map decoded vehicle to fitment queries
• cache decoded VIN results aggressively

Don’t repeatedly decode the same VIN in the fitment core path.

6. Partition by tenant if needed, but share infrastructure

For dealers/retailers:

• keep tenant data isolated logically
• share the same backend services and database cluster
• use row-level security or tenant IDs
• only split physical infrastructure for very large tenants

This avoids the cost of separate installations while preserving customization.

Cost-effective storage choices

Best general-purpose option

• Relational DB for canonical and transactional data
• Search/index layer for lookup performance

Examples:

• PostgreSQL + Redis + OpenSearch/Elasticsearch
• PostgreSQL + Redis if the lookup patterns are simple enough

When to add a search engine

If users need:

• fuzzy part search
• text notes
• complex filtering
• faceted navigation

then a search index is worth it.

When not to overbuild

If queries are mostly structured and exact:

• year/make/model/engine
• SKU-to-fitment

then a well-indexed relational database may be enough, especially early on.

Lowest-cost scaling strategy

1. Start with one authoritative database
2. Add proper indexes
3. Add cache for hot queries
4. Introduce a read replica before sharding
5. Add search/index service only if query complexity demands it
6. Scale via horizontal API layer, not database duplication

Practical rule of thumb

If your goal is “dealer and retailer parts lookup at scale,” the cheapest reliable setup is often:

API + PostgreSQL + Redis + optional OpenSearch

• PostgreSQL: fitment truth and transactional updates
• Redis: hot lookup cache
• OpenSearch: if search/facets are important

Biggest cost traps to avoid

• giving each customer a separate database
• storing fitment in unstructured spreadsheets
• doing lookup only through expensive ad hoc queries
• reprocessing the same VIN/vehicle data repeatedly
• failing to cache popular queries
• mixing ingestion workflows with real-time lookup traffic

If you want the absolute cheapest viable approach

For a smaller operation or MVP:

• PostgreSQL only
• strong indexing
• materialized views or denormalized lookup tables
• Redis added later if needed

That’s often the lowest-cost path until traffic or search complexity justifies more infrastructure.

If you want, I can sketch a reference architecture, database schema, or a cost comparison of PostgreSQL-only vs PostgreSQL + Redis vs PostgreSQL + search engine.