The database design choices that work well at 50,000 products start creating real problems at 500,000, and those problems are not just slow queries. They manifest as delayed catalog syncs, unreliable search results, attributes that are accurate in the source system but stale by the time they reach the storefront, and update operations that take so long they cannot run during business hours without affecting site performance.
Most of these problems trace back to architectural assumptions made when the catalog was small. Those assumptions are not wrong at small scale. They become wrong as the catalog grows. Understanding which assumptions are load-bearing for your current architecture helps you identify what actually needs to change versus what is just noise from a system running under more stress than it was designed for.
Where the Common Architectures Break Down
The most common catalog data architecture at mid-market scale is a relational database with product records in a primary table, attribute values in an EAV (entity-attribute-value) schema, and a search index built from nightly batch exports. This works reasonably well up to a few hundred thousand products, assuming your attribute schema is stable and your category structure does not change frequently.
The EAV schema breaks down at scale for a specific reason: it transforms simple attribute reads into multi-table joins that grow expensive as the number of distinct attribute names expands. A query that retrieves all products in a category with their full attribute sets is doing one join per attribute type, and a large catalog accumulates hundreds of attribute names over time. What started as 20 attribute types becomes 200 as new product types are added and old attribute names are never cleaned up.
The nightly batch export breaks down for a different reason: the window available for batch processing shrinks as the catalog grows. At 50,000 products, a nightly export that takes 45 minutes is fine. At 500,000 products with complex attribute joins, that same export can take four or five hours, and any run that extends into business hours causes search index staleness that is directly visible to shoppers.
The Case for Denormalized Read Models
One architectural shift that helps at large scale is separating your catalog's write model from its read model. The write model, the system of record for product data, can stay normalized because write operations are relatively infrequent and can afford the join overhead. The read model, the data structure that search and storefront queries actually hit, should be denormalized.
A denormalized product document for search looks like a single JSON object containing all of a product's attributes, its category path, its current price, and its inventory status. No joins required to read it. The tradeoff is that you need a synchronization layer that propagates changes from the normalized write model to the denormalized read model.
That synchronization layer introduces latency and complexity, which is the honest cost of this architecture. A product update in the write model needs to trigger a document rebuild in the read model. If you are doing this in batch, you are back to a batch-window problem. If you are doing it in real time via event streaming, you have a new operational complexity to manage: ensuring the event pipeline is reliable, that document builds complete in reasonable time, and that partial failures do not result in stale read-model documents without alerting.
Neither approach is universally correct. The batch model is simpler to operate and sufficient for catalogs where attribute updates are infrequent. The event-driven model is appropriate when your catalog has active price changes, frequent inventory updates, or when you need search results to reflect catalog changes within minutes rather than overnight.
Partitioning and Shard Strategy
At 500,000 or more SKUs, a single-node search index starts showing latency issues under concurrent query load. The standard response is sharding the index across multiple nodes. The key decision is your partition key: what dimension you use to distribute records across shards.
Category-based partitioning keeps related products on the same shard, which is efficient for category-scoped queries but creates hot-spot problems if your traffic is concentrated in a small number of categories. A sporting goods retailer whose search traffic is 60% in footwear will hammer the footwear shard while other shards sit underutilized.
Hash-based partitioning distributes load more evenly but means that category-scoped queries must fan out to all shards and merge results, adding latency for those query types. The right approach depends on your query patterns. Most large catalogs end up using a hybrid: category-based partitioning for the highest-traffic categories, with a separate hash-partitioned general index for long-tail queries.
This is an area where we want to be direct about the limits of general guidance. The right partitioning strategy depends heavily on your specific catalog structure, your query distribution, and your infrastructure constraints. What we can say is that any partitioning strategy you choose should be validated against your actual query log, not against a synthetic benchmark. The query patterns that matter for your catalog may look very different from industry-generic assumptions.
Update Throughput and Batch Window Management
A catalog that receives 50,000 attribute updates per day has a very different infrastructure requirement than one that receives 5,000. High-velocity catalogs, those with frequent price changes, active promotional pricing, and regular supplier feed updates, need an update pipeline that can process changes faster than they arrive.
The common failure mode is that the update pipeline processes changes at roughly the rate they arrive during normal periods, but falls behind during high-volume periods such as the days before a major sales event when suppliers push updated pricing across large portions of the catalog. The backlog accumulates. Products appear in search with stale prices. The backlog does not clear until volume returns to normal, which may be days later.
Designing for peak throughput rather than average throughput is the standard advice, but it requires knowing what your actual peak looks like and sizing your update infrastructure to handle it with headroom. Monitoring update lag as a key operational metric, separate from your usual infrastructure metrics, is necessary to catch backlog accumulation before it affects storefront data quality.
Deduplication at Scale
A problem that does not get enough attention in catalog architecture discussions is SKU proliferation. Large catalogs accumulate duplicate product records over time. The same physical product arrives from different suppliers under different SKU identifiers. A product is entered manually by one buyer and imported via a feed by another, creating two records for the same item. Seasonal variants are added without checking whether an equivalent record already exists.
Duplicate records cause several specific problems. They fragment review signal and purchase history across two records, so neither looks as strong as the merged record would. They can appear side by side in search results, looking like a site with poor curation. They create inventory accounting confusion if stock for the same product is tracked under two separate records.
Deduplication at scale is harder than it sounds because product identity is not always clean. Two records for "Men's Merino Wool Quarter-Zip, Size Large, Charcoal" from different suppliers may refer to the same physical product or to two similar but distinct products. The deduplication logic needs to handle approximate string matching, attribute similarity scoring, and in some cases image similarity, without generating so many false positives that it merges records that should stay separate.
For most large catalogs, exact-match deduplication on structured attributes (GTIN, UPC, manufacturer part number) handles a large share of obvious duplicates. The harder cases, those without shared structured identifiers, require more sophisticated matching and some mechanism for human review of ambiguous merge candidates. Getting this right is a combination of data engineering and editorial process, not just a technical problem.
The Infrastructure Is Upstream of the Data Quality Problem
The point of all this is that many catalog data quality problems that look like process failures are actually infrastructure failures. When your update pipeline cannot keep pace with incoming changes, the data quality problem is not that your team is not diligent enough. It is that the system cannot process changes as fast as they arrive. When your search results show stale attributes, the problem may not be that nobody updated the product record. It may be that the update propagation system has a lag that makes timely updates invisible to search.
Before investing heavily in merchandising process improvements, it is worth auditing whether the infrastructure is actually capable of reflecting those improvements in the storefront within a reasonable window. Process improvements that do not reach the customer are wasted effort. The infrastructure audit tells you where the real constraint is.